oracle的TSPITR是是以表空间为粒度单元，单独将某个表空间内容还原到一个特定可恢复时间点上或者日志序号上（实质都是恢复到指定日志记录的最晚那个SCN上）。
TSPITR方法是使用RMAN进行自动的恢复。其使用前提为两个，一个是必须存在相应的备份集，另一个是表空间对象是“self-contain”，也就是其他表空间中不包括与这个表空间对象相关的对象数据。其工作原理就是利用RMAN备份集恢复出数据文件到辅助实例上，然后通过expdp工具从辅助实例上导出表空间的数据对象，然后在目标库中删除原来表空间，使用impdp工具导入数据对象到目标库。最后，清理辅助实例信息，将创建的auxiliary数据库删除。
以下是模拟故障，然后选择TSPSTR来恢复表空间CSTP到指定的日志序号上。
SQL> create tablespace CSTP datafile '/u01/oracle/oradata/cstp01.dbf' size 10M;
Tablespace created.
SQL> alter tablespace CSTP add datafile '/u01/oracle/oradata/cstp02.dbf' size 10M;
Tablespace altered.
SQL> create user cstp01 identified by cstp01 account unlock default tablespace CSTP;
User created.
SQL> grant dba to cstp01;
Grant succeeded.
切换几次日志，然后做备份（可以做全备份，也可以选择做单个表空间或者数据文件的备份，备份控制文件，本例做全库备份）
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
oracle@nwsj:/u01/auxiliary> $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Wed Jan 20 13:21:10 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: CESHIKU (DBID=404431364)
RMAN> backup full database format '/u01/backup/backup_%T_%s_%p.bak';
Starting backup at 20-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00009 name=/u01/oracle/oradata/ceshiku/cs_dir03.dbf
input datafile fno=00004 name=/u01/oracle/oradata/ceshiku/users01.dbf
input datafile fno=00002 name=/u01/oracle/oradata/ceshiku/undotbs01.dbf
input datafile fno=00001 name=/u01/oracle/oradata/ceshiku/system01.dbf
input datafile fno=00003 name=/u01/oracle/oradata/ceshiku/sysaux01.dbf
input datafile fno=00006 name=/u01/oracle/oradata/ceshiku/CS_DIR02.dbf
input datafile fno=00007 name=/u01/oracle/oradata/ceshiku/aa01.dbf
input datafile fno=00005 name=/u01/oracle/oradata/ceshiku/cs_dir01.dbf
input datafile fno=00010 name=/u01/oracle/oradata/ceshiku/b02.dbf
input datafile fno=00008 name=/u01/oracle/oradata/ceshiku/b01.dbf
input datafile fno=00011 name=/u01/oracle/oradata/ceshiku/user001.dbf
input datafile fno=00013 name=/u01/oracle/oradata/ceshiku/b03.dbf
input datafile fno=00014 name=/u01/oracle/oradata/ceshiku/b04.dbf
input datafile fno=00015 name=/u01/oracle/oradata/ceshiku/b05.dbf
input datafile fno=00016 name=/u01/oracle/oradata/cstp01.dbf
input datafile fno=00017 name=/u01/oracle/oradata/cstp02.dbf
channel ORA_DISK_1: starting piece 1 at 20-JAN-16
channel ORA_DISK_1: finished piece 1 at 20-JAN-16
piece handle=/u01/backup/backup_20160120_17_1.bak tag=TAG20160120T132113 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 20-JAN-16
channel ORA_DISK_1: finished piece 1 at 20-JAN-16
piece handle=/u01/backup/backup_20160120_18_1.bak tag=TAG20160120T132113 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-JAN-16
RMAN> backup archivelog all format '/u01/backup/arch_%T_%s_%p.bak';
Starting backup at 20-JAN-16
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=607 recid=607 stamp=901629121
input archive log thread=1 sequence=608 recid=608 stamp=901629125
input archive log thread=1 sequence=609 recid=609 stamp=901629209
input archive log thread=1 sequence=610 recid=610 stamp=901629214
input archive log thread=1 sequence=611 recid=611 stamp=901629439
input archive log thread=1 sequence=612 recid=612 stamp=901629440
input archive log thread=1 sequence=613 recid=613 stamp=901632120
input archive log thread=1 sequence=614 recid=614 stamp=901632121
input archive log thread=1 sequence=615 recid=615 stamp=901632122
input archive log thread=1 sequence=616 recid=616 stamp=901632123
input archive log thread=1 sequence=617 recid=617 stamp=901632128
input archive log thread=1 sequence=618 recid=618 stamp=901632152
input archive log thread=1 sequence=619 recid=619 stamp=901632155
channel ORA_DISK_1: starting piece 1 at 20-JAN-16
channel ORA_DISK_1: finished piece 1 at 20-JAN-16
piece handle=/u01/backup/arch_20160120_19_1.bak tag=TAG20160120T132235 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 20-JAN-16
RMAN> backup current controlfile format '/u01/backup/bak_con_%U';
Starting backup at 20-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 20-JAN-16
channel ORA_DISK_1: finished piece 1 at 20-JAN-16
piece handle=/u01/backup/bak_con_0kqrrk5u_1_1 tag=TAG20160120T132310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-JAN-16
完成后，可以切换几次日志（主要用来模拟备份是比较早时备份的，此时产生了新数据，数据库日志做了归档）
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> conn cstp01/cstp01;
Connected.
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 622 52428800 1 YES INACTIVE
9.6710E+12 20-JAN-16
2 1 623 52428800 1 NO CURRENT
9.6710E+12 20-JAN-16
3 1 621 52428800 1 YES INACTIVE
9.6710E+12 20-JAN-16
SQL> create table cs01 (id number,name nvarchar2(20));
Table created.
SQL> insert into cs01 values(1,'a1');
1 row created.
SQL> insert into cs01 values(2,'a2');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 625 52428800 1 NO CURRENT
9.6710E+12 20-JAN-16
2 1 623 52428800 1 YES ACTIVE
9.6710E+12 20-JAN-16
3 1 624 52428800 1 YES ACTIVE
9.6710E+12 20-JAN-16
SQL> select * from cs01;
ID NAME
---------- --------------------
1 a1
2 a2
SQL> insert into cs01 values(3,'a3');
1 row created.
SQL> insert into cs01 values(4,'a4');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 625 52428800 1 YES ACTIVE
9.6710E+12 20-JAN-16
2 1 626 52428800 1 YES ACTIVE
9.6710E+12 20-JAN-16
3 1 627 52428800 1 NO CURRENT
9.6710E+12 20-JAN-16
SQL> select * from cs01;
ID NAME
---------- --------------------
1 a1
2 a2
3 a3
4 a4
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@nwsj:/u01/backup/ceshiku/archivelog>
关闭数据库，然后模拟归档日志和数据文件出现故障
oracle@nwsj:/u01/oracle/oradata/ceshiku> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 20 10:40:58 2016
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
模拟归档日志625丢失，插入的id为3和4的数据，会写入到625的日志组里，所以此次模拟625日志丢失，这样采用TSPITR恢复到日志组624，id为3和4的数据会丢失。
oracle@nwsj:~> cd /u01/backup/ceshiku/archivelog
oracle@nwsj:/u01/backup/ceshiku/archivelog> ls
1_607_879871873.dbf 1_611_879871873.dbf 1_615_879871873.dbf 1_619_879871873.dbf 1_623_879871873.dbf
1_608_879871873.dbf 1_612_879871873.dbf 1_616_879871873.dbf 1_620_879871873.dbf 1_624_879871873.dbf
1_609_879871873.dbf 1_613_879871873.dbf 1_617_879871873.dbf 1_621_879871873.dbf 1_625_879871873.dbf
1_610_879871873.dbf 1_614_879871873.dbf 1_618_879871873.dbf 1_622_879871873.dbf 1_626_879871873.dbf
oracle@nwsj:/u01/backup/ceshiku/archivelog> mv 1_625_879871873.dbf 1_625_879871873.dbf.ora
oracle@nwsj:/u01/backup/ceshiku/archivelog>cd /u01/oracle/oradata
oracle@nwsj:/u01/oracle/oradata> ls
ceshiku cstp01.dbf cstp02.dbf orcl
oracle@nwsj:/u01/oracle/oradata> mv cstp0* /u01/oracle/oradata/ceshiku/
oracle@nwsj:/u01/oracle/oradata> ls
ceshiku orcl
oracle@nwsj:/u01/oracle/oradata> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 20 10:44:20 2016
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2101608 bytes
Variable Size 260050584 bytes
Database Buffers 788529152 bytes
Redo Buffers 23060480 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 16 - see DBWR trace file
ORA-01110: data file 16: '/u01/oracle/oradata/cstp01.dbf'
可以看到，启动数据库时报错，不能加载数据文件datafile 16，为了不影响其他业务使用，可以采用TSPITR单独来恢复CSTP表空间的数据。所以可以采用先offline数据文
件16，和数据文件17，然后启动数据库，其他业务正常，这时来单独恢复CSTP这个表空间。
SQL> alter database datafile 16 offline;
Database altered.
SQL> alter database datafile 17 offline;
Database altered.
SQL> alter database open;
Database altered.
SQL> exit
恢复目标是将表空间CSTP恢复到logseq=624位置上。
4、TSPITR操作
自动操作的第一步，要确定auxiliary数据库的临时存放位置。
创建auxiliary的目录
oracle@nwsj:/u01/oracle/oradata> mkdir -p /u01/auxiliary
编辑参数文件(由于测试版本为10g版本，需要指定下参数文件，否则会报出 ORA-00821: Specified value of sga_target 180M is too small, needs to be at least
228M，详见http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmtspit.htm#BRADV89809)
vi initpfile.ora
db_block_size=8192
db_name='ceshiku'
db_unique_name='ceshiku_tsp'
db_files=200
sga_target=900M
large_pool_size=300M
shared_pool_size=300M
processes=50
执行TSPITR的恢复操作
oracle@nwsj:~> $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Wed Jan 20 13:57:22 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: CESHIKU (DBID=404431364)
RMAN> run
2> {
3> set auxiliary instance parameter file to '/u01/oracle/oradata/initpfile.ora';
4> recover tablespace CSTP until logseq 625 auxiliary destination '/u01/auxiliary';
5> }
executing command: SET auxiliary parameter file
using target database control file instead of recovery catalog
Starting recover at 20-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK
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='mjCr'
using contents of file /u01/oracle/oradata/initpfile.ora
initialization parameters used for automatic instance:
db_name=CESHIKU
compatible=10.2.0.5.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_CESHIKU_mjCr
sga_target=180M
processes=50
ifile=/u01/oracle/oradata/initpfile.ora
db_create_file_dest=/u01/auxiliary
control_files=/u01/auxiliary/cntrl_tspitr_CESHIKU_mjCr.f
starting up automatic instance CESHIKU
Oracle instance started
Total System Global Area 943718400 bytes
Fixed Size 2100616 bytes
Variable Size 633340536 bytes
Database Buffers 285212672 bytes
Redo Buffers 23064576 bytes
Automatic instance created
contents of Memory Script:
{
# set the until clause
set until logseq 625 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;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 20-JAN-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=43 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/backup/bak_con_0kqrrk5u_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/backup/bak_con_0kqrrk5u_1_1 tag=TAG20160120T132310
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/auxiliary/cntrl_tspitr_CESHIKU_mjCr.f
Finished restore at 20-JAN-16
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:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until logseq 625 thread 1;
# 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 16 to
"/u01/oracle/oradata/cstp01.dbf";
# set a destination filename for restore
set newname for datafile 17 to
"/u01/oracle/oradata/cstp02.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 16, 17;
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 16 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 17 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "CSTP", "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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to /u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 20-JAN-16
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/auxiliary/CESHIKU_TSP/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00016 to /u01/oracle/oradata/cstp01.dbf
restoring datafile 00017 to /u01/oracle/oradata/cstp02.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/backup_20160120_17_1.bak
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/backup/backup_20160120_17_1.bak tag=TAG20160120T132113
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 20-JAN-16
datafile 1 switched to datafile copy
input datafile copy recid=11 stamp=901634273 filename=/u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_system_c9y8gld2_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=12 stamp=901634273 filename=/u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_undotbs1_c9y8glcv_.dbf
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 16 online
sql statement: alter database datafile 17 online
Starting recover at 20-JAN-16
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 613 is already on disk as file /u01/backup/ceshiku/archivelog/1_613_879871873.dbf
archive log thread 1 sequence 614 is already on disk as file /u01/backup/ceshiku/archivelog/1_614_879871873.dbf
archive log thread 1 sequence 615 is already on disk as file /u01/backup/ceshiku/archivelog/1_615_879871873.dbf
archive log thread 1 sequence 616 is already on disk as file /u01/backup/ceshiku/archivelog/1_616_879871873.dbf
archive log thread 1 sequence 617 is already on disk as file /u01/backup/ceshiku/archivelog/1_617_879871873.dbf
archive log thread 1 sequence 618 is already on disk as file /u01/backup/ceshiku/archivelog/1_618_879871873.dbf
archive log thread 1 sequence 619 is already on disk as file /u01/backup/ceshiku/archivelog/1_619_879871873.dbf
archive log thread 1 sequence 620 is already on disk as file /u01/backup/ceshiku/archivelog/1_620_879871873.dbf
archive log thread 1 sequence 621 is already on disk as file /u01/backup/ceshiku/archivelog/1_621_879871873.dbf
archive log thread 1 sequence 622 is already on disk as file /u01/backup/ceshiku/archivelog/1_622_879871873.dbf
archive log thread 1 sequence 623 is already on disk as file /u01/backup/ceshiku/archivelog/1_623_879871873.dbf
archive log thread 1 sequence 624 is already on disk as file /u01/backup/ceshiku/archivelog/1_624_879871873.dbf
archive log filename=/u01/backup/ceshiku/archivelog/1_613_879871873.dbf thread=1 sequence=613
archive log filename=/u01/backup/ceshiku/archivelog/1_614_879871873.dbf thread=1 sequence=614
archive log filename=/u01/backup/ceshiku/archivelog/1_615_879871873.dbf thread=1 sequence=615
archive log filename=/u01/backup/ceshiku/archivelog/1_616_879871873.dbf thread=1 sequence=616
archive log filename=/u01/backup/ceshiku/archivelog/1_617_879871873.dbf thread=1 sequence=617
archive log filename=/u01/backup/ceshiku/archivelog/1_618_879871873.dbf thread=1 sequence=618
archive log filename=/u01/backup/ceshiku/archivelog/1_619_879871873.dbf thread=1 sequence=619
archive log filename=/u01/backup/ceshiku/archivelog/1_620_879871873.dbf thread=1 sequence=620
archive log filename=/u01/backup/ceshiku/archivelog/1_621_879871873.dbf thread=1 sequence=621
archive log filename=/u01/backup/ceshiku/archivelog/1_622_879871873.dbf thread=1 sequence=622
archive log filename=/u01/backup/ceshiku/archivelog/1_623_879871873.dbf thread=1 sequence=623
archive log filename=/u01/backup/ceshiku/archivelog/1_624_879871873.dbf thread=1 sequence=624
media recovery complete, elapsed time: 00:00:03
Finished recover at 20-JAN-16
database opened
contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/oracle/product/10.2.0/db_1/bin/oracle\)\(ARGV0=oraclemjCr
\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=mjCr^'\)\)\(CONNECT_DATA=\(SID=mjCr\)\)\) as sysdba\"
point_in_time_recover=y tablespaces=
CSTP file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace CSTP online";
sql "alter tablespace CSTP offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
Export: Release 10.2.0.5.0 - Production on Wed Jan 20 13:57:58 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace CSTP ...
. exporting cluster definitions
. exporting table definitions
. . exporting table CS01
. 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 Wed Jan 20 13:58:18 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit 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 US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SYS's objects into SYS
. importing CSTP01's objects into CSTP01
. . importing table "CS01"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace CSTP online
sql statement: alter tablespace CSTP offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/auxiliary/cntrl_tspitr_CESHIKU_mjCr.f deleted
auxiliary instance file /u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_system_c9y8gld2_.dbf deleted
auxiliary instance file /u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_undotbs1_c9y8glcv_.dbf deleted
auxiliary instance file /u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_temp_c9y8h5t9_.tmp deleted
auxiliary instance file /u01/auxiliary/CESHIKU_TSP/onlinelog/o1_mf_1_c9y8h565_.log deleted
auxiliary instance file /u01/auxiliary/CESHIKU_TSP/onlinelog/o1_mf_2_c9y8h597_.log deleted
auxiliary instance file /u01/auxiliary/CESHIKU_TSP/onlinelog/o1_mf_3_c9y8h5d3_.log deleted
Finished recover at 20-JAN-16
online表空间CSTP
SQL> alter tablespace CSTP online;
Tablespace altered.
查看数据
SQL> select * from cstp01.cs01;
ID NAME
---------- --------------------
1 a1
2 a2
总结：TSPITR是一个基于备份或者归档的恢复，可以单独来恢复一个表空间，而不影响其他表空间的数据，这样在实际应用中，可以在不影响其他业务的前提下，来恢复故
障表空间的数据（由于归档日志有丢失或者损坏，属于不完全恢复）。
TSPITR方法是使用RMAN进行自动的恢复。其使用前提为两个，一个是必须存在相应的备份集，另一个是表空间对象是“self-contain”，也就是其他表空间中不包括与这个表空间对象相关的对象数据。其工作原理就是利用RMAN备份集恢复出数据文件到辅助实例上，然后通过expdp工具从辅助实例上导出表空间的数据对象，然后在目标库中删除原来表空间，使用impdp工具导入数据对象到目标库。最后，清理辅助实例信息，将创建的auxiliary数据库删除。
以下是模拟故障，然后选择TSPSTR来恢复表空间CSTP到指定的日志序号上。
SQL> create tablespace CSTP datafile '/u01/oracle/oradata/cstp01.dbf' size 10M;
Tablespace created.
SQL> alter tablespace CSTP add datafile '/u01/oracle/oradata/cstp02.dbf' size 10M;
Tablespace altered.
SQL> create user cstp01 identified by cstp01 account unlock default tablespace CSTP;
User created.
SQL> grant dba to cstp01;
Grant succeeded.
切换几次日志，然后做备份（可以做全备份，也可以选择做单个表空间或者数据文件的备份，备份控制文件，本例做全库备份）
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
oracle@nwsj:/u01/auxiliary> $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Wed Jan 20 13:21:10 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: CESHIKU (DBID=404431364)
RMAN> backup full database format '/u01/backup/backup_%T_%s_%p.bak';
Starting backup at 20-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00009 name=/u01/oracle/oradata/ceshiku/cs_dir03.dbf
input datafile fno=00004 name=/u01/oracle/oradata/ceshiku/users01.dbf
input datafile fno=00002 name=/u01/oracle/oradata/ceshiku/undotbs01.dbf
input datafile fno=00001 name=/u01/oracle/oradata/ceshiku/system01.dbf
input datafile fno=00003 name=/u01/oracle/oradata/ceshiku/sysaux01.dbf
input datafile fno=00006 name=/u01/oracle/oradata/ceshiku/CS_DIR02.dbf
input datafile fno=00007 name=/u01/oracle/oradata/ceshiku/aa01.dbf
input datafile fno=00005 name=/u01/oracle/oradata/ceshiku/cs_dir01.dbf
input datafile fno=00010 name=/u01/oracle/oradata/ceshiku/b02.dbf
input datafile fno=00008 name=/u01/oracle/oradata/ceshiku/b01.dbf
input datafile fno=00011 name=/u01/oracle/oradata/ceshiku/user001.dbf
input datafile fno=00013 name=/u01/oracle/oradata/ceshiku/b03.dbf
input datafile fno=00014 name=/u01/oracle/oradata/ceshiku/b04.dbf
input datafile fno=00015 name=/u01/oracle/oradata/ceshiku/b05.dbf
input datafile fno=00016 name=/u01/oracle/oradata/cstp01.dbf
input datafile fno=00017 name=/u01/oracle/oradata/cstp02.dbf
channel ORA_DISK_1: starting piece 1 at 20-JAN-16
channel ORA_DISK_1: finished piece 1 at 20-JAN-16
piece handle=/u01/backup/backup_20160120_17_1.bak tag=TAG20160120T132113 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 20-JAN-16
channel ORA_DISK_1: finished piece 1 at 20-JAN-16
piece handle=/u01/backup/backup_20160120_18_1.bak tag=TAG20160120T132113 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-JAN-16
RMAN> backup archivelog all format '/u01/backup/arch_%T_%s_%p.bak';
Starting backup at 20-JAN-16
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=607 recid=607 stamp=901629121
input archive log thread=1 sequence=608 recid=608 stamp=901629125
input archive log thread=1 sequence=609 recid=609 stamp=901629209
input archive log thread=1 sequence=610 recid=610 stamp=901629214
input archive log thread=1 sequence=611 recid=611 stamp=901629439
input archive log thread=1 sequence=612 recid=612 stamp=901629440
input archive log thread=1 sequence=613 recid=613 stamp=901632120
input archive log thread=1 sequence=614 recid=614 stamp=901632121
input archive log thread=1 sequence=615 recid=615 stamp=901632122
input archive log thread=1 sequence=616 recid=616 stamp=901632123
input archive log thread=1 sequence=617 recid=617 stamp=901632128
input archive log thread=1 sequence=618 recid=618 stamp=901632152
input archive log thread=1 sequence=619 recid=619 stamp=901632155
channel ORA_DISK_1: starting piece 1 at 20-JAN-16
channel ORA_DISK_1: finished piece 1 at 20-JAN-16
piece handle=/u01/backup/arch_20160120_19_1.bak tag=TAG20160120T132235 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 20-JAN-16
RMAN> backup current controlfile format '/u01/backup/bak_con_%U';
Starting backup at 20-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 20-JAN-16
channel ORA_DISK_1: finished piece 1 at 20-JAN-16
piece handle=/u01/backup/bak_con_0kqrrk5u_1_1 tag=TAG20160120T132310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-JAN-16
完成后，可以切换几次日志（主要用来模拟备份是比较早时备份的，此时产生了新数据，数据库日志做了归档）
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> conn cstp01/cstp01;
Connected.
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 622 52428800 1 YES INACTIVE
9.6710E+12 20-JAN-16
2 1 623 52428800 1 NO CURRENT
9.6710E+12 20-JAN-16
3 1 621 52428800 1 YES INACTIVE
9.6710E+12 20-JAN-16
SQL> create table cs01 (id number,name nvarchar2(20));
Table created.
SQL> insert into cs01 values(1,'a1');
1 row created.
SQL> insert into cs01 values(2,'a2');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 625 52428800 1 NO CURRENT
9.6710E+12 20-JAN-16
2 1 623 52428800 1 YES ACTIVE
9.6710E+12 20-JAN-16
3 1 624 52428800 1 YES ACTIVE
9.6710E+12 20-JAN-16
SQL> select * from cs01;
ID NAME
---------- --------------------
1 a1
2 a2
SQL> insert into cs01 values(3,'a3');
1 row created.
SQL> insert into cs01 values(4,'a4');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 625 52428800 1 YES ACTIVE
9.6710E+12 20-JAN-16
2 1 626 52428800 1 YES ACTIVE
9.6710E+12 20-JAN-16
3 1 627 52428800 1 NO CURRENT
9.6710E+12 20-JAN-16
SQL> select * from cs01;
ID NAME
---------- --------------------
1 a1
2 a2
3 a3
4 a4
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@nwsj:/u01/backup/ceshiku/archivelog>
关闭数据库，然后模拟归档日志和数据文件出现故障
oracle@nwsj:/u01/oracle/oradata/ceshiku> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 20 10:40:58 2016
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
模拟归档日志625丢失，插入的id为3和4的数据，会写入到625的日志组里，所以此次模拟625日志丢失，这样采用TSPITR恢复到日志组624，id为3和4的数据会丢失。
oracle@nwsj:~> cd /u01/backup/ceshiku/archivelog
oracle@nwsj:/u01/backup/ceshiku/archivelog> ls
1_607_879871873.dbf 1_611_879871873.dbf 1_615_879871873.dbf 1_619_879871873.dbf 1_623_879871873.dbf
1_608_879871873.dbf 1_612_879871873.dbf 1_616_879871873.dbf 1_620_879871873.dbf 1_624_879871873.dbf
1_609_879871873.dbf 1_613_879871873.dbf 1_617_879871873.dbf 1_621_879871873.dbf 1_625_879871873.dbf
1_610_879871873.dbf 1_614_879871873.dbf 1_618_879871873.dbf 1_622_879871873.dbf 1_626_879871873.dbf
oracle@nwsj:/u01/backup/ceshiku/archivelog> mv 1_625_879871873.dbf 1_625_879871873.dbf.ora
oracle@nwsj:/u01/backup/ceshiku/archivelog>cd /u01/oracle/oradata
oracle@nwsj:/u01/oracle/oradata> ls
ceshiku cstp01.dbf cstp02.dbf orcl
oracle@nwsj:/u01/oracle/oradata> mv cstp0* /u01/oracle/oradata/ceshiku/
oracle@nwsj:/u01/oracle/oradata> ls
ceshiku orcl
oracle@nwsj:/u01/oracle/oradata> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 20 10:44:20 2016
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2101608 bytes
Variable Size 260050584 bytes
Database Buffers 788529152 bytes
Redo Buffers 23060480 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 16 - see DBWR trace file
ORA-01110: data file 16: '/u01/oracle/oradata/cstp01.dbf'
可以看到，启动数据库时报错，不能加载数据文件datafile 16，为了不影响其他业务使用，可以采用TSPITR单独来恢复CSTP表空间的数据。所以可以采用先offline数据文
件16，和数据文件17，然后启动数据库，其他业务正常，这时来单独恢复CSTP这个表空间。
SQL> alter database datafile 16 offline;
Database altered.
SQL> alter database datafile 17 offline;
Database altered.
SQL> alter database open;
Database altered.
SQL> exit
恢复目标是将表空间CSTP恢复到logseq=624位置上。
4、TSPITR操作
自动操作的第一步，要确定auxiliary数据库的临时存放位置。
创建auxiliary的目录
oracle@nwsj:/u01/oracle/oradata> mkdir -p /u01/auxiliary
编辑参数文件(由于测试版本为10g版本，需要指定下参数文件，否则会报出 ORA-00821: Specified value of sga_target 180M is too small, needs to be at least
228M，详见http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmtspit.htm#BRADV89809)
vi initpfile.ora
db_block_size=8192
db_name='ceshiku'
db_unique_name='ceshiku_tsp'
db_files=200
sga_target=900M
large_pool_size=300M
shared_pool_size=300M
processes=50
执行TSPITR的恢复操作
oracle@nwsj:~> $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Wed Jan 20 13:57:22 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: CESHIKU (DBID=404431364)
RMAN> run
2> {
3> set auxiliary instance parameter file to '/u01/oracle/oradata/initpfile.ora';
4> recover tablespace CSTP until logseq 625 auxiliary destination '/u01/auxiliary';
5> }
executing command: SET auxiliary parameter file
using target database control file instead of recovery catalog
Starting recover at 20-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK
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='mjCr'
using contents of file /u01/oracle/oradata/initpfile.ora
initialization parameters used for automatic instance:
db_name=CESHIKU
compatible=10.2.0.5.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_CESHIKU_mjCr
sga_target=180M
processes=50
ifile=/u01/oracle/oradata/initpfile.ora
db_create_file_dest=/u01/auxiliary
control_files=/u01/auxiliary/cntrl_tspitr_CESHIKU_mjCr.f
starting up automatic instance CESHIKU
Oracle instance started
Total System Global Area 943718400 bytes
Fixed Size 2100616 bytes
Variable Size 633340536 bytes
Database Buffers 285212672 bytes
Redo Buffers 23064576 bytes
Automatic instance created
contents of Memory Script:
{
# set the until clause
set until logseq 625 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;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 20-JAN-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=43 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/backup/bak_con_0kqrrk5u_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/backup/bak_con_0kqrrk5u_1_1 tag=TAG20160120T132310
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/auxiliary/cntrl_tspitr_CESHIKU_mjCr.f
Finished restore at 20-JAN-16
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:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until logseq 625 thread 1;
# 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 16 to
"/u01/oracle/oradata/cstp01.dbf";
# set a destination filename for restore
set newname for datafile 17 to
"/u01/oracle/oradata/cstp02.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 16, 17;
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 16 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 17 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "CSTP", "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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to /u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 20-JAN-16
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/auxiliary/CESHIKU_TSP/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00016 to /u01/oracle/oradata/cstp01.dbf
restoring datafile 00017 to /u01/oracle/oradata/cstp02.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/backup_20160120_17_1.bak
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/backup/backup_20160120_17_1.bak tag=TAG20160120T132113
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 20-JAN-16
datafile 1 switched to datafile copy
input datafile copy recid=11 stamp=901634273 filename=/u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_system_c9y8gld2_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=12 stamp=901634273 filename=/u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_undotbs1_c9y8glcv_.dbf
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 16 online
sql statement: alter database datafile 17 online
Starting recover at 20-JAN-16
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 613 is already on disk as file /u01/backup/ceshiku/archivelog/1_613_879871873.dbf
archive log thread 1 sequence 614 is already on disk as file /u01/backup/ceshiku/archivelog/1_614_879871873.dbf
archive log thread 1 sequence 615 is already on disk as file /u01/backup/ceshiku/archivelog/1_615_879871873.dbf
archive log thread 1 sequence 616 is already on disk as file /u01/backup/ceshiku/archivelog/1_616_879871873.dbf
archive log thread 1 sequence 617 is already on disk as file /u01/backup/ceshiku/archivelog/1_617_879871873.dbf
archive log thread 1 sequence 618 is already on disk as file /u01/backup/ceshiku/archivelog/1_618_879871873.dbf
archive log thread 1 sequence 619 is already on disk as file /u01/backup/ceshiku/archivelog/1_619_879871873.dbf
archive log thread 1 sequence 620 is already on disk as file /u01/backup/ceshiku/archivelog/1_620_879871873.dbf
archive log thread 1 sequence 621 is already on disk as file /u01/backup/ceshiku/archivelog/1_621_879871873.dbf
archive log thread 1 sequence 622 is already on disk as file /u01/backup/ceshiku/archivelog/1_622_879871873.dbf
archive log thread 1 sequence 623 is already on disk as file /u01/backup/ceshiku/archivelog/1_623_879871873.dbf
archive log thread 1 sequence 624 is already on disk as file /u01/backup/ceshiku/archivelog/1_624_879871873.dbf
archive log filename=/u01/backup/ceshiku/archivelog/1_613_879871873.dbf thread=1 sequence=613
archive log filename=/u01/backup/ceshiku/archivelog/1_614_879871873.dbf thread=1 sequence=614
archive log filename=/u01/backup/ceshiku/archivelog/1_615_879871873.dbf thread=1 sequence=615
archive log filename=/u01/backup/ceshiku/archivelog/1_616_879871873.dbf thread=1 sequence=616
archive log filename=/u01/backup/ceshiku/archivelog/1_617_879871873.dbf thread=1 sequence=617
archive log filename=/u01/backup/ceshiku/archivelog/1_618_879871873.dbf thread=1 sequence=618
archive log filename=/u01/backup/ceshiku/archivelog/1_619_879871873.dbf thread=1 sequence=619
archive log filename=/u01/backup/ceshiku/archivelog/1_620_879871873.dbf thread=1 sequence=620
archive log filename=/u01/backup/ceshiku/archivelog/1_621_879871873.dbf thread=1 sequence=621
archive log filename=/u01/backup/ceshiku/archivelog/1_622_879871873.dbf thread=1 sequence=622
archive log filename=/u01/backup/ceshiku/archivelog/1_623_879871873.dbf thread=1 sequence=623
archive log filename=/u01/backup/ceshiku/archivelog/1_624_879871873.dbf thread=1 sequence=624
media recovery complete, elapsed time: 00:00:03
Finished recover at 20-JAN-16
database opened
contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/oracle/product/10.2.0/db_1/bin/oracle\)\(ARGV0=oraclemjCr
\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=mjCr^'\)\)\(CONNECT_DATA=\(SID=mjCr\)\)\) as sysdba\"
point_in_time_recover=y tablespaces=
CSTP file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace CSTP online";
sql "alter tablespace CSTP offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
Export: Release 10.2.0.5.0 - Production on Wed Jan 20 13:57:58 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace CSTP ...
. exporting cluster definitions
. exporting table definitions
. . exporting table CS01
. 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 Wed Jan 20 13:58:18 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit 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 US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SYS's objects into SYS
. importing CSTP01's objects into CSTP01
. . importing table "CS01"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace CSTP online
sql statement: alter tablespace CSTP offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/auxiliary/cntrl_tspitr_CESHIKU_mjCr.f deleted
auxiliary instance file /u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_system_c9y8gld2_.dbf deleted
auxiliary instance file /u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_undotbs1_c9y8glcv_.dbf deleted
auxiliary instance file /u01/auxiliary/CESHIKU_TSP/datafile/o1_mf_temp_c9y8h5t9_.tmp deleted
auxiliary instance file /u01/auxiliary/CESHIKU_TSP/onlinelog/o1_mf_1_c9y8h565_.log deleted
auxiliary instance file /u01/auxiliary/CESHIKU_TSP/onlinelog/o1_mf_2_c9y8h597_.log deleted
auxiliary instance file /u01/auxiliary/CESHIKU_TSP/onlinelog/o1_mf_3_c9y8h5d3_.log deleted
Finished recover at 20-JAN-16
online表空间CSTP
SQL> alter tablespace CSTP online;
Tablespace altered.
查看数据
SQL> select * from cstp01.cs01;
ID NAME
---------- --------------------
1 a1
2 a2
总结：TSPITR是一个基于备份或者归档的恢复，可以单独来恢复一个表空间，而不影响其他表空间的数据，这样在实际应用中，可以在不影响其他业务的前提下，来恢复故
障表空间的数据（由于归档日志有丢失或者损坏，属于不完全恢复）。
99
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
