自动表空间恢复TSPITR

我们使用表空间恢复test表空间，其实TSPITR是调用的传输表空间的技术，所以在做TSPITR的时候要首先保证恢复的表空间没有和其它的表空间相关联的，如果有则需要同时恢复相应的表空间。

测试之前oracle提供了数据字典来查询是否存在冲突

SQL> set line 200
SQL> col obj2_owner for a30
SQL> col obj2_owner for a30
SQL> col reason for a60
SQL> SELECT OBJ1_owner,obj1_name,obj1_type,reason FROM sys.Ts_Pitr_Check WHERE (ts1_name IN ('TEST') AND TS2_NAME NOT IN ('TEST')) OR (ts1_name NOT IN ('TEST') AND TS2_NAME IN ('TEST'))
  2  /

no rows selected

如果不冲突则没有显示，如果有则会显示具对象的信息。
例如我们在test用户下test表上创建一个索引表空间指定到uses表空间

SQL> create table test(id int);

Table created.

SQL> create index ind_test on test(id) tablespace users;

Index created.
再来执行就有了输出：
SQL> /

OBJ1_OWNER		       OBJ1_NAME		      OBJ1_TYPE        REASON
------------------------------ ------------------------------ ---------------- ------------------------------------------------------------
TEST			       TEST			      TABLE	       Tables and associated indexes not fully contained in the recovery set

使用RMAN进行TSPITR，一条命令oracle自动为我们创建辅助实例，并且为我们自动恢复，我们只要指定一个恢复时间点，并且保证有可用的备份来进行恢复即可。恢复完成之后辅助实例可用直接删除

RMAN> recover tablespace "TEST" until time "to_date('2014-05-21 10:00:00','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/home/oracle/tsprec';

Starting recover at 21-MAY-14
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='liyq'

initialization parameters used for automatic instance:
db_name=SQUAN
db_unique_name=liyq_tspitr_SQUAN
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
db_create_file_dest=/home/oracle/tsprec
log_archive_dest_1='location=/home/oracle/tsprec'
#No auxiliary parameter file used


starting up automatic instance SQUAN

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes
Variable Size                281019272 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5517312 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2014-05-21 10:00:00','yyyy-mm-dd hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log 
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 21-MAY-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=80 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/oracle/flash_recovery_area/SQUAN/backupset/2014_05_20/o1_mf_ncsnf_TAG20140520T202057_9qpl63tv_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/oracle/flash_recovery_area/SQUAN/backupset/2014_05_20/o1_mf_ncsnf_TAG20140520T202057_9qpl63tv_.bkp tag=TAG20140520T202057
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/tsprec/SQUAN/controlfile/o1_mf_9qr3v5r1_.ctl
Finished restore at 21-MAY-14

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2014-05-21 10:00:00','yyyy-mm-dd hh24:mi:ss')";
plsql <<<-- tspitr_2
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  '"TEST"' ||' 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 datafiles
set 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  5 to 
 "/opt/oracle/oradata/squan/test01.dbf";
set newname for datafile  6 to 
 "/opt/oracle/oradata/squan/test02.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 5, 6;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace "TEST" offline immediate

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/tsprec/SQUAN/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 21-MAY-14
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle/tsprec/SQUAN/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/tsprec/SQUAN/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /home/oracle/tsprec/SQUAN/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/squan/test01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /opt/oracle/oradata/squan/test02.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/oracle/flash_recovery_area/SQUAN/backupset/2014_05_20/o1_mf_nnndf_TAG20140520T202057_9qpl59wz_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/oracle/flash_recovery_area/SQUAN/backupset/2014_05_20/o1_mf_nnndf_TAG20140520T202057_9qpl59wz_.bkp tag=TAG20140520T202057
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 21-MAY-14

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=848140175 file name=/home/oracle/tsprec/SQUAN/datafile/o1_mf_system_9qr3vdow_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=848140175 file name=/home/oracle/tsprec/SQUAN/datafile/o1_mf_undotbs1_9qr3vdoz_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=848140175 file name=/home/oracle/tsprec/SQUAN/datafile/o1_mf_sysaux_9qr3vdoy_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2014-05-21 10:00:00','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql 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  5 online";
sql clone "alter database datafile  6 online";
# recover and open resetlogs
recover clone database tablespace  "TEST", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  5 online

