小编给大家分享一下Oracle 12c如何使用RMAN备份对Non-CDB中的表按时间点进行恢复，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

RMAN使用recover命令来将表或表分区恢复到指定的时间点。为了从RMAN备份中恢复表与表分区，你必须提供以下信息：
.要被恢复的表或表分区
.表或表分区要被恢复到的特定时间点
.被恢复的表或表分区是否要被导入到目标数据库中

RMAN使用这些信息来自动对表或表分区执行恢复操作。作为恢复处理的一部分，RMAN会创建一个辅助数据库用来将表或表分区恢复到指定的时间点。如果被恢复的表或表分区需要被重命名，映射到新表空间或映射到新方案中，那么必须指定新的表名，表空间名或方案名。

当从RMAN备份中自动处理表或表分区的恢复操作时RMAN将会执行以下步骤:
1.基于指定的恢复时间点来判断包含被恢复表或表分区的是那个备份文件。

2.判断在目标主机上是否有足够的空间用来创建辅助实例来执行对表或表分区的恢复操作，如果没有足够空间，那么RMAN将会显示错误信息并且退出恢复操作。

3.在目标主机上创建一个辅助数据库并且在辅助数据库中将指定的表或表分区恢复到指定的时间点。可以在目标主机上指定存储辅助数据库相关恢复数据文件的目录。

4.创建对恢复的表或表分区使用Data Pump进行导出。可以指定用来存储被恢复表或表分区元数据的dump文件的文件名与存储目录。

5.可选操作，将步骤4导出的表或表分区导入到目标数据库中。可以选择不将包含被恢复表或表分区导出dump文件导入到目标数据库中。如果选择不将导出dump文件导入目标数据库作为恢复操作的一部分，那么之后必须使用Data Pump导入工具进行导入操作。

6.可选操作，在目标数据库中重命名被恢复的表或表分区。也可以将被恢复的对象导入与它原始表空间或方案不同的表空间或方案中。

RMAN表恢复操作时辅助数据库文件的存储目录
为了恢复指定的表或表分区，RMAN会创建一个辅助数据库在恢复操作时使用。使用以下一种方法来在目标主机上指定用来存储辅助数据库文件的目录：
.在recover命令中指定auxiliary destination子句。
.使用set newname命令。在run块中使用recover命令并且使用set newname命令来重命名数据文件。

建议通过使用auxiliary destination子句来为辅助数据库的数据文件指定存储目录。当使用set newname命令时，即使是只对恢复操作所请求的一个数据文件没有执行set newname命令，那么就不能对表或表分区执行恢复操作。

RMAN恢复表与表分区操作中所使用的Data Pump导出dump文件
当在辅助数据库中将表或表分区恢复到指定的时间点之后，RMAN会创建包含被恢复对象的Data Pump导出dump文件。可以指定dump文件的文件名与位置或者允许RMAN使用缺省的文件名与位置。在recover命令中使用datapump destination子句来指定创建Data Pump导出dump文件的存储目录。这个目录通常是操作系统目录路径来存储dump文件。如果忽略这个子句，dump文件会被存储在由auxiliary destination参数所指定的目录中。如果不指定辅助目录，那么dump文件会被存储在缺省操作系统特定的目录中。在Linux操作系统中，缺省目录为$ORACLE_HOME/dbs。在Windows操作系统中，缺省目录为%ORACLE_HOME\database。在recover命令中使用dump file子句来指定创建Data Pump导出dump文件的存储目录。如果忽略这个子句，RMAN使用缺省操作系统特定的dump文件名。在Linux与Windows操作系统中，缺省的dump文件名为tspitr_SID-of-clone_n.dmp，SID-of-clone是RMAN在执行恢复操作时所创建的辅助数据库的Oracle SID，其中n是任意随机生成的数字。如果由dump file所指定的文件名在目录中已经存在，那么恢复操作将会失败。

将被恢复的表与表分区导入到目标数据库
缺省情况下，RMAN会将存储在dump文件中的被恢复表或表分区导入到目标数据库中。然而，可以选择在recover命令中使用notableimport子句来避免将被恢复的表或表分区导入到目标数据库中。当notableimport子句被使用时，RMAN会将表或表分区恢复到指定的时间点，然后创建导出dump文件。然而，这个dump文件不会被导入到目标数据库中。当需要时可以通过手动使用Data Pump导入工具将dump文件导入到目标数据库。如果在导入操作时出现了错误，RMAN在表恢复操作结束时不会删除导出dump文件。这可以让你手动导入dump文件。

