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.
Can I Turn On The Oracle Supplemental Log At The DB Level Only? [ID 970903.1]