ORACLE TSPITR 介紹

一、 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

·

· 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

·

· 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

·

· 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 <<

· 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

·