Tuesday, January 22, 2008

Using logminer

A customer wanted to know how a certain record in their EBS database was updated.
A colleque of mine decided to use logminer to find out what happened to that specific record.
Using logminer, all changes in our archived logfiles are written to the v$logmnr_contents and you can query this view using sql statements.

So, how does it work ? We choose the online catalog variant..(see Meatlink note 291686.1 for more options)

First, try to find out which logfiles you should examine. In our case, when was the record updated. We figured it had to be between 10:00 am untill 04:00 pm. So we had to use like 33 archived logfiles ! Teh change had to be in one of them.
Once you have decided which logfiles to use, you must 'add' them using the following syntax :

SQL> execute DBMS_LOGMNR.ADD_LOGFILE (LOGFILENAME => '/XXX01.LOG',OPTIONS =>DBMS_LOGMNR.NEW);

The first logfile must be added with the LOGMNR.NEW option. If you have more logfiles, the follwoing ones must have the LOGMNR.addfile option. Otherwise, only the last logfile is used :)

When all logfiles are added, you can start the logminer

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => RBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Now, you can query the v$logmnr_contents view. We decided to spool the output, make it a little bit eassier to search.

SQL> select username, operation, SQL_REDO,SQL_UNDO FROM V$LOGMNR_CONTENTS where sql_redo like '%updated_table%';

When finished..

SQL> EXECUTE DBMS_LOGMNR.end_LOGMNR

Now the searching begins. We finally succeeded, but searching is not easy.

3 comments:

Philippe said...

I'm trying to recover data using logmnr on a cluster RAC 10g r2.

How can I specify the file name because the file is in ASM and not on the filesystem.

Thanks foir your help

Bas Klaassen said...

Hi Philippe,

I don't know if there is a special way for ASM files when using logminer. I have to check it out...

Microsoft Office 2007 said...

NEVERTHELESS, THE CIVIL LAW is and must be neutral about who has a more noble or rewarding faith. The breakaway parishes ought to win every Office 2010facet of the lawsuit not becauseMicrosoft Office 2010 their beliefs or their politics are better, Microsoft wordbut because both lawOffice 2007and equity, along with common sense, are on Microsoft Officetheir side.Microsoft Office 2007 Not only does Virginia state law (the Division Statute)Office 2007 keyexplicitly apply to just such a Office 2007 downloadsituation as now exists, but the history Office 2007 Professionalespecially of The Falls Church argues against the claims of Outlook 2010the Virginia Diocese with which theyMicrosoft outlookhave disassociated.Microsoft outlook 2010First, The Falls Church wasWindows 7 founded, formed, and developed long before the diocese, or the national Episcopal Church, even existed.