Oracle 11g的Redo Log和Archive Log的分析方法

自Oracle 11g起，无需设置UTL_FILE_DIR就可以使用LOGMNR对本地数据库的日志进行分析，以下是使用LOGMNR的DICT_FROM_ONLINE_CATALOG分析REDO和归档日志的步骤

分析REDO日志的实验

创建测试表，并做DML操作。

SQL> create table t_test(id number,name varchar2(15)); Table created. SQL> insert into t_test values(1,'stream'); 1 row created. SQL> insert into t_test values(2,'dbdream'); 1 row created. SQL> commit; Commit complete. SQL> update t_test set name='streamsong' where id=1; 1 row updated. SQL> commit; Commit complete. SQL> delete from t_test; 2 rows deleted. SQL> commit; Commit complete.

查看REDO日志的路径。

SQL> select member from v$logfile; MEMBER ------------------------------------------------ /u01/app/oracle/oradata/stream/STREAM/redo03.log /u01/app/oracle/oradata/stream/STREAM/redo02.log /u01/app/oracle/oradata/stream/STREAM/redo01.log

添加REDO日志

第一个添加的日志需指定NEW，如果确定要查询的信息在指定的REDO日志内，可以只添加那个REDO日志，而不需要再添加其他

SQL> exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/stream/STREAM/redo01.log',options=>dbms_logmnr.new); PL/SQL procedure successfully completed.

添加其他REDO日志
不是第一个添加的日志需指定ADDFILE。

SQL> exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/stream/STREAM/redo02.log',options=>dbms_logmnr.addfile); PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/stream/STREAM/redo03.log',options=>dbms_logmnr.addfile); PL/SQL procedure successfully completed.

开始对添加的REDO进行分析

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

查看LOGMNR分析后得到的信息

LOGMNR分析后的数据会存放在v$logmnr_contents视图中，通过查询v$logmnr_contents视图就可以查询到REDO日志的信息。

SQL> select timestamp,sql_redo,sql_undo 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';

SQL_REDO就是执行的SQL语句，SQL_UNDO是回滚操作的SQL语句，也就是执行SQL_UNDO的相关SQL，就可以回滚对应的操作。

注：LOGMNR是SESSION级的，以上实验第3步到第6步需在同一个SESSION中进行，SESSION断开连接后需重新执行，否则会报以下错误提示。
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents

结束LOGMNR操作
由于LOGMNR是会话级的，可以用直接退出或关闭当前的终端的方式来结束LOGMNR的操作，当然，正确的结束LOGMNR操作需使用下面的命令。

SQL> exec dbms_logmnr.end_logmnr; PL/SQL procedure successfully completed. 分析归档日志的实验