Supplemental logging in Oracle Database

This topic describes how to configure supplemental logging in Oracle Database.

Background information

When OceanBase Migration Service (OMS) synchronizes incremental data from an Oracle database to the Oracle compatible mode of OceanBase database, it relies on the Oracle database to have the necessary logs for proper data processing and synchronization. By default, an Oracle database records only basic log information. Therefore, to enable incremental synchronization from an Oracle database, you must enable supplemental logging for the relevant migration objects.

Database-level supplemental logging

Oracle databases support enabling supplemental logging at the database level. The options are as follows:

• All fields supplemental logging (All)

  • Description

    Supplemental logging is enabled for all fields in the database. After this option is enabled, the Oracle database records logs for all fields except LOB and LONG fields, significantly increasing the log volume.

  • Requirements

    This option is not mandatory, but if enabled, you do not need to make any other supplemental logging settings.

  • Enable method

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
    

• Minimal supplemental logging (Minimal Supplemental Logging)

  • Description

    This option records changes to 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 involving incremental synchronization from an Oracle database, this option must be enabled.

  • Enable method

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    

• Primary key supplemental logging (Primary Key)

  • Description

    This option unconditionally records the old values of the primary key fields of modified rows. If the target table has no primary key, it records the non-null unique index fields. If the target table has neither a primary key nor non-null unique index fields, it records all fields except LOB and LONG fields.

  • Requirements

    We recommend that you enable this option. If it is not enabled, you must enable primary key supplemental logging at the table level.

  • Enable method

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
    

• Unique key supplemental logging (Unique Key)

  • Description

    When a unique key field is modified, this option records the old values of all fields of the unique key (which applies to composite unique constraints).

  • Requirements

    We recommend that you enable this option. If it is not enabled, you must enable unique key supplemental logging at the table level.

  • Enable 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 level. The options are as follows:

• All fields supplemental logging (All)

  • Description

    This option unconditionally records logs for all fields in the table.

  • Requirements

    If the table has a unique key, this option is not mandatory. However, if enabled, you do not need to make any other supplemental logging settings at the table or column level.

  • Enable method

    ALTER TABLE schema.table_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
    

• Primary key supplemental logging (Primary Key)

  • Description

    This option unconditionally records the old values of the primary key fields of modified rows. If the target table has no primary key, it records the non-null unique constraint fields or all fields.

  • Requirements

    If primary key supplemental logging is not enabled at the database level, you must enable primary key supplemental logging at the table level.

  • Enable method

    ALTER TABLE schema.table_name ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
    

• Unique key supplemental logging (Unique Key)

  • Description

    When a unique key field is modified, this option records the old values of all fields of the unique key (which applies to composite unique constraints).

  • Requirements

    If unique key supplemental logging is not enabled at the database level, you must enable unique key supplemental logging at the table level.

  • Enable 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 a SQL filter condition is set for the migration object, supplemental logging is required for the columns involved in the row filter condition.

  • If the target is a partitioned table, supplemental logging is required for the partitioning columns of the partitioned table.

  • If both the source and the target have multiple unique keys, supplemental logging is required for the columns involved in all unique keys. If not, data quality risks may occur during data synchronization.

  • If both the source and the target have both primary keys and unique keys, supplemental logging is required for the columns involved in all unique keys. If not, 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 is a table without a non-null unique key and no additional hidden columns are created in the target, supplemental logging for all columns must be enabled in the corresponding table object.

  ALTER TABLE <schema.table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
  

Incremental DDL operations

The following table describes the risks and solutions that may be encountered during data migration if a DDL operation is performed.

Operations after enabling supplemental logging

When the Store component is started, to ensure data integrity, incremental data is pulled back (5 minutes after restarting the Store component and 15 minutes after starting a new Store component). To avoid pulling back incremental logs before supplemental logging was enabled, manually switch the archive and wait 5 or 15 minutes before starting the task.

After enabling supplemental logging, you must manually switch the archive logs of the Oracle database at least twice.

ALTER SYSTEM SWITCH LOGFILE;