② Enabling Supplemental Logging

You must enable supplemental logging for the captured tables or databases so that the previous state of changed database rows can also be captured when data changes occur. The following sections describe how to enable supplemental logging at the table or database level. You are advised to enable supplemental logging at the database level.

Note:

Refer to the corresponding section below for the operation based on your actual database version.

Choose one of the following solutions based on your actual situation:

Solution	Description	Step
Enable supplemental logging for a single table.	You must enable supplemental logging for the table to be synchronized. Advantage: The operation is precise. Note: You must enable full supplemental logging for a single table before running the pipeline task. Otherwise, data loss may occur. Disadvantage: You must enable it table by table.	ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; -- Enable minimal supplemental logging. ALTER TABLE Schema name.Table name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;--Enable full supplemental logging for a specific table.
Enable supplemental logging for the entire database.	If the data volume of the tables in your database is manageable and you prefer a database-level configuration (without enabling supplemental logging for each table you synchronize), you can enable supplemental logging for the entire database. Disadvantage: This will occupy more database space.	alter database add supplemental log data; alter system switch logfile; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Determining Whether the Database Supports CDBs/PDBs

Oracle of 12c and later versions introduces a multitenant environment, which includes two core concepts: container database (CDB) and pluggable database (PDB).

A CDB is a database that contains zero or more PDBs. A PDB is an independent database that can be plugged into or unplugged from a CDB, and each PDB has its own data files and user data. A CDB manages multiple PDBs, and PDBs share the system resources in the CDB, but their data and objects are isolated.

There is no difference between the two and a non-multitenant database instance in terms of usage, but data change capture can only be performed at the CDB level. Therefore, the configuration procedure differs depending on whether your database uses the CDB/PDB architecture.

1. Determine whether the currently connected database is a CDB.

Log in to the database as the database admin and use the following command to check if multi-tenancy is enabled for the database:

SELECT CDB FROM V$DATABASE;

If the CDB column value in the query result is YES, the currently connected database is a CDB. If the value is NO, the currently connected database is a non-container database.

2. Determine whether the current connection is to the root container or a PDB.

SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM DUAL;

If CDB$ROOT is returned, the current connection is to the root container of the CDB. If another name is returned, the current connection is to a PDB.

Scenario One: Non-multitenant Database

Use the following command to check if supplemental logging is enabled:

SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all FROM v$database;

1. If the values of all three columns are YES or IMPLICIT:

If the values of all three columns are YES or IMPLICIT, both identification key logging and full supplemental logging are enabled.

2. If the values of the first two columns are YES or IMPLICIT:

If the values of the first two columns are YES or IMPLICIT, only identification key logging has been enabled, and you must enable full supplemental logging.

Use the following command to enable full supplemental logging for a single table:

ALTER TABLE Schema name.Table name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Use the following command to enable full supplemental logging for all tables:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Submit the configuration changes:

ALTER SYSTEM SWITCH LOGFILE;

You can then use the following commands to check if supplemental logging is enabled for a specific table:

SELECT OWNER, TABLE_NAME, LOG_GROUP_NAME, ALWAYS, LOGGING_COLUMNS
FROM DBA_LOG_GROUPS
WHERE TABLE_NAME = Table name;

Scenario Two: CDB/PDB

Choose one of the following solutions based on your actual situation:

Solution	Description	Step
Enable supplemental logging for a single table.	You must enable supplemental logging for the table to be synchronized. Advantage: The operation is precise. Note: You must enable full supplemental logging for a single table before running the pipeline task. Otherwise, data loss may occur. Disadvantage: You must enable it table by table.	ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;     ALTER TABLE Schema name.Table name ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Enable supplemental logging for the entire database.	If the data volume of the tables in your database is manageable and you prefer a database-level configuration (without enabling supplemental logging for each table you synchronize), you can enable supplemental logging for the entire database. Disadvantage: This will occupy more database space.	ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

For details, see ③ Reading Data Change Records.