Perth, Australia
+ (61) 417713124

Using Log Miner

  • Posted by Arjun Raja
  • On July 13, 2009

Steps to take to use logminer to check for changes made to a certain table on a certain date.

1. Restore the archive log files from backup pertaining to archivelogs generated on the day and approximate time if you are not certain of the exact time –

$ rman target / catalog rman/rmann@rcat

Recovery Manager: Release – 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: TESTDBA (DBID=168397266)
connected to recovery catalog database

RMAN> run {
set archivelog destination to ‘/tmp/backup/arch_restore’;
restore archivelog from sequence 1000 until sequence 1005 ;

starting full resync of recovery catalog

Starting restore at 08-APR-08

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=185 devtype=SBT_TAPE

Finished restore at 08-APR-08

Create DICTIONARY….Make sure that the path is in UTL_FILE_DIR.

If it is not in the UTL_FILE_DIR path, you can use any path in the UTL_FILE_DIR if you don’t want an outage – otherwise if an outage is not an issue you can choose a new path, update the init.ora file and restart the database.

execute DBMS_LOGMNR_D.BUILD(‘dictionary.ora’, ‘/tmp/backup’, options => dbms_logmnr_d.store_in_flat_file);

Then create script file for logs :

exec dbms_logmnr.add_logfile(LogFileName=>’/tmp/backup/1_1000.ora’,Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName=>’/tmp/backup/1_1001.ora’,Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName=>’/tmp/backup/1_1002.ora’,Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName=>’/tmp/backup/1_1003.ora’,Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName=>’/tmp/backup/1_1004.ora’,Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName=>’/tmp/backup/1_1005.ora’,Options=>dbms_logmnr.ADDFILE);

Next run statement to ‘data mine’ the logfiles for that date and period when the changes were made –

DBMS_LOGMNR.start_logmnr (
dictfilename => ‘/tmp/backup/dictionary.ora’,
starttime => TO_DATE(’03-APR-2008 09:00:00′, ‘DD-MON-YYYY HH:MI:SS’),
endtime => TO_DATE(’03-APR-2008 12:00:00′, ‘DD-MON-YYYY HH:MI:SS’));

Once this is complete run this statement to extract actions made against table test.

select username,to_char(TIMESTAMP,’DD-MON:YYYY,HH24:MI:SS’),operation,sql_redo,session_info from v$logmnr_contents
where seg_name=’TEST’;

The command above will return rows if there were any changes made to this table.



Leave Reply

Your email address will not be published. Required fields are marked *