ITPub博客

首页 > 数据库 > Oracle > ORACLE TSPITR 介绍

ORACLE TSPITR 介绍

Oracle 作者:Otakuzel 时间:2016-01-10 15:01:19 0 删除 编辑

一、      TSPITR 架构

TSPITR 是基于表空间的时间点恢复,架构如下:

过程如下:

1创建实例(自动或者手动)

2 Restore database

只恢复(system undotbs  sysaux和指定的需要恢复的表空间)

3 recover database until time

4 使用exp从还原数据库导出

5 导入到源数据库

二、      3种实现方式

1 Fully automated TSPITR

全自动本机恢复,只需要设置 创建auxiliary destination 

rman target /

RECOVER TABLESPACE "TEST" until time "to_date( '2012-12-09 17:05:07', 'yyyy-mm-dd HH24:MI:SS')" AUXILIARY DESTINATION '/data/auxdest/';

2 Customized TSPITR with an automatic auxiliary instance

在第一种基础上添加一些自定义控制,: SET NEWNAME, CONFIGURE AUXNAME,初始化参数(SET AUXILIARY INSTANCE PARAMETER FILE)

   rman target /

RUN

{

  SET NEWNAME FOR DATAFILE '?/oradata/prod/system01.dbf'

    TO '/disk1/auxdest/system01.dbf';

  SET NEWNAME FOR DATAFILE '?/oradata/prod/sysaux01.dbf'

    TO '/disk1/auxdest/sysaux01.dbf';

  SET NEWNAME FOR DATAFILE '?/oradata/prod/undotbs01.dbf'

    TO '/disk1/auxdest/undotbs01.dbf';

  RECOVER TABLESPACE users, tools

    UNTIL LOGSEQ 1300 THREAD 1

    AUXILIARY DESTINATION '/disk1/auxdest';

}

3 TSPITR with your own auxiliary instance

相比前两种需要手动创建auxiliary instance,并在完成后手动清理该实例

该方式步骤:

1 创建密码文件

2 创建初始化参数

3 配置tnsnames.ora 并能连接上Auxiliary Instance

4 执行恢复

rman target / auxiliary sys/syspwd@pitprod

RUN

{

# Specify NEWNAME for recovery set data files

  SET NEWNAME FOR TABLESPACE clients

                        TO '?/oradata/prod/rec/%b';

 

# Specify NEWNAMES for some auxiliary set

# data files that have a valid image copy to avoid restores:

  SET NEWNAME FOR DATAFILE '?/oradata/prod/system01.dbf'

                        TO '/backups/prod/system01_monday_noon.dbf';

  SET NEWNAME FOR DATAFILE '?/oradata/prod/system02.dbf'

                        TO '/backups/prod/system02_monday_noon.dbf';

  SET NEWNAME FOR DATAFILE '?/oradata/prod/sysaux01.dbf'

                        TO '/backups/prod/sysaux01_monday_noon.dbf';

  SET NEWNAME FOR DATAFILE '?/oradata/prod/undo01.dbf'

                        TO '/backups/prod/undo01_monday_noon.dbf';

 

# Specify the types of channels to use

  ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE DISK;

  ALLOCATE AUXILIARY CHANNEL t1 DEVICE TYPE sbt;

 

# Recover the clients tablespace to 24 hours ago:

  RECOVER TABLESPACE clients UNTIL TIME 'sysdate-1';

}

三、      恢复后需要做的工作

表空间恢复后,状态是offline

BACKUP TABLESPACE users, tools;

SQL "ALTER TABLESPACE users, tools ONLINE";

四、      TSPITR一些限制条件

1 要恢复的表空间必须是自包含

2 如果表空间被rename必须使用以前的名字

3 不能恢复包含sys对象的表空间

4 不能恢复包含Undo or rollback的表空间

5 包含物化视图的表空间

五、      11G 新特性

支持对DROP过的TABLESPACE进行恢复

六、      Fully automated TSPITR— 方式示例:

·          

·         [oracle@localhost ~]$ rman target /

·          

·         Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 9 17:04:22 2013

·          

·         Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

·          

·         connected to target database: ORCL2 (DBID=796401487)

·          

·         RMAN> backup database;

·          

·         Starting backup at 09-DEC-13

·         using target database control file instead of recovery catalog

