RMAN基于表空间的不完全恢复（TSPITR)
表空间时间点恢复(TSPITR)：概念
通过执行TSPITR 可将一个或多个表空间快速恢复到以前的某个时间。
执行TSPITR 不会影响数据库中其它表空间或对象的状态。
使用RMAN 自动表空间时间点恢复(TSPITR) 可将Oracle DB 中的一个或多个表空间快速恢复到以前的某个时间，而不会影响数据库中其它表空间和对象的状态。
表空间时间点恢复(TSPITR)：术语
目标时间：表空间恢复到的时间点或SCN
恢复集：组成要恢复的表空间的数据文件
辅助集：对恢复集执行TSPITR 时所必需的，但不属于恢复集的数据文件。辅助集通常包括：
– SYSTEM表空间
– 还原段表空间
– 临时表空间
辅助目标：存储文件的磁盘位置
讨论TSPITR 时使用了以下术语：
目标时间：执行TSPITR 期间表空间将恢复到的时间点或系统更改号(SCN)。
恢复集：组成要恢复的表空间的数据文件。
辅助集：对恢复集执行TSPITR 时所必需的，但本身不属于恢复集的数据文件。辅助集通常包括：
- SYSTEM表空间的副本
- 包含来自目标实例的还原段的数据文件
- 某些情况下从辅助实例导出数据库对象期间使用的临时表空间
辅助目标：执行TSPITR 期间在磁盘上存储辅助实例的任何辅助集数据文件、控制文件和联机日志的位置。执行完TSPITR 之后可删除辅助目标中存储的文件。
表空间时间点恢复：体系结构
图表中显示了下列TSPITR 实体：
目标数据库：包含要恢复的表空间
控制文件：向RMAN 提供备份信息
备份集：来自目标数据库，用作重建表空间的来源
归档重做日志：来自目标数据库，用作重建表空间的来源
辅助实例：在恢复过程中用来执行恢复的Oracle DB 实例
RMAN 在执行表空间时间点恢复期间会执行下列步骤：
1.将目标时间之前某个时间点的备份控制文件还原到辅助实例。将恢复集的数据文件还原到目标数据库，将辅助集的数据文件还原到辅助实例。
2.将还原的数据文件恢复到指定的时间点。
3.将已恢复表空间中对象的字典元数据导出到目标数据库。
4.在目标数据库上发出SWITCH命令，以使目标数据库控制文件指向辅助实例上已恢复的恢复集中的数据文件。
5.将辅助实例中的字典元数据导入目标实例，以便能访问已恢复对象。
执行基本RMAN TSPITR
注意：要恢复的表空间必须是自包含的表空间
完全自动执行的TSPITR
– 指定辅助目标。
– RMAN 会管理TSPITR 的所有方面。
– 这是推荐的方法。
使用自动辅助实例的定制TSPITR
– 这是在完全自动执行TSPITR 的基础上进行的。
– 定制文件位置。
– 指定初始化参数。
– 指定通道配置。
使用您自己辅助实例的TSPITR
– 配置并管理辅助实例。
执行TSPITR 时有下列选项：
完全自动执行的TSPITR：指定辅助目标，RMAN 会管理TSPITR 操作的所有方面。这是最简单的执行TSPITR 的方法，建议采用这种方法，除非明确指出需要在执行
TSPITR 之后控制恢复集的位置，或者在执行TSPITR 期间控制辅助集文件的位置，或者需要控制辅助实例的通道配置或其它某个方面。
使用自动辅助实例的定制TSPITR：TSPITR 基于完全自动执行的TSPITR 的行为，可能仍使用辅助目标。可以自定义其行为的一个或多个方面，如辅助集文件或恢复集
文件的位置。可以为RMAN 创建并管理的辅助实例指定初始化参数或通道配置。
使用你自己辅助实例的TSPITR：设置、启动、停止和清除在TSPITR 中使用的辅助实例。另外，可以借助使用自动辅助实例的定制TSPITR 中的一些方法来管理TSPITR 过程。
RMAN自动执行TSPITR案例：
1、工作环境
12:21:32 SCOTT@ prod>create table emp1 as select * from emp; Table created. Elapsed: 00:00:00.65 14:07:41 SCOTT@ prod>insert into emp1 select * from emp where rownum <5; 4 rows created. Elapsed: 00:00:00.02 14:08:18 SCOTT@ prod>commit; Commit complete. Elapsed: 00:00:00.04 14:08:19 SCOTT@ prod>select count(*) from emp1; COUNT(*) ---------- 18 Elapsed: 00:00:00.02 emp1表数据被误删除： 14:08:20 SCOTT@ prod>truncate table emp1; Table truncated. Elapsed: 00:00:00.32 14:08:30 SCOTT@ prod>insert into emp1 select * from emp where empno=7788; 1 row created. Elapsed: 00:00:00.03 14:08:46 SCOTT@ prod>commit; Commit complete. Elapsed: 00:00:00.06 14:08:48 SCOTT@ prod>select * from emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 Elapsed: 00:00:00.08 14:08:55 SCOTT@ prod>update emp1 set empno=8888; 1 row updated. Elapsed: 00:00:00.02 14:09:06 SCOTT@ prod>commit; Commit complete. Elapsed: 00:00:00.01 14:09:08 SCOTT@ prod>select * from emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 8888 SCOTT ANALYST 7566 19-APR-87 3000 20 Elapsed: 00:00:00.01 14:09:12 SCOTT@ prod>
2、做恢复前的检测
检测tablespace是否自包含： 10:39:16 SCOTT@ prod>CONN /as sysdba Connected. 10:39:25 SYS@ prod>exec DBMS_TTS.TRANSPORT_SET_CHECK(‘TBS1‘,TRUE); PL/SQL procedure successfully completed. Elapsed: 00:01:58.45 10:41:43 SYS@ prod>select * from TRANSPORT_SET_VIOLATIONS t; no rows selected 确认所要恢复的数据文件： 14:13:34 SYS@ prod>select * from dba_data_files t where T.TABLESPACE_NAME=‘TBS1‘; FILE_NAME ------------------------------------------------------------------------------------------------------------------------ FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_ ------------ ---------- ----------- ------- /u01/app/oracle/oradata/prod/tbs1.dbf 6 TBS1 10485760 1280 AVAILABLE 6 NO 0 0 0 9437184 1152 ONLINE Elapsed: 00:00:00.02 14:14:42 SYS@ prod> 检查是否包含辅助集SYSTEM UNDO和CONTROLFILE： 14:14:38 SYS@ prod>select file_name name from dba_data_files t where T.TABLESPACE_NAME IN(‘SYSTEM‘,‘UNDOTBS1‘) 14:14:40 2 union 14:14:41 3 select max(name) from v$controlfile t; NAME ------------------------------------------------------------------------------------------- /u01/app/oracle/oradata/prod/control01.ctl /u01/app/oracle/oradata/prod/system01.dbf /u01/app/oracle/oradata/prod/undotbs01.dbf Elapsed: 00:00:00.03 检查TSP之后可能丢失的对象： 14:14:42 SYS@ prod>select * from TS_PITR_OBJECTS_TO_BE_DROPPED T where T.OWNER=‘TBS1‘; no rows selected Elapsed: 00:00:00.17
3、执行基于RMAN的TSPITR
[root@rh6 prod]# mkdir /home/oracle/prod
存放辅助库文件
[oracle@rh6 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 24 14:24:46 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=239333010)
执行recover tablespace：
RMAN> recover tablespace tbs1 until time "to_date(‘2014-06-24 14:08:18‘,‘yyyy-mm-dd hh24:mi:ss‘)" auxiliary destination ‘/home/oracle/prod‘;
Starting recover at 24-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 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=‘nhxv‘
建立Auxiliary instance：
initialization parameters used for automatic instance:
db_name=PROD
db_unique_name=nhxv_tspitr_PROD
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/home/oracle/prod
log_archive_dest_1=‘location=/home/oracle/prod‘
#No auxiliary parameter file used
starting up automatic instance PROD
Oracle instance started
Total System Global Area 292278272 bytes
Fixed Size 2212736 bytes
Variable Size 100666496 bytes
Database Buffers 184549376 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(‘2014-06-24 14:08:18‘,‘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;‘;
}
通过备份建立Auxiliary database：
executing Memory Script
executing command: SET until clause
Starting restore at 24-JUN-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 /dsk4/bak/PROD_10.bak
channel ORA_AUX_DISK_1: piece handle=/dsk4/bak/PROD_10.bak tag=TAG20140624T121907
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/prod/PROD/controlfile/o1_mf_9tl6lyhz_.ctl
Finished restore at 24-JUN-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 time "to_date(‘2014-06-24 14:08:18‘,‘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 ‘|| ‘TBS1‘ ||‘ 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 6 to
"/u01/app/oracle/oradata/prod/tbs1.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, 6;
switch clone datafile all;
}
restore 数据到Auxiliary database：
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace TBS1 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 /home/oracle/prod/PROD/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 24-JUN-14
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 /home/oracle/prod/PROD/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/prod/PROD/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /home/oracle/prod/PROD/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/prod/tbs1.dbf
channel ORA_AUX_DISK_1: reading from backup piece /dsk4/bak/PROD_9.bak
channel ORA_AUX_DISK_1: piece handle=/dsk4/bak/PROD_9.bak tag=TAG20140624T121907
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 24-JUN-14
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=851092186 file name=/home/oracle/prod/PROD/datafile/o1_mf_system_9tl6m7sd_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=851092186 file name=/home/oracle/prod/PROD/datafile/o1_mf_undotbs1_9tl6m7x5_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=851092186 file name=/home/oracle/prod/PROD/datafile/o1_mf_sysaux_9tl6m7vy_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date(‘2014-06-24 14:08:18‘,‘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 6 online";
# recover and open resetlogs
recover clone database tablespace "TBS1", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
将Auxiliary database的datafile激活，只需要system、undo、sysaux和要恢复的表空间（tbs1）：
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 6 online
Starting recover at 24-JUN-14
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 25 is already on disk as file /dsk4/arch1/arch_1_25_847900609.log
archived log file name=/dsk4/arch1/arch_1_25_847900609.log thread=1 sequence=25
media recovery complete, elapsed time: 00:00:05
Finished recover at 24-JUN-14
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone ‘alter tablespace TBS1 read only‘;
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ‘‘
/home/oracle/prod‘‘";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ‘‘
/home/oracle/prod‘‘";
}
通过Auxiliary database导出恢复的数据：
executing Memory Script
sql statement: alter tablespace TBS1 read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ‘‘/home/oracle/prod‘‘
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ‘‘/home/oracle/prod‘‘
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_nhxv":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_nhxv" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_nhxv is:
EXPDP> /home/oracle/prod/tspitr_nhxv_69835.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TBS1:
EXPDP> /u01/app/oracle/oradata/prod/tbs1.dbf
EXPDP> Job "SYS"."TSPITR_EXP_nhxv" successfully completed at 14:32:23
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql ‘drop tablespace TBS1 including contents keep datafiles‘;
}
在目标库导入要恢复的数据：
executing Memory Script
database closed
database dismounted
Oracle instance shut down
sql statement: drop tablespace TBS1 including contents keep datafiles
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_nhxv" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_nhxv":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_nhxv" successfully completed at 14:32:55
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql ‘alter tablespace TBS1 read write‘;
sql ‘alter tablespace TBS1 offline‘;
# enable autobackups after TSPITR is finished
sql ‘begin dbms_backup_restore.AutoBackupFlag(TRUE); end;‘;
}
executing Memory Script
sql statement: alter tablespace TBS1 read write
sql statement: alter tablespace TBS1 offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/prod/PROD/datafile/o1_mf_temp_9tl6qbyc_.tmp deleted
auxiliary instance file /home/oracle/prod/PROD/onlinelog/o1_mf_3_9tl6q6v9_.log deleted
auxiliary instance file /home/oracle/prod/PROD/onlinelog/o1_mf_2_9tl6q41j_.log deleted
auxiliary instance file /home/oracle/prod/PROD/onlinelog/o1_mf_1_9tl6q1bx_.log deleted
auxiliary instance file /home/oracle/prod/PROD/datafile/o1_mf_sysaux_9tl6m7vy_.dbf deleted
auxiliary instance file /home/oracle/prod/PROD/datafile/o1_mf_undotbs1_9tl6m7x5_.dbf deleted
auxiliary instance file /home/oracle/prod/PROD/datafile/o1_mf_system_9tl6m7sd_.dbf deleted
auxiliary instance file /home/oracle/prod/PROD/controlfile/o1_mf_9tl6lyhz_.ctl deleted
Finished recover at 24-JUN-14
RMAN>4、恢复完成校验
14:33:44 SCOTT@ prod>conn /as sysdba Connected. 14:34:17 SYS@ prod>select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE TBS1 OFFLINE 7 rows selected. Elapsed: 00:00:00.03 14:34:20 SYS@ prod>alter tablespace tbs1 online; Tablespace altered. Elapsed: 00:00:00.24 14:34:30 SYS@ prod>select count(*) from scott.emp1; COUNT(*) ---------- 18
当执行表空间基于时间的恢复，通过使用当前数据文件的镜像副本可以避免转储数据文件，从而提高TSPITR的性能。
建立数据文件镜像的两种方法: RMAN> backup as copy format=‘/dsk4/bak/system.bak‘ datafile 1; RMAN> copy datafile 2 to ‘/dsk4/bak/sysaux.bak‘; RMAN> configure auxname for datafile 1 to ‘/dsk4/bak/system.bak‘; RMAN> configure auxname for datafile 2 to ‘/dsk4/bak/sysaux.bak‘; RMAN> recover tablespace tbs1 untiltime ‘2014-06-24 14:08:18‘ auxiliary destination ‘/home/oracle/prod‘; RMAN> configure auxname for datafile 1 clear; RMAN> configure auxname for datafile 2 clear;
本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/1430322
RMAN基于表空间的不完全恢复（TSPITR),布布扣,bubuko.com
原文：http://tiany.blog.51cto.com/513694/1430322