This topic describes how to configure supplemental logging in Oracle databases.
Background information
When OceanBase Migration Service (OMS) synchronizes incremental data from an Oracle database to an Oracle-compatible mode OceanBase database, it relies on the Oracle database to have the necessary logs to correctly process and synchronize the data. In the default scenario, the Oracle database records only basic log information. Therefore, when synchronizing incremental data from an Oracle database, you must enable supplemental logging for the relevant migration objects.
Notice
Supplemental logging must be enabled in the primary Oracle database.
Database-level supplemental logging
Oracle databases support enabling supplemental logging at the database (Database) level. The following are the types:
All (All Columns)
Description
Supplemental logging is enabled for all columns in the database. After enabling this type of supplemental logging, the Oracle database records logs for all columns except LOB and LONG columns, significantly increasing the log volume.
Requirements
This type of supplemental logging is not mandatory, but if enabled, you do not need to configure any other supplemental logging settings.
Method
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Minimal Supplemental Logging
Description
This type of supplemental logging records change information about special structures such as chained rows, clustered tables, and index-organized tables. It serves as the foundation for other types of supplemental logging.
Requirements
In scenarios where incremental synchronization is performed on the Oracle database, supplemental logging must be enabled for this type.
Method
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Primary Key
Description
This type of supplemental logging unconditionally records the old values of the primary key columns of modified rows. If the target table does not have a primary key, it records the old values of the non-null unique index columns. If the target table has neither a primary key nor non-null unique index columns, it records the old values of all columns except LOB and LONG columns.
Requirements
We recommend that you enable this type of supplemental logging. If it is not enabled, you must enable primary key supplemental logging at the table level.
Method
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Unique Key
Description
This type of supplemental logging records the old values of all columns of the unique key when the unique key is modified (suitable for composite unique keys).
Requirements
We recommend that you enable this type of supplemental logging. If it is not enabled, you must enable unique key supplemental logging at the table level.
Method
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
You can execute the following statement to view the configuration of supplemental logging at the database level.
SELECT supplemental_log_data_min, supplemental_log_data_pk,
supplemental_log_data_ui, supplemental_log_data_fk,
supplemental_log_data_all
FROM v$database;
Table-level supplemental logging
Oracle databases support enabling supplemental logging at the table (Table) level. The following are the types:
All (All Columns)
Description
This type of supplemental logging unconditionally records logs for all columns of the table.
Requirements
If the table has a unique key, this type of supplemental logging is not mandatory. However, if enabled, you do not need to configure any other supplemental logging settings at the table or column level.
Method
ALTER TABLE schema.table_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Primary Key
Description
This type of supplemental logging unconditionally records the old values of the primary key columns of modified rows. If the target table does not have a primary key, it records the old values of all columns or uses the non-null unique index.
Requirements
If primary key supplemental logging is not enabled at the database level, you must enable primary key supplemental logging at the table level.
Method
ALTER TABLE schema.table_name ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Unique Key
Description
This type of supplemental logging records the old values of all columns of the unique key when the unique key is modified (suitable for composite unique keys).
Requirements
If unique key supplemental logging is not enabled at the database level, you must enable unique key supplemental logging at the table level.
Method
ALTER TABLE schema.table_name ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
Column-level supplemental logging
OMS needs to enable column-level supplemental logging for the corresponding Oracle database in specific scenarios.
Supplemental logging for specific columns
If the SQL filter conditions are set for the migration object, supplemental logging is required for the columns that correspond to the row filter conditions.
If the target is a partitioned table, supplemental logging is required for the partitioning columns of the partitioned table.
If both the source and target have multiple unique keys, supplemental logging is required for the columns involved in the unique keys. If not enabled, data quality risks may occur during data synchronization.
You can execute the following statement to add specific columns to supplemental logging:
ALTER TABLE <table_name> ADD SUPPLEMENTAL LOG GROUP <table_name_group> (c1, c2) ALWAYS;Supplemental logging for all columns (All)
If the target table does not have a non-null unique key and no hidden columns are created in the target, supplemental logging for all columns must be enabled for the corresponding table object.
ALTER TABLE <schema.table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Handling of incremental DDL operations
The following table describes the risks and solutions for data migration tasks if DDL operations are performed during the task.
| Operation | Risk | Solution |
|---|---|---|
| CREATE TABLE (and the table needs to be synchronized) | If the target is a partitioned table, the indexes of the source and target are inconsistent, or ETL operations are required, data migration performance may be affected, and ETL results may not meet expectations. | Supplemental logging for the primary key (PK) and unique key (UK) at the database level must be enabled. The involved columns must be manually added to supplemental logging. |
| Adding, deleting, or modifying the PK/UK, partitioning columns, or ETL columns | The rules for adding supplemental logging may not be met, which may result in data inconsistencies or reduced data migration performance. | The involved columns must be manually added to supplemental logging based on the rules for adding supplemental logging. |
Operations after supplemental logging is enabled
When the Store component is started, to ensure data integrity, incremental data is pulled back (5 minutes after restarting the Store component, or 15 minutes after starting a new Store component). To avoid pulling back incremental logs before supplemental logging was enabled, you must manually switch the archive logs and wait 5 or 15 minutes before starting the task.
After supplemental logging is enabled, you must switch the archive logs of the Oracle database at least twice.
Notice
If the database is an Oracle RAC, multiple instances must be alternately switched. In an Oracle RAC environment, if an instance is switched multiple times and then another instance is switched without alternating, the later instance may locate the starting pull file to the logs before supplemental logging was enabled.
ALTER SYSTEM SWITCH LOGFILE;