Oracle DBA Tips and Techniques

By Arjun Raja

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 a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: