Oracle - RMAN-06067 when doing DPITR with partial tablespace
I just want to restore SYSTEM/SYSAUX/UNDOTBS to a specific time in order to use flashback query on some dictionary table to determine the exact time of a mistake.
When I tried to use a normal way to restore database, like:
RMAN> run{ 2> set dbid 766629926; 3> restore controlfile from AUTOBACKUP; 4> } RMAN> alter database mount; RMAN> run{ 2> set UNTIL sequence 66; 3> restore tablespace SYSTEM,SYSAUX,UNDOTBS1; 4> }
When I do recover, RMAN-06067 reported. This is because this is a backup controlfile, and cannot just do RECOVER TABLESPACE on same specific tablespaces.
RMAN> run{ 2> set UNTIL sequence 66; 3> recover tablespace SYSTEM,SYSAUX,UNDOTBS1; 4> } executing command: SET until clause Starting recover at 22-MAR-19 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 03/22/2019 12:40:29 RMAN-06067: RECOVER DATABASE required with a backup or created control file
We should use “recover database skip tablespace” to achieve this goal.
RMAN> run{ 2> set until sequence 66; 3> recover database skip tablespace USERS,"PDB$SEED":SYSTEM,"PDB$SEED":SYSAUX,"PDB$SEED":UNDOTBS1,HR:SYSTEM,HR:SYSAUX,HR:UNDOTBS1; 4> } executing command: SET until clause Starting recover at 22-MAR-19 using channel ORA_DISK_1 Executing: alter database datafile 7 offline Executing: alter database datafile 5 offline Executing: alter database datafile 6 offline Executing: alter database datafile 8 offline Executing: alter database datafile 9 offline Executing: alter database datafile 10 offline Executing: alter database datafile 11 offline starting media recovery archived log for thread 1 with sequence 57 is already on disk as file /home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_57_g99ypj0t_.arc archived log for thread 1 with sequence 58 is already on disk as file /home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_58_g99ypmxn_.arc archived log for thread 1 with sequence 59 is already on disk as file /home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_59_g99yppch_.arc archived log for thread 1 with sequence 60 is already on disk as file /home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_60_g99ypplv_.arc archived log for thread 1 with sequence 61 is already on disk as file /home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_61_g99ypsll_.arc archived log for thread 1 with sequence 62 is already on disk as file /home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_62_g99ypso9_.arc archived log for thread 1 with sequence 63 is already on disk as file /home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_63_g99ypwlg_.arc archived log for thread 1 with sequence 64 is already on disk as file /home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_64_g99ypwo2_.arc archived log for thread 1 with sequence 65 is already on disk as file /home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_65_g99ypzkh_.arc archived log file name=/home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_57_g99ypj0t_.arc thread=1 sequence=57 archived log file name=/home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_58_g99ypmxn_.arc thread=1 sequence=58 archived log file name=/home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_59_g99yppch_.arc thread=1 sequence=59 archived log file name=/home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_60_g99ypplv_.arc thread=1 sequence=60 archived log file name=/home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_61_g99ypsll_.arc thread=1 sequence=61 archived log file name=/home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_62_g99ypso9_.arc thread=1 sequence=62 archived log file name=/home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_63_g99ypwlg_.arc thread=1 sequence=63 archived log file name=/home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_64_g99ypwo2_.arc thread=1 sequence=64 archived log file name=/home/oracle/fast_recovery_area/O12201/O12201/archivelog/2019_03_22/o1_mf_1_65_g99ypzkh_.arc thread=1 sequence=65 media recovery complete, elapsed time: 00:00:01 Finished recover at 22-MAR-19 RMAN> alter database open resetlogs;
But the better way to do DPITR/TSPITR is to use DUPLICATE
run{ set dbid 766629926; set until time "to_date('20190322 130700','yyyymmdd hh24miss')"; duplicate database to O12201 tablespace SYSTEM,SYSAUX,UNDOTBS1 BACKUP LOCATION '/home/oracle/fast_recovery_area/O12201/O12201/backupset/2019_03_22' ; }