Oracle 12C使用UNTIL SEQUENCE子句对Non-CDB中的表执行按时间点恢复

Oracle 12C使用UNTIL SEQUENCE子句对Non-CDB中的表执行按时间点恢复执行操作如下
1.对整个Non-CDB(orcl)生成RMAN备份

RMAN> backup as compressed backupset database format '+data/backup/%d_%I_%U_%t' plus archivelog format 'arc_%d_%T_%U';Starting backup at 10-JAN-18current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=13 RECID=1 STAMP=964995986input archived log thread=1 sequence=14 RECID=2 STAMP=965007422input archived log thread=1 sequence=15 RECID=3 STAMP=965007493input archived log thread=1 sequence=16 RECID=4 STAMP=965007542input archived log thread=1 sequence=17 RECID=5 STAMP=965011311input archived log thread=1 sequence=18 RECID=6 STAMP=965011687channel ORA_DISK_1: starting piece 1 at 10-JAN-18channel ORA_DISK_1: finished piece 1 at 10-JAN-18piece handle=/u01/app/oracle/product/12.2.0/db/dbs/arc_ORCL_20180110_21so9q78_1_1 tag=TAG20180110T024807 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15Finished backup at 10-JAN-18Starting backup at 10-JAN-18using channel ORA_DISK_1channel ORA_DISK_1: starting compressed full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00004 name=+DATA/orcl/datafile/users01.dbfinput datafile file number=00001 name=+DATA/orcl/datafile/system01.dbfinput datafile file number=00002 name=+DATA/orcl/datafile/sysaux01.dbfinput datafile file number=00003 name=+DATA/orcl/datafile/undotbs01.dbfinput datafile file number=00005 name=+DATA/orcl/datafile/usertbs01.dbfchannel ORA_DISK_1: starting piece 1 at 10-JAN-18channel ORA_DISK_1: finished piece 1 at 10-JAN-18piece handle=+DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705 tag=TAG20180110T024824 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:45Finished backup at 10-JAN-18Starting backup at 10-JAN-18current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=19 RECID=7 STAMP=965011751channel ORA_DISK_1: starting piece 1 at 10-JAN-18channel ORA_DISK_1: finished piece 1 at 10-JAN-18piece handle=/u01/app/oracle/product/12.2.0/db/dbs/arc_ORCL_20180110_23so9q98_1_1 tag=TAG20180110T024912 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 10-JAN-18Starting Control File and SPFILE Autobackup at 10-JAN-18piece handle=+DATA/backup/c-1492772871-20180110-01 comment=NONEFinished Control File and SPFILE Autobackup at 10-JAN-18

2.在删除表t_emp之前记录当前log sequence,在执行恢复时它们被用来指定恢复时间点

SQL> select count(*) from t_emp;  COUNT(*)----------       1071 row selected.

记录当前时间点的logseq号为29

SQL> select sequence#, status from v$log; SEQUENCE# STATUS---------- --------        28 INACTIVE        29 CURRENT        27 INACTIVE3 rows selected.

进行日志切换，记录truncate操作之前的logseq号为30，并执行truncate操作

SQL> alter system switch logfile;System altered.SQL> select sequence#, status from v$log; SEQUENCE# STATUS---------- ----------------        28 INACTIVE        29 ACTIVE        30 CURRENTSQL> truncate table t_emp;Table truncated.

进行日志切换，记录truncate操作之后的logseq号为31

SQL> alter system switch logfile;System altered.SQL> select sequence#, status from v$log; SEQUENCE# STATUS---------- ----------------        31 CURRENT        29 ACTIVE        30 ACTIVE

3.启动RMAN并使用有sysbacup或sysdba权限的用户连接到目标数据库

[oracle@jytest3 ~]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'[oracle@jytest3 ~]$ rman target/Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jan 10 02:22:13 2018Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCL (DBID=1492772871)

