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;

Oracle LogMiner (2) supplemental logging

Environment

Oracle 10g (10.2.0.4) on Solaris 10

Purpose

Test Logminer with different supplemental logging modes.

Compare actual and Logminer SQL and choose proper supplemental logging to re-apply some application code.

Prepare database

CREATE TABLE "ALEXZ"."TBL1"

("ID" NUMBER,

 "N1" NUMBER,

 "V1" VARCHAR2(77),

 "D1" DATE, "D2" DATE,

PRIMARY KEY ("ID") VALIDATE);

Select log_mode, SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;

LOG_MODE     SUPPLEME SUP SUP

------------ -------- --- ---

NOARCHIVELOG NO       NO  NO

Startup mount;

alter database archivelog;

alter database add supplemental log data (primary key) columns;

alter database open;

Logminer session

Choose current log file.

select * from v$log;

select * from v$logfile;

Start logminer session.

EXECUTE sys.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/redo/01/DHMERT01/DHMERT01redo01.dbf', OPTIONS => sys.DBMS_LOGMNR.NEW);

EXECUTE sys.DBMS_LOGMNR.START_LOGMNR(OPTIONS => sys.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + sys.DBMS_LOGMNR.COMMITTED_DATA_ONLY);

select * from V$LOGMNR_CONTENTS

where seg_name ='TBL1' order by CSCN;

Task 1

PK supplemental logging

Actual SQL:

insert into tbl1 values (1,1,'aa',sysdate,sysdate);

update tbl1 set d2=sysdate where id=1;

SQL_REDO from Logminer:

insert into "ALEXZ"."TBL1"("ID","N1","V1","D1","D2") values ('1','1','aa',TO_DATE('2009-12-01 12:29', 'yyyy-mm-dd hh24:mi'),TO_DATE('2009-12-01 12:29', 'yyyy-mm-dd hh24:mi'));

update "ALEXZ"."TBL1" set "D2" = TO_DATE('2009-12-01 12:29', 'yyyy-mm-dd hh24:mi') where "ID" = '1' and "D2" = TO_DATE('2009-12-01 12:29', 'yyyy-mm-dd hh24:mi') and ROWID = 'AAAip0AADAAA6GqAAA';

Task 2

All columns supplemental logging

alter database drop supplemental log data (primary key) columns;

alter database add supplemental log data (all) columns;

Select log_mode, SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;

LOG_MODE     SUPPLEME SUP SUP

------------ -------- --- ---

ARCHIVELOG   IMPLICIT NO  NO

Actual SQL:

insert into tbl1 values (2,2,'aa',sysdate,sysdate);

update tbl1 set d2=sysdate+1 where id=2;

SQL_REDO from Logminer:

insert into "ALEXZ"."TBL1"("ID","N1","V1","D1","D2") values ('2','2','aa',TO_DATE('2009-12-01 12:35', 'yyyy-mm-dd hh24:mi'),TO_DATE('2009-12-01 12:35', 'yyyy-mm-dd hh24:mi'));

update "ALEXZ"."TBL1" set "D2" = TO_DATE('2009-12-02 12:36', 'yyyy-mm-dd hh24:mi') where "ID" = '2' and "N1" = '2' and "V1" = 'aa' and "D1" = TO_DATE('2009-12-01 12:35', 'yyyy-mm-dd hh24:mi') and "D2" = TO_DATE('2009-12-01 12:35', 'yyyy-mm-dd hh24:mi') and ROWID = 'AAAip0AADAAA6GqAAB';

Task 3

All columns + PK supplemental logging (it seems working like “all columns”)

alter database add supplemental log data (primary key) columns;

LOG_MODE     SUPPLEME SUP SUP

------------ -------- --- ---

ARCHIVELOG   IMPLICIT YES NO

insert into tbl1 values (3,3,'aa',sysdate,sysdate);

update tbl1 set d2=sysdate+1 where id=3;

SQL_REDO from Logminer:

insert into "ALEXZ"."TBL1"("ID","N1","V1","D1","D2") values ('3','3','aa',TO_DATE('2009-12-01 12:37', 'yyyy-mm-dd hh24:mi'),TO_DATE('2009-12-01 12:37', 'yyyy-mm-dd hh24:mi'));

update "ALEXZ"."TBL1" set "D2" = TO_DATE('2009-12-02 12:38', 'yyyy-mm-dd hh24:mi') where "ID" = '3' and "N1" = '3' and "V1" = 'aa' and "D1" = TO_DATE('2009-12-01 12:37', 'yyyy-mm-dd hh24:mi') and "D2" = TO_DATE('2009-12-01 12:37', 'yyyy-mm-dd hh24:mi') and ROWID = 'AAAip0AADAAA6GqAAC';

Task 4

Update without PK

PK supplemental logging

alter database drop supplemental log data (all) columns;

alter database drop supplemental log data (primary key) columns;

LOG_MODE     SUPPLEME SUP SUP

------------ -------- --- ---

ARCHIVELOG   NO       NO  NO

alter database add supplemental log data (primary key) columns;

LOG_MODE     SUPPLEME SUP SUP

------------ -------- --- ---

ARCHIVELOG   IMPLICIT YES NO

update tbl1 set v1='bb' where n1<3;

SQL_REDO from Logminer:

update "ALEXZ"."TBL1" set "V1" = 'bb' where "ID" = '1' and "V1" = 'aa' and ROWID = 'AAAip0AADAAA6GqAAA';

update "ALEXZ"."TBL1" set "V1" = 'bb' where "ID" = '2' and "V1" = 'aa' and ROWID = 'AAAip0AADAAA6GqAAB';

Task 5

Update without PK

Minimal supplemental logging

alter database drop supplemental log data (primary key) columns;

alter database add supplemental log data;

LOG_MODE     SUPPLEME SUP SUP

------------ -------- --- ---

ARCHIVELOG   YES      NO  NO

update tbl1 set v1='cc' where n1<3;

SQL_REDO from Logminer:

update "ALEXZ"."TBL1" set "V1" = 'cc' where "V1" = 'bb' and ROWID = 'AAAip0AADAAA6GqAAA';

update "ALEXZ"."TBL1" set "V1" = 'cc' where "V1" = 'bb' and ROWID = 'AAAip0AADAAA6GqAAB';