当前位置: 首页 > BASIC, ORACLE > 正文

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

       下面是测试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".

        通过上面两个测试发现,默认情况下(未开启附加日志):10G数据库sql_redo里面并不能显示insert语句,而11g里面是正常的。特别是如果用logmnr来做数据恢复的时候,在10G中需要注意了,可以操作部分数据不正常的情况。

本文固定链接: http://www.htz.pw/2013/08/16/logmnr%e5%88%86%e6%9e%90%e6%97%a5%e5%bf%97%ef%bc%8c%e4%b8%8d%e8%83%bd%e5%8f%91%e7%8e%b0insert%e6%93%8d%e4%bd%9c.html | 认真就输

该日志由 huangtingzhong 于2013年08月16日发表在 BASIC, ORACLE 分类下,
原创文章转载请注明: logmnr分析日志,不能发现insert操作 | 认真就输
关键字:

报歉!评论已关闭.