--在10g以上中默认情况下LOGMNR已经不是一个可靠的数据获取的方式，希望通过这种方式获取丢失数据，则需要提前设置SUPPLEMENTAL LOG DATA
--案例如下：

---案例1：没有设置SUPPLEMENTAL LOG DATA

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL> create table zyp_test as select * from dba_users;

Table created.

SQL> select count(*) from zyp_test;

  COUNT(*)
----------
        39

SQL> update zyp_test set user_id=999 where user_id=88;

1 row updated.

SQL> commit;

Commit complete.

SQL> execute dbms_logmnr_d.build(dictionary_filename => 'logmnr_dict.ora',dictionary_location => '/home/oracle/');

PL/SQL procedure successfully completed.

SQL> execute DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle/oracle11/oradata/ora11/redo01.log', PTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> execute DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle/oracle11/oradata/ora11/redo02.log', PTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL> execute DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle/oracle11/oradata/ora11/redo03.log', PTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.start_logmnr(DictFileName=> '/home/oracle/logmnr_dict.ora');

PL/SQL procedure successfully completed.

SQL> select sql_redo , sql_undo from v$logmnr_contents where table_name='ZYP_TEST';

SQL_REDO                                           SQL_UNDO
-------------------------------------------------- --------------------------------------------------
create table zyp_test as select * from dba_users;

----可以看出在没有设置SUPPLEMENTAL LOG DATA时，是看不到dml语句的

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

---案例2：设置SUPPLEMENTAL LOG DATA

SQL> drop table zyp_test;

Table dropped.

SQL>
SQL>
SQL>
SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;

Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

SQL> create table zyp_test as select * from dba_users;

Table created.

SQL> update zyp_test set user_id=999 where user_id=88;

1 row updated.

SQL> commit;

Commit complete.

SQL> execute dbms_logmnr_d.build(dictionary_filename => 'logmnr_dict.ora',dictionary_location => '/home/oracle/');

PL/SQL procedure successfully completed.

SQL> execute DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle/oracle11/oradata/ora11/redo01.log', PTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> execute DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle/oracle11/oradata/ora11/redo02.log', PTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL> execute DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle/oracle11/oradata/ora11/redo03.log', PTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL>  execute dbms_logmnr.start_logmnr(DictFileName=> '/home/oracle/logmnr_dict.ora');

PL/SQL procedure successfully completed.

SQL> set linesize 500
SQL> set pagesize 1000
SQL> l
  1* select sql_redo , sql_undo from v$logmnr_contents where table_name='ZYP_TEST'
SQL> /

SQL_REDO                                           SQL_UNDO
-------------------------------------------------- --------------------------------------------------
create table zyp_test as select * from dba_users;
drop table zyp_test;
create table zyp_test as select * from dba_users;
insert into "SYS"."ZYP_TEST"("USERNAME","USER_ID", delete from "SYS"."ZYP_TEST" where "USERNAME" = 'M
"PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DA GMT_VIEW' and "USER_ID" = '74' and "PASSWORD" IS N
TE","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","C ULL and "ACCOUNT_STATUS" = 'OPEN' and "LOCK_DATE"
REATED","PROFILE","INITIAL_RSRC_CONSUMER_GROUP","E IS NULL and "EXPIRY_DATE" = TO_DATE('26-JUN-12', '
XTERNAL_NAME","PASSWORD_VERSIONS","EDITIONS_ENABLE DD-MON-RR') and "DEFAULT_TABLESPACE" = 'SYSTEM' an
D","AUTHENTICATION_TYPE") values ('MGMT_VIEW','74' d "TEMPORARY_TABLESPACE" = 'TEMP' and "CREATED" =
,NULL,'OPEN',NULL,TO_DATE('26-JUN-12', 'DD-MON-RR' TO_DATE('13-AUG-09', 'DD-MON-RR') and "PROFILE" =
),'SYSTEM','TEMP',TO_DATE('13-AUG-09', 'DD-MON-RR' 'DEFAULT' and "INITIAL_RSRC_CONSUMER_GROUP" = 'DEF
),'DEFAULT','DEFAULT_CONSUMER_GROUP',NULL,'10G 11G AULT_CONSUMER_GROUP' and "EXTERNAL_NAME" IS NULL a
 ','N','PASSWORD');                                nd "PASSWORD_VERSIONS" = '10G 11G ' and "EDITIONS_
                                                   ENABLED" = 'N' and "AUTHENTICATION_TYPE" = 'PASSWO
                                                   RD' and ROWID = 'AAASN6AABAAAVbhAAA';

......中间部分省略

update "SYS"."ZYP_TEST" set "USER_ID" = '999' wher update "SYS"."ZYP_TEST" set "USER_ID" = '88' where
e "USER_ID" = '88' and ROWID = 'AAASN6AABAAAVbhAAg  "USER_ID" = '999' and ROWID = 'AAASN6AABAAAVbhAAg
';                                                 ';

43 rows selected.

SQL> update "SYS"."ZYP_TEST" set "USER_ID" = '88' where  "USER_ID" = '999' and ROWID = 'AAASN6AABAAAVbhAAg';                                                     

1 row updated.

SQL> commit;

Commit complete.

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.