对被恢复的表与表分区进行重命名
当你恢复表或表分区时，可以在它们被导入到目标数据库后进行重命名。remap table子句可以用来对目标数据库中被恢复的表或表分区进行重命名。为了将被恢复的表或表分区导入与原始对象所存储的不同表空间，可以在recover命令中使用remap tablespace子句。只有被恢复的表或表分区会被重新映射，已有的对象不会发生改变。如果目标数据库中有与被恢复对象同名的对象，RMAN会显示错误信息指示需要使用remap table子句来重命名被恢复的表。当恢复表分区时，每个表分区被恢复成一个单独的表。使用remap table子句来指定每个被恢复的分区在导入时所使用的表名。如果没有显式地指定表名，RMAN会通过组合被恢复的表与分区名来生成表名。生成的表名格式为tablename_partitionname。如果表名在目标数据库中已经存在了，那么RMAN会在表名后加上_1。如果这个表名也存在了，那么就在表名后加上_2依此类推。当使用remap选项时，任何命名约束与索引不会被导入。这可以避免与现有表发生冲突。

将表与分区恢复到新用户方案中
将表或表分区恢复到不同的用户方案中可以避免与原用户方案中已经存在的约束，索引或触发器名字发生命名冲突。从Oracle 12.2开始，可以将表或表分区恢复到与原用户方案不同的用户方案中。当将对象恢复到不同用户方案中时，可以保留它们的原始名字或重新命名。在单个恢复操作中可以重命名表与重新映射用户方案。例如，可以将hr.employees表恢复成new_hr.employees表，hr.new_employees表或new_hr.new_employees表。remap table子句能让你重命名对象并且将它们恢复到不同的用户方案中。在执行表恢复操作时，对recover table命令使用remap table子句来将原用户方案映射成新用户方案。在执行恢复操作之前新用户方案必须先在目标数据库中存在。表恢复在物理备库中不支持。对于逻辑备库，在主库中执行的对象恢复也会被同步到逻辑备库。

使用RMAN备份来恢复表与表分区的限制
当使用recover命令与RMAN备份来恢复表或表分区时存在以下限制:
.sys用户方案中的表与表分区不能被恢复。
.system与sysaux表空间中的表与表分区不能被恢复。
.备库中的表与表分区不能被恢复。
.有not null约束的表在使用remap选项时不能被恢复。

恢复表与表分区所需要的准备工作
使用RMAN备份来恢复表或表分区所需要的准备工作如下:
.验证恢复表或表分区所需要的条件是否满足。
.判断表或表分区需要被恢复到的时间点。
.决定是否要将被恢复的表或表分区导入到目标数据库中。缺省情况下，RMAN会将被恢复的表或表分区导入到目标数据库中。然而可以指定RMAN不志入被恢复的对象。
.决定是否要对被恢复的表或表分区进行重命名，映射到新表空间或映射到新用户方案。

使用RMAN备份恢复表与表分区的先决条件
.目标数据库必须处于读写状态。
.目标数据库必须处于归档模式。
.对于这些对象所恢复的时间点来说被恢复的表或表分区必须有RMAN备份。
.为了恢复单个表分区，目标数据库的compatible参数必须被设置为11.1.0或更高版本。

判断表与表分区所要被恢复到的时间点
判断表或表分区所要被恢复到的时间点是非常重要的。RMAN可以使用以下一种方法来指定恢复时间点:
.SCN，将表或表分区恢复到由SCN所指定的时间点。
.Time(时间)，将表或表分区恢复到指定的时间点。所使用的日期格式是由NLS_LANG与NLS_DATE_FORMAT环境变量所组成的。也可以使用数据常量比如SYSDATE来指定时间，例如SYSDATE-30。
.Sequence number(日志序列号)，将表或表分区恢复到由日志序列号与日志线程号所指定的时间点。

