这篇文章主要为大家展示了“RMAN中如何使用until time子句对Non-CDB中的表执行按时间点恢复”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“RMAN中如何使用until time子句对Non-CDB中的表执行按时间点恢复”这篇文章吧。

在RMAN中使用until time子句对Non-CDB中的表执行按时间点恢复操作步骤如下
1.对整个Non-CDB(orcl)生成RMAN备份

RMAN>backupascompressedbackupsetdatabaseformat'+data/backup/%d_%I_%U_%t'plusarchivelogformat'arc_%d_%T_%U';Startingbackupat10-JAN-18currentlogarchivedusingchannelORA_DISK_1channelORA_DISK_1:startingcompressedarchivedlogbackupsetchannelORA_DISK_1:specifyingarchivedlog(s)inbackupsetinputarchivedlogthread=1sequence=13RECID=1STAMP=964995986inputarchivedlogthread=1sequence=14RECID=2STAMP=965007422inputarchivedlogthread=1sequence=15RECID=3STAMP=965007493inputarchivedlogthread=1sequence=16RECID=4STAMP=965007542inputarchivedlogthread=1sequence=17RECID=5STAMP=965011311inputarchivedlogthread=1sequence=18RECID=6STAMP=965011687channelORA_DISK_1:startingpiece1at10-JAN-18channelORA_DISK_1:finishedpiece1at10-JAN-18piecehandle=/u01/app/oracle/product/12.2.0/db/dbs/arc_ORCL_20180110_21so9q78_1_1tag=TAG20180110T024807comment=NONEchannelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:15Finishedbackupat10-JAN-18Startingbackupat10-JAN-18usingchannelORA_DISK_1channelORA_DISK_1:startingcompressedfulldatafilebackupsetchannelORA_DISK_1:specifyingdatafile(s)inbackupsetinputdatafilefilenumber=00004name=+DATA/orcl/datafile/users01.dbfinputdatafilefilenumber=00001name=+DATA/orcl/datafile/system01.dbfinputdatafilefilenumber=00002name=+DATA/orcl/datafile/sysaux01.dbfinputdatafilefilenumber=00003name=+DATA/orcl/datafile/undotbs01.dbfinputdatafilefilenumber=00005name=+DATA/orcl/datafile/usertbs01.dbfchannelORA_DISK_1:startingpiece1at10-JAN-18channelORA_DISK_1:finishedpiece1at10-JAN-18piecehandle=+DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705tag=TAG20180110T024824comment=NONEchannelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:45Finishedbackupat10-JAN-18Startingbackupat10-JAN-18currentlogarchivedusingchannelORA_DISK_1channelORA_DISK_1:startingcompressedarchivedlogbackupsetchannelORA_DISK_1:specifyingarchivedlog(s)inbackupsetinputarchivedlogthread=1sequence=19RECID=7STAMP=965011751channelORA_DISK_1:startingpiece1at10-JAN-18channelORA_DISK_1:finishedpiece1at10-JAN-18piecehandle=/u01/app/oracle/product/12.2.0/db/dbs/arc_ORCL_20180110_23so9q98_1_1tag=TAG20180110T024912comment=NONEchannelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:01Finishedbackupat10-JAN-18StartingControlFileandSPFILEAutobackupat10-JAN-18piecehandle=+DATA/backup/c-1492772871-20180110-01comment=NONEFinishedControlFileandSPFILEAutobackupat10-JAN-18

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

SQL>selectcount(*)fromt_emp;COUNT(*)----------1071rowselected.SQL>selectsysdatefromdual;SYSDATE-------------------2018-01-1020:10:011rowselected.SQL>selectcurrent_scnfromv$database;CURRENT_SCN-----------4472151rowselected.

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

