logmnr常见使用方法，以前的一个测试例子。
--数据库版本

SQL> select * from gv$version;

   INST_ID BANNER
---------- ----------------------------------------------------------------
         1 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
         1 PL/SQL Release 10.2.0.5.0 - Production
         1 CORE 10.2.0.5.0      Production
         1 TNS for Linux: Version 10.2.0.5.0 - Production
         1 NLSRTL Version 10.2.0.5.0 - Production
         2 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
         2 PL/SQL Release 10.2.0.5.0 - Production
         2 CORE 10.2.0.5.0      Production
         2 TNS for Linux: Version 10.2.0.5.0 - Production
         2 NLSRTL Version 10.2.0.5.0 - Production

10 rows selected.

--准备配置：
--打开数据库补充日志功能

SQL> select LOG_MODE from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;

SUPPLEME SUP SUP
-------- --- ---
NO       NO  NO

SQL> alter database add supplemental log data;    <<如果不开启supplemental log，则在查询日志分析表的时候，username列将为空；以及发生行迁移的redo无法追踪。

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;

SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO

--设置会话时间格式

SQL> alter session set nls_date_format ='yyyy/mm/dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2012/09/18 10:35:13

--创建测试数据

SQL> create tablespace test datafile '+DATA/racdb/test01.dbf' size 2m;

Tablespace created.

SQL> create user test identified by test default tablespace test quota unlimited on test;

User created.

SQL> grant resource,connect to test;

Grant succeeded.

SQL> conn test/test;
Connected.
SQL> create table test(id number,name varchar2(10));

Table created.

SQL> select a.group#,thread#,member,b.status,archived from v$logfile a,v$log b where a.group#=b.group# order by 1;

    GROUP#    THREAD# MEMBER                    STATUS           ARC
---------- ---------- ------------------------- ---------------- ---
         1          1 +DATA/racdb/redo01.log    CURRENT          NO
         2          1 +DATA/racdb/redo02.log    INACTIVE         YES
         3          2 +DATA/racdb/redo03.log    INACTIVE         YES
         4          2 +DATA/racdb/redo04.log    CURRENT          NO

SQL> conn test/test
Connected.
SQL> insert into test values(1,'yallonking');

1 row created.

SQL> conn /as sysdba
Connected.
SQL> create user yallonking identified by yallonking default tablespace test quota unlimited on test;

User created.

SQL> grant connect,resource to yallonking;

Grant succeeded.

SQL> conn yallonking/yallonking
Connected.
SQL> create table yallonking (id number,name varchar2(10));

Table created.

SQL> insert into yallonking values (2,'test');

1 row created.

SQL> commit;

Commit complete.

SQL> conn test/test
Connected.
SQL> alter table test modify name varchar2(20);

Table altered.

SQL> update test set id=3 where id=1;

1 row updated.

SQL> delete from test where id=3;

1 row deleted.

SQL> commit;

Commit complete.

SQL> insert into test values (4,'test')
  2  ;

1 row created.

SQL> rollback;

Rollback complete.

SQL> conn /as sysdba
Connected.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select a.group#,thread#,member,b.status,archived from v$logfile a,v$log b where a.group#=b.group# order by 1;

    GROUP#    THREAD# MEMBER                    STATUS           ARC
---------- ---------- ------------------------- ---------------- ---
         1          1 +DATA/racdb/redo01.log    INACTIVE         YES
         2          1 +DATA/racdb/redo02.log    CURRENT          NO
         3          2 +DATA/racdb/redo03.log    INACTIVE         YES
         4          2 +DATA/racdb/redo04.log    CURRENT          NO

示例一：查看最后一个归档日志中所有的操作信息
注：此logmnr查询视图中含有未提交事务，且输出按时间顺序输出
--确定归档日志

SQL> select name from v$archived_log where first_time=(select max(first_time) from v$archived_log);

NAME
--------------------------------------------------------------------------------
+DATA/racdb/archivelog/2012_09_18/thread_1_seq_5.278.794314659

--将日志添加到logmnr中

SQL> execute dbms_logmnr.add_logfile(logfilename=>'+DATA/racdb/archivelog/2012_09_18/thread_1_seq_5.278.794314659',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

--使用logmnr进行分析

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.print_pretty_sql);

PL/SQL procedure successfully completed.

--查询logmnr视图

select username,(xidusn||'.'||xidslt||'.'||xidsqn)as xid,sql_redo,sql_undo from v$logmnr_contents where username in ('TEST','YALLONKING');

--结束logmnr

SQL> execute dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

示例二：查看最后一个归档日志中所有的操作信息
注：此logmnr查询视图中只含有已提交事务，且输出按时间顺序按用户分组输出
--确定归档日志

SQL> select name from v$archived_log where first_time=(select max(first_time) from v$archived_log);

NAME
--------------------------------------------------------------------------------
+DATA/racdb/archivelog/2012_09_18/thread_1_seq_5.278.794314659

--将日志添加到logmnr中

SQL> execute dbms_logmnr.add_logfile(logfilename=>'+DATA/racdb/archivelog/2012_09_18/thread_1_seq_5.278.794314659',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

--使用logmnr进行分析

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only + dbms_logmnr.print_pretty_sql);

PL/SQL procedure successfully completed.

--查询logmnr视图

select username,(xidusn||'.'||xidslt||'.'||xidsqn)as xid,sql_redo,sql_undo from v$logmnr_contents where username in ('TEST','YALLONKING');

--结束logmnr

SQL> execute dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.