Skip to content

How-to restore dropped flashback archive or dropped table

How-to restore dropped flashback archive or dropped table published on Комментариев к записи How-to restore dropped flashback archive or dropped table нет

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:

SET DBID xxxxxxxx

run {
    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:

 select distinct tablespace_name from dba_segments where owner in ('SYS', 'SYSTEM'); 	

SYSAUX
UNDOTBS1
USERS
SYSTEM
UNDOTBS2

3) some fixes

SQL> alter database flashback off;
SQL> alter database disable  BLOCK CHANGE TRACKING; 

4) Generate restore list ( list of tablespaces with flashback archives )

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

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

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:

controlfile

CREATE CONTROLFILE REUSE DATABASE «P00xxxx» RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 37376
LOGFILE
GROUP 1 ‘+DATA/s00xxxx/onlinelog/group_1.263.857911649’ SIZE 500M BLOCKSIZE 512,
GROUP 2 ‘+DATA/s00xxxx/onlinelog/group_2.256.857911657’ SIZE 500M BLOCKSIZE 512,
GROUP 3 ‘+DATA/s00xxxx/onlinelog/group_3.265.857911663’ SIZE 500M BLOCKSIZE 512,
GROUP 4 ‘+DATA/s00xxxx/onlinelog/group_4.284.857911669’ SIZE 500M BLOCKSIZE 512,
GROUP 5 ‘+DATA/s00xxxx/onlinelog/group_5.283.857911675’ SIZE 500M BLOCKSIZE 512
— STANDBY LOGFILE
— GROUP 101 ‘+DATA/S00xxxx/ONLINELOG/group_101.267.936536737’ SIZE 500M BLOCKSIZE 512,
— GROUP 102 ‘+DATA/s00xxxx/onlinelog/group_102.293.851280253’ SIZE 500M BLOCKSIZE 512,
— GROUP 103 ‘+DATA/s00xxxx/onlinelog/group_103.294.851280265’ SIZE 500M BLOCKSIZE 512,
— GROUP 104 ‘+DATA/s00xxxx/onlinelog/group_104.295.851280275’ SIZE 500M BLOCKSIZE 512,
— GROUP 105 ‘+DATA/s00xxxx/onlinelog/group_105.296.851280285’ SIZE 500M BLOCKSIZE 512,
— GROUP 106 ‘+DATA/s00xxxx/onlinelog/group_106.277.867109841’ SIZE 500M BLOCKSIZE 512
DATAFILE
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.259.936581479’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.262.936580465’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.263.936580465’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.264.936580465’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.288.936583713’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.289.936583745’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.306.936581753’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_02.278.936583949’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_02.293.936583947’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_02.294.936583949’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_03.302.936582809’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_03.304.936581961’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_04_big.276.936584015’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_05.305.936581923’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_emergency.277.936584001’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_emergency.291.936583867’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_emergency.292.936583931’,
‘+DATA/S00xxxx/DATAFILE/slow_fda.298.936582809’,
‘+DATA/S00xxxx/DATAFILE/sysaux.258.936581503’,
‘+DATA/S00xxxx/DATAFILE/system.290.936583843’,
‘+DATA/S00xxxx/DATAFILE/undotbs1.261.936581223’,
‘+DATA/S00xxxx/DATAFILE/undotbs1.299.936582325’,
‘+DATA/S00xxxx/DATAFILE/undotbs1.312.936583685’,
‘+DATA/S00xxxx/DATAFILE/undotbs2.260.936581325’,
‘+DATA/S00xxxx/DATAFILE/undotbs2.310.936582849’,
‘+DATA/S00xxxx/DATAFILE/undotbs2.311.936582849’,
‘+DATA/S00xxxx/DATAFILE/users.287.936583685’
CHARACTER SET AL32UTF8
;

[свернуть]

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:

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:

  SQL> create table xxxx.reg_ctx_flashback tablespace xxxx parallel (degree 4) unrecoverable as select * from xxxx.SYS_FBA_HIST_112683;
   expdp \'/ as sysdba\' directory=dump dumpfile=reg_cxt_fbda.dmp logfile=DROP_ME:export.log tables=xxxx.reg_ctx_flashback;

10) restore flashback

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;
/

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика