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 column be logged in the redo log files. The process of logging these additional columns is called supplemental logging.

There are two types of supplemental logging:

  1. Minimal Supplemental Logging.
  2. Identification Supplemental Logging

Generally, we have used Minimal Supplemental logging, as it does not impose significant overhead on the database generating redo log files. Identification supplemental logging can create overhead on database generating redo log files.

Minimal Supplemental logging :

Minimal supplemental logging logs the minimal amount of information needed for LogMiner to identify, group, and merge the redo operations associated with DML changes. It ensures that LogMiner (and any product building on LogMiner technology) has sufficient information to support chained rows and various storage arrangements, such as cluster tables and index-organized tables.

To check minimal supplemental logging is enabled or not?

To enable supplemental logging

Oracle recommends that you at least enable minimal supplemental logging for LogMiner.

Identification Supplemental Logging

Identification Supplemental logging is useful when redo log files are mined at Logical Standby database and not at Source database Instance. Using database identification key logging, you can enable database-wide before-image logging for all updates by specifying one or more of the following options to the ALTER DATABASE ADD SUPPLEMENTAL LOG  statement.

There is four option with ALTER DATABASE ADD SUPPLEMENTAL LOG:

  1. ALL
  2. PRIMARY KEY
  3. .UNIQUE KEY
  4. FOREIGN KEY

ALL: This option specifies that after enabling ALL option of supplemental logging , whenever a row is updated all columns of this row will be recorded in redo log file.

NoteLOBs, LONGS, and ADT[abstract data types] data types will not be placed in redo log if any other column is being updated in this row.

to enable supplemental log data for ALL column

To check whether it is enabled or not

PRIMARY KEY

This option causes the database to place all columns of a row’s primary key in the redo log file whenever a row containing a primary key is updated (even if no value in the primary key has changed).

If table does not contain Primary Key but have unique key then unique key is chosen for logging . If table is having multiple unique key then one of the unique key is chosen for logging.

If table does not contain either primary key or unique key then ALL columns are used for logging and gives same behavior as supplemental_log_data_all.

NOTE: Oracle recommends when you enable primary key supplemental logging all or most tables be defined to have primary or unique index keys..

To enable primary key supplemental logging :

To check whether primary key supplemental logging is enabled or not :

UNIQUE KEY :

With this option, database places all columns of a composite unique key or bitmap index in the redo log file if any column of composite unique key or bitmap index is being updated. A unique index can be created with unique constraint or unique index.

To enable unique key supplemental logging :

To check whether unique key supplemental logging is enabled or not :

FOREIGN KEY

This option causes the database to place all columns of a row’s foreign key in the redo log file if any column belonging to the foreign key is modified.

To enable foreign key supplemental log data

To check whether foreign key supplemental logging is enabled or not

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Comments

  1. Pingback: Types of Supplemental logging at Database level - SSWUG.ORG

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.