PRIMARY KEY creation effect on SUPPLEMENTAL LOGGING

Recently I’ve discovered a bug that affects all versions of Oracle RDBMS from 11g up to the 19c.

If you are using SUPPLEMENTAL LOGGING feature for any reason (security audit of logs, Data Guard, tables replication…) you might be affected.

To follow my example you need to fulfill prerequisites to prepare database for supplemental logging.

For start your database needs to be in archivelog mode, then you need to choose whether you want database level or table level supplemental logging.

Here I’ll describe the common case where I want to perform real-time table replication based on the PRIMARY KEY, that will have the minimal impact on your database.

As a privileged user you need to execute:

 

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

In a schema SCOTT you can create test table:

 

create table ttest
(
id number,  
col1 varchar2(50)
) tablespace users;

create unique index ttest_pk on ttest(id);  

alter table ttest add constraint ttest_pk primary key (id) using index enable novalidate;

As a privileged user you can add SUPPLEMENTAL LOGGING for the table of interest.

 

alter table scott.ttest add supplemental log data (primary key) columns;

Check if table is added intot he list of tables with SUPPLEMENTAL LOGGING enabled:

 

select * from dba_log_groups where owner = 'SCOTT' and table_name = 'TTEST';

As a user SCOTT, perform two DMLs:

 

insert into ttest values (1,1);
commit;

update ttest set id = 555 where id = 1;
commit;

In a log you’ll find the following:

 

update "SCOTT"."TTEST" set "ID" = '555' where "ID" = '1' and "COL1" = '1' and ROWID = 'AAAVZ+AAHAAAKvLAAA';

As you can see, instead of only ID column, we can observe additional column – COL1.

This is the same effect as ALL columns based supplemental logging:

 

alter table scott.ttest add supplemental log data (ALL) columns; 

Besides the following DDL command:

 

alter table ttest add constraint ttest_pk primary key (id) using index enable novalidate;

the following DDL produces the same effect.

 

alter table ttest add constraint ttest_pk primary key (id) using index disable;

If you are using:

 

alter table ttest add constraint ttest_pk primary key (id) using index enable validate;

or

 

alter table ttest add constraint ttest_pk primary key (id) using index;

you’ll get expected result (without COL1):

 

update "SCOTT"."TTEST" set "ID" = '555' where "ID" = '1' and ROWID = 'AAAVZ+AAHAAAKvLAAA';

 

There are two kinds of impact you need to be aware of.

If you have security audit in place, you want to be sure that what is written in the Oracle log hast to be accurate.

In this case bug has actually changed the statement you executed which implies that you cannot rely on the log content.

 

Second impact is on performance.

Depending how many UPDATE statement you have on table you are replicating, by applying ALL columns instead of primary key and column(s) that is actually changed by the update, you are adding a lot of overhead.

Update statement will take longer and you’ll have increased volume of log generation per hour.

 

Summary:

 

Oracle has so many options and combinations of many options that prevent any regression test to test them all.

For that reason the best strategy is to carefully check all the effects of functionality you want to use, and to always stick with common used options (here I’ll always remember a story with DBA who created DWH database with 32K database block size and numerous related issues).

It takes some time for me to analyze the reason of extra columns in the WHERE clause for the UPDATE statements.

I’ve started with RLS and Policy, transformation package etc. till I narrow down the problem on PRIMARY KEY constraint creation.

Oracle will fix this bug, but till then, you can use the standard (ENABLE VALIDATE which is a default) way of creating PK constraints.

---