用oracle logminer挖不出数据报In Memory Undo is unsupported

用oracle logminer挖不出数据报In Memory Undo is unsupported

 

        最近在用logminer在oracle 10g上进行日志挖掘，无意之中create table test as select * from v$logmnr_contents; 发现其实是有记录的，只是记录的内容无法解析，info字段报In Memory Undo is unsupported这个问题。几经周折，加上附件日志就ok了，下面是实验再现：

 

C:\Documents and Settings\guogang>sqlplus test/test

 

SQL> create table tt as select rownum rn from dual connect by rownum <10;

表已创建。

第一次做实验：

 

SQL> select to_char(dbms_flashback.get_system_change_number) from dual;

TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE

----------------------------------------

10309235923278

 

SQL> delete from tt where rn=1;

已删除 1 行。

SQL> commit;

提交完成。

SQL> select to_char(dbms_flashback.get_system_change_number) from dual;

TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE

----------------------------------------

10309235923281

 

SQL> alter database add supplemental log data;--一定要加上附加日志

数据库已更改。

 

 

第二次做实验：

SQL> select to_char(dbms_flashback.get_system_change_number) from dual;

TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE

----------------------------------------

10309235923453

 

SQL> delete from tt where rn=2;

已删除 1 行。

SQL> commit;

提交完成。

 

SQL> select to_char(dbms_flashback.get_system_change_number) from dual;

TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE

----------------------------------------

10309235923456

 

 

C:\Documents and Settings\guogang>sqlplus / as sysdba;

 

第一次做实验--挖掘：

SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:\oracle\product\10.2.0\oradata\ordb10\REDO01.LOG',Options=>dbms_logmnr.new);

PL/SQL 过程已成功完成。

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog,StartScn=>10309235923278,EndScn=>10309235923281);

PL/SQL 过程已成功完成。

SQL> select sql_redo,sql_undo from v$logmnr_contents;

SQL_REDO

----------------------------------------------------------------------------------------------------

SQL_UNDO

----------------------------------------------------------------------------------------------------

 

第二次做实验--挖掘：

SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:\oracle\product\10.2.0\oradata\ordb10\REDO01.LOG',Options=>dbms_logmnr.new);

PL/SQL 过程已成功完成。

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog,StartScn=>10309235923453,EndScn=>10309235923456);

PL/SQL 过程已成功完成。

 

SQL> select sql_redo,sql_undo from v$logmnr_contents;

SQL_REDO

----------------------------------------------------------------------------------------------------

SQL_UNDO

----------------------------------------------------------------------------------------------------

set transaction read write;

delete from "TEST"."TT" where "RN" = '2' and ROWID = 'AAAOFRAAEAACku0AAB';

insert into "TEST"."TT"("RN") values ('2');

commit;

 

 

总结：

UNSUPPORTED Value In Sql_redo,Operation Columns Of V$Logmnr_contents [ID 282994.1]

Cause:

Logminer cannot always populate all the fields of the v$logmnr_contents this is because the redo may/may not have all the information that we need for every column.

Fix:

Enable supplemental logging on the database

 

Oracle认为这种情况不是个BUG，当打开附加日志时，将得到足够的信息， logminer也就能够正确的得到SQL结果。

alter database add supplemental log data;

alter database drop supplemental log data; ------加附加日志

查看更多关于用oracle logminer挖不出数据报In Memory Undo is unsupported的详细内容...