The document comes from Mei Yuan. Thanks Mei, for sending this to me. Questions and comments, please contact the author.

Log Miner

Setup Log Miner

Check if dbms_logmnr_d package is installed in the database.  If not, run dbmslmd.sql for 8I and dbmslogmnrd.sql for 8

Generate dict file for the database:  

dbms_logmnr_d.build('file name', 'directory name');# the directory name has to be in the init.ora file for dump location.

Using Log Miner

To begin built a new file supply container:

dbms_logmnr.add_logfile('<file path/file name', dbms_logmnr.NEW)

To add more file to use the log miner:

dbms_logmnr.add_logfile('file path/file name', dbms_logmnr.ADDFILE)

Initiate log analysis:

dbms_logmnr.start_logmnr(dictfilename=>'dict file name with file path', starttime=>to_date('01/01/98:08AM','DD/MM/YY:HHAM'), endtime=>to_date('03/01/1998:09AM', 'DD/MM/YYYY:HHAM'));

To view log information:

select scn, to_char(timestamp ‘MM/DD/YYYY HH24:MI:SS’), username, sql_redo, sql_undo from v$logmnr_contents    where seg_name = 'table name';

If the entry is for rolling back the previous transaction, the ROLLBACK column should be 1, otherwise the value is 0.

If a dictionary file exists, the object number showing as “OBJ #”, otherwise, it shows as “Objn”.

To release resources:   dbms_logmnr.end_logmnr;

Log information views:

v$logmnr_dictionary

v$logmnr_logs

v$logmnr_parameters

Read information from V$logmnr_contents:

Insert/delete will be the exact statement

DDL search by object # which can be got from all_objects table

Lob data type value can not be seen in the redo sql / undo sql.

Notes:

Before reverse the changes, check dependency relationships.

Check the character fields for ‘ and “.

Using dictionary file is more readable but take longer time to analyze.