·         allocated channel: ORA_DISK_1

·         channel ORA_DISK_1: SID=673 device type=DISK

·         channel ORA_DISK_1: starting full datafile backup set

·         channel ORA_DISK_1: specifying datafile(s) in backup set

·         input datafile file number=00001 name=/u01/app/oracle/oradata/orcl2/system01.dbf

·         input datafile file number=00002 name=/u01/app/oracle/oradata/orcl2/sysaux01.dbf

·         input datafile file number=00003 name=/u01/app/oracle/oradata/orcl2/undotbs01.dbf

·         input datafile file number=00004 name=/u01/app/oracle/oradata/orcl3/test.dbf

·         input datafile file number=00005 name=/u01/app/oracle/oradata/orcl2/test1.dbf

·         channel ORA_DISK_1: starting piece 1 at 09-DEC-13

·         channel ORA_DISK_1: finished piece 1 at 09-DEC-13

·         piece handle=/backup/db_0for3icv_1_1 tag=TAG20131209T170431 comment=NONE

·         channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

·         Finished backup at 09-DEC-13

·          

·         Starting Control File and SPFILE Autobackup at 09-DEC-13

·         piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2013_12_09/o1_mf_s_833735087_9bc1xjb0_.bkp comment=NONE

·         Finished Control File and SPFILE Autobackup at 09-DEC-13

·          

·         RMAN> exit

·          

·          

·         Recovery Manager complete.

·         [oracle@localhost ~]$ sqlplus / as sysdba

·          

·         SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 9 17:05:03 2013

·          

·         Copyright (c) 1982, 2009, Oracle.  All rights reserved.

·          

·          

·         Connected to:

·         Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

·         With the Partitioning, OLAP, Data Mining and Real Application Testing options

·          

·         SQL> select to_char(sysdate,'yyyy-mm-dd HH24:MI:SS') from dual;

·          

