Supplemental Logging. Subtle differences with Oracle versions 9i-12c

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
2 min read

Problem Description

This articles describes subtle differences in supplemental logging between versions of oracle. Especially between 9i and rest of the version.

Solution

Problem description goes here

For Oracle 10g, 11g and 12c if there is no primary key defined on the table, then Oracle 
automatically adds additional columns to the supplemental log data to satisfy either the 
unique key, or if there is no unique key, then it adds all the columns in the table.


To query for >= 10g
SELECT supplemental_log_data_min MIN, 
supplemental_log_data_pk PK, 
supplemental_log_data_ui UI, 
supplemental_log_data_fk FK,
supplemental_log_data_all "ALL" 
FROM v$database;


For Oracle 9i, Oracle does not automatically adds additional columns. The specific 
supplemental log level has to be defined. Dbvisit Replicate does this automatically.

ALTER TABLE <table_name> ADD SUPPLEMENTAL LOG GROUP <log_grp1> 
(Column1,....,Column33) ALWAYS;

View v$database doesn't have same fields as >=10g
SELECT supplemental_log_data_min MIN, 
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI
FROM v$database;

 

An example for 9i

SQL> create table temp (
id1 number(2),id2 varchar2(10),id3 number(2),id4 number(2),id5 varchar2(10),
id6 number(2),id7 number(2),id8 number(2),id9 number(2),id10 number(2),
id11 number(2),id12 number(2),id13 number(2),id14 number(2),id15 varchar2(10),
id16 number(2),id17 number(2),id18 number(2),id19 number(2),id20 number(2),
id21 number(2),id22 number(2),id23 number(2),id24 number(2),id25 number(2),
id26 number(2),id27 number(2),id28 number(2),id29 number(2),id30 number(2),
id31 number(2),id32 number(2),id33 number(2),id34 number(2),id35 number(2),
id36 number(2),id37 number(2),id38 number(2),id39 number(2),id40 number(2),
id41 number(2),id42 number(2),id43 number(2),id44 number(2),id45 number(2),
id46 number(2),id47 number(2),id48 number(2),id49 number(2),id50 varchar2(10));

SQL> insert into temp values(1,'aa',1,1,'ab',1,1,1,1,1,1,4,5,6,'cc',7,8,4,5,6,7,8,2,3,5,5,6,7,8,8,9,9,9,90,3,2,4,5,2,1,4,5,6,7,8,8,9,9,20,'dd');

SQL>insert into temp values(1,'ee',1,1,'gg',1,1,1,1,13,1,4,5,6,'gh',7,8,4,25,6,7,8,32,3,5,5,66,7,8,8,9,9,9,90,3,2,4,5,32,1,4,5,6,7,8,8,9,9,20,'vb');

SQL>ALTER TABLE test.temp ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; 

  • - FAILS in 9i

SQL>ALTER TABLE test.temp ADD SUPPLEMENTAL LOG GROUP LOG_GROUP1
(id1,id2,id3,id4,id5,id6,id7,id8,id9,id10,id11,id12,id13,id14,id15,id16,id17,id18,id19,id20,
id21,id22,id23,id24,id25,id26,id27,id28,id29,id30,id31,id32,id33
) ALWAYS;

Verify log group.. 

SQL> SELECT supplemental_log_data_min MIN, 
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI
FROM v$database;

MIN SUP SUP

  • -- --- ---

YES NO NO