Types of Supplemental logging at Database level

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?

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

To enable supplemental logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

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.

Note: LOBs, 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

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Database altered.

To check whether it is enabled or not

SQL> SELECT SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;

SUP
---
YES

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 :

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

Database altered.

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

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK FROM V$DATABASE;

SUP
---
YES

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 :

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(UNIQUE) COLUMNS;

Database altered.

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

SQL> SELECT SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUP
---
YES

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

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(FOREIGN KEY) COLUMNS;

Database altered.

To check whether foreign key supplemental logging is enabled or not

SQL> SELECT SUPPLEMENTAL_LOG_DATA_FK FROM V$DATABASE;

SUP
---
YES

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