·         TO_CHAR(SYSDATE,'YY

·         -------------------

·         2013-12-09 17:05:07

·          

·         SQL> conn test/test

·         Connected.

·         SQL> truncate table tab_abc;

·          

·         Table truncated.

·          

·         SQL> select count(*) from tab_abc;

·          

·           COUNT(*)

·         ----------

·                  0

·          

·         SQL> exit

·         Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

·         With the Partitioning, OLAP, Data Mining and Real Application Testing options

·         [oracle@localhost ~]$ rman target /     

·          

·         Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 9 17:06:02 2013

·          

·         Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

·          

·         connected to target database: ORCL2 (DBID=796401487)

·          

·         RMAN> RECOVER TABLESPACE "TEST" until time "to_date( '2013-12-09 17:05:07', 'yyyy-mm-dd HH24:MI:SS')" AUXILIARY DESTINATION '/data/auxdest/';

·          

·         Starting recover at 09-DEC-13

·         using target database control file instead of recovery catalog

·         allocated channel: ORA_DISK_1

·         channel ORA_DISK_1: SID=673 device type=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='xogB'

·          

·         initialization parameters used for automatic instance:

·         db_name=ORCL2

·         db_unique_name=xogB_tspitr_ORCL2

·         compatible=11.2.0.0.0

·         db_block_size=8192

·         db_files=200

·         sga_target=280M

·         processes=50

·         db_create_file_dest=/data/auxdest/

·         log_archive_dest_1='location=/data/auxdest/'

·         #No auxiliary parameter file used

·          

·          

·         starting up automatic instance ORCL2

·          

·         Oracle instance started

·          

·         Total System Global Area     292278272 bytes

·          

·         Fixed Size                     2212736 bytes

·         Variable Size                104860800 bytes

·         Database Buffers             180355072 bytes

·         Redo Buffers                   4849664 bytes

·         Automatic instance created

·         Running TRANSPORT_SET_CHECK on recovery set tablespaces

·         TRANSPORT_SET_CHECK completed successfully

·         自包含检测

·         contents of Memory Script:

·         {

·         # set requested point in time

·         set until  time "to_date( '2013-12-09 17:05:07', 'yyyy-mm-dd HH24:MI:SS')";

·         # 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 09-DEC-13

·         allocated channel: ORA_AUX_DISK_1

·         channel ORA_AUX_DISK_1: SID=30 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/app/oracle/flash_recovery_area/ORCL2/autobackup/2013_12_09/o1_mf_s_833735087_9bc1xjb0_.bkp

·         channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2013_12_09/o1_mf_s_833735087_9bc1xjb0_.bkp tag=TAG20131209T170447

·         channel ORA_AUX_DISK_1: restored backup piece 1

·         channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

·         output file name=/data/auxdest/ORCL2/controlfile/o1_mf_9bc234dp_.ctl

·         Finished restore at 09-DEC-13

·          

·         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  time "to_date( '2013-12-09 17:05:07', 'yyyy-mm-dd HH24:MI:SS')";

·         plsql <<<-- tspitr_2

·         declare

·           sqlstatement       varchar2(512);

·           offline_not_needed exception;

·           pragma exception_init(offline_not_needed, -01539);

·         begin

·           sqlstatement := 'alter tablespace '||  '"TEST"' ||' 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  4 to

·          "/u01/app/oracle/oradata/orcl3/test.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, 4;

·         switch clone datafile all;

·         }

·         executing Memory Script

·          

·         executing command: SET until clause

·          

·         sql statement: alter tablespace "TEST" 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 /data/auxdest/ORCL2/datafile/o1_mf_temp1_%u_.tmp in control file

·          

·         Starting restore at 09-DEC-13

·         using channel ORA_AUX_DISK_1

·          

·         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 /data/auxdest/ORCL2/datafile/o1_mf_system_%u_.dbf

·         channel ORA_AUX_DISK_1: restoring datafile 00003 to /data/auxdest/ORCL2/datafile/o1_mf_undotbs1_%u_.dbf

·         channel ORA_AUX_DISK_1: restoring datafile 00002 to /data/auxdest/ORCL2/datafile/o1_mf_sysaux_%u_.dbf

·         channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl3/test.dbf

·         channel ORA_AUX_DISK_1: reading from backup piece /backup/db_0for3icv_1_1

·         channel ORA_AUX_DISK_1: piece handle=/backup/db_0for3icv_1_1 tag=TAG20131209T170431

·         channel ORA_AUX_DISK_1: restored backup piece 1

·         channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

·         Finished restore at 09-DEC-13

·          

·         datafile 1 switched to datafile copy

·         input datafile copy RECID=4 STAMP=833735301 file name=/data/auxdest/ORCL2/datafile/o1_mf_system_9bc23d3c_.dbf

·         datafile 3 switched to datafile copy

·         input datafile copy RECID=5 STAMP=833735301 file name=/data/auxdest/ORCL2/datafile/o1_mf_undotbs1_9bc23d4v_.dbf

·         datafile 2 switched to datafile copy

·         input datafile copy RECID=6 STAMP=833735301 file name=/data/auxdest/ORCL2/datafile/o1_mf_sysaux_9bc23d43_.dbf

·          

·         contents of Memory Script:

·         {

·         # set requested point in time

·         set until  time "to_date( '2013-12-09 17:05:07', 'yyyy-mm-dd HH24:MI:SS')";

·         # 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  4 online";

·         # recover and open resetlogs

·         recover clone database tablespace  "TEST", "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  4 online

·          

·         Starting recover at 09-DEC-13

·         using channel ORA_AUX_DISK_1

·          

·         starting media recovery

·          

·         archived log for thread 1 with sequence 166 is already on disk as file /data/archivelog/1_166_821555741.dbf

·         archived log file name=/data/archivelog/1_166_821555741.dbf thread=1 sequence=166

·         media recovery complete, elapsed time: 00:00:00

·         Finished recover at 09-DEC-13

·          

·         database opened

·          

·         contents of Memory Script:

·         {

·         # make read only the tablespace that will be exported

·         sql clone 'alter tablespace  "TEST" read only';

·         # create directory for datapump import

·         sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

·         /data/auxdest/''";

·         # create directory for datapump export

·         sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

·         /data/auxdest/''";

·         }

·         executing Memory Script

·          

·         sql statement: alter tablespace  "TEST" read only

·          

·         sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/data/auxdest/''

·          

·         sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/data/auxdest/''

·          

·         Performing export of metadata...

·            EXPDP> Starting "SYS"."TSPITR_EXP_xogB": 

·            EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

·            EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

·            EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

·            EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

·            EXPDP> Master table "SYS"."TSPITR_EXP_xogB" successfully loaded/unloaded

·            EXPDP> ******************************************************************************

·            EXPDP> Dump file set for SYS.TSPITR_EXP_xogB is:

·            EXPDP>   /data/auxdest/tspitr_xogB_17243.dmp

·            EXPDP> ******************************************************************************

·            EXPDP> Datafiles required for transportable tablespace TEST:

·            EXPDP>   /u01/app/oracle/oradata/orcl3/test.dbf

·            EXPDP> Job "SYS"."TSPITR_EXP_xogB" successfully completed at 17:09:04

·         Export completed

·          

·          

·         contents of Memory Script:

·         {

·         # shutdown clone before import

·         shutdown clone immediate

·         # drop target tablespaces before importing them back

·         sql 'drop tablespace  "TEST" including contents keep datafiles';

·         }

·         executing Memory Script

·          

·         database closed

·         database dismounted

·         Oracle instance shut down

·          

·         sql statement: drop tablespace  "TEST" including contents keep datafiles

·          

·         Performing import of metadata...

·            IMPDP> Master table "SYS"."TSPITR_IMP_xogB" successfully loaded/unloaded

·            IMPDP> Starting "SYS"."TSPITR_IMP_xogB": 

·            IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

·            IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

·            IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

·            IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

·            IMPDP> Job "SYS"."TSPITR_IMP_xogB" successfully completed at 17:09:21

·         Import completed

·          

·          

·         contents of Memory Script:

·         {

·         # make read write and offline the imported tablespaces

·         sql 'alter tablespace  "TEST" read write';

·         sql 'alter tablespace  "TEST" offline';

·         # enable autobackups after TSPITR is finished

·         sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';

·         }

·         executing Memory Script

·          

·         sql statement: alter tablespace  "TEST" read write

·          

·         sql statement: alter tablespace  "TEST" offline

·          

·         sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

·          

·         Removing automatic instance

·         Automatic instance removed

·         auxiliary instance file /data/auxdest/ORCL2/datafile/o1_mf_temp1_9bc24g99_.tmp deleted

·         auxiliary instance file /data/auxdest/ORCL2/onlinelog/o1_mf_3_9bc24cxy_.log deleted

·         auxiliary instance file /data/auxdest/ORCL2/onlinelog/o1_mf_2_9bc24boh_.log deleted

·         auxiliary instance file /data/auxdest/ORCL2/onlinelog/o1_mf_1_9bc249d6_.log deleted

·         auxiliary instance file /data/auxdest/ORCL2/datafile/o1_mf_sysaux_9bc23d43_.dbf deleted

·         auxiliary instance file /data/auxdest/ORCL2/datafile/o1_mf_undotbs1_9bc23d4v_.dbf deleted

·         auxiliary instance file /data/auxdest/ORCL2/datafile/o1_mf_system_9bc23d3c_.dbf deleted

·         auxiliary instance file /data/auxdest/ORCL2/controlfile/o1_mf_9bc234dp_.ctl deleted

·         Finished recover at 09-DEC-13

以下是对AUXILIARY DESTINATION的监控,只恢复了系统必须的表空间和包含要恢复的表空间

|-- ORCL2

|   |-- controlfile

|   |   `-- o1_mf_9bc234dp_.ctl

|   |-- datafile

|   |   |-- o1_mf_sysaux_9bc23d43_.dbf

|   |   |-- o1_mf_system_9bc23d3c_.dbf

|   |   |-- o1_mf_temp1_9bc24g99_.tmp

|   |   `-- o1_mf_undotbs1_9bc23d4v_.dbf

|   `-- onlinelog

|       |-- o1_mf_1_9bc249d6_.log

|       |-- o1_mf_2_9bc24boh_.log

|       `-- o1_mf_3_9bc24cxy_.log

|-- tspitr_aaBm_25445.dmp

 

 

恢复后文件自动清除

 

|-- ORCL2

|   |-- controlfile

|   |-- datafile

|   `-- onlinelog

|-- tspitr_aaBm_25445.dmp

 

 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30604784/viewspace-1974533/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 11gRAC安装
请登录后发表评论 登录
全部评论

注册时间:2015-12-01

  • 博文量
    58
  • 访问量
    206384