一.表空间按时间点恢复
RMAN的自动表空间按时间点恢复（TSPITR）能让你将数据库中的一个或多个表空间快速恢复到之前的某个时间点，而不会影响数据库中的其它表空间和对象。
为了有效地使用表空间按时间点恢复（TSPITR），需要理解你需要解决的问题是什么，使用TSPITR的主要原因是什么，在执行TSPITR时RMAN将会执行那些操作，何时以及如何使用TSPITR，使用TSPITR有那些限制。
二.执行TSPITR的操作步骤
为了使用RMAN和自动辅助实例来对恢复集执行TSPITR,需要对TSPITR进行一些准备工作，然后执行recover tablespace命令来指定要恢复的表空间和按时间点恢复的目标时间和辅助目标（如果需要的话）。
执行TSPITR，RMAN将会执行以下步骤:
1.如果没有连接到辅助实例，RMAN将创建一个辅助实例，启动并连接到辅助实例。
2.在目标数据库中将要被恢复的表空间置于脱机状态。
3.对辅助实例使用恢复目标时间之前的控制文件备份来还原控制文件。
4.从恢复集和辅助集中将数据文件还原到辅助实例。还原的数据文件可以存储在你指定的目录中或者是文件的原始目录中（对于恢复集文件）或者在辅助目录中（对于辅助集文件，如果你使用了有auxliary destination参数的recover tablespace命令)。
5.在辅助实例中将数据文件还原和恢复到指定的目标时间点。
6.使用resetlogs选项将辅助实例打开
7.导出目标数据库中要恢复表空间的数据字典元数据
8.关闭辅助实例
9.在目标数据库中执行switch命令，因为目标数据库的控制文件现在指示的是在辅助实例中被恢复的恢复集中的数据文件
10.将辅助数据库实的数据字典元数据导入到目标数据库中让恢复的对象可以被访问。
11.删除所有辅助集数据文件
执行完以上步骤，TSPITR处理就已经完成了。对于目标数据库恢复集中的数据文件已经被返回到指定的恢复目标时间点。
三.TSPITR的执行方式
选择好要恢复的表空间和恢复目标时间后，就可以执行RMAN的TSPITR。在执行TSPITR时可以有以下不同的选择:
1.完全自动执行TSPITR–在指定辅助目录后就可以让RMAN来管理TSPITR的所有方面。这是执行TSPITR最简单的方式，除非你需要在执行TSPITR后更好的控制恢复集文件的目录或者在执行TSPITR时控制辅助集文件或者控制通道配置或者控制辅助实例的一些其它方面，否则建议使用这种方式来执行TSPITR。
2.使用自定义自动辅助实例执行TSPITR–在完全自动TSPITR的基础上，自定义一个或多个方面的RMAN行为，比如辅助集目录或者恢复集文件，或者指定初始化参数或者辅助实例的通道配置等。
3.使用自己的辅助实例执行TSPITR–在这种情况下要手动设置，启动，停止和清除辅助实例，也可以使用自动辅助实例自定义TSPITR中的一些方法来管理TSPITR。
完全自动执行TSPITR
当完全自动执行TSPITR时，让RMAN来管理整个过程，并且有两个条件:
1.必须给RMAN指定辅助目录让辅助实例的辅助集数据文件和其它文件使用。
2.必须在目标实例上配置任何需要的通道（当目标实例执行TSPITR时，辅助实例使用相同的通道配置）。
对于TSPITR的基础配置在目标数据库上进行。当执行TSPITR时，恢复集数据文件会被写到目标数据库的当前目录。当从备份中还原文件时在目标数据库上有效的相同配置通道将被辅助实例所用。辅助集数据文件和其它辅助实例文件，会被存储在辅助目录中。
自动TSPITR的辅助目录
oracle建议在使用辅助实例时使用辅助目录。即使你使用其它的方法来重命名一些或者所有的辅助集数据文件，通过设置auxliary destination参数来为没有指定名称的辅助集数据文件提供一个默认的目录。为了指定辅助目录，在磁盘上找到有足够空间的目录来处理辅助集数据文件。在recover tablespace命令中使用auxliary destination参数来指定辅助目录。
这里我使用oracle 10g进行完全自动执行TSPITR的实验,下面来准备实验环境：
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG
创建实验表空间tspitr，用户tspitr,实验表tspitr:
SQL> create tablespace tspitr datafile '/u01/app/oracle/oradata/test/tspitr01.dbf' size 100M autoextend off extent management local segment space management auto; Tablespace created. SQL> create user tspitr identified by "tspitr" default tablespace tspitr temporary tablespace temp; User created. SQL> grant connect,resource to tspitr; Grant succeeded.
这里为了实例的方便使用给tspitr用户授予了DBA权限
SQL> grant dba to tspitr;
Grant succeeded.
SQL> conn tspitr/tspitr
Connected.
SQL> create table tspitr as select * from dba_objects;
Table created.
SQL> select count(*) from tspitr;
COUNT(*)
----------
50678
SQL> select sequence#, status from v$log;
SEQUENCE# STATUS
---------- ----------------
28 INACTIVE
29 CURRENT
27 INACTIVE
在创建完实验表tspitr后，记录一下当前的logseq号为29。并对数据库进行一次全备份。
[root@oracle11g ~]# su - oracle
[oracle@oracle11g ~]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
[oracle@oracle11g ~]$ rman target sys/zzh_2046@test catalog rman/rman@jy
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 16 10:34:12 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database
RMAN> backup as backupset database plus archivelog delete all input;
Starting backup at 16-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=13 recid=20 stamp=872692763
input archive log thread=1 sequence=14 recid=21 stamp=873390068
input archive log thread=1 sequence=15 recid=22 stamp=873477705
input archive log thread=1 sequence=16 recid=23 stamp=873557208
input archive log thread=1 sequence=17 recid=24 stamp=873628193
input archive log thread=1 sequence=18 recid=25 stamp=873881475
input archive log thread=1 sequence=19 recid=26 stamp=873882506
input archive log thread=1 sequence=20 recid=27 stamp=873886639
input archive log thread=1 sequence=21 recid=28 stamp=873886719
input archive log thread=1 sequence=22 recid=29 stamp=873969562
input archive log thread=1 sequence=23 recid=30 stamp=873969604
input archive log thread=1 sequence=24 recid=31 stamp=873974555
input archive log thread=1 sequence=25 recid=32 stamp=873974650
input archive log thread=1 sequence=26 recid=33 stamp=874234950
input archive log thread=1 sequence=27 recid=34 stamp=874315417
input archive log thread=1 sequence=28 recid=35 stamp=874486326
input archive log thread=1 sequence=29 recid=36 stamp=874492547
channel ORA_DISK_1: starting piece 1 at 16-MAR-15
channel ORA_DISK_1: finished piece 1 at 16-MAR-15
piece handle=/u02/ora_test874492549_351 tag=TAG20150316T103548 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_13_870806981.dbf recid=20 stamp=872692763
archive log filename=/u02/1_14_870806981.dbf recid=21 stamp=873390068
archive log filename=/u02/1_15_870806981.dbf recid=22 stamp=873477705
archive log filename=/u02/1_16_870806981.dbf recid=23 stamp=873557208
archive log filename=/u02/1_17_870806981.dbf recid=24 stamp=873628193
archive log filename=/u02/1_18_870806981.dbf recid=25 stamp=873881475
archive log filename=/u02/1_19_870806981.dbf recid=26 stamp=873882506
archive log filename=/u02/1_20_870806981.dbf recid=27 stamp=873886639
archive log filename=/u02/1_21_870806981.dbf recid=28 stamp=873886719
archive log filename=/u02/1_22_870806981.dbf recid=29 stamp=873969562
archive log filename=/u02/1_23_870806981.dbf recid=30 stamp=873969604
archive log filename=/u02/1_24_870806981.dbf recid=31 stamp=873974555
archive log filename=/u02/1_25_870806981.dbf recid=32 stamp=873974650
archive log filename=/u02/1_26_870806981.dbf recid=33 stamp=874234950
archive log filename=/u02/1_27_870806981.dbf recid=34 stamp=874315417
archive log filename=/u02/1_28_870806981.dbf recid=35 stamp=874486326
archive log filename=/u02/1_29_870806981.dbf recid=36 stamp=874492547
Finished backup at 16-MAR-15
Starting backup at 16-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-MAR-15
channel ORA_DISK_1: finished piece 1 at 16-MAR-15
piece handle=/u02/ora_test874492578_361 tag=TAG20150316T103617 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 16-MAR-15
Starting backup at 16-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=30 recid=37 stamp=874492654
channel ORA_DISK_1: starting piece 1 at 16-MAR-15
channel ORA_DISK_1: finished piece 1 at 16-MAR-15
piece handle=/u02/ora_test874492656_371 tag=TAG20150316T103735 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_30_870806981.dbf recid=37 stamp=874492654
Finished backup at 16-MAR-15
Starting Control File and SPFILE Autobackup at 16-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-MAR-15
RMAN> list backup ;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1197 106.98M DISK 00:00:23 2015-03-16 10:36:12
BP Key: 1198 Status: AVAILABLE Compressed: NO Tag: TAG20150316T103548
Piece Name: /u02/ora_test874492549_351
List of Archived Logs in backup set 1197
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 13 479270 2015-02-13 11:15:05 507890 2015-02-26 14:39:21
1 14 507890 2015-02-26 14:39:21 537468 2015-03-03 16:21:06
1 15 537468 2015-03-03 16:21:06 565660 2015-03-04 16:41:41
1 16 565660 2015-03-04 16:41:41 597853 2015-03-05 14:46:22
1 17 597853 2015-03-05 14:46:22 627695 2015-03-06 10:29:51
1 18 627695 2015-03-06 10:29:51 662451 2015-03-09 08:51:08
1 19 662451 2015-03-09 08:51:08 663522 2015-03-09 09:08:25
1 20 663522 2015-03-09 09:08:25 666107 2015-03-09 10:17:18
1 21 666107 2015-03-09 10:17:18 666153 2015-03-09 10:18:39
1 22 666153 2015-03-09 10:18:39 692585 2015-03-10 09:19:20
1 23 692585 2015-03-10 09:19:20 692853 2015-03-10 09:20:04
1 24 692853 2015-03-10 09:20:04 695060 2015-03-10 10:42:34
1 25 695060 2015-03-10 10:42:34 695112 2015-03-10 10:44:09
1 26 695112 2015-03-10 10:44:09 726710 2015-03-13 11:02:26
1 27 726710 2015-03-13 11:02:26 759353 2015-03-14 09:23:34
1 28 759353 2015-03-14 09:23:34 786212 2015-03-16 08:52:00
1 29 786212 2015-03-16 08:52:00 789814 2015-03-16 10:35:43
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1226 Full 612.91M DISK 00:01:14 2015-03-16 10:37:32
BP Key: 1244 Status: AVAILABLE Compressed: NO Tag: TAG20150316T103617
Piece Name: /u02/ora_test874492578_361
List of Datafiles in backup set 1226
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 789843 2015-03-16 10:36:18 /u01/app/oracle/oradata/test/system01.dbf
2 Full 789843 2015-03-16 10:36:18 /u01/app/oracle/oradata/test/undotbs01.dbf
3 Full 789843 2015-03-16 10:36:18 /u01/app/oracle/oradata/test/sysaux01.dbf
4 Full 789843 2015-03-16 10:36:18 /u01/app/oracle/oradata/test/users01.dbf
5 Full 789843 2015-03-16 10:36:18 /u01/app/oracle/oradata/test/example01.dbf
6 Full 789843 2015-03-16 10:36:18 /u01/app/oracle/oradata/test/tspitr01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1268 2.50K DISK 00:00:01 2015-03-16 10:37:37
BP Key: 1275 Status: AVAILABLE Compressed: NO Tag: TAG20150316T103735
Piece Name: /u02/ora_test874492656_371
List of Archived Logs in backup set 1268
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 30 789814 2015-03-16 10:35:43 789871 2015-03-16 10:37:33
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1286 Full 6.80M DISK 00:00:01 2015-03-16 10:37:40
BP Key: 1288 Status: AVAILABLE Compressed: NO Tag: TAG20150316T103739
Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp
Control File Included: Ckp SCN: 789893 Ckp time: 2015-03-16 10:37:39
SPFILE Included: Modification time: 2015-03-16 08:52:02
模拟误操作:
记录当前时间点的logseq号为31
SQL> select sequence#, status from v$log;
SEQUENCE# STATUS
---------- ----------------
31 CURRENT
29 INACTIVE
30 INACTIVE
进行日志切换，记录truncate操作之前的logseq号为32，并执行truncate操作
SQL> alter system switch logfile;
System altered.
SQL> select sequence#, status from v$log;
SEQUENCE# STATUS
---------- ----------------
31 ACTIVE
32 CURRENT
30 INACTIVE
SQL> truncate table tspitr;
Table truncated.
SQL> alter system switch logfile;
System altered.
SQL> select sequence#, status from v$log;
SEQUENCE# STATUS
---------- ----------------
31 ACTIVE
32 ACTIVE
33 CURRENT
在执行truncate table tspitr操作之后，进行日志切换，并记录truncate操作之后的当前logseq号为33。我们这里的恢复目标是将表空间tspitr恢复到logseq=32的时间点。为了执行自动RMAN TSPITR，启动RMAN客户端，连接到目标数据库和恢复目录（如果有的话）。注意，为了使用自动TSPITR在启动RMAN客户端时不要连接到辅助实例。如果在执行recover tablespace命令时，RMAN连接到一个辅助实例，RMAN会假设你将试管理自己单独的辅助实例。
创建辅助目录:
[oracle@oracle11g oradata]$ mkdir auxiliary [oracle@oracle11g oradata]$ ls -lrt total 8 drwxr-x--- 2 oracle oinstall 4096 Mar 16 10:22 test drwxr-xr-x 2 oracle oinstall 4096 Mar 16 10:55 auxiliary
如果已经配置了通道，RMAN可以在主实例上从备份中执行还原，那么通过执行recover tablespace …until …命令就能执行TSPITR了。这里使用目标数据库中的缺省磁盘通道。
执行下面的命令将表空间tspitr恢复到logseq=32的时间点:
RMAN> recover tablespace tspitr until logseq 32 auxiliary destination '/u01/app/oracle/oradata/auxiliary';
Starting recover at 2015-03-16 11:27:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
Creating automatic instance, with SID='hswB'
initialization parameters used for automatic instance:
db_name=TEST
compatible=10.2.0.5.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_TEST_hswB
sga_target=180M
processes=50
#No auxiliary parameter file used
db_create_file_dest=/u01/app/oracle/oradata/auxiliary
control_files=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_hswB.f
starting up automatic instance TEST
Oracle instance started
Total System Global Area 188743680 bytes
Fixed Size 1272720 bytes
Variable Size 62915696 bytes
Database Buffers 121634816 bytes
Redo Buffers 2920448 bytes
Automatic instance created
contents of Memory Script:
{
# set the until clause
set until logseq 32 thread 1;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script
executing command: SET until clause
Starting restore at 2015-03-16 11:27:38
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=47 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp tag=TAG20150316T103739
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_hswB.f
Finished restore at 2015-03-16 11:27:41
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
starting full resync of recovery catalog
full resync complete
contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until logseq 32 thread 1;
plsql < <<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'TSPITR' ||' offline for recover';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 6 to
"/u01/app/oracle/oradata/test/tspitr01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "TSPITR", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace TSPITR offline for recover
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2015-03-16 11:27:54
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492578_361
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492578_361 tag=TAG20150316T103617
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 2015-03-16 11:29:00
datafile 1 switched to datafile copy
input datafile copy recid=19 stamp=874495741 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_system_bjdmfw0k_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=20 stamp=874495741 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_undotbs1_bjdmfw23_.dbf
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 6 online
Starting recover at 2015-03-16 11:29:01
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 31 is already on disk as file /u02/1_31_870806981.dbf
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=30
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492656_371
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492656_371 tag=TAG20150316T103735
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf thread=1 sequence=30
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf recid=38 stamp=874495743
archive log filename=/u02/1_31_870806981.dbf thread=1 sequence=31
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-03-16 11:29:05
database opened
contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/10.2.0/db/bin/oracle\)\(ARGV0=oraclehswB\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=hswB^'\)\)\(CONNECT_DATA=\(SID=hswB\)\)\) as sysdba\" point_in_time_recover=y tablespaces=
TSPITR file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"sys/zzh_2046@test as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TSPITR online";
sql "alter tablespace TSPITR offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing Memory Script
Export: Release 10.2.0.5.0 - Production on Mon Mar 16 11:29:30 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace TSPITR ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TSPITR
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete
database closed
database dismounted
Oracle instance shut down
Import: Release 10.2.0.5.0 - Production on Mon Mar 16 11:29:46 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing TSPITR's objects into TSPITR
. . importing table "TSPITR"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace TSPITR online
sql statement: alter tablespace TSPITR offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
starting full resync of recovery catalog
full resync complete
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_hswB.f deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_system_bjdmfw0k_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_undotbs1_bjdmfw23_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_temp_bjdmjfov_.tmp deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/onlinelog/o1_mf_1_bjdmj1o6_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/onlinelog/o1_mf_2_bjdmj44r_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/onlinelog/o1_mf_3_bjdmj6f1_.log deleted
Finished recover at 2015-03-16 11:29:59
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TSPITR OFFLINE
7 rows selected.
TSPITR成功执行完成，RMAN会将表空间脱机，从备份中还原并在辅助实例中将表空间恢复到指定的目标时间点。在整个过程执行完成后，表空间仍然是脱机状态。所有的辅助集数据文件和其它的辅助实例文件会从辅助目录中删除。
TSPITR执行成功后需要执行的操作:
1.必须要备份恢复的表空间然后将表空间置于联机状态。
2.在TSPITR执行成功后立即备份恢复成功的表空间是非常重要的。在对表空间执行TSPITR后，在TSPITR恢复目标时间之前的表空间备份将不能再使用。如果不对恢复的表空间进行备份就开始使用，你将在没有这些表空间备份的情况下运行数据库。对于这个例子，执行以下命令来备份tspitr表空间或者对整个数据库进行备份:
RMAN> backup tablespace tspitr; Starting backup at 2015-03-16 11:40:53 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf channel ORA_DISK_1: starting piece 1 at 2015-03-16 11:40:54 channel ORA_DISK_1: finished piece 1 at 2015-03-16 11:40:55 piece handle=/u02/ora_test874496454_391 tag=TAG20150316T114053 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2015-03-16 11:40:55 Starting Control File and SPFILE Autobackup at 2015-03-16 11:40:55 piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874496456_bjdn69mh_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 2015-03-16 11:40:59
然后可以安全的将表空间置于联机状态:
RMAN> sql 'alter tablespace tspitr online'; sql statement: alter tablespace tspitr online starting full resync of recovery catalog full resync complete
现在恢复的表空间就可以供用户使用了。
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TSPITR ONLINE
7 rows selected.
SQL> show user
USER is "TSPITR"
SQL> select count(*) from tspitr;
COUNT(*)
----------
50678