Loading....

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 9.2.0.6.0 – 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 ;
}

executing command: SET ARCHIVELOG DESTINATION
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 –

SQL> BEGIN
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’));
END;
/

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.

Please follow and like us:

Last Update: July 13, 2009  

July 13, 2009 150 Arjun Raja
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, ,

Leave a Comment

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

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?