Step-by-Step Guide to Enable Supplemental Logging in Oracle Database for GoldenGate Part -5

Purpose

In environments using Oracle GoldenGate, enabling Supplemental Logging is a mandatory prerequisite. 

Supplemental logging ensures that additional information about database changes is written into redo logs. This allows GoldenGate to correctly capture UPDATE and DELETE operations during replication.

In Multitenant Databases (CDB/PDB), supplemental logging must be enabled at the CDB$ROOT container level. This document explains how to check and enable supplemental logging step by step.

Prerequisites

• SYSDBA access to the database
• Database running in ARCHIVELOG mode
• Access to the CDB root container
• GoldenGate environment planning completed

Step 1: Check Supplemental Logging Status

Verify whether supplemental logging is already enabled.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI
from gv$database;

Example Output

SUPPLEME SUP SUP
-------- --- ---
NO       NO  NO
NO       NO  NO

Explanation

Column	Description
SUPPLEMENTAL_LOG_DATA_MIN	Minimal supplemental logging
SUPPLEMENTAL_LOG_DATA_PK	Primary key logging
SUPPLEMENTAL_LOG_DATA_UI	Unique index logging

If all values show NO, supplemental logging is not enabled.

Step 2: Attempt to Enable Supplemental Logging

If you try to enable supplemental logging directly, you may encounter the following error.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
*
ERROR at line 1:
ORA-31541: Supplemental logging is not enabled in CDB$ROOT.

Reason: Supplemental logging must first be enabled in the CDB$ROOT container.

Step 3: Connect to Root Container

SQL> conn / as sysdba

Connected.

Step 4: Enable Minimal Supplemental Logging

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

Step 5: Enable All Column Logging

This step is recommended for Oracle GoldenGate replication.

SQL> alter database add supplemental log data (all) columns;

Database altered.

Step 6: Verify Supplemental Logging

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI
from gv$database;

Example Output

SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO
YES      NO  NO

Now minimal supplemental logging is successfully enabled.

Why Supplemental Logging is Important

• Captures row identification information
• Ensures accurate replication of UPDATE operations
• Helps identify rows during DELETE operations
• Maintains data consistency between source and target databases

Summary

Step	Description
1	Check supplemental logging status
2	Connect to CDB root
3	Enable minimal supplemental logging
4	Enable ALL column logging
5	Verify configuration