Wednesday, December 23, 2009

Using Flashback Archive to avoid ORA-01555

SQL> create table test as select object_id from dba_objects;

Table created.

SQL> create table fla_test as select object_id from dba_objects;

Table created.

SQL> create flashback archive default fla tablespace users quota 1G retention 1 year;

Flashback archive created.

SQL> alter table a.fla_test flashback archive fla;

Table altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
5936742

SQL> begin
for i in 1..10000 loop
update test set object_id = object_id where object_id = 20;
update fla_test set object_id = object_id where object_id = 20;
commit;
end loop;
end;
/ 2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

SQL> select object_id from test as of scn 5936742 where object_id = 20;
select object_id from test as of scn 5936742 where object_id = 20
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 6 with name
"_SYSSMU6_2443381498$" too small


SQL> select object_id from fla_test as of scn 5936742 where object_id = 20;

OBJECT_ID
----------
20

Tuesday, December 8, 2009

asmcmd CP command fails

ASMCMD> cp DWH* +ASM_ORADATA44/T01DWH/DATAFILE
ASMCMD-08004: ASM file path 'DWH*' contains an invalid alias name
ASMCMD-08014: can not open file->'DWH*'


ASMCMD> cp DWH_ACT_CLIENTS_F_PAR2003.2775.704056207 +ASM_ORADATA44/T01DWH/DATAFILE
copying +ASM_ORADATA41/S02DWH/DATAFILE/DWH_ACT_CLIENTS_F_PAR2003.2775.704056207 -> +ASM_ORADATA44/T01DWH/DATAFILE/DWH_ACT_CLIENTS_F_PAR2003.2775.704056207
ASMCMD-08016: copy source->'+ASM_ORADATA41/S02DWH/DATAFILE/DWH_ACT_CLIENTS_F_PAR2003.2775.704056207' and target->'+ASM_ORADATA44/T01DWH/DATAFILE/DWH_ACT_CLIENTS_F_PAR2003.2775.704056207' failed
ORA-19505: failed to identify file "+ASM_ORADATA44/T01DWH/DATAFILE/DWH_ACT_CLIENTS_F_PAR2003.2775.704056207"
ORA-17502: ksfdcre:4 Failed to create file +ASM_ORADATA44/T01DWH/DATAFILE/DWH_ACT_CLIENTS_F_PAR2003.2775.704056207
ORA-15046: ASM file name '+ASM_ORADATA44/T01DWH/DATAFILE/DWH_ACT_CLIENTS_F_PAR2003.2775.704056207' is not in single-file creation form
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 258
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)