Oracle 学习之RMAN（十五）恢复实战--TSPITR

TSPITR是英文Tablespace Point In Time Recovery的缩写。也就是表空间定点时间恢复。TSPITR是一种相对细粒度的不完全恢复技术。我们通常见到的还原操作，都是将所有的表空间和数据还原到相同的一个时间点上。而TSPITR则是以表空间为粒度单元，单独将某个表空间内容还原到一个特定可恢复时间点上。举一个例子：一个Oracle数据库运行在归档模式下，在夜间零时保留一份完全备份。早上七点时候，某个特定表空间上数据表（单个表独占表空间）发生一个误操作，数据损坏。要求在不伤害其他数据表数据的情况下，将表空间数据恢复到早上六点。这样部分数据恢复的场景，就是TSPITR的典型应用。

1. 建立一个表空间

SQL> create tablespace tspitr datafile '/u01/app/oracle/oradata/devdb/tspitr01.dbf' size 20M;Tablespace created.

2. 在这个表空间上创建一个表，并插入数据

SQL> conn scott/tigerConnected.SQL> create table t_tspitr_test tablespace tspitr as select * from emp;Table created.SQL> select count(*) from t_tspitr_test;  COUNT(*)----------        14

3. 对数据库做一个备份

RMAN> backup database plus archivelog delete all input;Starting backup at 2015/07/09 14:40:29current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=3 RECID=1 STAMP=884613136channel ORA_DISK_1: starting piece 1 at 2015/07/09 14:40:30channel ORA_DISK_1: finished piece 1 at 2015/07/09 14:40:31piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_annnn_TAG20150709T144030_bsw5ty9m_.bkp tag=TAG20150709T144030 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: deleting archived log(s)archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2015_07_09/o1_mf_1_3_bsw30j7b_.arc RECID=1 STAMP=884613136channel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=1 RECID=2 STAMP=884616029channel ORA_DISK_1: starting piece 1 at 2015/07/09 14:40:31channel ORA_DISK_1: finished piece 1 at 2015/07/09 14:40:32piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_annnn_TAG20150709T144030_bsw5tzsf_.bkp tag=TAG20150709T144030 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: deleting archived log(s)archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2015_07_09/o1_mf_1_1_bsw5txwl_.arc RECID=2 STAMP=884616029Finished backup at 2015/07/09 14:40:32Starting backup at 2015/07/09 14:40:32using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/devdb/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/devdb/sysaux01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/devdb/example01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/devdb/undotbs01.dbfinput datafile file number=00006 name=/u01/app/oracle/oradata/devdb/tspitr01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/devdb/users01.dbfchannel ORA_DISK_1: starting piece 1 at 2015/07/09 14:40:33channel ORA_DISK_1: finished piece 1 at 2015/07/09 14:42:18piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_nnndf_TAG20150709T144032_bsw5v1cb_.bkp tag=TAG20150709T144032 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:45channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 2015/07/09 14:42:19channel ORA_DISK_1: finished piece 1 at 2015/07/09 14:42:20piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_ncsnf_TAG20150709T144032_bsw5ycl2_.bkp tag=TAG20150709T144032 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 2015/07/09 14:42:20Starting backup at 2015/07/09 14:42:20current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=2 RECID=3 STAMP=884616140channel ORA_DISK_1: starting piece 1 at 2015/07/09 14:42:20channel ORA_DISK_1: finished piece 1 at 2015/07/09 14:42:21piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_annnn_TAG20150709T144220_bsw5ydxj_.bkp tag=TAG20150709T144220 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: deleting archived log(s)archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2015_07_09/o1_mf_1_2_bsw5ydp6_.arc RECID=3 STAMP=884616140Finished backup at 2015/07/09 14:42:21

4. 我们做如下操作

SQL> select sequence#, status,sysdate from  v$log; SEQUENCE# STATUS     SYSDATE---------- ---------- -------------------         1 INACTIVE   2015/07/09 14:56:10         2 INACTIVE   2015/07/09 14:56:10         3 CURRENT    2015/07/09 14:56:10SQL> alter system switch logfile;System altered.SQL> select sequence#, status,sysdate from  v$log; SEQUENCE# STATUS     SYSDATE---------- ---------- -------------------         4 CURRENT    2015/07/09 14:57:20         2 INACTIVE   2015/07/09 14:57:20         3 ACTIVE     2015/07/09 14:57:20SQL> insert into scott.t_tspitr_test select * from scott.t_tspitr_test;14 rows created.SQL> select count(*) from scott.t_tspitr_test;  COUNT(*)----------        28SQL> alter system switch logfile;System altered.SQL> select sequence#, status,sysdate from  v$log; SEQUENCE# STATUS     SYSDATE---------- ---------- -------------------         4 ACTIVE     2015/07/09 14:59:11         5 CURRENT    2015/07/09 14:59:11         3 INACTIVE   2015/07/09 14:59:11SQL>

