Oracle10G LOGMNR 捕获不到DML数据

数据库：oracle 10.2.04

操作系统：red hat as 4

学习logmnr做实验当中，老是捕获不到DML数据（即delete,insert,update），首先以为是我系统参数哪里出错了，后来经过查资料。发现oracle10g,如果希望LOGMNR可以得到记录，应该设置SUPPLEMENTAL LOG DATA PRIMARY KEY和UNIQUE INDEX，这样Oracle才能确保LOGMNR可以获取SQL语句：

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUP SUP
--- ---
YES YES

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

System altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUP SUP
--- ---
NO NO

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> execute dbms_logmnr.add_logfile('/u01/flash_recovery_area/TIEGE/archivelog/2009_07_16/o1_mf_1_75_55xq939j_.arc',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/orabak/logmnr/dictionary.ora');

PL/SQL procedure successfully completed.

SQL> select count(*) from v$logmnr_contents;

  COUNT(*)
----------
      5010

SQL> create table abc as select * from v$logmnr_contents;

Table created.

SQL> execute dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

SQL> select sequence#,status from v$log;

 SEQUENCE# STATUS
---------- ----------------
        76 CURRENT
        74 INACTIVE
        75 INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> drop table test;

Table dropped.

SQL> drop table abc;

Table dropped.

SQL> create table t_logmnr(i int);

Table created.

SQL> insert into t_logmnr values(1);

1 row created.

SQL> insert into t_logmnr values(1);

1 row created.

SQL> insert into t_logmnr values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select sequence#,status from v$log;

 SEQUENCE# STATUS
---------- ----------------
        76 ACTIVE
        77 CURRENT
        75 INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select sequence#,status from v$log;

 SEQUENCE# STATUS
---------- ----------------
        76 ACTIVE
        77 ACTIVE
        78 CURRENT

SQL> select sequence#,name from v$archived_log where sequence#=78;

no rows selected

SQL> select sequence#,name from v$archived_log where sequence#=77;

 SEQUENCE#
----------
NAME
--------------------------------------------------------------------------------
        77
/u01/flash_recovery_area/TIEGE/archivelog/2009_07_16/o1_mf_1_77_55xr3wr9_.arc

SQL> execute dbms_logmnr_d.build('dictionary.ora','/orabak/logmnr');

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('/u01/flash_recovery_area/TIEGE/archivelog/2009_07_16/o1_mf_1_77_55xr3wr9_.arc',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/orabak/logmnr/dictionary.ora');

PL/SQL procedure successfully completed.

SQL> SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE TABLE_NAME = 't_logmnr';

no rows selected

SQL> SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE TABLE_NAME = 'T_LOGMNR';

SQL_REDO
--------------------------------------------------------------------------------
create table t_logmnr(i int);
insert into "SYS"."T_LOGMNR"("I") values ('1');
insert into "SYS"."T_LOGMNR"("I") values ('1');
insert into "SYS"."T_LOGMNR"("I") values ('1');

SQL> execute dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

成功显示DML语句

 

注：oracle9i和oracle11g 不必设置SUPPLEMENTAL LOG DATA PRIMARY KEY和UNIQUE INDEX，logmnr也能获取DML