恢复表与表分区
下面将描述对Non-CDB中的表或表分区恢复到指定时间点的操作步骤。
1.执行恢复表与表分区所需要的准备工作
.验证恢复表或表分区所需要的条件是否满足。
.判断表或表分区需要被恢复到的时间点。
.决定是否要将被恢复的表或表分区导入到目标数据库中。缺省情况下，RMAN会将被恢复的表或表分区导入到目标数据库中。然而可以指定RMAN不志入被恢复的对象。
.决定是否要对被恢复的表或表分区进行重命名，映射到新表空间或映射到新用户方案。

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

3.通过使用recover table命令将要被恢复的表或表分区恢复到指定的时间点。必须使用auxiliary destination子句与以下子句中的一个用来指定恢复时间点：until time,until scn或until sequence。在recover命令中还

可以使用以下子句:
.dump file与datapump destination，指定包含被恢复表或表分区的导出dump文件的文件名与存储位置。

.notableimport，指示被恢复的表或表分区不用导入到目标数据库。

.remap table，在目标数据库中将被恢复的表或表分区进行重命名。这个子句也可用来将原用户方案中的表或表分区恢复到新用户方案中。

.remap tablespace，将表与表分区恢复到与原始表空间不同的表空间中。

下面的例子使用RMAN备份对表t_emp进行按时间点恢复
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-1002:50:101rowselected.SQL>selectcurrent_scnfromv$database;CURRENT_SCN-----------3994111rowselected.SQL>deletefromt_emp;107rowsdeleted.SQL>commit;Commitcomplete.SQL>selectcount(*)fromt_emp;COUNT(*)----------01rowselected.

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

[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.dmp)与存储位置(/ora_xtts/dump)。使用notableimport子句指示被恢复的表或表分区不用导入到目标数据库。

