用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; ------加附加日志
