This is a quick note about using LogMiner for mining archived redo logs on the database itself. (Source and mining databases are same)
- Specify a dictionary file with the following procedure:

EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora','/oracle/acs/logmnr', OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

- Determine the minimum and maximum archived log sequences that is in the mining scope.


- Specify one archived log file with DBMS_LOGMNR.ADD_LOGFILE procedure, dbms_logmnr.NEW option. Then we can add other archived log sequences with dbms_logmnr.ADDFILE option:

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+ARCH1/xx/archivelog/2013_01_28/thread_2_seq_31844.932.805914077', OPTIONS => dbms_logmnr.NEW); 
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+ARCH1/xxx/archivelog/2013_01_28/thread_2_seq_31844.932.805914077', OPTIONS => dbms_logmnr.ADDFILE); 
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+ARCH1/xxx/archivelog/2013_01_28/thread_2_seq_31845.1334.805914145', OPTIONS => dbms_logmnr.ADDFILE); 
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+ARCH1/xxx/archivelog/2013_01_28/thread_2_seq_31846.1062.805914271', OPTIONS => dbms_logmnr.ADDFILE);


- Start LogMiner with the EXECUTE DBMS_LOGMNR.START_LOGMNR procedure:
EXECUTE DBMS_LOGMNR.START_LOGMNR( DICTFILENAME =>'/oracle/acs/logmnr/dictionary.ora');

- It's also possible to start LogMiner by specifying a time or SCN range:
EXECUTE DBMS_LOGMNR.START_LOGMNR( DICTFILENAME => '/oracle/acs/logmnr/dictionary.ora', STARTTIME => to_date('01-Jan-2013 08:30:00', 'DD-MON-YYYY HH:MI:SS'), - ENDTIME => to_date('01-Jan-2013 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
- Now it's possible to query details about the operations inside archived logs. We can use V$LOGMNR_CONTENTS view for this purpose. However it may be slow to return data because it'll do log file sequential read. If several queries will be run for mining log, it'll be better to create a table by selecting all from V$LOGMNR_CONTENTS view. 
CREATE TABLE ORAMINE.LOGMNR as select * from v$logmnr_contents;
- OWhen we're finished with LogMiner, we can stop it with the following procedure:
EXECUTE DBMS_LOGMNR.end_logmnr;


10 Responses so far.

  1. Nice & helpful! I suppose you know that in recent versions (9i onwards, if I recall that right), you don't need a dictionary file anymore:
    begin dbms_logmnr.start_logmnr(options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
    end;


    Just as additional information :)

    Kind regards
    Uwe

  2. Thanks for the information Uwe. I didn't know that..

  3. Hi, Nice Basic Oracle Log Miner Steps.Thanks, its really helped me......

    -Aparna
    Theosoft

  4. This info you provided in the blog that was really unique I love it!!

    Oracle Training in Chennai

  5. Sravani says:

    Thank you for sharing such a nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information.

    Oracle Fusion HCM Online Training

  6. sushma says:

    Hey Really Thanks for sharing the best information regarding category, hope you will write more great blogs.
    Oracle Fusion Financials Online Training

  7. raju says:

    Thank you for sharing such a nice and interesting blog.
    Oracle Fusion Financials Online Training

  8. Oracle says:

    I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    Oracle Fusion SCM Online Training

  9. Naga says:

    Very interesting blog, keep posting such an informative post.

    Oracle Fusion Financials Online Training

  10. leela says:

    Really very helpful article, Thank you for sharing interesting blogs.

    Oracle Fusion Technical Online Training

Powered by Blogger.

Page Views

- Copyright © Emre Baransel - Oracle Blog -Metrominimalist- Powered by Blogger - Designed by Johanes Djogan -