Supplemental logging in Oracle Database|V4.2.5|OceanBase Migration Service|docs|OceanBase Documentations

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.

Notice

Supplemental logging must be enabled in the primary Oracle database.

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.

Operation	Risk	Solution
CREATE TABLE (and the table needs to be synchronized)	If the target is a partitioned table, the indexes in 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, and modifying PK/UK/partitioning columns or ETL columns	The rules for adding supplemental logging may not be met, which may cause data inconsistencies or reduced data migration performance.	The involved columns must be manually added to supplemental logging based on the rules for supplemental logging.

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.

Notice

If the database is an Oracle RAC, multiple instances must be alternately switched. In the case of an Oracle RAC, if an instance is switched multiple times and then another instance is switched without alternating, the later instance may locate the start pull file to the log before supplemental logging was enabled.

ALTER SYSTEM SWITCH LOGFILE;