Monday, March 29, 2010

How to read the contents of Oracle Archive files (Log miner)


  1. Determine which archive files you will read (e.x we will read archives between 395552..395557)

  2. execute following command with user sys (Note that first add command called with sys.dbms_logmnr.new parameter and others callled with dbms_logmnr.addfile )

    begin
    sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395552_1_571790194.arc',options=>sys.dbms_logmnr.new);
    sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395553_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
    sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395554_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
    sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395555_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
    sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395556_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
    sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395557_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
    sys.DBMS_LOGMNR.START_LOGMNR(options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

    end;
    /

  3. You can select log content from V$LOGMNR_CONTENTS;
    select * from V$LOGMNR_CONTENTS;

  4. Selecting from V$LOGMNR_CONTENTS is very slow and another restriction is You can select the content of V$LOGMNR_CONTENTS only from the session that you execute the dbms_logmnr commands.If you want to to query it multiple times suggest you to insert this data to another local table

    create table logminer_table tablespace tools as select * from V$LOGMNR_CONTENTS;

    also you can create index on it in order to query time based
    create index logminer_table_ix on logminer_table("TIMESTAMP") tablespace tools ;

    Note:Be carefull for the space of logminer_table .Each archive will cover 4-5 bigger than the size of the archive .(for the above example think each archive is about 100m than for 6 archive. logminer_table will cover nealy 6*5*100m =3g )

  5. After you finish with log miner, execute following query:
    begin
    sys.dbms_logmnr.end_logmnr;
    end;

  6. Then you can select it from table logminer_table

No comments: