本帖最后由 paulyi 于 2014-2-11 10:03 编辑

1 概述
问题简述：由于用户误操作导致实例1上的应用数据有问题，需要恢复实例1这段时间的归档，并且提取出这段时间的归档信息记录，提交给开发人员分析。
操作系统：AIX 4330-10数据库：oracle 9.2.0.4+RAC
2 处理过程1. 检查实例1这段时间有多少归档文件oracle@s85_1$rman target orabak/123456Recovery Manager: Release 9.2.0.4.0 - 64bit ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights reserved.connected to target database: TESTORA (DBID=891760189)RMAN> connect rcvcat rman/rman@rmanconnected to recovery catalog database
RMAN> list backup of archivelog time between "to_date('2010-01-14 15:00:00','yyyy-mm-dd hh24:mi:ss')" and "to_date('2010-01-14 17:00','yyyy-mm-dd hh24:mi:ss')";
List of Backup Sets===================BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ -------------------616868 699M SBT_TAPE 00:04:27 2010-01-15:12:14:09 BP Key: 616871 Status: AVAILABLE Tag: TAG20100115T120940 Piece Name: al_t708350982_s9229_p1List of Archived Logs in backup set 616868Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- ---------2 55879 4211350190 2010-01-14:14:41:52 4211504738 2010-01-14:15:05:022 55880 4211504738 2010-01-14:15:05:02 4211646621 2010-01-14:15:28:272 55881 4211646621 2010-01-14:15:28:27 4211740968 2010-01-14:15:43:362 55882 4211740968 2010-01-14:15:43:36 4211853307 2010-01-14:16:03:042 55883 4211853307 2010-01-14:16:03:04 4212005140 2010-01-14:16:30:092 55884 4212005140 2010-01-14:16:30:09 4212149788 2010-01-14:16:54:532 55885 4212149788 2010-01-14:16:54:53 4212320699 2010-01-14:17:37:59BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ -------------------616869 287M SBT_TAPE 00:05:18 2010-01-15:12:16:34 BP Key: 616872 Status: AVAILABLE Tag: TAG20100115T121109 Piece Name: al_t708351076_s9230_p1 List of Archived Logs in backup set 616869Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- ---------1 61231 4211350478 2010-01-14:14:43:21 4211741023 2010-01-14:15:45:061 61232 4211741023 2010-01-14:15:45:06 4212096722 2010-01-14:16:44:101 61233 4212096722 2010-01-14:16:44:10 4212373810 2010-01-14:18:14:06可以看出，只需要恢复实例1上61231到61233上的归档日志

2. 恢复归档日志oracle@s85_1$rman target orabak/123456Recovery Manager: Release 9.2.0.4.0 - 64bit ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights reserved.connected to target database: TESTORA (DBID=891760189)
RMAN> connect rcvcat rman/rman@rmanconnected to recovery catalog database
RMAN> run {allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_SERV=s85_1, NB_ORA_SCHED=Default-Application-Backup, NB_ORA_CLIENT=s85_1, NB_ORA_POLICY=oraarch1)';restore archivelog from sequence 61231 until sequence 61233;release channel t1;}allocated channel: t1channel t1: sid=123 devtype=SBT_TAPEchannel t1: VERITAS NetBackup for Oracle - Release 4.5GA (00000000)Starting restore at 2010-02-01:10:34:04channel t1: starting archive log restore to default destinationchannel t1: restoring archive logarchive log thread=1 sequence=61231channel t1: restoring archive logarchive log thread=1 sequence=61232channel t1: restoring archive logarchive log thread=1 sequence=61233channel t1: restored backup piece 1piece handle=al_t708351076_s9230_p1 tag=TAG20100115T121109 params=NULLchannel t1: restore completeFinished restore at 2010-02-01:10:38:36released channel: t1

3. 恢复出来后，用logmnr工具，提取归档信息，一个归档文件对应一个临时表以下是过程信息oracle@s85_1$sqlplus "/as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Mon Feb 1 10:41:42 2010Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit ProductionWith the Partitioning and Real Application Clusters optionsJServer Release 9.2.0.4.0 - Production

SQL>exec sys.dbms_logmnr.add_logfile(logfilename=>'/arch/arch/arch_1_61231.arc',options=>dbms_logmnr.new);PL/SQL procedure successfully completed.

SQL>exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);PL/SQL procedure successfully completed.

SQL>create table system.tmp_logmnr1 as select operation,sql_redo,sql_undo from v$logmnr_contents;Table created.

SQL> select count(*) from system.tmp_logmnr1; COUNT(*)---------- 274792

SQL> exec sys.dbms_logmnr.end_logmnr;PL/SQL procedure successfully completed.

SQL>exec sys.dbms_logmnr.add_logfile(logfilename=>'/arch/arch/arch_1_61232.arc',options=>dbms_logmnr.new);PL/SQL procedure successfully completed.

SQL>exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);PL/SQL procedure successfully completed.
SQL>create table system.tmp_logmnr2 as select operation,sql_redo,sql_undo from v$logmnr_contents;Table created.
SQL> select count(*) from system.tmp_logmnr2; COUNT(*)---------- 295932SQL> exec sys.dbms_logmnr.end_logmnr;PL/SQL procedure successfully completed.
SQL>exec sys.dbms_logmnr.add_logfile(logfilename=>'/arch/arch/arch_1_61233.arc',options=>dbms_logmnr.new);PL/SQL procedure successfully completed.
SQL>exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);PL/SQL procedure successfully completed.
SQL>create table system.tmp_logmnr3 as select operation,sql_redo,sql_undo from v$logmnr_contents;Table created.
SQL> select count(*) from system.tmp_logmnr3; COUNT(*)---------- 344618
SQL> exec sys.dbms_logmnr.end_logmnr;PL/SQL procedure successfully completed.
4. 用exp导出数据exp system/123456 file=logmnr_export.dmp log=logmnr_export.log buffer=4096000 tables=system.tmp_logmnr1,system.tmp_logmnr2,system.tmp_logmnr3 compress=n feedback=10000将logmnr_export.dmp文件发给开发人员分析即可。