Why to enable Supplemental logging in Oracle Golden Gate

Supplemental logging is an Oracle Database feature that adds extra information to the redo logs. This information is not normally recorded but is essential for GoldenGate to accurately capture changes made to the database.

In dataguard replication, the physical structure remains the same, which means the rowid for primary and standby databases is identical. Therefore, the archive log operates on rowid for insert, update, and delete queries.

But with GoldenGate, when we insert a row in a table, both the primary and standby generate their own unique row IDs, meaning they are different. When we run an update or delete command, the redo log must have an entry for an extra column, like a primary key or unique key, to identify the unique column in the standby database for SQL application. If this isn’t done, GoldenGate replication fails. To avoid this, we need to enable supplemental logging for the replicated database or table.

Check the supplemental logging is enabled at Database level:

SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI 
FROM V$DATABASE;

Important: `

1. Supplemental logging must be enabled before starting the Golden Gate extract or replication process.
2. Table good to have same primary key in both source and target database.

Types of Supplemental Logging

1. Minimal Supplemental Logging

• Purpose: Required for any supplemental logging to work.
• What it does: Logs minimal information like row identifiers (ROWID).
• GoldenGate Requirement: Mandatory.

Enable Minimal Supplemental Logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

2. Primary Key Supplemental Logging

• Purpose: Logs primary key columns for all tables.
• What it does: Ensures GoldenGate can identify rows uniquely during UPDATE or DELETE.

3. Unique Key Supplemental Logging

• Purpose: Logs all unique key columns (if primary key is not present).
• What it does: Helps GoldenGate identify rows using alternate unique constraints.

Enable Primary Key and Unique Key Logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE);

4. Foreign Key Supplemental Logging

• Purpose: Logs foreign key columns.
• What it does: Useful for referential integrity in some replication scenarios.

Enable Foreign Key Logging (if needed)

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY);

5. Table-Level Supplemental Logging

• Purpose: Logs specific columns for specific tables.
• What it does: Needed when tables lack primary/unique keys or when specific columns must be tracked.

Enable Table-Level Supplemental Logging

-- Enable for all columns of table
ALTER TABLE schema_name.table_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS


-- Enable for specific columns for table
ALTER TABLE schema_name.table_name ADD SUPPLEMENTAL LOG GROUP log_group_name (column1, column2) ALWAYS;

Check the supplemental logging of object in Golden Gate:

dblogin userid username@tnsentry, password passwordvalue

example 
--Loging the database from golden gate
#dblogin userid hr@orcl,password oracle;

-- Check the logging 
#info trandata hr.emp;

Enable the supplemental logging of object from Golden Gate:

-- add the logging for hr.emp table
#add trandata hr.emp;

-- Verify again
#info trandata hr.emp;

Verify the enabling of logging from database:

select * from dba_log_groups;