- Determine which archive files you will read (e.x we will read archives between 395552..395557)
- 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;
/ - You can select log content from V$LOGMNR_CONTENTS;
select * from V$LOGMNR_CONTENTS; - 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 ) - After you finish with log miner, execute following query:
begin
sys.dbms_logmnr.end_logmnr;
end; - Then you can select it from table logminer_table
Monday, March 29, 2010
How to read the contents of Oracle Archive files (Log miner)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment