Oracle 12.1新特性----使用RMAN从备份中实现recover table

在Oracle12c版本之前，使用RMAN能恢复的级别为数据库级别和表空间级别，如果只有一张表需要恢复，而在数据库级别或表空间级别做恢复，影响范围就太大了。因此12.2版本中提供了一个新特性使用RMAN在表级别做恢复，并且恢复过程中不影响数据库的正常使用。这一功能不仅可以恢复表，还可以恢复表分区。

To recover a table or table partition, you must have a full backup of undo, SYSTEM, SYSAUX, and the tablespace that contains the table or table partition.

使用recover table的一些限制条件：

When you use the RECOVER command to recover tables or table partitions contained in an RMAN backup, the following limitations exist.

• Tables and table partitions belonging to SYS schema cannot be recovered.

• Tables and table partitions from SYSTEM and SYSAUX tablespaces cannot be recovered.

• Tables and table partitions on standby databases cannot be recovered.

• Tables with named NOT NULL constraints cannot be recovered with the REMAP option.

下面在12.2版本上做表级别恢复的实验

sys@ORA12C>select * from v$version;BANNER                                                                               CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production                 0PL/SQL Release 12.2.0.1.0 - Production                                                        0CORE    12.2.0.1.0      Production                                                                0TNS for Linux: Version 12.2.0.1.0 - Production                                              0NLSRTL Version 12.2.0.1.0 - Production                                                        0

1、创建一个数据库的全备

RMAN> backup database;Starting backup at 20170720 17:12:05using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=36 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=+DATA/ORA12C/DATAFILE/system.256.949764433channel ORA_DISK_1: starting piece 1 at 20170720 17:12:07channel ORA_DISK_1: finished piece 1 at 20170720 17:12:22piece handle=/u01/app/oracle/product/12.2/db_home1/dbs/0vs9rar7_1_1 tag=TAG20170720T171206 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00004 name=+DATA/ORA12C/DATAFILE/users.266.949764465channel ORA_DISK_1: starting piece 1 at 20170720 17:12:22channel ORA_DISK_1: finished piece 1 at 20170720 17:12:23piece handle=/u01/app/oracle/product/12.2/db_home1/dbs/10s9rarm_1_1 tag=TAG20170720T171206 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00002 name=+DATA/ORA12C/DATAFILE/sysaux.261.949764491channel ORA_DISK_1: starting piece 1 at 20170720 17:12:23channel ORA_DISK_1: finished piece 1 at 20170720 17:12:38piece handle=/u01/app/oracle/product/12.2/db_home1/dbs/11s9rarn_1_1 tag=TAG20170720T171206 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00005 name=+DATA/ORA12C/DATAFILE/examples.265.949764515channel ORA_DISK_1: starting piece 1 at 20170720 17:12:38channel ORA_DISK_1: finished piece 1 at 20170720 17:12:39piece handle=/u01/app/oracle/product/12.2/db_home1/dbs/12s9ras6_1_1 tag=TAG20170720T171206 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00008 name=/tmp/FY_RST_DATA.DATinput datafile file number=00007 name=/tmp/FY_REC_DATA.DATinput datafile file number=00003 name=+DATA/ORA12C/DATAFILE/undotbs1.264.949764541input datafile file number=00006 name=+DATA/ORA12C/DATAFILE/t_move.dbfchannel ORA_DISK_1: starting piece 1 at 20170720 17:12:40channel ORA_DISK_1: finished piece 1 at 20170720 17:12:41piece handle=/u01/app/oracle/product/12.2/db_home1/dbs/13s9ras8_1_1 tag=TAG20170720T171206 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 20170720 17:12:41Starting Control File and SPFILE Autobackup at 20170720 17:12:41piece handle=/u01/app/oracle/product/12.2/db_home1/dbs/c-326793150-20170720-00 comment=NONEFinished Control File and SPFILE Autobackup at 20170720 17:12:42

2、创建测试表，插入测试数据并记录中间的scn号

zx@ORA12C>create table t2 (id number,name varchar2(10),birthday date);Table created.zx@ORA12C>insert into t2 values(1,'zx',sysdate);1 row created.zx@ORA12C>commit;Commit complete.zx@ORA12C>select current_scn from v$database;CURRENT_SCN-----------     650101zx@ORA12C>insert into t2 values(2,'lx',sysdate);1 row created.zx@ORA12C>commit;Commit complete.zx@ORA12C>select * from t2;        ID NAME       BIRTHDAY---------- ---------- -----------------         1 zx       20170720 17:18:52         2 lx       20170720 17:19:34

3、执行表级别恢复，使用remap table参数不覆盖原表，恢复成t2_r表

RMAN> recover table zx.t2 until scn 650101 remap table zx.t2:t2_r auxiliary destination '/tmp';Starting recover at 20170720 17:23:50current 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='htzD'initialization parameters used for automatic instance:db_name=ORA12Cdb_unique_name=htzD_pitr_ORA12Ccompatible=12.2.0db_block_size=8192db_files=200diagnostic_dest=/u01/app/oracle_system_trig_enabled=FALSEsga_target=1712Mprocesses=200db_create_file_dest=/tmplog_archive_dest_1='location=/tmp'#No auxiliary parameter file usedstarting up automatic instance ORA12COracle instance startedTotal System Global Area    1795162112 bytesFixed Size                     8621760 bytesVariable Size                436207936 bytesDatabase Buffers            1342177280 bytesRedo Buffers                   8155136 bytesAutomatic instance createdcontents of Memory Script:{# set requested point in timeset until  scn 650101;# restore the controlfilerestore clone controlfile; # mount the controlfilesql clone 'alter database mount clone database'; # archive current online log sql 'alter system archive log current';}executing Memory Scriptexecuting command: SET until clauseStarting restore at 20170720 17:24:12allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=18 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 /u01/app/oracle/product/12.2/db_home1/dbs/c-326793150-20170720-00channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2/db_home1/dbs/c-326793150-20170720-00 tag=TAG20170720T171241channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01output file name=/tmp/ORA12C/controlfile/o1_mf_dq0xoxot_.ctlFinished restore at 20170720 17:24:14sql statement: alter database mount clone databasesql statement: alter system archive log currentcontents of Memory Script:{# set requested point in timeset until  scn 650101;# 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 /tmp/ORA12C/datafile/o1_mf_temp_%u_.tmp in control fileStarting restore at 20170720 17:24:19using 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 /tmp/ORA12C/datafile/o1_mf_system_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2/db_home1/dbs/0vs9rar7_1_1channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2/db_home1/dbs/0vs9rar7_1_1 tag=TAG20170720T171206channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45channel 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 00002 to /tmp/ORA12C/datafile/o1_mf_sysaux_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2/db_home1/dbs/11s9rarn_1_1channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2/db_home1/dbs/11s9rarn_1_1 tag=TAG20170720T171206channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35channel 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 00003 to /tmp/ORA12C/datafile/o1_mf_undotbs1_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2/db_home1/dbs/13s9ras8_1_1channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2/db_home1/dbs/13s9ras8_1_1 tag=TAG20170720T171206channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15Finished restore at 20170720 17:25:55datafile 1 switched to datafile copyinput datafile copy RECID=16 STAMP=949857956 file name=/tmp/ORA12C/datafile/o1_mf_system_dq0xp4jt_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=17 STAMP=949857956 file name=/tmp/ORA12C/datafile/o1_mf_undotbs1_dq0xrnq2_.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=18 STAMP=949857956 file name=/tmp/ORA12C/datafile/o1_mf_sysaux_dq0xqkm0_.dbfcontents of Memory Script:{# set requested point in timeset until  scn 650101;# 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 20170720 17:26:02using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/12.2/db_home1/dbs/arch2_2_949077789.dbfarchived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/product/12.2/db_home1/dbs/arch2_3_949077789.dbfarchived log file name=/u01/app/oracle/product/12.2/db_home1/dbs/arch2_2_949077789.dbf thread=1 sequence=2archived log file name=/u01/app/oracle/product/12.2/db_home1/dbs/arch2_3_949077789.dbf thread=1 sequence=3media recovery complete, elapsed time: 00:00:02Finished recover at 20170720 17:26:07sql 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 =   ''/tmp/ORA12C/controlfile/o1_mf_dq0xoxot_.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    1795162112 bytesFixed Size                     8621760 bytesVariable Size                436207936 bytesDatabase Buffers            1342177280 bytesRedo Buffers                   8155136 bytessql statement: alter system set  control_files =   ''/tmp/ORA12C/controlfile/o1_mf_dq0xoxot_.ctl'' comment= ''RMAN set'' scope=spfileOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area    1795162112 bytesFixed Size                     8621760 bytesVariable Size                436207936 bytesDatabase Buffers            1342177280 bytesRedo Buffers                   8155136 bytessql statement: alter database mount clone databasecontents of Memory Script:{# set requested point in timeset until  scn 650101;# 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 20170720 17:27:18allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=23 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 /tmp/HTZD_PITR_ORA12C/datafile/o1_mf_users_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2/db_home1/dbs/10s9rarm_1_1channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2/db_home1/dbs/10s9rarm_1_1 tag=TAG20170720T171206channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25Finished restore at 20170720 17:27:44datafile 4 switched to datafile copyinput datafile copy RECID=20 STAMP=949858064 file name=/tmp/HTZD_PITR_ORA12C/datafile/o1_mf_users_dq0xvq9q_.dbfcontents of Memory Script:{# set requested point in timeset until  scn 650101;# 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 20170720 17:27:44using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/12.2/db_home1/dbs/arch2_2_949077789.dbfarchived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/product/12.2/db_home1/dbs/arch2_3_949077789.dbfarchived log file name=/u01/app/oracle/product/12.2/db_home1/dbs/arch2_2_949077789.dbf thread=1 sequence=2archived log file name=/u01/app/oracle/product/12.2/db_home1/dbs/arch2_3_949077789.dbf thread=1 sequence=3media recovery complete, elapsed time: 00:00:01Finished recover at 20170720 17:27:48database openedcontents of Memory Script:{# create directory for datapump importsql "create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp''";# create directory for datapump exportsql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp''";}executing Memory Scriptsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp''sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp''Performing export of tables...   EXPDP> Starting "SYS"."TSPITR_EXP_htzD_mhEh":     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/TABLE   EXPDP> . . exported "ZX"."T2"                                   5.898 KB       1 rows   EXPDP> Master table "SYS"."TSPITR_EXP_htzD_mhEh" successfully loaded/unloaded   EXPDP> ******************************************************************************   EXPDP> Dump file set for SYS.TSPITR_EXP_htzD_mhEh is:   EXPDP>   /tmp/tspitr_htzD_98436.dmp   EXPDP> Job "SYS"."TSPITR_EXP_htzD_mhEh" successfully completed at Thu Jul 20 17:29:48 2017 elapsed 0 00:01:05Export 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_htzD_bhqf" successfully loaded/unloaded   IMPDP> Starting "SYS"."TSPITR_IMP_htzD_bhqf":     IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA   IMPDP> . . imported "ZX"."T2_R"                                 5.898 KB       1 rows   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS   IMPDP> Job "SYS"."TSPITR_IMP_htzD_bhqf" successfully completed at Thu Jul 20 17:30:10 2017 elapsed 0 00:00:10Import completedRemoving automatic instanceAutomatic instance removedauxiliary instance file /tmp/ORA12C/datafile/o1_mf_temp_dq0xskvm_.tmp deletedauxiliary instance file /tmp/HTZD_PITR_ORA12C/onlinelog/o1_mf_3_dq0xwo3d_.log deletedauxiliary instance file /tmp/HTZD_PITR_ORA12C/onlinelog/o1_mf_2_dq0xwo3d_.log deletedauxiliary instance file /tmp/HTZD_PITR_ORA12C/onlinelog/o1_mf_1_dq0xwo0q_.log deletedauxiliary instance file /tmp/HTZD_PITR_ORA12C/datafile/o1_mf_users_dq0xvq9q_.dbf deletedauxiliary instance file /tmp/ORA12C/datafile/o1_mf_sysaux_dq0xqkm0_.dbf deletedauxiliary instance file /tmp/ORA12C/datafile/o1_mf_undotbs1_dq0xrnq2_.dbf deletedauxiliary instance file /tmp/ORA12C/datafile/o1_mf_system_dq0xp4jt_.dbf deletedauxiliary instance file /tmp/ORA12C/controlfile/o1_mf_dq0xoxot_.ctl deletedauxiliary instance file tspitr_htzD_98436.dmp deletedFinished recover at 20170720 17:30:13

4、验证结果，查询t2_r表

zx@ORA12C>select * from t2_r;        ID NAME       BIRTHDAY---------- ---------- -----------------         1 zx       20170720 17:18:52

5、即使表t2做了DDL操作修改了表结构，也可以用这种方法进行恢复

--表t2添加一个字段zx@ORA12C>alter table t2 add address varchar2(10);Table altered.--恢复表到t2_r2RMAN> recover table zx.t2 until scn 650101 remap table zx.t2:t2_r2 auxiliary destination '/tmp';Starting recover at 20170720 17:57:00using channel ORA_DISK_1RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time......Finished recover at 20170720 18:02:03--验证表t2_r2zx@ORA12C>select * from t2_r2;        ID NAME       BIRTHDAY---------- ---------- -----------------         1 zx       20170720 17:18:52         --恢复成功

这一功能很大程度减小了数据恢复的影响范围。

参考：http://docs.oracle.com/database/121/BRADV/rcmresind.htm#BRADV686