Hello, it’s happened that my college instead of disassociating flashback archive disabled it ( so all archive was lost ) and I had to restore it, here is what I have done to fix it:
1) restore controlfile:
1 2 3 4 5 6 | SET DBID xxxxxxxxrun { allocate channel t1 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxxx.xxxx.local,NB_ORA_CLIENT=xxxxx.00.xxxx.local)'; restore controlfile from autobackup;} |
2) find out tablespaces that must be restored:
1 2 3 4 5 6 7 | select distinct tablespace_name from dba_segments where owner in ('SYS', 'SYSTEM'); SYSAUXUNDOTBS1USERSSYSTEMUNDOTBS2 |
3) some fixes
1 2 | SQL> alter database flashback off;SQL> alter database disable BLOCK CHANGE TRACKING; |
4) Generate restore list ( list of tablespaces with flashback archives )
1 2 3 | select listagg (name,',') WITHIN GROUP (order by name) from v$tablespace where name like '%ARCH%' or name in ('SYSAUX','UNDOTBS1','USERS','SYSTEM','UNDOTBS2','SLOW_FRA');xxxx_ARCH,xxxx_ARCH_02,xxxx_ARCH_03,xxxx_ARCH_04_BIG,xxxx_ARCH_05,xxxx_ARCH_EMERGENCY,SYSAUX,SYSTEM,UNDOTBS1,UNDOTBS2,USERS |
5)Generate skip list
1 2 3 4 | select listagg (name,',') WITHIN GROUP (order by name) from v$tablespace where name not like 'PRE_DM_%' and name not in ('xxxx_ARCH','xxxx_ARCH_02','xxxx_ARCH_03','xxxx_ARCH_04_BIG','xxxx_ARCH_05','xxxx_ARCH_EMERGENCY','SYSAUX','SYSTEM','UNDOTBS1','UNDOTBS2','USERS');AUD_EVT_01,AUD_EVT_02,AUD_EVT_03,AUD_EVT_04,AUD_EVT_05,AUD_EVT_06,EGS_DB,EGS_SIA_DB,xxxx,xxxx_ADM,xxxx_CPP,xxxx_PDS,xxxx_SUPPORT,xxxx_TECH,xxxx_TEMP,SIA_DB,SIA_TEMP,SLOW_DATA,SLOW_FDA,TEMP |
6) rman script look that way
1 2 3 4 5 6 7 8 9 10 11 12 | run { allocate channel t1 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxxx.xxx.local,NB_ORA_CLIENT=xxxxxx.xx.local)'; allocate channel t2 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxx.xxx.local,NB_ORA_CLIENT=xxxxx.xx.local)'; allocate channel t3 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxx.xxx.local,NB_ORA_CLIENT=xxxxx.xx.local)'; allocate channel t4 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxx.xxx.local,NB_ORA_CLIENT=xxxx.xx.local)';set until scn 1142308665621;set newname for datafile 39 to '+DATA';restore tablespace xxxx_ARCH,xxxx_ARCH_02,xxxx_ARCH_03,xxxx_ARCH_04_BIG,xxxx_ARCH_05,xxxx_ARCH_EMERGENCY,SYSAUX,SYSTEM,UNDOTBS1,UNDOTBS2,USERS,SLOW_FDA;switch datafile all;recover database skip forever tablespace AUD_EVT_01,AUD_EVT_02,AUD_EVT_03,AUD_EVT_04,AUD_EVT_05,AUD_EVT_06,xxx_DB,xxx_xxxxx_DB,xxxx,xxxx_ADM,xxxx_CPP,xxxx_PDS,xxxx_SUPPORT,xxxx_TECH,xxxx_TEMP,xxx_DB,xxx_TEMP,SLOW_DATA;} |
7) alter database backup controlfile to trace as ‘/home/oracle/control.ctl’;
then I’ve removed all non restored datafiles and got this script:
ALTER TABLESPACE TEMP ADD TEMPFILE ;
ALTER TABLESPACE SIA_TEMP ADD TEMPFILE ;
ALTER TABLESPACE xxxx_TEMP ADD TEMPFILE ;
8) after that database is opened and query work like a charm:
1 2 3 4 5 6 7 | create table xxxx.reg_ctx_flashback tablespace xxxx_fbda parallel (degree 4) unrecoverable as select * from xxxx.SYS_FBA_HIST_112683;SQL> select /*+ parallel 4*/ count (*) from xxxx.SYS_FBA_HIST_112683; COUNT(*)---------- 84774720 |
9) CTAS all flashback data and export it, then move to target host:
1 | SQL> create table xxxx.reg_ctx_flashback tablespace xxxx parallel (degree 4) unrecoverable as select * from xxxx.SYS_FBA_HIST_112683; |
1 | expdp \'/ as sysdba\' directory=dump dumpfile=reg_cxt_fbda.dmp logfile=DROP_ME:export.log tables=xxxx.reg_ctx_flashback; |
10) restore flashback
1 2 3 4 5 6 7 8 9 | alter table xxxx.reg_cxt FLASHBACK ARCHIVE xxxx_ARCH_FL;BEGIN DBMS_FLASHBACK_ARCHIVE.import_history ( owner_name1 => 'xxxx', table_name1 => 'REG_CXT', temp_history_name => 'REG_CTX_FLASHBACK', options => DBMS_FLASHBACK_ARCHIVE.NODELETE); END;/ |