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;