使用RMAN进行表空间TSPITR自动恢复

使用RMAN进行表空间TSPITR自动恢复

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_m f_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_m f_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_m f_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_m f_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_1 6/o1_mf_1_498_9j17t9c5_.arc thread=1 sequence=498

archived log file

name=/u01/flash_recovery_area/WILSON/archivelog/2014_02_1 6/o1_mf_1_499_9j17vtmf_.arc thread=1 sequence=499 archived log file

name=/u01/flash_recovery_area/WILSON/archivelog/2014_02_1 6/o1_mf_1_500_9j1blznh_.arc thread=1 sequence=500 archived log file

name=/u01/flash_recovery_area/WILSON/archivelog/2014_02_1 6/o1_mf_1_501_9j1bm5go_.arc thread=1 sequence=501 archived log file

name=/u01/flash_recovery_area/WILSON/archivelog/2014_02_1 6/o1_mf_1_502_9j1bmsb4_.arc thread=1 sequence=502 archived log file

name=/u01/flash_recovery_area/WILSON/archivelog/2014_02_1 6/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_tsptipt b_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_.db f'

检查表空间情况。

--状态是offline