4.通过使用recover table命令将要被恢复的表或表分区恢复到指定的时间点。 使用auxiliary destination子句（/ora_xtts/recover辅助数据文件存储目录)与until scn来指定恢复时间点,并且在recover命令中使用子句 dump file与datapump destination，指定包含被恢复表或表分区的导出dump文件的文件名(t_emp_recvr.dmp)与存储位置(/ora_xtts/dump)。 使用remap table子句将hr.t_emp恢复成jy.t_emp_new。使用remap tablespace子 句将表t_emp从hr用户方案恢复到jy用户方案中

RMAN> run2> {3> recover table hr.t_emp4> until sequence 305> auxiliary destination '/ora_xtts/recover'6> datapump destination '/ora_xtts/dump'7> dump file 't_emp_recvr.dmp'8> remap table 'HR'.'T_EMP':'JY'.'T_EMP_NEW'9> remap tablespace 'USERS':'USERTBS';10> }Starting recover at 2018-01-10 22:32:35using channel ORA_DISK_1RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-timeList of tablespaces expected to have UNDO segmentsTablespace SYSTEMTablespace UNDOTBS1Creating automatic instance, with SID='zdjl'initialization parameters used for automatic instance:db_name=ORCLdb_unique_name=zdjl_pitr_ORCLcompatible=12.2.0db_block_size=8192db_files=200diagnostic_dest=/u01/app/oracle_system_trig_enabled=FALSEsga_target=1024Mprocesses=120db_create_file_dest=/ora_xtts/recoverlog_archive_dest_1='location=/ora_xtts/recover'#No auxiliary parameter file usedstarting up automatic instance ORCLOracle instance startedTotal System Global Area    1073741824 bytesFixed Size                     8628936 bytesVariable Size                293602616 bytesDatabase Buffers             763363328 bytesRedo Buffers                   8146944 bytesAutomatic instance createdcontents of Memory Script:{# set requested point in timeset until  logseq 30 thread 1;# restore the controlfilerestore clone controlfile;# mount the controlfilesql clone 'alter database mount clone database';# archive current online logsql 'alter system archive log current';}executing Memory Scriptexecuting command: SET until clauseStarting restore at 2018-01-10 22:33:40allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=6 device type=DISKchannel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/c-1492772871-20180110-02channel ORA_AUX_DISK_1: piece handle=+DATA/backup/c-1492772871-20180110-02 tag=TAG20180110T200959channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05output file name=/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d929nw_.ctlFinished restore at 2018-01-10 22:33:46sql statement: alter database mount clone databasesql statement: alter system archive log currentcontents of Memory Script:{# set requested point in timeset until  logseq 30 thread 1;# set destinations for recovery set and auxiliary set datafilesset newname for clone datafile  1 to new;set newname for clone datafile  3 to new;set newname for clone datafile  2 to new;set newname for clone tempfile  1 to new;# switch all tempfilesswitch clone tempfile all;# restore the tablespaces in the recovery set and the auxiliary setrestore clone datafile  1, 3, 2;switch clone datafile all;}executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed tempfile 1 to /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_%u_.tmp in control fileStarting restore at 2018-01-10 22:33:55using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00001 to /ora_xtts/recover/ORCL/datafile/o1_mf_system_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00003 to /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00002 to /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_26sobn77_1_1_965074151channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151 tag=TAG20180110T200911channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45Finished restore at 2018-01-10 22:34:41datafile 1 switched to datafile copyinput datafile copy RECID=4 STAMP=965082881 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5d92n67_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=5 STAMP=965082881 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5d92n7d_.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=6 STAMP=965082881 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5d92n6t_.dbfcontents of Memory Script:{# set requested point in timeset until  logseq 30 thread 1;# online the datafiles restored or switchedsql clone "alter database datafile  1 online";sql clone "alter database datafile  3 online";sql clone "alter database datafile  2 online";# recover and open database read onlyrecover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";sql clone 'alter database open read only';}executing Memory Scriptexecuting command: SET until clausesql statement: alter database datafile  1 onlinesql statement: alter database datafile  3 onlinesql statement: alter database datafile  2 onlineStarting recover at 2018-01-10 22:34:43using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 24 is already on disk as file +DATA/arch/orcl/1_24_964992135.dbfarchived log for thread 1 with sequence 25 is already on disk as file +DATA/arch/orcl/1_25_964992135.dbfarchived log for thread 1 with sequence 26 is already on disk as file +DATA/arch/orcl/1_26_964992135.dbfarchived log for thread 1 with sequence 27 is already on disk as file +DATA/arch/orcl/1_27_964992135.dbfarchived log for thread 1 with sequence 28 is already on disk as file +DATA/arch/orcl/1_28_964992135.dbfarchived log for thread 1 with sequence 29 is already on disk as file +DATA/arch/orcl/1_29_964992135.dbfarchived log file name=+DATA/arch/orcl/1_24_964992135.dbf thread=1 sequence=24archived log file name=+DATA/arch/orcl/1_25_964992135.dbf thread=1 sequence=25archived log file name=+DATA/arch/orcl/1_26_964992135.dbf thread=1 sequence=26archived log file name=+DATA/arch/orcl/1_27_964992135.dbf thread=1 sequence=27archived log file name=+DATA/arch/orcl/1_28_964992135.dbf thread=1 sequence=28archived log file name=+DATA/arch/orcl/1_29_964992135.dbf thread=1 sequence=29media recovery complete, elapsed time: 00:00:24Finished recover at 2018-01-10 22:35:09sql statement: alter database open read onlycontents of Memory Script:{   sql clone "create spfile from memory";   shutdown clone immediate;   startup clone nomount;   sql clone "alter system set  control_files =  ''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d929nw_.ctl'' comment= ''RMAN set'' scope=spfile";   shutdown clone immediate;   startup clone nomount;# mount databasesql clone 'alter database mount clone database';}executing Memory Scriptsql statement: create spfile from memorydatabase closeddatabase dismountedOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area    1073741824 bytesFixed Size                     8628936 bytesVariable Size                293602616 bytesDatabase Buffers             763363328 bytesRedo Buffers                   8146944 bytessql statement: alter system set  control_files =   ''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d929nw_.ctl'' comment= ''RMAN set'' scope=spfileOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area    1073741824 bytesFixed Size                     8628936 bytesVariable Size                293602616 bytesDatabase Buffers             763363328 bytesRedo Buffers                   8146944 bytessql statement: alter database mount clone databasecontents of Memory Script:{# set requested point in timeset until  logseq 30 thread 1;# set destinations for recovery set and auxiliary set datafilesset newname for datafile  4 to new;# restore the tablespaces in the recovery set and the auxiliary setrestore clone datafile  4;switch clone datafile all;}executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEStarting restore at 2018-01-10 22:36:13allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=7 device type=DISKchannel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00004 to /ora_xtts/recover/ZDJL_PITR_ORCL/datafile/o1_mf_users_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_26sobn77_1_1_965074151channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151 tag=TAG20180110T200911channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55Finished restore at 2018-01-10 22:37:09datafile 4 switched to datafile copyinput datafile copy RECID=8 STAMP=965083029 file name=/ora_xtts/recover/ZDJL_PITR_ORCL/datafile/o1_mf_users_f5d96ycb_.dbfcontents of Memory Script:{# set requested point in timeset until  logseq 30 thread 1;# online the datafiles restored or switchedsql clone "alter database datafile  4 online";# recover and open resetlogsrecover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;alter clone database open resetlogs;}executing Memory Scriptexecuting command: SET until clausesql statement: alter database datafile  4 onlineStarting recover at 2018-01-10 22:37:10using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 24 is already on disk as file +DATA/arch/orcl/1_24_964992135.dbfarchived log for thread 1 with sequence 25 is already on disk as file +DATA/arch/orcl/1_25_964992135.dbfarchived log for thread 1 with sequence 26 is already on disk as file +DATA/arch/orcl/1_26_964992135.dbfarchived log for thread 1 with sequence 27 is already on disk as file +DATA/arch/orcl/1_27_964992135.dbfarchived log for thread 1 with sequence 28 is already on disk as file +DATA/arch/orcl/1_28_964992135.dbfarchived log for thread 1 with sequence 29 is already on disk as file +DATA/arch/orcl/1_29_964992135.dbfarchived log file name=+DATA/arch/orcl/1_24_964992135.dbf thread=1 sequence=24archived log file name=+DATA/arch/orcl/1_25_964992135.dbf thread=1 sequence=25archived log file name=+DATA/arch/orcl/1_26_964992135.dbf thread=1 sequence=26archived log file name=+DATA/arch/orcl/1_27_964992135.dbf thread=1 sequence=27archived log file name=+DATA/arch/orcl/1_28_964992135.dbf thread=1 sequence=28archived log file name=+DATA/arch/orcl/1_29_964992135.dbf thread=1 sequence=29media recovery complete, elapsed time: 00:00:06Finished recover at 2018-01-10 22:37:18database openedcontents of Memory Script:{# create directory for datapump importsql "create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''";# create directory for datapump exportsql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''";}executing Memory Scriptsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''Performing export of tables...   EXPDP> Starting "SYS"."TSPITR_EXP_zdjl_ctic":   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE   EXPDP> . . exported "HR"."T_EMP"                                17.08 KB     107 rows   EXPDP> Master table "SYS"."TSPITR_EXP_zdjl_ctic" successfully loaded/unloaded   EXPDP> ******************************************************************************   EXPDP> Dump file set for SYS.TSPITR_EXP_zdjl_ctic is:   EXPDP>   /ora_xtts/dump/t_emp_recvr.dmp   EXPDP> Job "SYS"."TSPITR_EXP_zdjl_ctic" successfully completed at Wed Jan 10 22:38:12 2018 elapsed 0 00:00:33Export completedcontents of Memory Script:{# shutdown clone before importshutdown clone abort}executing Memory ScriptOracle instance shut downPerforming import of tables...   IMPDP> Master table "SYS"."TSPITR_IMP_zdjl_nBfi" successfully loaded/unloaded   IMPDP> Starting "SYS"."TSPITR_IMP_zdjl_nBfi":   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA   IMPDP> . . imported "JY"."T_EMP_NEW"                            17.08 KB     107 rows   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER   IMPDP> Job "SYS"."TSPITR_IMP_zdjl_nBfi" successfully completed at Wed Jan 10 22:38:51 2018 elapsed 0 00:00:32Import completedRemoving automatic instanceAutomatic instance removedauxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_f5d94xrx_.tmp deletedauxiliary instance file /ora_xtts/recover/ZDJL_PITR_ORCL/onlinelog/o1_mf_3_f5d98yqq_.log deletedauxiliary instance file /ora_xtts/recover/ZDJL_PITR_ORCL/onlinelog/o1_mf_2_f5d98yq4_.log deletedauxiliary instance file /ora_xtts/recover/ZDJL_PITR_ORCL/onlinelog/o1_mf_1_f5d98ypg_.log deletedauxiliary instance file /ora_xtts/recover/ZDJL_PITR_ORCL/datafile/o1_mf_users_f5d96ycb_.dbf deletedauxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5d92n6t_.dbf deletedauxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5d92n7d_.dbf deletedauxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_system_f5d92n67_.dbf deletedauxiliary instance file /ora_xtts/recover/ORCL/controlfile/o1_mf_f5d929nw_.ctl deletedauxiliary instance file t_emp_recvr.dmp deletedFinished recover at 2018-01-10 22:38:56

5.验证表hr.t_emp的数据是否成功恢复到表jy.t_emp_new中，从查询记录可以看到已经成功恢复到表jy.t_emp_new中。

SQL> select count(*) from jy.t_emp_new;  COUNT(*)----------       107