RMAN基于表空间的不完全恢复(TSPITR)
来源:互联网 发布:网络女主播 色情表演 编辑:程序博客网 时间:2024/05/16 14:39
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.6514:07:41 SCOTT@ prod>insert into emp1 select * from emp where rownum <5;4 rows created.Elapsed: 00:00:00.0214:08:18 SCOTT@ prod>commit;Commit complete.Elapsed: 00:00:00.0414:08:19 SCOTT@ prod>select count(*) from emp1; COUNT(*)---------- 18Elapsed: 00:00:00.02emp1表数据被误删除:14:08:20 SCOTT@ prod>truncate table emp1;Table truncated.Elapsed: 00:00:00.3214:08:30 SCOTT@ prod>insert into emp1 select * from emp where empno=7788;1 row created.Elapsed: 00:00:00.0314:08:46 SCOTT@ prod>commit;Commit complete.Elapsed: 00:00:00.0614:08:48 SCOTT@ prod>select * from emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-APR-87 3000 20Elapsed: 00:00:00.0814:08:55 SCOTT@ prod>update emp1 set empno=8888;1 row updated.Elapsed: 00:00:00.0214:09:06 SCOTT@ prod>commit;Commit complete.Elapsed: 00:00:00.0114:09:08 SCOTT@ prod>select * from emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 8888 SCOTT ANALYST 7566 19-APR-87 3000 20Elapsed: 00:00:00.0114:09:12 SCOTT@ prod>
2、做恢复前的检测
检测tablespace是否自包含:10:39:16 SCOTT@ prod>CONN /as sysdbaConnected.10:39:25 SYS@ prod>exec DBMS_TTS.TRANSPORT_SET_CHECK('TBS1',TRUE);PL/SQL procedure successfully completed.Elapsed: 00:01:58.4510: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 ONLINEElapsed: 00:00:00.0214: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 union14: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.dbfElapsed: 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 selectedElapsed: 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 2014Copyright (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-14using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=40 device type=DISKRMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-timeList of tablespaces expected to have UNDO segmentsTablespace SYSTEMTablespace UNDOTBS1Creating automatic instance, with SID='nhxv'建立Auxiliary instance:initialization parameters used for automatic instance:db_name=PRODdb_unique_name=nhxv_tspitr_PRODcompatible=11.2.0.0.0db_block_size=8192db_files=200sga_target=280Mprocesses=50db_create_file_dest=/home/oracle/prodlog_archive_dest_1='location=/home/oracle/prod'#No auxiliary parameter file usedstarting up automatic instance PRODOracle instance startedTotal System Global Area 292278272 bytesFixed Size 2212736 bytesVariable Size 100666496 bytesDatabase Buffers 184549376 bytesRedo Buffers 4849664 bytesAutomatic instance createdRunning TRANSPORT_SET_CHECK on recovery set tablespacesTRANSPORT_SET_CHECK completed successfullycontents of Memory Script:{# set requested point in timeset until time "to_date('2014-06-24 14:08:18','yyyy-mm-dd hh24:mi:ss')";# restore the controlfilerestore clone controlfile;# mount the controlfilesql clone 'alter database mount clone database';# archive current online log sql 'alter system archive log current';# avoid unnecessary autobackups for structural changes during TSPITRsql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';}通过备份建立Auxiliary database:executing Memory Scriptexecuting command: SET until clauseStarting restore at 24-JUN-14allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=19 device type=DISKchannel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: reading from backup piece /dsk4/bak/PROD_10.bakchannel ORA_AUX_DISK_1: piece handle=/dsk4/bak/PROD_10.bak tag=TAG20140624T121907channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01output file name=/home/oracle/prod/PROD/controlfile/o1_mf_9tl6lyhz_.ctlFinished restore at 24-JUN-14sql statement: alter database mount clone databasesql statement: alter system archive log currentsql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;contents of Memory Script:{# set requested point in timeset until time "to_date('2014-06-24 14:08:18','yyyy-mm-dd hh24:mi:ss')";plsql <<<-- tspitr_2declare 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 datafilesset 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 tempfilesswitch clone tempfile all;# restore the tablespaces in the recovery set and the auxiliary setrestore clone datafile 1, 3, 2, 6;switch clone datafile all;}restore 数据到Auxiliary database:executing Memory Scriptexecuting command: SET until clausesql statement: alter tablespace TBS1 offline immediateexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed tempfile 1 to /home/oracle/prod/PROD/datafile/o1_mf_temp_%u_.tmp in control fileStarting restore at 24-JUN-14using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle/prod/PROD/datafile/o1_mf_system_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/prod/PROD/datafile/o1_mf_undotbs1_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00002 to /home/oracle/prod/PROD/datafile/o1_mf_sysaux_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/prod/tbs1.dbfchannel ORA_AUX_DISK_1: reading from backup piece /dsk4/bak/PROD_9.bakchannel ORA_AUX_DISK_1: piece handle=/dsk4/bak/PROD_9.bak tag=TAG20140624T121907channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:55Finished restore at 24-JUN-14datafile 1 switched to datafile copyinput datafile copy RECID=5 STAMP=851092186 file name=/home/oracle/prod/PROD/datafile/o1_mf_system_9tl6m7sd_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=6 STAMP=851092186 file name=/home/oracle/prod/PROD/datafile/o1_mf_undotbs1_9tl6m7x5_.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=7 STAMP=851092186 file name=/home/oracle/prod/PROD/datafile/o1_mf_sysaux_9tl6m7vy_.dbfcontents of Memory Script:{# set requested point in timeset until time "to_date('2014-06-24 14:08:18','yyyy-mm-dd hh24:mi:ss')";# online the datafiles restored or switchedsql 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 resetlogsrecover clone database tablespace "TBS1", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;alter clone database open resetlogs;}将Auxiliary database的datafile激活,只需要system、undo、sysaux和要恢复的表空间(tbs1):executing Memory Scriptexecuting command: SET until clausesql statement: alter database datafile 1 onlinesql statement: alter database datafile 3 onlinesql statement: alter database datafile 2 onlinesql statement: alter database datafile 6 onlineStarting recover at 24-JUN-14using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 25 is already on disk as file /dsk4/arch1/arch_1_25_847900609.logarchived log file name=/dsk4/arch1/arch_1_25_847900609.log thread=1 sequence=25media recovery complete, elapsed time: 00:00:05Finished recover at 24-JUN-14database openedcontents of Memory Script:{# make read only the tablespace that will be exportedsql clone 'alter tablespace TBS1 read only';# create directory for datapump importsql "create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/prod''";# create directory for datapump exportsql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/prod''";}通过Auxiliary database导出恢复的数据:executing Memory Scriptsql statement: alter tablespace TBS1 read onlysql 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:23Export completedcontents of Memory Script:{# shutdown clone before importshutdown clone immediate# drop target tablespaces before importing them backsql 'drop tablespace TBS1 including contents keep datafiles';}在目标库导入要恢复的数据:executing Memory Scriptdatabase closeddatabase dismountedOracle instance shut downsql statement: drop tablespace TBS1 including contents keep datafilesPerforming 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:55Import completedcontents of Memory Script:{# make read write and offline the imported tablespacessql 'alter tablespace TBS1 read write';sql 'alter tablespace TBS1 offline';# enable autobackups after TSPITR is finishedsql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';}executing Memory Scriptsql statement: alter tablespace TBS1 read writesql statement: alter tablespace TBS1 offlinesql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;Removing automatic instanceAutomatic instance removedauxiliary instance file /home/oracle/prod/PROD/datafile/o1_mf_temp_9tl6qbyc_.tmp deletedauxiliary instance file /home/oracle/prod/PROD/onlinelog/o1_mf_3_9tl6q6v9_.log deletedauxiliary instance file /home/oracle/prod/PROD/onlinelog/o1_mf_2_9tl6q41j_.log deletedauxiliary instance file /home/oracle/prod/PROD/onlinelog/o1_mf_1_9tl6q1bx_.log deletedauxiliary instance file /home/oracle/prod/PROD/datafile/o1_mf_sysaux_9tl6m7vy_.dbf deletedauxiliary instance file /home/oracle/prod/PROD/datafile/o1_mf_undotbs1_9tl6m7x5_.dbf deletedauxiliary instance file /home/oracle/prod/PROD/datafile/o1_mf_system_9tl6m7sd_.dbf deletedauxiliary instance file /home/oracle/prod/PROD/controlfile/o1_mf_9tl6lyhz_.ctl deletedFinished recover at 24-JUN-14RMAN>
4、恢复完成校验
14:33:44 SCOTT@ prod>conn /as sysdbaConnected.14:34:17 SYS@ prod>select tablespace_name,status from dba_tablespaces;TABLESPACE_NAME STATUS------------------------------ ---------SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS ONLINEEXAMPLE ONLINETBS1 OFFLINE7 rows selected.Elapsed: 00:00:00.0314:34:20 SYS@ prod>alter tablespace tbs1 online;Tablespace altered.Elapsed: 00:00:00.2414: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)
- rman基于TSPITR恢复drop掉的表空间
- RMAN TSPITR 使用rman进行表空间基于时间点的恢复
- 【RMAN】TSPITR--RMAN表空间基于时间点的自动恢复
- TSPITR基于表空间的时间恢复
- rman表空间基于时间点的不完全恢复
- RMAN表空间时间点恢复,TSPITR
- TSPITR(基于时间点的表空间恢复)
- 表空间基于时间点的恢复(TSPITR)
- TSPITR:Tablespace Point-in-Time Recovery表空间不完全恢复
- TSPITR 基于表空间时间点恢复
- TSPITR恢复drop 表空间的例子
- RMAN基于时间点的不完全恢复
- Oracle基于RMAN的不完全恢复
- rman 基于时间的不完全恢复
- rman基于SCN的不完全恢复
- Oracle Study之--基于ASM的TSPITR(基于表空间的完全恢复)
- Oracle 基于 RMAN 的不完全恢复(incomplete recovery by RMAN)
- django 1.7 新的migration框架,取代south
- 小鹰登山背包用户手册
- Oracle数据库进程
- [误入深谷陷死路,雷电交加滚泥坡]--2014/6/21山后老刘队“安家庄-清水尖-京西十八潭”冒雨穿越版
- 数据库范式的设计
- RMAN基于表空间的不完全恢复(TSPITR)
- spring定时器入门例子
- Oracle Buffer Cache 原理(1)
- Oracle Buffer Cache工作原理(2)
- Oracle Share Pool工作原理
- Oracle Undo tablespace恢复(无备份)
- Oracle中的ORA-01548: active rollback segment '_SYSSMU1$' found
- Oracle Library Cache深入解析
- UNDO Segment深入解析