RMAN>run2>{3>recovertablehr.t_emp4>untilscn3994115>auxiliarydestination'/ora_xtts/recover'6>datapumpdestination'/ora_xtts/dump'7>dumpfile't_emp.dmp'8>notableimport;9>}Startingrecoverat10-JAN-18usingchannelORA_DISK_1RMAN-05026:warning:presumingfollowingsetoftablespacesappliestospecifiedpoint-in-timeListoftablespacesexpectedtohaveUNDOsegmentsTablespaceSYSTEMTablespaceUNDOTBS1Creatingautomaticinstance,withSID='fcsj'initializationparametersusedforautomaticinstance:db_name=ORCLdb_unique_name=fcsj_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:{#setrequestedpointintimesetuntilscn399411;#restorethecontrolfilerestoreclonecontrolfile;#mountthecontrolfilesqlclone'alterdatabasemountclonedatabase';#archivecurrentonlinelogsql'altersystemarchivelogcurrent';}executingMemoryScriptexecutingcommand:SETuntilclauseStartingrestoreat10-JAN-18allocatedchannel: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-01channelORA_AUX_DISK_1:piecehandle=+DATA/backup/c-1492772871-20180110-01tag=TAG20180110T024913channelORA_AUX_DISK_1:restoredbackuppiece1channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:05outputfilename=/ora_xtts/recover/ORCL/controlfile/o1_mf_f5b4xmpo_.ctlFinishedrestoreat10-JAN-18sqlstatement:alterdatabasemountclonedatabasesqlstatement:altersystemarchivelogcurrentcontentsofMemoryScript:{#setrequestedpointintimesetuntilscn399411;#setdestinationsforrecoverysetandauxiliarysetdatafilessetnewnameforclonedatafile1tonew;setnewnameforclonedatafile3tonew;setnewnameforclonedatafile2tonew;setnewnameforclonetempfile1tonew;#switchalltempfilesswitchclonetempfileall;#restorethetablespacesintherecoverysetandtheauxiliarysetrestoreclonedatafile1,3,2;switchclonedatafileall;}executingMemoryScriptexecutingcommand:SETuntilclauseexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMErenamedtempfile1to/ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_%u_.tmpincontrolfileStartingrestoreat10-JAN-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_22so9q7p_1_1_965011705channelORA_AUX_DISK_1:piecehandle=+DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705tag=TAG20180110T024824channelORA_AUX_DISK_1:restoredbackuppiece1channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:45Finishedrestoreat10-JAN-18datafile1switchedtodatafilecopyinputdatafilecopyRECID=4STAMP=965013098filename=/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5b4xwvo_.dbfdatafile3switchedtodatafilecopyinputdatafilecopyRECID=5STAMP=965013098filename=/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5b4xwx2_.dbfdatafile2switchedtodatafilecopyinputdatafilecopyRECID=6STAMP=965013098filename=/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5b4xwwj_.dbfcontentsofMemoryScript:{#setrequestedpointintimesetuntilscn399411;#onlinethedatafilesrestoredorswitchedsqlclone"alterdatabasedatafile1online";sqlclone"alterdatabasedatafile3online";sqlclone"alterdatabasedatafile2online";#recoverandopendatabasereadonlyrecoverclonedatabasetablespace"SYSTEM","UNDOTBS1","SYSAUX";sqlclone'alterdatabaseopenreadonly';}executingMemoryScriptexecutingcommand:SETuntilclausesqlstatement:alterdatabasedatafile1onlinesqlstatement:alterdatabasedatafile3onlinesqlstatement:alterdatabasedatafile2onlineStartingrecoverat10-JAN-18usingchannelORA_AUX_DISK_1startingmediarecoveryarchivedlogforthread1withsequence19isalreadyondiskasfile+DATA/arch/orcl/1_19_964992135.dbfarchivedlogforthread1withsequence20isalreadyondiskasfile+DATA/arch/orcl/1_20_964992135.dbfarchivedlogfilename=+DATA/arch/orcl/1_19_964992135.dbfthread=1sequence=19archivedlogfilename=+DATA/arch/orcl/1_20_964992135.dbfthread=1sequence=20mediarecoverycomplete,elapsedtime:00:00:00Finishedrecoverat10-JAN-18sqlstatement:alterdatabaseopenreadonlycontentsofMemoryScript:{sqlclone"createspfilefrommemory";shutdowncloneimmediate;startupclonenomount;sqlclone"altersystemsetcontrol_files=''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5b4xmpo_.ctl''comment=''RMANset''scope=spfile";shutdowncloneimmediate;startupclonenomount;#mountdatabasesqlclone'alterdatabasemountclonedatabase';}executingMemoryScriptsqlstatement:createspfilefrommemorydatabasecloseddatabasedismountedOracleinstanceshutdownconnectedtoauxiliarydatabase(notstarted)OracleinstancestartedTotalSystemGlobalArea1073741824bytesFixedSize8628936bytesVariableSize293602616bytesDatabaseBuffers763363328bytesRedoBuffers8146944bytessqlstatement:altersystemsetcontrol_files=''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5b4xmpo_.ctl''comment=''RMANset''scope=spfileOracleinstanceshutdownconnectedtoauxiliarydatabase(notstarted)OracleinstancestartedTotalSystemGlobalArea1073741824bytesFixedSize8628936bytesVariableSize293602616bytesDatabaseBuffers763363328bytesRedoBuffers8146944bytessqlstatement:alterdatabasemountclonedatabasecontentsofMemoryScript:{#setrequestedpointintimesetuntilscn399411;#setdestinationsforrecoverysetandauxiliarysetdatafilessetnewnamefordatafile4tonew;#restorethetablespacesintherecoverysetandtheauxiliarysetrestoreclonedatafile4;switchclonedatafileall;}executingMemoryScriptexecutingcommand:SETuntilclauseexecutingcommand:SETNEWNAMEStartingrestoreat10-JAN-18allocatedchannel: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/FCSJ_PITR_ORCL/datafile/o1_mf_users_%u_.dbfchannelORA_AUX_DISK_1:readingfrombackuppiece+DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705channelORA_AUX_DISK_1:piecehandle=+DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705tag=TAG20180110T024824channelORA_AUX_DISK_1:restoredbackuppiece1channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:01:15Finishedrestoreat10-JAN-18datafile4switchedtodatafilecopyinputdatafilecopyRECID=8STAMP=965013242filename=/ora_xtts/recover/FCSJ_PITR_ORCL/datafile/o1_mf_users_f5b51h8p_.dbfcontentsofMemoryScript:{#setrequestedpointintimesetuntilscn399411;#onlinethedatafilesrestoredorswitchedsqlclone"alterdatabasedatafile4online";#recoverandopenresetlogsrecoverclonedatabasetablespace"USERS","SYSTEM","UNDOTBS1","SYSAUX"deletearchivelog;alterclonedatabaseopenresetlogs;}executingMemoryScriptexecutingcommand:SETuntilclausesqlstatement:alterdatabasedatafile4onlineStartingrecoverat10-JAN-18usingchannelORA_AUX_DISK_1startingmediarecoveryarchivedlogforthread1withsequence19isalreadyondiskasfile+DATA/arch/orcl/1_19_964992135.dbfarchivedlogforthread1withsequence20isalreadyondiskasfile+DATA/arch/orcl/1_20_964992135.dbfarchivedlogfilename=+DATA/arch/orcl/1_19_964992135.dbfthread=1sequence=19archivedlogfilename=+DATA/arch/orcl/1_20_964992135.dbfthread=1sequence=20mediarecoverycomplete,elapsedtime:00:00:01Finishedrecoverat10-JAN-18databaseopenedcontentsofMemoryScript:{#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_fcsj_pkfh":EXPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATAEXPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSEXPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSEXPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/MARKEREXPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLEEXPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/INDEXEXPDP>..exported"HR"."T_EMP"17.08KB107rowsEXPDP>Mastertable"SYS"."TSPITR_EXP_fcsj_pkfh"successfullyloaded/unloadedEXPDP>******************************************************************************EXPDP>DumpfilesetforSYS.TSPITR_EXP_fcsj_pkfhis:EXPDP>/ora_xtts/dump/t_emp.dmpEXPDP>Job"SYS"."TSPITR_EXP_fcsj_pkfh"successfullycompletedatWedJan1003:15:082018elapsed000:00:32ExportcompletedNotperformingtableimportafterpoint-in-timerecoveryRemovingautomaticinstanceshuttingdownautomaticinstanceOracleinstanceshutdownAutomaticinstanceremovedauxiliaryinstancefile/ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_f5b4zhf9_.tmpdeletedauxiliaryinstancefile/ora_xtts/recover/FCSJ_PITR_ORCL/onlinelog/o1_mf_3_f5b53yp4_.logdeletedauxiliaryinstancefile/ora_xtts/recover/FCSJ_PITR_ORCL/onlinelog/o1_mf_2_f5b53yol_.logdeletedauxiliaryinstancefile/ora_xtts/recover/FCSJ_PITR_ORCL/onlinelog/o1_mf_1_f5b53ynw_.logdeletedauxiliaryinstancefile/ora_xtts/recover/FCSJ_PITR_ORCL/datafile/o1_mf_users_f5b51h8p_.dbfdeletedauxiliaryinstancefile/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5b4xwwj_.dbfdeletedauxiliaryinstancefile/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5b4xwx2_.dbfdeletedauxiliaryinstancefile/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5b4xwvo_.dbfdeletedauxiliaryinstancefile/ora_xtts/recover/ORCL/controlfile/o1_mf_f5b4xmpo_.ctldeletedFinishedrecoverat10-JAN-18

如果会使用remap table子句将hr.t_emp恢复成jy.t_emp_recvr。使用remap tablespace子句将表t_emp从users表空间恢复到usertbs表空间。那么可以执行下面的命令来进行恢复就不需要执行步骤5

RMAN>run2>{3>recovertablehr.t_emp4>untilscn3994115>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>}Startingrecoverat10-JAN-18currentlogarchivedusingchannelORA_DISK_1RMAN-05026:warning:presumingfollowingsetoftablespacesappliestospecifiedpoint-in-timeListoftablespacesexpectedtohaveUNDOsegmentsTablespaceSYSTEMTablespaceUNDOTBS1Creatingautomaticinstance,withSID='jzdF'initializationparametersusedforautomaticinstance:db_name=ORCLdb_unique_name=jzdF_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:{#setrequestedpointintimesetuntilscn399411;#restorethecontrolfilerestoreclonecontrolfile;#mountthecontrolfilesqlclone'alterdatabasemountclonedatabase';#archivecurrentonlinelogsql'altersystemarchivelogcurrent';}executingMemoryScriptexecutingcommand:SETuntilclauseStartingrestoreat10-JAN-18allocatedchannel: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_f5d2402v_.ctlFinishedrestoreat10-JAN-18sqlstatement:alterdatabasemountclonedatabasesqlstatement:altersystemarchivelogcurrentcontentsofMemoryScript:{#setrequestedpointintimesetuntilscn399411;#setdestinationsforrecoverysetandauxiliarysetdatafilessetnewnameforclonedatafile1tonew;setnewnameforclonedatafile3tonew;setnewnameforclonedatafile2tonew;setnewnameforclonetempfile1tonew;#switchalltempfilesswitchclonetempfileall;#restorethetablespacesintherecoverysetandtheauxiliarysetrestoreclonedatafile1,3,2;switchclonedatafileall;}executingMemoryScriptexecutingcommand:SETuntilclauseexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMErenamedtempfile1to/ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_%u_.tmpincontrolfileStartingrestoreat10-JAN-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:45Finishedrestoreat10-JAN-18datafile1switchedtodatafilecopyinputdatafilecopyRECID=4STAMP=965075765filename=/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5d248q8_.dbfdatafile3switchedtodatafilecopyinputdatafilecopyRECID=5STAMP=965075766filename=/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5d248r7_.dbfdatafile2switchedtodatafilecopyinputdatafilecopyRECID=6STAMP=965075766filename=/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5d248qv_.dbfcontentsofMemoryScript:{#setrequestedpointintimesetuntilscn399411;#onlinethedatafilesrestoredorswitchedsqlclone"alterdatabasedatafile1online";sqlclone"alterdatabasedatafile3online";sqlclone"alterdatabasedatafile2online";#recoverandopendatabasereadonlyrecoverclonedatabasetablespace"SYSTEM","UNDOTBS1","SYSAUX";sqlclone'alterdatabaseopenreadonly';}executingMemoryScriptexecutingcommand:SETuntilclausesqlstatement:alterdatabasedatafile1onlinesqlstatement:alterdatabasedatafile3onlinesqlstatement:alterdatabasedatafile2onlineStartingrecoverat10-JAN-18usingchannelORA_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:01Finishedrecoverat10-JAN-18sqlstatement:alterdatabaseopenreadonlycontentsofMemoryScript:{sqlclone"createspfilefrommemory";shutdowncloneimmediate;startupclonenomount;sqlclone"altersystemsetcontrol_files=''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d2402v_.ctl''comment=''RMANset''scope=spfile";shutdowncloneimmediate;startupclonenomount;#mountdatabasesqlclone'alterdatabasemountclonedatabase';}executingMemoryScriptsqlstatement:createspfilefrommemorydatabasecloseddatabasedismountedOracleinstanceshutdownconnectedtoauxiliarydatabase(notstarted)OracleinstancestartedTotalSystemGlobalArea1073741824bytesFixedSize8628936bytesVariableSize293602616bytesDatabaseBuffers763363328bytesRedoBuffers8146944bytessqlstatement:altersystemsetcontrol_files=''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d2402v_.ctl''comment=''RMANset''scope=spfileOracleinstanceshutdownconnectedtoauxiliarydatabase(notstarted)OracleinstancestartedTotalSystemGlobalArea1073741824bytesFixedSize8628936bytesVariableSize293602616bytesDatabaseBuffers763363328bytesRedoBuffers8146944bytessqlstatement:alterdatabasemountclonedatabasecontentsofMemoryScript:{#setrequestedpointintimesetuntilscn399411;#setdestinationsforrecoverysetandauxiliarysetdatafilessetnewnamefordatafile4tonew;#restorethetablespacesintherecoverysetandtheauxiliarysetrestoreclonedatafile4;switchclonedatafileall;}executingMemoryScriptexecutingcommand:SETuntilclauseexecutingcommand:SETNEWNAMEStartingrestoreat10-JAN-18allocatedchannel: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/JZDF_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:55Finishedrestoreat10-JAN-18datafile4switchedtodatafilecopyinputdatafilecopyRECID=8STAMP=965075892filename=/ora_xtts/recover/JZDF_PITR_ORCL/datafile/o1_mf_users_f5d27wvd_.dbfcontentsofMemoryScript:{#setrequestedpointintimesetuntilscn399411;#onlinethedatafilesrestoredorswitchedsqlclone"alterdatabasedatafile4online";#recoverandopenresetlogsrecoverclonedatabasetablespace"USERS","SYSTEM","UNDOTBS1","SYSAUX"deletearchivelog;alterclonedatabaseopenresetlogs;}executingMemoryScriptexecutingcommand:SETuntilclausesqlstatement:alterdatabasedatafile4onlineStartingrecoverat10-JAN-18usingchannelORA_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:00Finishedrecoverat10-JAN-18databaseopenedcontentsofMemoryScript:{#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_jzdF_fxiC":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_jzdF_fxiC"successfullyloaded/unloadedEXPDP>******************************************************************************EXPDP>DumpfilesetforSYS.TSPITR_EXP_jzdF_fxiCis:EXPDP>/ora_xtts/dump/t_emp_recvr.dmpEXPDP>Job"SYS"."TSPITR_EXP_jzdF_fxiC"successfullycompletedatWedJan1020:39:092018elapsed000:00:32ExportcompletedcontentsofMemoryScript:{#shutdownclonebeforeimportshutdowncloneabort}executingMemoryScriptOracleinstanceshutdownPerformingimportoftables...IMPDP>Mastertable"SYS"."TSPITR_IMP_jzdF_BDce"successfullyloaded/unloadedIMPDP>Starting"SYS"."TSPITR_IMP_jzdF_BDce":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_jzdF_BDce"successfullycompletedatWedJan1020:39:472018elapsed000:00:31ImportcompletedRemovingautomaticinstanceAutomaticinstanceremovedauxiliaryinstancefile/ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_f5d25tp8_.tmpdeletedauxiliaryinstancefile/ora_xtts/recover/JZDF_PITR_ORCL/onlinelog/o1_mf_3_f5d29sj0_.logdeletedauxiliaryinstancefile/ora_xtts/recover/JZDF_PITR_ORCL/onlinelog/o1_mf_2_f5d29shf_.logdeletedauxiliaryinstancefile/ora_xtts/recover/JZDF_PITR_ORCL/onlinelog/o1_mf_1_f5d29sgs_.logdeletedauxiliaryinstancefile/ora_xtts/recover/JZDF_PITR_ORCL/datafile/o1_mf_users_f5d27wvd_.dbfdeletedauxiliaryinstancefile/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5d248qv_.dbfdeletedauxiliaryinstancefile/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5d248r7_.dbfdeletedauxiliaryinstancefile/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5d248q8_.dbfdeletedauxiliaryinstancefile/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d2402v_.ctldeletedauxiliaryinstancefilet_emp_recvr.dmpdeletedFinishedrecoverat10-JAN-18

5.通过t_emp.dmp文件将表t_emp中的数据导入

[oracle@jytest3dump]$impdphr/hr@orcldumpfile=dump_dir:t_emp.dmpImport:Release12.2.0.1.0-ProductiononWedJan1003:23:342018Copyright(c)1982,2017,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProductionMastertable"HR"."SYS_IMPORT_FULL_01"successfullyloaded/unloadedStarting"HR"."SYS_IMPORT_FULL_01":hr/********@orcldumpfile=dump_dir:t_emp.dmpProcessingobjecttypeTABLE_EXPORT/TABLE/TABLEORA-39151:Table"HR"."T_EMP"exists.Alldependentmetadataanddatawillbeskippedduetotable_exists_actionofskipProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATAProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/INDEXProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/MARKERJob"HR"."SYS_IMPORT_FULL_01"completedwith1error(s)atWedJan1003:23:512018elapsed000:00:10

报错是因为表t_emp已经存在，impdp的缺省操作就是跳过对这张表进行导入操作，所以需要使用选项table_exists_action=truncate来进行导入。

[oracle@jytest3dump]$impdphr/hr@orcldumpfile=dump_dir:t_emp.dmptable_exists_action=truncateImport:Release12.2.0.1.0-ProductiononWedJan1003:29:102018Copyright(c)1982,2017,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProductionMastertable"HR"."SYS_IMPORT_FULL_01"successfullyloaded/unloadedStarting"HR"."SYS_IMPORT_FULL_01":hr/********@orcldumpfile=dump_dir:t_emp.dmptable_exists_action=truncateProcessingobjecttypeTABLE_EXPORT/TABLE/TABLETable"HR"."T_EMP"existsandhasbeentruncated.Datawillbeloadedbutalldependentmetadatawillbeskippedduetotable_exists_actionoftruncateProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA..imported"HR"."T_EMP"17.08KB107rowsProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/INDEXProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/MARKERJob"HR"."SYS_IMPORT_FULL_01"successfullycompletedatWedJan1003:29:302018elapsed000:00:18

6.验证表t_emp中的记录，可以看到已经成功恢复到被删了记录之前的状态。

SQL>selectcount(*)fromt_emp;COUNT(*)----------107

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