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;
