分类: Oracle
2009-07-15 23:39:00
数据库: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