sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle logmnr日志挖掘 不能分析到DML操作的insert语句

时间:2016-10-20 18:48   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 分享一篇关于Oracle10G与11G数据库之间logmnr日志挖掘的差别,在10G中不能分析到DML操作的insert语句,11G数据可以分析到insert语句。
下面是测试logmnr用于分析日志,可以用于审计,也可以用于恢复用于误操作删除的数据,测试发现logmnr在10g环境中,sql_redo不能显示出insert操作,delete,update操作正常,在11g中,所有的dml操作都能解析出来,下面是测试环境,都没有启用附加日志。

1.环境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.环境2:DB 10.20.4.8,OS:REHL 4.8 X86


oracleplus.net>alter database drop supplemental log data;
oracleplus.net>conn scott/oracle
Connected.
oracleplus.net>drop table test;
oracleplus.net>create table test as select * from emp;
oracleplus.net>insert into test select * from test;
oracleplus.net>update test set empno=1000;
oracleplus.net>commit;
oracleplus.net>delete test;
oracleplus.net>commit;
oracleplus.net> conn / as sysdba
Connected.
oracleplus.net>alter system archive log current;
oracleplus.net>select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/arch/1_986_816197230.dbf
oracleplus.net>EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAMOracleоE => '/arch/1_986_816197230.dbf',OPTIONS => DBMS_LOGMNR.NEW);
oracleplus.net>EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
oracleplus.net>drop table test3;
Table dropped.
oracleplus.net>
oracleplus.net>create table test3 as select * from V$LOGMNR_CONTENTS ;
这里注意,只发现update,delete语句,并没有发现insert操作
oracleplus.net>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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle logmnr日志挖掘 不能分析到DML操作的insert语句

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1011.html

Oracle研究中心

关键词:

logmnr日志挖掘

Oracle 10G使用logmnr日志挖掘时不能分析到insert语句

Oracle 11G与10G数据库的logmnr日志挖掘的区别