Why is Supplemental logging required for Replication?
Integrated Capture (IC):
1. In the Integrated Capture mode, Oracle GoldenGate works directly with the database log mining server to receive the data changes in the form of logical change records (LCRs).
2. IC mode does not require any special setup for the databases using ASM, transparent data encryption, or Oracle RAC.
3. This feature is only available for oracle databases in Version 220.127.116.11 or higher.
4. It also supports various object types which were previously not supported by Classic Capture.
5. This Capture mode supports extracting data from source databases using compression.
Integrated Capture can be configured in an online or downstream mode.
Supplemental Logging Levels
There is two level of supplemental logging :
1. Database level
2. Table level
Why is Supplemental Logging Required?
Supplemental logging is required by each Change Data Capture (CDC) tool. GoldenGate being CDC tool hence it also requires supplemental logging to be enabled so that rows updated on Source can be uniquely identified at Target and updated.
One of the reasons to enable supplemental logging is to generate change logs for the update and delete statements. Supplemental logging makes sure that enough information is captured during this updates and deletes that can be used by any method based on logminer technology. Since GoldenGate is a SQL Apply hence once a row is updated or deleted from the source, GoldenGate Replicat Process searches for the same row on target on which update/delete needs to be performed.
A minimum level of supplemental logging at the database level is required for GoldenGate and can be achieved by issuing below command:
1. Enabling Supplemental Logging at DB Level:
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL>alter database add supplemental log data (all) columns;
To ensure same has been enabled query V$Database view.
By default, only changed column is logged into transaction logs for any operations. Normally, this means that primary key columns are not logged during an update/delete operation. However, Replicat requires the extra column(primary key/Unique Key columns) in order to apply the update/delete on the target system. The ADD TRANDATA command in GGSCI is used to cause Oracle to log extra column in logs for all transactions on a table.
For enabling Table level Supplemental logging, Database level supplemental logging is required. Consider DB level supplemental logging as a gateway to enable supplemental logging at the table level.
2. Enabling Supplemental Logging at Table Level:
GGSCI>dblogin userid c##gguser@pdb1,password *****
Note: For enabling supplemental logging at table level dblogin to the pluggable database is required if database architecture is Multitenant.
GGSCI>ADD TRANDATA pdb1.ggtraining1.dept1
The above command at GGSCI level is equivalent at database level as below depending on supplemental logging has to be enabled on primary key/Unique key for all columns