Oracle发展到今天，在备份还原领域已经构建了完善的解决方案体系。绝大多数情况下，我们的备份还原需求都可以用Oracle成熟方案实现的。TSPITR（Tablespace Point In Time Recover）数据表空间定点恢复是我们在实践中偶尔会使用的一种恢复场景。本篇就围绕这个问题进行讨论实验。
1、聊聊TSPITR
严格的说TSPITR是一种相对细粒度的不完全恢复技术。我们通常见到的还原操作，都是将所有的表空间和数据还原到相同的一个时间点上，无论是否是故障恢复点。而TSPITR则是以表空间为粒度单元，单独将某个表空间内容还原到一个特定可恢复时间点上。
举一个例子：一个Oracle数据库运行在归档模式下，在夜间零时保留一份完全备份。早上七点时候，某个特定表空间上数据表（单个表独占表空间）发生一个误操作，数据损坏。要求在不伤害其他数据表数据的情况下，将表空间数据恢复到早上六点。
这样部分数据恢复的场景，就是TSPITR的典型应用。我们在实际场景中，常常希望恢复部分的数据到过去时间点上，而不希望对现有数据进行补录。
TSPITR目前最方便的方法是使用RMAN进行自动的恢复。其使用前提为两个，一个是必须存在相应的备份集合，另一个是表空间对象是“self-contain”，也就是其他表空间中不包括与这个表空间对象相关的对象数据。
恢复步骤和原理如下：
ü RMAN三个对象集合：catalog、target和auxiliary。其中auxiliary就是用于辅助target各种备份还原要求的操作数据库。这个库在TSPITR中扮演临时数据还原作用的；
ü 首先完成数据检查工作，确定备份集合和表空间完整性；
ü 当前时间是T1，如果需要将数据还原为T0。使用RMAN提取备份数据集合，还原到auxiliary上形成一个新的实例数据库。选择性应用归档日志序列，还原auxiliary到TO时间点；
ü 使用expdp工具从auxiliary中导出目标表空间为dump对象。注意：还原auxiliary并不是100%重建target，而是选择系统运行表空间和目标表空间；
ü 在target数据库中删除原表空间。使用impdp工具导入dump文件进入target数据库；
ü 清理环境，将创建的auxiliary数据库删除；
这种系列操作，我们如果使用RMAN命令系列是可以逐步完成的。自动化RMAN的TSPITR操作也是对这个过程的完全自动化运行。下面我们通过实验来进行演示操作。
2、环境准备
我们选择Oracle 11gR2进行试验。
SQL> select * from v$version;
BANNER
-----------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
创建实验表空间和实验数据表T。注意：由于我们使用的是11.2.0.1版本，如果使用sys用户进行测试，会引起Bug 12411104 : RMAN DUPLICATE RMAN-05548 WHEN CONNECTED TO TARGET。为避免问题，我们切换入scott用户执行。
SQL> create tablespace tsptiptbl datafile size 100m autoextend off
2 extent management local uniform size 1m
3 segment space management auto;
Tablespace created
--切换到scott用户进行试验，这个很重要！！（在11.2.0.1版本上）
SQL> conn scott/tiger@wilson ;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> create table t tablespace tsptiptbl as select * from dba_objects;
Table created
保留一个备份集合。
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
142 Full 1.33G DISK 00:02:52 16-FEB-14
BP Key: 142 Status: AVAILABLE Compressed: NO Tag: TAG20140216T191156
Piece Name: /u01/flash_recovery_area/WILSON/backupset/2014_02_16/o1_mf_nnndf_TAG20140216T191156_9j177yk1_.bkp
List of Datafiles in backup set 142
（篇幅原因，有省略……）
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
143 21.50K DISK 00:00:00 16-FEB-14
BP Key: 143 Status: AVAILABLE Compressed: NO Tag: TAG20140216T191454
Piece Name: /u01/flash_recovery_area/WILSON/backupset/2014_02_16/o1_mf_annnn_TAG20140216T191454_9j17fgd3_.bkp
List of Archived Logs in backup set 143
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 497 5398705 16-FEB-14 5398798 16-FEB-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
144 Full 9.67M DISK 00:00:04 16-FEB-14
BP Key: 144 Status: AVAILABLE Compressed: NO Tag: TAG20140216T191455
Piece Name: /u01/flash_recovery_area/WILSON/autobackup/2014_02_16/o1_mf_s_839704495_9j17flq9_.bkp
SPFILE Included: Modification time: 16-FEB-14
SPFILE db_unique_name: WILSON
Control File Included: Ckp SCN: 5398809 Ckp time: 16-FEB-14
3、误操作过程
当前时间，我们通过logseq编号进行表示。
SQL> select sequence#, status from v$log;
SEQUENCE# STATUS
---------- ----------------
502 INACTIVE
503 CURRENT
501 INACTIVE
SQL> create table t tablespace TSPTIPTBL as select * from dba_objects;
Table created
SQL> alter system switch logfile;
System altered
SQL> select sequence#, status from v$log;
SEQUENCE# STATUS
---------- ----------------
502 INACTIVE
503 ACTIVE
504 CURRENT
切换日志，到下一个日志对象中出现误操作。
SQL> alter system switch logfile;
System altered
SQL> select sequence#, status from v$log;
SEQUENCE# STATUS
---------- ----------------
505 CURRENT
503 ACTIVE
504 ACTIVE
SQL> truncate table t;
Table truncated
SQL> alter system switch logfile;
System altered
SQL> select sequence#, status from v$log;
SEQUENCE# STATUS
---------- ----------------
505 ACTIVE
506 CURRENT
504 ACTIVE
恢复目标是将表空间TSPTIPTBL恢复到logseq=504位置上。
4、TSPITR操作
自动操作的第一步，要确定auxiliary数据库的临时存放位置。
[root@bspdev /]# mkdir /aux
[root@bspdev /]# ls -l | grep aux
drwxr-xr-x 2 root root 4096 Feb 16 19:33 aux
[root@bspdev /]# chown oracle:oinstall aux
[root@bspdev /]# ls -l | grep aux
drwxr-xr-x 2 oracle oinstall 4096 Feb 16 19:33 aux
在数据库启动open状态，我们就可以进行特定表空间的恢复。代码过程日志比较长，笔者在步骤中进行注释说明。
--直接使用控制文件作为catalog源；
RMAN> connect target /
connected to target database: WILSON (DBID=3906514064)
using target database control file instead of recovery catalog
RMAN> recover tablespace tsptiptbl until logseq 504 auxiliary destination '/aux'; --指定恢复需求
Starting recover at 16-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time
--设置auxiliary需要恢复的范围
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='bvua' –随机sid设置
initialization parameters used for automatic instance:
db_name=WILSON
db_unique_name=bvua_tspitr_WILSON
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/aux
log_archive_dest_1='location=/aux'
#No auxiliary parameter file used
starting up automatic instance WILSON
Oracle instance started
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 100666596 bytes
Database Buffers 184549376 bytes
Redo Buffers 6381568 bytes
Automatic instance created
--self contained检查
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully
--运行第一个脚本
contents of Memory Script:
{
# set requested point in time
set until logseq 504 thread 1;
# 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 16-FEB-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 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 /u01/flash_recovery_area/WILSON/autobackup/2014_02_16/o1_mf_s_839704495_9j17flq9_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/flash_recovery_area/WILSON/autobackup/2014_02_16/o1_mf_s_839704495_9j17flq9_.bkp tag=TAG20140216T191455
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:06
output file name=/aux/WILSON/controlfile/o1_mf_9j1dbxfk_.ctl
Finished restore at 16-FEB-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 logseq 504 thread 1;
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'TSPTIPTBL' ||' 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 12 to
"/u01/oradata/WILSON/datafile/o1_mf_tsptiptb_9j17231x_.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, 12;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace TSPTIPTBL offline immediate
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /aux/WILSON/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 16-FEB-14
using channel ORA_AUX_DISK_1
--只还原最重要的相关文件：system、sysaux和undo
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 /aux/WILSON/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /aux/WILSON/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /aux/WILSON/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/oradata/WILSON/datafile/o1_mf_tsptiptb_9j17231x_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/flash_recovery_area/WILSON/backupset/2014_02_16/o1_mf_nnndf_TAG20140216T191156_9j177yk1_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/flash_recovery_area/WILSON/backupset/2014_02_16/o1_mf_nnndf_TAG20140216T191156_9j177yk1_.bkp tag=TAG20140216T191156
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:08:05
Finished restore at 16-FEB-14
datafile 1 switched to datafile copy
input datafile copy RECID=27 STAMP=839710037 file name=/aux/WILSON/datafile/o1_mf_system_9j1dchvb_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=28 STAMP=839710037 file name=/aux/WILSON/datafile/o1_mf_undotbs1_9j1dchwb_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=29 STAMP=839710037 file name=/aux/WILSON/datafile/o1_mf_sysaux_9j1dchwm_.dbf
--脚本三：完成aux构建
contents of Memory Script:
{
# set requested point in time
set until logseq 504 thread 1;
# 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 12 online";
# recover and open resetlogs
recover clone database tablespace "TSPTIPTBL", "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 12 online
Starting recover at 16-FEB-14
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 498 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2014_02_16/o1_mf_1_498_9j17t9c5_.arc
archived log for thread 1 with sequence 499 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2014_02_16/o1_mf_1_499_9j17vtmf_.arc
archived log for thread 1 with sequence 500 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2014_02_16/o1_mf_1_500_9j1blznh_.arc
archived log for thread 1 with sequence 501 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2014_02_16/o1_mf_1_501_9j1bm5go_.arc
archived log for thread 1 with sequence 502 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2014_02_16/o1_mf_1_502_9j1bmsb4_.arc
archived log for thread 1 with sequence 503 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2014_02_16/o1_mf_1_503_9j1d153j_.arc
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=497
channel ORA_AUX_DISK_1: reading from backup piece /u01/flash_recovery_area/WILSON/backupset/2014_02_16/o1_mf_annnn_TAG20140216T191454_9j17fgd3_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/flash_recovery_area/WILSON/backupset/2014_02_16/o1_mf_annnn_TAG20140216T191454_9j17fgd3_.bkp tag=TAG20140216T191454
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/aux/1_497_796001920.dbf thread=1 sequence=497
channel clone_default: deleting archived log(s)
archived log file name=/aux/1_497_796001920.dbf RECID=342 STAMP=839710046
archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2014_02_16/o1_mf_1_498_9j17t9c5_.arc thread=1 sequence=498
archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2014_02_16/o1_mf_1_499_9j17vtmf_.arc thread=1 sequence=499
archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2014_02_16/o1_mf_1_500_9j1blznh_.arc thread=1 sequence=500
archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2014_02_16/o1_mf_1_501_9j1bm5go_.arc thread=1 sequence=501
archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2014_02_16/o1_mf_1_502_9j1bmsb4_.arc thread=1 sequence=502
archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2014_02_16/o1_mf_1_503_9j1d153j_.arc thread=1 sequence=503
media recovery complete, elapsed time: 00:00:39
Finished recover at 16-FEB-14
database opened
--脚本5：抽取数据
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace TSPTIPTBL read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/aux''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/aux''";
}
executing Memory Script
sql statement: alter tablespace TSPTIPTBL read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/aux''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/aux''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_bvua":
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_bvua" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_bvua is:
EXPDP> /aux/tspitr_bvua_45849.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TSPTIPTBL:
EXPDP> /u01/oradata/WILSON/datafile/o1_mf_tsptiptb_9j17231x_.dbf
EXPDP> Job "SYS"."TSPITR_EXP_bvua" successfully completed at 20:50:20
Export completed
--脚本六
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace TSPTIPTBL including contents keep datafiles';
}
executing Memory Script
database closed
database dismounted
Oracle instance shut down
sql statement: drop tablespace TSPTIPTBL including contents keep datafiles
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_bvua" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_bvua":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_bvua" successfully completed at 20:51:43
Import completed
--脚本七：收尾还原
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace TSPTIPTBL read write';
sql 'alter tablespace TSPTIPTBL offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace TSPTIPTBL read write
sql statement: alter tablespace TSPTIPTBL offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /aux/WILSON/datafile/o1_mf_temp_9j1dwtx1_.tmp deleted
auxiliary instance file /aux/WILSON/onlinelog/o1_mf_3_9j1dwm37_.log deleted
auxiliary instance file /aux/WILSON/onlinelog/o1_mf_2_9j1dwfc7_.log deleted
auxiliary instance file /aux/WILSON/onlinelog/o1_mf_1_9j1dw7xk_.log deleted
auxiliary instance file /aux/WILSON/datafile/o1_mf_sysaux_9j1dchwm_.dbf deleted
auxiliary instance file /aux/WILSON/datafile/o1_mf_undotbs1_9j1dchwb_.dbf deleted
auxiliary instance file /aux/WILSON/datafile/o1_mf_system_9j1dchvb_.dbf deleted
auxiliary instance file /aux/WILSON/controlfile/o1_mf_9j1dbxfk_.ctl deleted
Finished recover at 16-FEB-14
最后，我们检查数据表T情况。
SQL> select count(*) from t;
select count(*) from t
ORA-00376: 此时无法读取文件 12
ORA-01110: 数据文件 12: '/u01/oradata/WILSON/datafile/o1_mf_tsptiptb_9j17231x_.dbf'
检查表空间情况。
--状态是offline
SQL> select status from dba_tablespaces where tablespace_name='TSPTIPTBL';
STATUS
---------
OFFLINE
进行online操作。
SQL> conn sys/oracle@wilson as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
SQL> alter tablespace tsptiptbl online;
Tablespace altered
SQL> select status from dba_tablespaces where tablespace_name='TSPTIPTBL';
STATUS
---------
ONLINE
--数据恢复
SQL> select count(*) from scott.t;
COUNT(*)
----------
72729
5、结论
RMAN进行TSPITR是非常方便的，Oracle已经将固定的流程以脚本script的方式进行设置。TSPITR的还原粒度是表空间级别，我们通常更希望的是进行数据表级别定点恢复可以通过flashback细节技术完成。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29798713/viewspace-1349247/ ，如需转载，请注明出处，否则将追究法律责任。
