Oracle以TSPITR方式恢复表空间数据一例

Oracle以TSPITR方式恢复表空间数据一例

通常见到的还原操作，都是将所有的表空间和数据还原到相同的一个时间点上，无论是否是故障恢复点。而TSPITR则是以表空间为粒度单元，单独将某个表空间内容还原到一个特定可恢复时间点上。

举一个例子：一个Oracle数据库运行在归档模式下，在夜间22点保留一份完全备份。早上8点时候，某个特定表空间上数据表（单个表独占表空间）发生一个误操作，数据损坏。要求在不伤害其他数据表数据的情况下，将表空间数据恢复到早上5点。这样部分数据恢复的场景，就是TSPITR的典型应用。在实际场景中，常常希望恢复部分的数据到过去时间点上。

 

TSPITR目前最方便的方法是使用RMAN进行自动的恢复。

其使用前提为两个：

1. 必须存在相应的备份集合

2. 表空间对象是子包含的，也就是其他表空间中不包括与这个表空间对象相关的对象数据（互相独立）

 

 

恢复步骤和原理如下：

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操作也是对这个过程的完全自动化运行。

下面通过实验来进行演示操作：

 

--创建测试用户zlm并赋予权限   SQL> create user zlm identified by zlm;   User created.   SQL> grant dba to zlm;   Grant succeeded.   --创建测试表空间tspitr   SQL> create tablespace tspitr datafile '/data/oradata/ora10g/tspitr01.dbf' size 100m autoextend off extent management local uniform size 1m segment space management auto;   Tablespace created.   --修改用户zlm缺省表空间为tspitr   SQL> alter user zlm default tablespace tspitr;   User altered.   SQL> show user USER is "" SQL> conn zlm/zlm@ora10g213 Connected. SQL>  !   --创建一个RMAN备份集   [oracle@bak ~]$ rman target /   Recovery Manager: Release 10.2.0.1.0 - Production on Fri Dec 26 16:44:00 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.   connected to target database: ORA10G (DBID=4175411955)   RMAN> backup as compressed backupset database format '/u01/orabackup/backupsets/full_ora10g_%U' plus archive log format '/u01/orabackup/backupsets/arc_ora10g_%U' delete all input;     Starting backup at 26-DEC-14 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=138 devtype=DISK channel ORA_DISK_1: starting compressed archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=30 recid=30 stamp=867343597 channel ORA_DISK_1: starting piece 1 at 26-DEC-14 channel ORA_DISK_1: finished piece 1 at 26-DEC-14 piece handle=/u01/orabackup/backupsets/arc_ora10g_13pr577g_1_1 tag=TAG20141226T164639 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 channel ORA_DISK_1: deleting archive log(s) archive log filename=/oracle/flash_recovery_area/ORA10G/archivelog/2014_12_26/o1_mf_1_30_b9t83f1s_.arc recid=30 stamp=867343597 Finished backup at 26-DEC-14   Starting backup at 26-DEC-14 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=/data/oradata/ora10g/system01.dbf input datafile fno=00003 name=/data/oradata/ora10g/sysaux01.dbf input datafile fno=00002 name=/data/oradata/ora10g/undotbs01.dbf input datafile fno=00005 name=/data/oradata/ora10g/example01.dbf input datafile fno=00006 name=/data/oradata/ora10g/tspitr01.dbf input datafile fno=00004 name=/data/oradata/ora10g/users01.dbf channel ORA_DISK_1: starting piece 1 at 26-DEC-14 channel ORA_DISK_1: finished piece 1 at 26-DEC-14 piece handle=/u01/orabackup/backupsets/full_ora10g_14pr577l_1_1 tag=TAG20141226T164644 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:02:47 Finished backup at 26-DEC-14   Starting backup at 26-DEC-14 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=31 recid=31 stamp=867343772 channel ORA_DISK_1: starting piece 1 at 26-DEC-14 channel ORA_DISK_1: finished piece 1 at 26-DEC-14 piece handle=/u01/orabackup/backupsets/arc_ora10g_15pr57ct_1_1 tag=TAG20141226T164933 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archive log(s) archive log filename=/oracle/flash_recovery_area/ORA10G/archivelog/2014_12_26/o1_mf_1_31_b9t88wnv_.arc recid=31 stamp=867343772 Finished backup at 26-DEC-14   Starting Control File and SPFILE Autobackup at 26-DEC-14 piece handle=/u01/orabackup/backupsets/ora10g-c-4175411955-20141226-05.ctl comment=NONE Finished Control File and SPFILE Autobackup at 26-DEC-14   RMAN> exit     Recovery Manager complete.   --连接到测试用户zlm查看当前日志   [oracle@bak ~]$ sqlplus /nolog   SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 26 16:50:46 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.     Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options   SQL> conn zlm/zlm@ora10g213 Connected. SQL> select sequence#,status from v$log;    SEQUENCE# STATUS ---------- ----------------        32 CURRENT         30 INACTIVE         31 ACTIVE   --创建测试表t1，并切换若干次日志   SQL> create table t1 as select * from dba_objects;   Table created.   SQL> alter system switch logfile;   System altered.   SQL> select sequence#,status from v$log;    SEQUENCE# STATUS ---------- ----------------         32 ACTIVE         33 CURRENT         31 ACTIVE   SQL> alter system switch logfile;   System altered.   SQL> select sequence#,status from v$log;    SEQUENCE# STATUS ---------- ----------------         32 ACTIVE         33 ACTIVE         34 CURRENT    --此时仍然时候数据的   SQL> select count(*) from t1;     COUNT(*) ----------      50382   --对表进行truancate操作，模拟误操作   SQL> truncate table t1;   Table truncated.   SQL> select count(*) from t1;     COUNT(*) ----------          0   SQL> alter system switch logfile;   System altered.   SQL> select sequence#,status from v$log;    SEQUENCE# STATUS ---------- ----------------         35 CURRENT    --truncate之后又切换了一次日志，当前日志为35         33 ACTIVE         34 ACTIVE   SQL> !   创建表的时候，logseq是32，之后切换了一次日志，到33，此时的表中仍然是有数据的，但是truncate操作后表中的数据就没有了，这个动作基本就是在logseq 34的时候发生的，truncate完以后又切了日志，到了35，我们实验的目的就是要把表空间恢复到logseq=34这个时间点（Time In Point）。根据刚才说的那个例子，就是到故障点8点以前的某个数据未丢失的时间点（5点）   --创建一个auxiliary目录（必要步骤） [oracle@bak ~]$ mkdir /u01/aux [oracle@bak ~]$ rman target /   Recovery Manager: Release 10.2.0.1.0 - Production on Fri Dec 26 16:54:36 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.   connected to target database: ORA10G (DBID=4175411955)   auxiliary目标目录中会在之后的RMAN脚本执行过程中，生成一些系统恢复需要的文件，等RMAN恢复完后会展示一下这个目录的结构。   --最关键的来了，用RMAN命令进行TSPITR恢复到故障点之前的某个时刻 RMAN> recover tablespace tspitr until logseq 33 auxiliary destination '/u01/aux';   Starting recover at 26-DEC-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=159 devtype=DISK RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in time   List of tablespaces expected to have UNDO segments    --有undo段的表空间列表 tablespace SYSTEM tablespace UNDOTBS1   Creating automatic instance, with SID='xvBc'    --创建一个自动化实例，随机SID为xvBc   initialization parameters used for automatic instance:    --为自动化实例分配的参数 db_name=ORA10G compatible=10.2.0.1.0 db_block_size=8192 db_files=200 db_unique_name=tspitr_ORA10G_xvBc large_pool_size=1M shared_pool_size=110M #No auxiliary parameter file used db_create_file_dest=/u01/aux    --之前创建的auxiliary目录 control_files=/u01/aux/cntrl_tspitr_ORA10G_xvBc.f    --控制文件的位置     starting up automatic instance ORA10G    --启动自动化实例   Oracle instance started   Total System Global Area     201326592 bytes   Fixed Size                     1218508 bytes Variable Size                146802740 bytes Database Buffers              50331648 bytes Redo Buffers                   2973696 bytes Automatic instance created   contents of Memory Script:   --第1个内存脚本内容 { # set the until clause set until  logseq 33 thread 1;    --指定的需要恢复到的logseq # 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 26-DEC-14 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=36 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/orabackup/backupsets/ora10g-c-4175411955-20141226-05.ctl channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/u01/orabackup/backupsets/ora10g-c-4175411955-20141226-05.ctl tag=TAG20141226T164935 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02 output filename=/u01/aux/cntrl_tspitr_ORA10G_xvBc.f Finished restore at 26-DEC-14   sql statement: alter database mount clone database   sql statement: alter system archive log current   sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end; released channel: ORA_DISK_1 released channel: ORA_AUX_DISK_1   contents of Memory Script:   --第2个内存脚本内容 { # generated tablespace point-in-time recovery script # set the until clause set until  logseq 33 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  2 to new; # set a destination filename for restore set newname for datafile  6 to   "/data/oradata/ora10g/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 2 to /u01/aux/TSPITR_ORA10G_XVBC/datafile/o1_mf_temp_%u_.tmp in control file   Starting restore at 26-DEC-14 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=39 devtype=DISK   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/aux/TSPITR_ORA10G_XVBC/datafile/o1_mf_system_%u_.dbf restoring datafile 00002 to /u01/aux/TSPITR_ORA10G_XVBC/datafile/o1_mf_undotbs1_%u_.dbf restoring datafile 00006 to /data/oradata/ora10g/tspitr01.dbf channel ORA_AUX_DISK_1: reading from backup piece /u01/orabackup/backupsets/full_ora10g_14pr577l_1_1 channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/u01/orabackup/backupsets/full_ora10g_14pr577l_1_1 tag=TAG20141226T164644 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:26 Finished restore at 26-DEC-14   datafile 1 switched to datafile copy input datafile copy recid=4 stamp=867344300 filename=/u01/aux/TSPITR_ORA10G_XVBC/datafile/o1_mf_system_b9t8nvw3_.dbf datafile 2 switched to datafile copy input datafile copy recid=5 stamp=867344300 filename=/u01/aux/TSPITR_ORA10G_XVBC/datafile/o1_mf_undotbs1_b9t8nvx5_.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 26-DEC-14 using channel ORA_AUX_DISK_1   starting media recovery   archive log thread 1 sequence 32 is already on disk as file /oracle/flash_recovery_area/ORA10G/archivelog/2014_12_26/o1_mf_1_32_b9t8dkr8_.arc 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=31 channel ORA_AUX_DISK_1: reading from backup piece /u01/orabackup/backupsets/arc_ora10g_15pr57ct_1_1 channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/u01/orabackup/backupsets/arc_ora10g_15pr57ct_1_1 tag=TAG20141226T164933 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_31_860802036.dbf thread=1 sequence=31 channel clone_default: deleting archive log(s) archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_31_860802036.dbf recid=32 stamp=867344306 archive log filename=/oracle/flash_recovery_area/ORA10G/archivelog/2014_12_26/o1_mf_1_32_b9t8dkr8_.arc thread=1 sequence=32 media recovery complete, elapsed time: 00:00:05 Finished recover at 26-DEC-14   database opened   contents of Memory Script:    --第3个内存脚本（逻辑导入导出） { # export the tablespaces in the recovery set host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/product/10.2.0/db_1/bin/oracle\)\(ARGV0=oraclexvBc\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=xvBc^'\)\)\(CONNECT_DATA=\(SID=xvBc\)\)\) 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 =\"/@ 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;'; } executing Memory Script     Export: Release 10.2.0.1.0 - Production on Fri Dec 26 16:58:55 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.     Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining 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...    --执行导出TSPITR对象 For tablespace TSPITR ... . exporting cluster definitions . exporting table definitions . . exporting table                              T . . exporting table                             T1    --导出误删除的测试表 . 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.1.0 - Production on Fri Dec 26 16:59:17 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.     Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining 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 ZLM's objects into ZLM . . importing table                            "T" . . importing table                           "T1"    --导入误删除的测试表 . 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    --最后一步操作是把要恢复的表空间offline的   sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;   Removing automatic instance    --删除自动化实例以及相关文件 Automatic instance removed auxiliary instance file /u01/aux/cntrl_tspitr_ORA10G_xvBc.f deleted auxiliary instance file /u01/aux/TSPITR_ORA10G_XVBC/datafile/o1_mf_system_b9t8nvw3_.dbf deleted auxiliary instance file /u01/aux/TSPITR_ORA10G_XVBC/datafile/o1_mf_undotbs1_b9t8nvx5_.dbf deleted auxiliary instance file /u01/aux/TSPITR_ORA10G_XVBC/datafile/o1_mf_temp_b9t8t370_.tmp deleted auxiliary instance file /u01/aux/TSPITR_ORA10G_XVBC/onlinelog/o1_mf_1_b9t8srrv_.log deleted auxiliary instance file /u01/aux/TSPITR_ORA10G_XVBC/onlinelog/o1_mf_2_b9t8svp8_.log deleted auxiliary instance file /u01/aux/TSPITR_ORA10G_XVBC/onlinelog/o1_mf_3_b9t8sy5v_.log deleted Finished recover at 26-DEC-14   RMAN> exit     Recovery Manager complete. [oracle@bak ~]$ sqlplus /nolog   SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 26 17:03:26 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.   SQL> conn zlm/zlm@ora10g213 Connected. SQL> select count(*) from t1; select count(*) from t1                      * ERROR at line 1: ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: '/data/oradata/ora10g/tspitr01.dbf'   无法打开恢复的6号文件，由于最后的操作是把该表空间offline的   SQL> select status from dba_tablespaces where tablespace_name='TSPITR';   STATUS --------- OFFLINE   --重新将tspitr表空间online   SQL> alter tablespace tspitr online;   Tablespace altered.   SQL> select count(*) from t1;     COUNT(*) ----------      50382   之前误删除的测试表T1的数据又回来了。   最后来看一下auxiliary目录结构   [oracle@bak admin]$ cd /u01/aux [oracle@bak aux]$ ll total 4 drwxr-x--- 4 oracle oinstall 4096 Dec 26 16:55 TSPITR_ORA10G_XVBC [oracle@bak aux]$ cd TSPITR_ORA10G_XVBC/ [oracle@bak TSPITR_ORA10G_XVBC]$ ll -lrth total 8.0K drwxr-x--- 2 oracle oinstall 4.0K Dec 26 16:59 onlinelog drwxr-x--- 2 oracle oinstall 4.0K Dec 26 16:59 datafile [oracle@bak TSPITR_ORA10G_XVBC]$ cd onlinelog/ [oracle@bak onlinelog]$ ll total 0 [oracle@bak onlinelog]$ cd .. [oracle@bak TSPITR_ORA10G_XVBC]$ cd datafile [oracle@bak datafile]$ ll total 0 [oracle@bak datafile]$    虽然在中间过程创建了相关目录和文件，恢复结束以后，只留下了目录结构，文件都被自动删除了   总结：   其实TSPITR方式是对整个表空间的恢复，无论该表空间上有多少张表或对象，只要是自包含的表空间，就可以使用这种方法来进行恢复。但是也有局限性，比方说要恢复的表空间上有许多表，也是自包含的表空间，由于某张表的误删除，恢复时把所有该表空间上表的数据回退到故障点之前的某个时间点，而有时往往并不想这么做。因此，对于粒度更细的精确到表级别的恢复，TSPITR并非最佳方式，利用闪回特性来恢复才是更好的方案。