sql statement: alter database datafile  6 online

Starting recover at 21-MAY-14
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 38 is already on disk as file /opt/arch/1_38_846708003.dbf
archived log for thread 1 with sequence 39 is already on disk as file /opt/arch/1_39_846708003.dbf
archived log file name=/opt/arch/1_38_846708003.dbf thread=1 sequence=38
archived log file name=/opt/arch/1_39_846708003.dbf thread=1 sequence=39
media recovery complete, elapsed time: 00:00:05
Finished recover at 21-MAY-14

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  "TEST" read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/tsprec''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/tsprec''";
}
executing Memory Script

sql statement: alter tablespace  "TEST" read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/tsprec''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/tsprec''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_liyq":  
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_liyq" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_liyq is:
   EXPDP>   /home/oracle/tsprec/tspitr_liyq_47871.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TEST:
   EXPDP>   /opt/oracle/oradata/squan/test01.dbf
   EXPDP>   /opt/oracle/oradata/squan/test02.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_liyq" successfully completed at Wed May 21 10:30:03 2014 elapsed 0 00:00:11
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace  "TEST" including contents keep datafiles cascade constraints';
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

sql statement: drop tablespace  "TEST" including contents keep datafiles cascade constraints

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_liyq" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_liyq":  
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_liyq" successfully completed at Wed May 21 10:30:19 2014 elapsed 0 00:00:02
Import completed


contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  "TEST" read write';
sql 'alter tablespace  "TEST" offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace  "TEST" read write

sql statement: alter tablespace  "TEST" offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/tsprec/SQUAN/datafile/o1_mf_temp_9qr3wq6t_.tmp deleted
auxiliary instance file /home/oracle/tsprec/SQUAN/onlinelog/o1_mf_3_9qr3woxh_.log deleted
auxiliary instance file /home/oracle/tsprec/SQUAN/onlinelog/o1_mf_1_9qr3wo45_.log deleted
auxiliary instance file /home/oracle/tsprec/SQUAN/datafile/o1_mf_sysaux_9qr3vdoy_.dbf deleted
auxiliary instance file /home/oracle/tsprec/SQUAN/datafile/o1_mf_undotbs1_9qr3vdoz_.dbf deleted
auxiliary instance file /home/oracle/tsprec/SQUAN/datafile/o1_mf_system_9qr3vdow_.dbf deleted
auxiliary instance file /home/oracle/tsprec/SQUAN/controlfile/o1_mf_9qr3v5r1_.ctl deleted
Finished recover at 21-MAY-14

最后online表空间即可：

RMAN> sql 'alter tablespace test online';

sql statement: alter tablespace test online

oracle提供数据字典查看表空间内对象的恢复情况：

SQL>  select * from ts_pitr_objects_to_be_dropped WHERE TABLESPACE_NAME='TEST';

OWNER			       NAME			      CREATION_TIME	  TABLESPACE_NAME
------------------------------ ------------------------------ ------------------- ------------------------------
TEST			       X			      2014-05-21 10:30:18 TEST
TEST			       SCORE			      2014-05-21 10:30:18 TEST
TEST			       T3			      2014-05-21 10:30:18 TEST
TEST			       T2			      2014-05-21 10:30:18 TEST
TEST			       OBJ			      2014-05-21 10:30:18 TEST
TEST			       T1			      2014-05-21 10:30:18 TEST

这个测试是oracle的自动恢复表空间。其实我们还可以手工创建辅助实例，那个过程稍显复杂。

最后 TSPITR也有一些限制 包括：

1.不恩呢该还原包括sys用户特有对象的表空间

2.不支持快照表空间

3.不能还原回滚段表空间

4.不能恢复表空间内存在如下对象：

varary

嵌套表 

外部 文件 

完成 TSPITR后 ，这个表空间以前的备份就不能用于以后的表空间恢复，所以建议恢复完成之后再进行一次表空间备份 。