Supplemental Logging Revisited in Oracle Databases

 One of my favorite feature in Oracle database is supplemental logging. This comes very handy when you are trying to migrate a database or a table from one location to another. 

As per Oracle docs,"Redo log files are generally used for instance recovery and media recovery. The data needed for such operations is automatically recorded in the redo log files. However, a redo-based application may require that additional columns be logged in the redo log files. The process of logging these additional columns is called supplemental logging."

When the supplemental logging is active on a database, the redo logs contain other columns from tables to uniquely identify a row. If the table has a primary key or unique index defined, the only columns involved in the primary key or unique index will be registered in the redo logs along with the actual column(s) that has changed. But this whole process won't affect the current performance of your Oracle instance.

To enable table-level supplemental logging:

    

If the table has a primary key, add PRIMARY KEY supplemental logging for the table by running the following command.

---------------------------------------------------------------------------

    ALTER TABLE <table_name> ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

---------------------------------------------------------------------------

 

If no primary key exists and the table has multiple unique indexes, then normally the replication scrpt uses the first unique index in alphabetical order of index name. Create a supplemental log group as shown preceding on that index’s columns.

If there is no primary key and no unique index, supplemental logging must be added on all columns. Run the following query to add supplemental logging to all columns.

---------------------------------------------------------------------------

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

One real world scenario tip is to limit the initial load of a table to avoid extra overhead on the Oracle instance, the instance has been designed to only use one thread on instances to avoid creating massive overhead but there is no other way to limit the overhead, it is recommended that you migrate very large tables outside of business hours so if there is any overhead it doesn't affect business functionality, you can also separate the tables into different tasks.

I hope that helps.