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:

9 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);

    Just as additional information :)

    Kind regards

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

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


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

    Oracle Training in Chennai

  5. Stephen says:

    There are lots of information about latest technology and how to get trained in them, like Hadoop Training Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies. By the way you are running a great blog. Thanks for sharing this.

  6. Thanks for your informative article on ios mobile application development. Your article helped me to explore the future of mobile apps developers. Having sound knowledge on mobile application development will help you to float in mobile application development. iOS Training in Chennai | iOS Training Institutes in Chennai

  7. geethu says:

    Great content. I really enjoyed while reading this content with useful information, keep sharing.
    Hadoop Training in Chennai | Hadoop Training Chennai | FITA Velachery | FITA Academy Chennai.

  8. kanchana says:

    Your blog is awesome..You have clearly explained about it ...Its very useful for me to know about new things..Keep on blogging..
    Hadoop training in chennai

  9. Thanu Abi says:

    The given article was very excellent & Great tips, and awesome way to get more tips,not only i like that post all peoples like that post,because of all given information was wonderful and it's very helpful for me.

    hadoop training in chennai

Powered by Blogger.

Page Views

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