logmnr分析日志，不能发现insert操作

huangtingzhong 发布于 2013-08-16 18:17BASIC, ORACLE已关闭评论

下面是测试logmnr用于分析日志，可以用于审计，也可以用于恢复用于误操作删除的数据，测试发现logmnr在10g环境中，sql_redo不能显示出insert操作，delete,update操作正常，在11g中,所有的dml操作都能解析出来，下面是测试环境，都没有启用附加日志。

环境1：DB:11.2.0.3.0 OS:WIN7

SQL> create table test as select * from emp;
SQL> insert into test select * from test;
SQL> update test set empno=1000;
SQL> commit;
SQL> delete test;
SQL> commit;
SQL> conn / as sysdba
Connected.
SQL> alter system archive log current;
System altered.
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
E:\INSTALL\RMAN\WIN\ARC0000000006_0823563417.0001
E:\INSTALL\RMAN\WIN\ARC0000000007_0823563417.0001
E:\INSTALL\RMAN\WIN\ARC0000000008_0823563417.0001
E:\INSTALL\RMAN\WIN\ARC0000000009_0823563417.0001
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'E:\INSTALL\RMAN\WIN\ARC0000000009_0823563417.0001',OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.

SQL>
SQL> create table test3 as select * from V$LOGMNR_CONTENTS;
这里发现能发现所有 的dml语句，特别注意这里insert操作
SQL> select distinct operation,substr(sql_redo,1,20) from test3 where table_name='TEST';

OPERATION                        SUBSTR(SQL_REDO,1,20)
-------------------------------- ----------------------------------------
DDL                              create table test as
DDL                              drop table test purg
UPDATE                           update "SCOTT"."TEST
INSERT                           insert into "SCOTT".
DELETE                           delete from "SCOTT".
UNSUPPORTED                      Unsupported

环境2：DB 10.20.4.8,OS:REHL 4.8 X86

www.htz.pw >alter database drop supplemental log data;
www.htz.pw >conn scott/oracle
Connected.
www.htz.pw >drop table test;
www.htz.pw >create table test as select * from emp;
www.htz.pw >insert into test select * from test;
www.htz.pw >update test set empno=1000;
www.htz.pw >commit;
www.htz.pw >delete test;
www.htz.pw >commit;
www.htz.pw > conn / as sysdba
Connected.
www.htz.pw >alter system archive log current;
www.htz.pw >select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/arch/1_986_816197230.dbf
www.htz.pw >EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/1_986_816197230.dbf',OPTIONS => DBMS_LOGMNR.NEW);
www.htz.pw >EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
www.htz.pw >drop table test3;
Table dropped.
www.htz.pw >
www.htz.pw >create table test3 as select * from V$LOGMNR_CONTENTS ;
这里注意，只发现update,delete语句，并没有发现insert操作
www.htz.pw >select distinct operation,substr(sql_redo,1,20) from test3 where table_name='TEST';

OPERATION                        SUBSTR(SQL_REDO,1,20)
-------------------------------- ----------------------------------------
DDL                              create table test as
DDL                              drop table test AS "
DDL                              ALTER TABLE "SCOTT".
UPDATE                           update "SCOTT"."TEST
DELETE                           delete from "SCOTT".