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';