ITPub博客

首页 > 数据库 > Oracle > 使用RMAN进行表空间TSPITR自动恢复

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

Oracle 作者:lllllcheng 时间:2016-01-11 18:57:38 0 删除 编辑

Oracle发展到今天,在备份还原领域已经构建了完善的解决方案体系。绝大多数情况下,我们的备份还原需求都可以用Oracle成熟方案实现的。TSPITRTablespace Point In Time Recover)数据表空间定点恢复是我们在实践中偶尔会使用的一种恢复场景。本篇就围绕这个问题进行讨论实验。

 

1、聊聊TSPITR

 

严格的说TSPITR是一种相对细粒度的不完全恢复技术。我们通常见到的还原操作,都是将所有的表空间和数据还原到相同的一个时间点上,无论是否是故障恢复点。而TSPITR则是以表空间为粒度单元,单独将某个表空间内容还原到一个特定可恢复时间点上。

举一个例子:一个Oracle数据库运行在归档模式下,在夜间零时保留一份完全备份。早上七点时候,某个特定表空间上数据表(单个表独占表空间)发生一个误操作,数据损坏。要求在不伤害其他数据表数据的情况下,将表空间数据恢复到早上六点。

这样部分数据恢复的场景,就是TSPITR的典型应用。我们在实际场景中,常常希望恢复部分的数据到过去时间点上,而不希望对现有数据进行补录。

TSPITR目前最方便的方法是使用RMAN进行自动的恢复。其使用前提为两个,一个是必须存在相应的备份集合,另一个是表空间对象是“self-contain”,也就是其他表空间中不包括与这个表空间对象相关的对象数据。

恢复步骤和原理如下:

 

ü  RMAN三个对象集合:catalogtargetauxiliary。其中auxiliary就是用于辅助target各种备份还原要求的操作数据库。这个库在TSPITR中扮演临时数据还原作用的;

ü  首先完成数据检查工作,确定备份集合和表空间完整性;

ü  当前时间是T1,如果需要将数据还原为T0。使用RMAN提取备份数据集合,还原到auxiliary上形成一个新的实例数据库。选择性应用归档日志序列,还原auxiliaryTO时间点;

ü  使用expdp工具从auxiliary中导出目标表空间为dump对象。注意:还原auxiliary并不是100%重建target,而是选择系统运行表空间和目标表空间;

ü  target数据库中删除原表空间。使用impdp工具导入dump文件进入target数据库;

ü  清理环境,将创建的auxiliary数据库删除;

 

这种系列操作,我们如果使用RMAN命令系列是可以逐步完成的。自动化RMANTSPITR操作也是对这个过程的完全自动化运行。下面我们通过实验来进行演示操作。

 

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位置上。

 

4TSPITR操作

 

自动操作的第一步,要确定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

 

--只还原最重要的相关文件:systemsysauxundo

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博客 ” ,链接:http://blog.itpub.net/30606744/viewspace-1974959/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 配置ASM
请登录后发表评论 登录
全部评论

注册时间:2015-12-02

  • 博文量
    41
  • 访问量
    119786