Oracle 12C如何使用RMAN将Non-CDB多个用户方案中的多个表或表分区恢复到新用户方案中

这篇文章主要介绍Oracle 12C如何使用RMAN将Non-CDB多个用户方案中的多个表或表分区恢复到新用户方案中，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

Oracle 12C使用RMAN将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之前记录当前时间,在执行恢复时它们被用来指定恢复时间点

SQL> select count(*) from cs.t1;  COUNT(*)----------         8SQL> select count(*) from hr.t1;  COUNT(*)----------         81 row selected.SQL> select sysdate from dual;SYSDATE-------------------2018-01-10 22:56:161 row selected.SQL> truncate table cs.t1;Table truncated.SQL> truncate table hr.t1;Table truncated.SQL> select count(*) from cs.t1;  COUNT(*)----------         0SQL> select count(*) from hr.t1;  COUNT(*)----------         0

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.t1恢复成jy.t1_1,cs.t1恢复成jy.t1_2。使用remap tablespace子句将hr与cs用户方案中的表t1从恢复到jy用户方案中

RMAN> run2> {3> recover table hr.t1,cs.t14> until time '2018-01-10 22:55:39'5> auxiliary destination '/ora_xtts/recover'6> datapump destination '/ora_xtts/dump'7> dump file 't_emp_recvr.dmp'8> remap table 'HR'.'T1':'JY'.'T1_1','CS'.'T1':'JY'.'T1_2'9> remap tablespace 'USERS':'USERTBS';10> }Starting recover at 2018-01-10 23:26:43current log archivedusing 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='fvtm'initialization parameters used for automatic instance:db_name=ORCLdb_unique_name=fvtm_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  time "2018-01-10 22:55:39";# 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 23:27:59allocated 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_f5dd84jp_.ctlFinished restore at 2018-01-10 23:28:05sql statement: alter database mount clone databasesql statement: alter system archive log currentcontents of Memory Script:{# set requested point in timeset until  time "2018-01-10 22:55:39";# 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 23:28:15using 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 23:29:02datafile 1 switched to datafile copyinput datafile copy RECID=4 STAMP=965086142 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5dd8k64_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=5 STAMP=965086142 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5dd8k8j_.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=6 STAMP=965086142 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5dd8k7d_.dbfcontents of Memory Script:{# set requested point in timeset until  time "2018-01-10 22:55:39";# 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 23:29:03using 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 for thread 1 with sequence 30 is already on disk as file +DATA/arch/orcl/1_30_964992135.dbfarchived log for thread 1 with sequence 31 is already on disk as file +DATA/arch/orcl/1_31_964992135.dbfarchived log for thread 1 with sequence 32 is already on disk as file +DATA/arch/orcl/1_32_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=29archived log file name=+DATA/arch/orcl/1_30_964992135.dbf thread=1 sequence=30archived log file name=+DATA/arch/orcl/1_31_964992135.dbf thread=1 sequence=31archived log file name=+DATA/arch/orcl/1_32_964992135.dbf thread=1 sequence=32media recovery complete, elapsed time: 00:00:34Finished recover at 2018-01-10 23:29:40sql 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_f5dd84jp_.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_f5dd84jp_.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  time "2018-01-10 22:55:39";# 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 23:30:43allocated 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/FVTM_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:01:05Finished restore at 2018-01-10 23:31:50datafile 4 switched to datafile copyinput datafile copy RECID=8 STAMP=965086310 file name=/ora_xtts/recover/FVTM_PITR_ORCL/datafile/o1_mf_users_f5ddf560_.dbfcontents of Memory Script:{# set requested point in timeset until  time "2018-01-10 22:55:39";# 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 23:31:50using 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 for thread 1 with sequence 30 is already on disk as file +DATA/arch/orcl/1_30_964992135.dbfarchived log for thread 1 with sequence 31 is already on disk as file +DATA/arch/orcl/1_31_964992135.dbfarchived log for thread 1 with sequence 32 is already on disk as file +DATA/arch/orcl/1_32_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=29archived log file name=+DATA/arch/orcl/1_30_964992135.dbf thread=1 sequence=30archived log file name=+DATA/arch/orcl/1_31_964992135.dbf thread=1 sequence=31archived log file name=+DATA/arch/orcl/1_32_964992135.dbf thread=1 sequence=32media recovery complete, elapsed time: 00:00:05Finished recover at 2018-01-10 23:31:58database 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_fvtm_ikCg":   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 "CS"."T1"                                   39.66 KB       8 rows   EXPDP> . . exported "HR"."T1"                                   39.66 KB       8 rows   EXPDP> Master table "SYS"."TSPITR_EXP_fvtm_ikCg" successfully loaded/unloaded   EXPDP> ******************************************************************************   EXPDP> Dump file set for SYS.TSPITR_EXP_fvtm_ikCg is:   EXPDP>   /ora_xtts/dump/t_emp_recvr.dmp   EXPDP> Job "SYS"."TSPITR_EXP_fvtm_ikCg" successfully completed at Wed Jan 10 23:32:53 2018 elapsed 0 00:00:34Export 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_fvtm_Dybh" successfully loaded/unloaded   IMPDP> Starting "SYS"."TSPITR_IMP_fvtm_Dybh":   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA   IMPDP> . . imported "JY"."T1_2"                                 39.66 KB       8 rows   IMPDP> . . imported "JY"."T1_1"                                 39.66 KB       8 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_fvtm_Dybh" successfully completed at Wed Jan 10 23:33:32 2018 elapsed 0 00:00:30Import completedRemoving automatic instanceAutomatic instance removedauxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_f5ddc523_.tmp deletedauxiliary instance file /ora_xtts/recover/FVTM_PITR_ORCL/onlinelog/o1_mf_3_f5ddhgrn_.log deletedauxiliary instance file /ora_xtts/recover/FVTM_PITR_ORCL/onlinelog/o1_mf_2_f5ddhgqw_.log deletedauxiliary instance file /ora_xtts/recover/FVTM_PITR_ORCL/onlinelog/o1_mf_1_f5ddhgq8_.log deletedauxiliary instance file /ora_xtts/recover/FVTM_PITR_ORCL/datafile/o1_mf_users_f5ddf560_.dbf deletedauxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5dd8k7d_.dbf deletedauxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5dd8k8j_.dbf deletedauxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_system_f5dd8k64_.dbf deletedauxiliary instance file /ora_xtts/recover/ORCL/controlfile/o1_mf_f5dd84jp_.ctl deletedauxiliary instance file t_emp_recvr.dmp deletedFinished recover at 2018-01-10 23:33:36

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

SQL> select count(*) from jy.t1_1;  COUNT(*)----------         8SQL> select count(*) from jy.t1_2;  COUNT(*)----------         8

以上是"Oracle 12C如何使用RMAN将Non-CDB多个用户方案中的多个表或表分区恢复到新用户方案中"这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注行业资讯频道！