Database level supplemental logging is an Oracle requirement that ensures that the Oracle redo log on the source database contains the information required to describe all data changes completely.
Turning on supplemental logging ensures additional information is inserted into redo stream in order to facilitate replication. The extra level of overhead of adding this information into the redo log is generally less than 1%.
Supplemental logging is also required in order to ensure the following items are replicated succesfully:
- chained rows
- clustered tables
- index organized tables (IOTs)
Supplemental logging can be enabled for
- For all columns
- For primary key columns
- For unique columns
- For foreign key columns
Dbvisit Replicate automatically turns on supplemental logging and sets the minimal logging level based on the requirement
The following SQL is run by Dbvisit Replicate to turn on supplemental logging
SQL> alter database add supplemental log data;
If DDL is enabled, then the following is run
SQL> alter database add supplemental log data (primary key) columns;
For each table to be replicated the following SQL is run
SQL> alter table '||owner||'.'||table_name||' add supplemental log data (primary key) columns;
To check and monitor what supplemental logging has been turned on, the following query can be run
SQL> SELECT supplemental_log_data_min MIN, supplemental_log_data_pk PK, supplemental_log_data_ui UI, supplemental_log_data_fk FK,supplemental_log_data_all "ALL" FROM v$database;