当恢复到日志编号为3时表中应该有14条数据。

4. 建立辅助恢复目录

11gdg-> mkdir /backup/aux

6.执行恢复

RMAN> RECOVER TABLESPACE 'TSPITR' UNTIL LOGSEQ 3  AUXILIARY DESTINATION '/backup/aux';Starting recover at 2015/07/09 15:16:06using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=47 device type=DISKRMAN-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='nkvk'initialization parameters used for automatic instance:db_name=DEVDBdb_unique_name=nkvk_tspitr_DEVDBcompatible=11.2.0.0.0db_block_size=8192db_files=200sga_target=280Mprocesses=50db_create_file_dest=/backup/auxlog_archive_dest_1='location=/backup/aux'#No auxiliary parameter file usedstarting up automatic instance DEVDBOracle instance startedTotal System Global Area     292278272 bytesFixed Size                     2227744 bytesVariable Size                100663776 bytesDatabase Buffers             184549376 bytesRedo Buffers                   4837376 bytesAutomatic instance createdRunning TRANSPORT_SET_CHECK on recovery set tablespacesTRANSPORT_SET_CHECK completed successfullycontents of Memory Script:{# set requested point in timeset until  logseq 3 thread 1;# restore the controlfilerestore clone controlfile;# mount the controlfilesql clone 'alter database mount clone database';# archive current online log sql 'alter system archive log current';# avoid unnecessary autobackups for structural changes during TSPITRsql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';}executing Memory Scriptexecuting command: SET until clauseStarting restore at 2015/07/09 15:16:22allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=19 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/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_ncsnf_TAG20150709T144032_bsw5ycl2_.bkpchannel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_ncsnf_TAG20150709T144032_bsw5ycl2_.bkp tag=TAG20150709T144032channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01output file name=/backup/aux/DEVDB/controlfile/o1_mf_bsw7y759_.ctlFinished restore at 2015/07/09 15:16:24sql statement: alter database mount clone databasesql statement: alter system archive log currentsql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;contents of Memory Script:{# set requested point in timeset until  logseq 3 thread 1;plsql <<<-- tspitr_2declare  sqlstatement       varchar2(512);  offline_not_needed exception;  pragma exception_init(offline_not_needed, -01539);begin  sqlstatement := 'alter tablespace '||  '"TSPITR"' ||' offline immediate';  krmicd.writeMsg(6162, sqlstatement);  krmicd.execSql(sqlstatement);exception  when offline_not_needed then    null;end; >>>;# 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;set newname for datafile  6 to  "/u01/app/oracle/oradata/devdb/tspitr01.dbf";# switch all tempfilesswitch clone tempfile all;# restore the tablespaces in the recovery set and the auxiliary setrestore clone datafile  1, 3, 2, 6;switch clone datafile all;}executing Memory Scriptexecuting command: SET until clausesql statement: alter tablespace "TSPITR" offline immediateexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed tempfile 1 to /backup/aux/DEVDB/datafile/o1_mf_temp_%u_.tmp in control fileStarting restore at 2015/07/09 15:16:29using 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 /backup/aux/DEVDB/datafile/o1_mf_system_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00003 to /backup/aux/DEVDB/datafile/o1_mf_undotbs1_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00002 to /backup/aux/DEVDB/datafile/o1_mf_sysaux_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/devdb/tspitr01.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_nnndf_TAG20150709T144032_bsw5v1cb_.bkpchannel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_nnndf_TAG20150709T144032_bsw5v1cb_.bkp tag=TAG20150709T144032channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05Finished restore at 2015/07/09 15:17:34datafile 1 switched to datafile copyinput datafile copy RECID=5 STAMP=884618255 file name=/backup/aux/DEVDB/datafile/o1_mf_system_bsw7yg0d_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=6 STAMP=884618255 file name=/backup/aux/DEVDB/datafile/o1_mf_undotbs1_bsw7yg16_.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=7 STAMP=884618255 file name=/backup/aux/DEVDB/datafile/o1_mf_sysaux_bsw7yg0j_.dbfcontents of Memory Script:{# set requested point in timeset until  logseq 3 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";sql clone "alter database datafile  6 online";# recover and open resetlogsrecover clone database tablespace  "TSPITR", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;alter clone database open resetlogs;}executing Memory Scriptexecuting command: SET until clausesql statement: alter database datafile  1 onlinesql statement: alter database datafile  3 onlinesql statement: alter database datafile  2 onlinesql statement: alter database datafile  6 onlineStarting recover at 2015/07/09 15:17:35using channel ORA_AUX_DISK_1starting media recoverychannel ORA_AUX_DISK_1: starting archived log restore to default destinationchannel ORA_AUX_DISK_1: restoring archived logarchived log thread=1 sequence=2channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_annnn_TAG20150709T144220_bsw5ydxj_.bkpchannel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_annnn_TAG20150709T144220_bsw5ydxj_.bkp tag=TAG20150709T144220channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01archived log file name=/backup/aux/1_2_884613136.dbf thread=1 sequence=2channel clone_default: deleting archived log(s)archived log file name=/backup/aux/1_2_884613136.dbf RECID=3 STAMP=884618256media recovery complete, elapsed time: 00:00:01Finished recover at 2015/07/09 15:17:38database openedcontents of Memory Script:{# make read only the tablespace that will be exportedsql clone 'alter tablespace  "TSPITR" read only';# create directory for datapump importsql "create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/aux''";# create directory for datapump exportsql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/aux''";}executing Memory Scriptsql statement: alter tablespace  "TSPITR" read onlysql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/aux''sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/aux''Performing export of metadata...   EXPDP> Starting "SYS"."TSPITR_EXP_nkvk":     EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK   EXPDP> Master table "SYS"."TSPITR_EXP_nkvk" successfully loaded/unloaded   EXPDP> ******************************************************************************   EXPDP> Dump file set for SYS.TSPITR_EXP_nkvk is:   EXPDP>   /backup/aux/tspitr_nkvk_59637.dmp   EXPDP> ******************************************************************************   EXPDP> Datafiles required for transportable tablespace TSPITR:   EXPDP>   /u01/app/oracle/oradata/devdb/tspitr01.dbf   EXPDP> Job "SYS"."TSPITR_EXP_nkvk" successfully completed at 15:19:40Export completedcontents of Memory Script:{# shutdown clone before importshutdown clone immediate# drop target tablespaces before importing them backsql 'drop tablespace  "TSPITR" including contents keep datafiles';}executing Memory Scriptdatabase closeddatabase dismountedOracle instance shut downsql statement: drop tablespace  "TSPITR" including contents keep datafilesRemoving automatic instanceshutting down automatic instance target database instance not startedAutomatic instance removedauxiliary instance file /backup/aux/DEVDB/datafile/o1_mf_temp_bsw80r22_.tmp deletedauxiliary instance file /backup/aux/DEVDB/onlinelog/o1_mf_3_bsw80n92_.log deletedauxiliary instance file /backup/aux/DEVDB/onlinelog/o1_mf_2_bsw80m9y_.log deletedauxiliary instance file /backup/aux/DEVDB/onlinelog/o1_mf_1_bsw80lhs_.log deletedauxiliary instance file /backup/aux/DEVDB/datafile/o1_mf_sysaux_bsw7yg0j_.dbf deletedauxiliary instance file /backup/aux/DEVDB/datafile/o1_mf_undotbs1_bsw7yg16_.dbf deletedauxiliary instance file /backup/aux/DEVDB/datafile/o1_mf_system_bsw7yg0d_.dbf deletedauxiliary instance file /backup/aux/DEVDB/controlfile/o1_mf_bsw7y759_.ctl deletedRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 07/09/2015 15:20:10RMAN-03015: error occurred in stored script Memory ScriptRMAN-03009: failure of sql command on default channel at 07/09/2015 15:20:10RMAN-11003: failure during parse/execution of SQL statement: drop tablespace  "TSPITR" including contents keep datafilesORA-00604: error occurred at recursive SQL level 1ORA-00054: resource busy and acquire with NOWAIT specified or timeout expiredRMAN>

报错了~

后面经过试验，"RECOVER TABLESPACE 'TSPITR' UNTIL LOGSEQ 3 " 的UNTIL LOGSEQ 3并不包括日志3 。应该将恢复语句改成

RECOVER TABLESPACE 'TSPITR' UNTIL LOGSEQ 4  AUXILIARY DESTINATION '/backup/aux';