分析归档日志的操作和分析REDO的操作基本一样，最重要的就是准确的找到需要查找的信息在哪些归档日志内。既然是利用LOGMNR分析归档日志，数据库一定是在归档模式，要不哪来的归档日志，可以通过如下命令查看数据库是否启用归档模式。
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 15 Next log sequence to archive 17 Current log sequence 17可以看到当前数据库已经开启归档模式，归档地址是USE_DB_RECOVERY_FILE_DEST，USE_DB_RECOVERY_FILE_DEST的具体位置可以通过下面的命令查看。
SQL> show parameter db_recove NAME TYPE VALUE --------------------------- ----------- ------------------------------- db_recovery_file_dest string /u01/app/oracle/flash_recovery_area db_recovery_file_dest_size big integer 852M如果数据据库开启闪回恢复区，闪回恢复区就是默认的归档地址，我个人建议使用这个空间存放归档日志，因为从Oracle 11g开始当该空间的使用率达到80%的时候，系统会自动删除已经备份过的归档文件，避免被撑爆。闪回恢复区的大小受db_recovery_file_dest_size大小的限制，是一个动态参数，可以随时在线修改。
本实验步骤如下：
切换日志，使REDO日志归档。
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered.按照归档日志的时间，找到存放需要分析信息的归档日志。
[oracle@stream 2012_03_21]$ pwd /u01/app/oracle/flash_recovery_area/STREAM/archivelog/2012_03_21 [oracle@stream 2012_03_21]$ ll total 32196 -rw-r----- 1 oracle oinstall 26598912 Mar 21 10:55 o1_mf_1_12_7pljs9lb_.arc -rw-r----- 1 oracle oinstall 6206976 Mar 21 14:05 o1_mf_1_13_7plvx3bc_.arc -rw-r----- 1 oracle oinstall 100864 Mar 21 14:07 o1_mf_1_14_7plw0bgo_.arc -rw-r----- 1 oracle oinstall 1024 Mar 21 14:07 o1_mf_1_15_7plw0d0q_.arc -rw-r----- 1 oracle oinstall 2048 Mar 21 14:07 o1_mf_1_16_7plw0gc1_.arc将归档日志添加到LOGMNR。
SQL> exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/flash_recovery_area/STREAM/archivelog/2012_03_21/o1_mf_1_13_7plvx3bc_.arc',options=>dbms_logmnr.new); PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/flash_recovery_area/STREAM/archivelog/2012_03_21/o1_mf_1_14_7plw0bgo_.arc',options=>dbms_logmnr.addfile); PL/SQL procedure successfully completed.开始分析。
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); PL/SQL procedure successfully completed.查看LOGMNR分析后的数据。
SQL> select timestamp,sql_redo from v$logmnr_contents where username='SCOTT' and table_name='T_TEST'; TIMESTAMP SQL_REDO --------- ----------------------------------------------------------------- 21-MAR-12 create table t_test(id number,name varchar2(15)); 21-MAR-12 insert into "SCOTT"."T_TEST"("ID","NAME") values ('1','stream'); 21-MAR-12 insert into "SCOTT"."T_TEST"("ID","NAME") values ('2','dbdream'); 21-MAR-12 update "SCOTT"."T_TEST" set "NAME" = 'streamsong' where "NAME" = 'stream' and ROWID = 'AAAR7fAAEAAAADXAAA'; 21-MAR-12 delete from "SCOTT"."T_TEST" where "ID" = '1' and "NAME" = 'streamsong' and ROWID = 'AAAR7fAAEAAAADXAAA'; 21-MAR-12 delete from "SCOTT"."T_TEST" where "ID" = '2' and "NAME" = 'dbdream' and ROWID = 'AAAR7fAAEAAAADXAAB'; 6 rows selected.结束LOGMNR操作。