[oracle@jytest3~]$exportNLS_DATE_FORMAT='yyyy-mm-ddhh34:mi:ss'[oracle@jytest3~]$rmantarget/RecoveryManager:Release12.2.0.1.0-ProductiononWedJan1002:22:132018Copyright(c)1982,2017,Oracleand/oritsaffiliates.Allrightsreserved.connectedtotargetdatabase: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>recovertablehr.t_emp4>untiltime'2018-01-1020:10:01'5>auxiliarydestination'/ora_xtts/recover'6>datapumpdestination'/ora_xtts/dump'7>dumpfile't_emp_recvr.dmp'8>remaptable'HR'.'T_EMP':'JY'.'T_EMP_NEW'9>remaptablespace'USERS':'USERTBS';10>}Startingrecoverat2018-01-1022:07:43allocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=49devicetype=DISKRMAN-05026:warning:presumingfollowingsetoftablespacesappliestospecifiedpoint-in-timeListoftablespacesexpectedtohaveUNDOsegmentsTablespaceSYSTEMTablespaceUNDOTBS1Creatingautomaticinstance,withSID='BxcB'initializationparametersusedforautomaticinstance:db_name=ORCLdb_unique_name=BxcB_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'#NoauxiliaryparameterfileusedstartingupautomaticinstanceORCLOracleinstancestartedTotalSystemGlobalArea1073741824bytesFixedSize8628936bytesVariableSize293602616bytesDatabaseBuffers763363328bytesRedoBuffers8146944bytesAutomaticinstancecreatedcontentsofMemoryScript:{#setrequestedpointintimesetuntiltime"2018-01-1020:10:01";#restorethecontrolfilerestoreclonecontrolfile;#mountthecontrolfilesqlclone'alterdatabasemountclonedatabase';#archivecurrentonlinelogsql'altersystemarchivelogcurrent';}executingMemoryScriptexecutingcommand:SETuntilclauseStartingrestoreat2018-01-1022:09:04allocatedchannel:ORA_AUX_DISK_1channelORA_AUX_DISK_1:SID=6devicetype=DISKchannelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:restoringcontrolfilechannelORA_AUX_DISK_1:readingfrombackuppiece+DATA/backup/c-1492772871-20180110-02channelORA_AUX_DISK_1:piecehandle=+DATA/backup/c-1492772871-20180110-02tag=TAG20180110T200959channelORA_AUX_DISK_1:restoredbackuppiece1channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:04outputfilename=/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d7n52r_.ctlFinishedrestoreat2018-01-1022:09:10sqlstatement:alterdatabasemountclonedatabasesqlstatement:altersystemarchivelogcurrentcontentsofMemoryScript:{#setrequestedpointintimesetuntiltime"2018-01-1020:10:01";#setdestinationsforrecoverysetandauxiliarysetdatafilessetnewnameforclonedatafile1tonew;setnewnameforclonedatafile3tonew;setnewnameforclonedatafile2tonew;setnewnameforclonetempfile1tonew;#switchalltempfilesswitchclonetempfileall;#restorethetablespacesintherecoverysetandtheauxiliarysetrestoreclonedatafile1,3,2;switchclonedatafileall;}executingMemoryScriptexecutingcommand:SETuntilclauseexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMErenamedtempfile1to/ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_%u_.tmpincontrolfileStartingrestoreat2018-01-1022:09:18usingchannelORA_AUX_DISK_1channelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_AUX_DISK_1:restoringdatafile00001to/ora_xtts/recover/ORCL/datafile/o1_mf_system_%u_.dbfchannelORA_AUX_DISK_1:restoringdatafile00003to/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_%u_.dbfchannelORA_AUX_DISK_1:restoringdatafile00002to/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_%u_.dbfchannelORA_AUX_DISK_1:readingfrombackuppiece+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151channelORA_AUX_DISK_1:piecehandle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151tag=TAG20180110T200911channelORA_AUX_DISK_1:restoredbackuppiece1channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:45Finishedrestoreat2018-01-1022:10:04datafile1switchedtodatafilecopyinputdatafilecopyRECID=4STAMP=965081404filename=/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5d7nhmp_.dbfdatafile3switchedtodatafilecopyinputdatafilecopyRECID=5STAMP=965081404filename=/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5d7nhq5_.dbfdatafile2switchedtodatafilecopyinputdatafilecopyRECID=6STAMP=965081405filename=/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5d7nhot_.dbfcontentsofMemoryScript:{#setrequestedpointintimesetuntiltime"2018-01-1020:10:01";#onlinethedatafilesrestoredorswitchedsqlclone"alterdatabasedatafile1online";sqlclone"alterdatabasedatafile3online";sqlclone"alterdatabasedatafile2online";#recoverandopendatabasereadonlyrecoverclonedatabasetablespace"SYSTEM","UNDOTBS1","SYSAUX";sqlclone'alterdatabaseopenreadonly';}executingMemoryScriptexecutingcommand:SETuntilclausesqlstatement:alterdatabasedatafile1onlinesqlstatement:alterdatabasedatafile3onlinesqlstatement:alterdatabasedatafile2onlineStartingrecoverat2018-01-1022:10:06usingchannelORA_AUX_DISK_1startingmediarecoveryarchivedlogforthread1withsequence24isalreadyondiskasfile+DATA/arch/orcl/1_24_964992135.dbfarchivedlogforthread1withsequence25isalreadyondiskasfile+DATA/arch/orcl/1_25_964992135.dbfarchivedlogfilename=+DATA/arch/orcl/1_24_964992135.dbfthread=1sequence=24archivedlogfilename=+DATA/arch/orcl/1_25_964992135.dbfthread=1sequence=25mediarecoverycomplete,elapsedtime:00:00:01Finishedrecoverat2018-01-1022:10:09sqlstatement:alterdatabaseopenreadonlycontentsofMemoryScript:{sqlclone"createspfilefrommemory";shutdowncloneimmediate;startupclonenomount;sqlclone"altersystemsetcontrol_files=''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d7n52r_.ctl''comment=''RMANset''scope=spfile";shutdowncloneimmediate;startupclonenomount;#mountdatabasesqlclone'alterdatabasemountclonedatabase';}executingMemoryScriptsqlstatement:createspfilefrommemorydatabasecloseddatabasedismountedOracleinstanceshutdownconnectedtoauxiliarydatabase(notstarted)OracleinstancestartedTotalSystemGlobalArea1073741824bytesFixedSize8628936bytesVariableSize293602616bytesDatabaseBuffers763363328bytesRedoBuffers8146944bytessqlstatement:altersystemsetcontrol_files=''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d7n52r_.ctl''comment=''RMANset''scope=spfileOracleinstanceshutdownconnectedtoauxiliarydatabase(notstarted)OracleinstancestartedTotalSystemGlobalArea1073741824bytesFixedSize8628936bytesVariableSize293602616bytesDatabaseBuffers763363328bytesRedoBuffers8146944bytessqlstatement:alterdatabasemountclonedatabasecontentsofMemoryScript:{#setrequestedpointintimesetuntiltime"2018-01-1020:10:01";#setdestinationsforrecoverysetandauxiliarysetdatafilessetnewnamefordatafile4tonew;#restorethetablespacesintherecoverysetandtheauxiliarysetrestoreclonedatafile4;switchclonedatafileall;}executingMemoryScriptexecutingcommand:SETuntilclauseexecutingcommand:SETNEWNAMEStartingrestoreat2018-01-1022:11:13allocatedchannel:ORA_AUX_DISK_1channelORA_AUX_DISK_1:SID=7devicetype=DISKchannelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_AUX_DISK_1:restoringdatafile00004to/ora_xtts/recover/BXCB_PITR_ORCL/datafile/o1_mf_users_%u_.dbfchannelORA_AUX_DISK_1:readingfrombackuppiece+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151channelORA_AUX_DISK_1:piecehandle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151tag=TAG20180110T200911channelORA_AUX_DISK_1:restoredbackuppiece1channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:55Finishedrestoreat2018-01-1022:12:09datafile4switchedtodatafilecopyinputdatafilecopyRECID=8STAMP=965081529filename=/ora_xtts/recover/BXCB_PITR_ORCL/datafile/o1_mf_users_f5d7r2q4_.dbfcontentsofMemoryScript:{#setrequestedpointintimesetuntiltime"2018-01-1020:10:01";#onlinethedatafilesrestoredorswitchedsqlclone"alterdatabasedatafile4online";#recoverandopenresetlogsrecoverclonedatabasetablespace"USERS","SYSTEM","UNDOTBS1","SYSAUX"deletearchivelog;alterclonedatabaseopenresetlogs;}executingMemoryScriptexecutingcommand:SETuntilclausesqlstatement:alterdatabasedatafile4onlineStartingrecoverat2018-01-1022:12:10usingchannelORA_AUX_DISK_1startingmediarecoveryarchivedlogforthread1withsequence24isalreadyondiskasfile+DATA/arch/orcl/1_24_964992135.dbfarchivedlogforthread1withsequence25isalreadyondiskasfile+DATA/arch/orcl/1_25_964992135.dbfarchivedlogfilename=+DATA/arch/orcl/1_24_964992135.dbfthread=1sequence=24archivedlogfilename=+DATA/arch/orcl/1_25_964992135.dbfthread=1sequence=25mediarecoverycomplete,elapsedtime:00:00:00Finishedrecoverat2018-01-1022:12:14databaseopenedcontentsofMemoryScript:{#createdirectoryfordatapumpimportsql"createorreplacedirectoryTSPITR_DIROBJ_DPDIRas''/ora_xtts/dump''";#createdirectoryfordatapumpexportsqlclone"createorreplacedirectoryTSPITR_DIROBJ_DPDIRas''/ora_xtts/dump''";}executingMemoryScriptsqlstatement:createorreplacedirectoryTSPITR_DIROBJ_DPDIRas''/ora_xtts/dump''sqlstatement:createorreplacedirectoryTSPITR_DIROBJ_DPDIRas''/ora_xtts/dump''Performingexportoftables...EXPDP>Starting"SYS"."TSPITR_EXP_BxcB_tfoq":EXPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATAEXPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSEXPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/MARKEREXPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLEEXPDP>..exported"HR"."T_EMP"17.08KB107rowsEXPDP>Mastertable"SYS"."TSPITR_EXP_BxcB_tfoq"successfullyloaded/unloadedEXPDP>******************************************************************************EXPDP>DumpfilesetforSYS.TSPITR_EXP_BxcB_tfoqis:EXPDP>/ora_xtts/dump/t_emp_recvr.dmpEXPDP>Job"SYS"."TSPITR_EXP_BxcB_tfoq"successfullycompletedatWedJan1022:13:082018elapsed000:00:33ExportcompletedcontentsofMemoryScript:{#shutdownclonebeforeimportshutdowncloneabort}executingMemoryScriptOracleinstanceshutdownPerformingimportoftables...IMPDP>Mastertable"SYS"."TSPITR_IMP_BxcB_Enlv"successfullyloaded/unloadedIMPDP>Starting"SYS"."TSPITR_IMP_BxcB_Enlv":IMPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLEIMPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATAIMPDP>..imported"JY"."T_EMP_NEW"17.08KB107rowsIMPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSIMPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/MARKERIMPDP>Job"SYS"."TSPITR_IMP_BxcB_Enlv"successfullycompletedatWedJan1022:13:482018elapsed000:00:33ImportcompletedRemovingautomaticinstanceAutomaticinstanceremovedauxiliaryinstancefile/ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_f5d7p27f_.tmpdeletedauxiliaryinstancefile/ora_xtts/recover/BXCB_PITR_ORCL/onlinelog/o1_mf_3_f5d7syo5_.logdeletedauxiliaryinstancefile/ora_xtts/recover/BXCB_PITR_ORCL/onlinelog/o1_mf_2_f5d7syng_.logdeletedauxiliaryinstancefile/ora_xtts/recover/BXCB_PITR_ORCL/onlinelog/o1_mf_1_f5d7symr_.logdeletedauxiliaryinstancefile/ora_xtts/recover/BXCB_PITR_ORCL/datafile/o1_mf_users_f5d7r2q4_.dbfdeletedauxiliaryinstancefile/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5d7nhot_.dbfdeletedauxiliaryinstancefile/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5d7nhq5_.dbfdeletedauxiliaryinstancefile/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5d7nhmp_.dbfdeletedauxiliaryinstancefile/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d7n52r_.ctldeletedauxiliaryinstancefilet_emp_recvr.dmpdeletedFinishedrecoverat2018-01-1022:13:53

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

SQL>selectcount(*)fromjy.t_emp_new;COUNT(*)----------107

以上是“RMAN中如何使用until time子句对Non-CDB中的表执行按时间点恢复”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！