Oracle supplemental logging

This topic describes how to configure Oracle supplemental logging.

Background information

OceanBase Migration Service (OMS) relies on Oracle databases to have the necessary logs for correct data processing and synchronization during incremental data synchronization from an Oracle database to an Oracle-compatible OceanBase database. By default, Oracle databases only record basic log information. Therefore, in scenarios involving 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 types are as follows:

• All Supplemental Logging

  • Description

    Supplemental logging is enabled for all fields in the database. When this type of supplemental logging is enabled, the Oracle database records logs for all fields except LOB and LONG types, significantly increasing the volume of logs.

  • Requirements

    Enabling this type of supplemental logging is not mandatory. However, if you enable it, you do not need to perform any additional supplemental logging settings.

  • Enablement

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
    

• Minimal Supplemental Logging

  • Description

    The database records changes to special structures such as chained rows, clustered tables, and index-organized tables. This serves as the foundation for other types of supplemental logging.

  • Requirements

    In scenarios involving incremental synchronization from an Oracle database, you must enable this type of supplemental logging.

  • Enablement

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    

• Primary Key Supplemental Logging

  • Description

    The old values of the primary key fields of modified rows are unconditionally recorded. If the target table does not have a primary key, the old values of non-null unique index fields are recorded. If the target table has neither a primary key nor non-null unique index fields, the old values of all fields except LOB and LONG types are recorded.

  • Requirements

    It is recommended to enable this type of supplemental logging. If you do not enable it, you must enable table-level primary key supplemental logging.

  • Enablement

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

• Unique Key Supplemental Logging

  • Description

    When a unique key field is modified, the old values of all fields of the unique key (applicable to composite unique indexes) are recorded.

  • Requirements

    It is recommended to enable this type of supplemental logging. If you do not enable it, you must enable table-level unique key supplemental logging.

  • Enablement

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
    

You can execute the following statement to view the configuration of database-level supplemental logging.

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 types are as follows:

• All Supplemental Logging

  • Description

    Logs for all fields in the table are unconditionally recorded.

  • Requirements

    For tables with unique keys, enabling this type of supplemental logging is not mandatory. However, if you enable it, you do not need to perform any additional supplemental logging settings at the table or column level.

  • Enablement

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

• Primary Key Supplemental Logging

  • Description

    The old values of the primary key fields of modified rows are unconditionally recorded. If the target table does not have a primary key, non-null unique index fields or all fields are recorded.

  • Requirements

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

  • Enablement

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

• Unique Key Supplemental Logging

  • Description

    When a unique key field is modified, the old values of all fields of the unique key (applicable to composite unique indexes) are recorded.

  • Requirements

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

  • Enablement

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

Column-level supplemental logging

OMS needs to enable column-level supplemental logging for specific use cases in the Oracle database.

• Supplemental logging for specific columns

  • If SQL filters are set for the migrated objects, supplemental logging must be set for the corresponding row filter columns.

  • If the destination is a partitioned table, supplemental logging must be set for the corresponding partitioned table's partition columns.

  • If there are multiple unique keys at the source and destination, supplemental logging must be set for the columns involved in the unique keys. Otherwise, data quality risks may occur during data synchronization.

  You can execute the following statement to add specific columns to the supplemental logging.

  ALTER TABLE <table_name> ADD SUPPLEMENTAL LOG GROUP <table_name_group> (c1, c2) ALWAYS;
  

• All fields supplemental logging (All)

  If the destination is a table without a non-null unique key and no additionalhidden columns are created, all fields supplemental logging must be enabled for the corresponding table object.

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

Incremental DDL processing

The following table describes the risks and solutions when DDL operations are performed during data migration.

Operations after enabling supplemental logging

When you start the Store component, to ensure data integrity, incremental data is pulled back (5 minutes for restarting the Store component and 15 minutes for starting the Store component for the first time). To avoid the Store component pulling back incremental logs before supplemental logging is enabled, you must manually switch the archive logs and wait for 5 minutes or 15 minutes before starting the task.

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

ALTER SYSTEM SWITCH LOGFILE;