From U - Logminer

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:'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:




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.


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.


Search Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to © The scripts, tips and articles appearing on cannot be reproduced elsewhere without the prior permission from the webmaster.