2009-12-15

Oracle LogMiner (3)

Environment

Oracle 10g (10.2.0.4) on Solaris 10

Purpose

Extract data with given time interval and filter.
Time interval:
from 2009-12-09 10:35 to 2009-12-09 10:35
Filter:
table_space='TABLES' and operation in ('UPDATE','INSERT','DELETE');

Solution

Switch logs to make sure that latest data changes are in archived logs.
alter system switch logfile;

Find needed logs:
select * from v$archived_log
where completion_time > to_date('2009-12-09 10:35','yyyy-mm-dd hh24:mi')
order by first_time;

Create SQL script to add logs to LogMiner session:
select 'DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '''||name||''');' from v$archived_log
where first_time > to_date('2009-12-06 10:35','yyyy-mm-dd hh24:mi')
order by first_time;

After adding DBMS_LOGMNR.NEW to first log, it looks like this:

begin
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/archive/DHMERR03/disk/1_628_689683506.dbf',OPTIONS => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/archive/DHMERR03/disk/1_629_689683506.dbf');
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/archive/DHMERR03/disk/1_630_689683506.dbf');
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/archive/DHMERR03/disk/1_631_689683506.dbf');
end;

Alternatively you can define only first log and use option CONTINUOUS_MINE to add following logs automatically.

Start LogMiner:
Begin
DBMS_LOGMNR.START_LOGMNR(startTime => to_date('2009-12-09 10:35','yyyy-mm-dd hh24:mi'), endTime => to_date('2009-12-15 11:10','yyyy-mm-dd hh24:mi'), OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
End;

As PK supplemental logging was used in this case, ROWID could be excluded by adding option DBMS_LOGMNR.NO_ROWID_IN_STMT to previous statement. It would look like:
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.NO_ROWID_IN_STMT)

Return data changes using given filter:
select * from V$LOGMNR_CONTENTS
where table_space='TABLES' and operation in ('UPDATE','INSERT','DELETE');

If necessary you can record the result into table.
create table alexz.LOGMINER_CONTENTS_20091215 tablespace users
as select * from V$LOGMNR_CONTENTS
where table_space='TABLES' and operation in ('UPDATE','INSERT','DELETE');

grant select on alexz.LOGMINER_CONTENTS_20091215 to public;

Finish LogMiner session
EXECUTE DBMS_LOGMNR.END_LOGMNR;

1 comment:

  1. This is for those who are well aware of the Oracle concepts. This post describe the code for oracle logminer(). The code seems difficult for beginners but logic is simple for experts. Try to understand it.
    sap support packs

    ReplyDelete