Supplemental logging for Golden Gate

I have submitted my post in another forum where discussion for supplemental logging was going on.
Here is the my reply but I am intending to write a separate blog on Golden Gate.



There are two level of supplemental logging mentioned in oracle documentation

1. Database level supplemental logging
2. Table level supplemental logging


WHY WE NEED SUPPLEMENTAL LOGGING?

One of the main reason to enable supplemental logging is to generate logs for update statement. As insert and delete involves change to whole row (either addition or deletion of complete row) and all columns change in it where as update can happen on a column of a row. Supplemental logging make sure that enough information is captured during this update that can be used by any method based on logminer technology.

Other reasons could be to capture chained rows etc.

MINIMUM LEVEL OF SUPPLEMENTAL LOGGING

Minimum level of supplemental logging that is required as per oracle documentation is

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

By default, Oracle only logs changed columns for update operations. Normally,this means that primary key columns are not logged during an update operation.However, Replicat requires the primary key columns in order to apply the update on the target system. The ADD TRANDATA command in GGSCI is used to cause Oracle to log primary key columns for all updates

For Golden gate replication minimum level of supplemental logging require is at primary key level to uniquely identify each row for replication purpose.

Golden gate command
ADD TRANDATA scott.DEPT

actually enable supplemental logging at table level on primary key and running this command in the background
SQL ALTER TABLE "SCOTT"."DEPT" ADD SUPPLEMENTAL LOG GROUP "GGS_DEPT_1668166" ("DEPT_ID") ALWAYS

I was initially not sure why we have to enable supplemental logging twice but now my understanding is that

1. From oracle 10.2 onwards minimum level of supplemental logging at database level is required before enabling supplemental logging at table level.


2. Golden gate require minimum primary key supplemental logging which is expensive if enabled at database level when only one schema or few tables are configured for replication.

Hence basic supplemental logging enable at database level and specific primary key level on table level via Golden gate.

If you enable only table level supplemental logging without database level then oracle will not capture all changes.

LogMiner is not Showing the DML Activity When Table-Level Supplemental Logging is Enabled [ID 760897.1]

 

same is the case with if you only enable database level supplemental logging then
Can I Turn On The Oracle Supplemental Log At The DB Level Only? [ID 970903.1]

It is a logical pre-requisite to enable database level supplemental logging before enabling table level supplemental logging. This is due to bug in some oracle versions. Above mentioned article shows that table level supplemental logging can be enabled without enabling at database level.