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;