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