Oracle supplemental logging and logging rules

This article was further revised based on section 11.4.2 "Building the most available Oracle Database System: Oracle 11gR2 RAC management, maintenance and performance optimization", and thanked Lao tan for his reminder and help.

Supplemental Log is a complementary type of Log. It is mainly used for technologies such as logical Standby Database Synchronization, LogMiner, Streams, and Flashback. For a logical Standby database, ROWID cannot be used to identify a row. Oracle recommends that you use primary keys, constraints, and indexes to uniquely identify row data, however, if a table shard does not have a primary key, unique constraint, or unique index for some reason, row data cannot be effectively identified, the Supplemental Log must record multiple fields or even all field values to uniquely identify the table rows. Supplemental logs are also recorded in online Redo logs, so enabling Supplemental logs will accelerate the filling of online Redo logs.

Supplemental Log can be started at the database level. Starting Minimal Logging at the database level records sufficient information about the operation. Generally, it is sufficient to create Minimal Logging in the primary database. Database-level Supplemental logs can also be created for all fields, primary key fields, unique fields, and foreign key fields. You can also set Supplemental Log at the table level, which is called Identification Key Logging. Different types of Supplemental logs generate different Log records of different sizes.

Run the following SQL statement to enable or disable Minimal Logging at the database level:
Alter database add | drop supplemental log data;

The log information recorded in the logical Standby database has the following rules:
1) if a table has a primary key defined, the primary key is recorded as part of the UPDATE statement to identify the modified row.
2) If the primary key does not exist, the shortest non-null unique constraint and the unique index are recorded as part of the UPDATE statement to identify the modified row.
3) when the primary key and non-null unique constraints/indexes do not exist, all columns are recorded as part of the UPDATE statement to identify the modified rows. In other words, all fields except LONG, LOB, long raw, object type, and set type will be recorded.
4). A function index, even if it is defined as unique, cannot be used to uniquely identify the modified row. However, as long as the modified row can be uniquely identified, the logical Standby Database supports function index table synchronization.

Oracle recommends that you add primary keys or non-null unique indexes to tables in the primary database as much as possible to ensure that SQL Apply can effectively Apply Redo data update logic to Standby databases.

The following describes the syntax and type of Oracle supplemental logs through experiments:

C: \ Users \ LIUBINGLIN> sqlplus/as sysdba

SQL * Plus: Release 11.2.0.3.0 Production on Saturday March 15 20:27:28 2014

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Connect:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col supplemental_log_data_fk format a30
SQL> col supplemental_log_data_all format a30
SQL> col supplemental_log_data_min format a30
SQL> set linesize 200
SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

The database has been changed.

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
NO YES

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

The database has been changed.

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
NO

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

The database has been changed.

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
YES NO IMPLICIT
SUPPLEMENTAL_LOG_DATA_FK = YES indicates that the foreign key is enabled to supplement the log.

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

The database has been changed.

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
NO

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

The database has been changed.

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
NO IMPLICIT

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

The database has been changed.

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
NO

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

The database has been changed.

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
NO IMPLICIT

IMPLICIT-Minimal supplemental logging is enabled because all or a combination of primary key, unique key, and foreign key supplemental logging is enabled.
YES-Minimal supplemental logging is enabled through an alter database add supplemental log data statement.

SUPPLEMENTAL_LOG_DATA_MIN = YES and IMPLICIT both indicate that the database has started the minimal Supplemental log. YES indicates the minimum supplemental log that is started through the alter database add supplemental log data statement. If the supplemental log for the primary key, unique key, foreign key, and ALL is started, by default, the minimum Supplemental log is enabled. IMPLICIT indicates the minimum Supplemental log opened by enabling support for primary keys, unique keys, foreign keys, or ALL.

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
NO

The syntax for alter database add supplemental log data is as follows:

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

The database has been changed.

You can set multiple types of supplemental logs together.

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
YES NO IMPLICIT

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;

The database has been changed.

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
NO

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

The database has been changed.

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
NO YES IMPLICIT

Enable ALL types of supplemental logs that do not contain foreign keys. ALL indicates all column.

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

The database has been changed.

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
YES IMPLICIT

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

The database has been changed.

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------------------------------------------------------------------
YES

The preceding query results indicate that all types of supplemental logs are enabled.

From this experiment, we can conclude that the supplemental logs of the Oracle database are divided into: Minimum supplemental logs, primary key supplement logs, foreign key supplement logs, unique key supplement logs, and ALL types of supplemental logs.

After you execute the alter database SQL statement to open the DATABASE supplement log, the alarm log will show the status of the Supplemental log:
SUPLOG: Commencing to rollback failed DDL at scn = 13732025618576
SUPLOG: minimal = ON, primary key = OFF
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF

References:
Http://www.askmaclean.com/archives/oracle-supplemental-%E8%A1%A5%E5%85%A8%E6%97%A5%E5%BF%97%E4%BB%8B%E7%BB%8D.html

In addition, it should be noted that if the logs are supplemented for very busy database startup, it may cause performance problems and Hang problems. The following metalink article describes the problem:

Alter Database Add Supplemental Log Data Hangs (Document ID 406498.1)
Modification time: 2013-2-6 type: PROBLEM

In this Document

Symptoms
Changes
Cause
Solution
References

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

 

Applies:
Oracle Database-Enterprise Edition-Version 10.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS
As part of the Streams setup you can specify supplemental logging at the database level.

It can be done manually:

 

Alter database add supplemental log data (primary key, unique index) COLUMNS;
 

Or implicitly running procedures like DBMS_STREAMS_ADM.ADD_SCHEMA_RULES

Or if you setup Streams using Grid Control it is automatically executed by SrcSchemaRule1 Stage Step 12.

In some cases, this step hangs and the statement alter database add supplemental log data remains waiting for TX lock in shared mode.

CHANGES

CAUSE
The statement alter database add supplemental log data is waiting for TX lock in shared mode when there is any uncommitted transaction. This is the expected behavior.

You can issue alter database add supplemental log data when the database is open. however, OracleDatabase will invalidate all DML cursors in the cursor cache, which will have an effect on performance until the cache is repopulated. besides, we also need to wait for the completion of all the in-flight transaction so that any redo generated after this DDL wocould have the right supplemental logging attributes.

SOLUTION
You need to wait for the completion of all the in-flight transaction.

In those databases with high activity where there are always active transactions the supplemental logging can be enabled by bouncing the database and running the statement manually:

STARTUP MOUNT
Alter database add supplemental log data;
Alter database open;
When starting the database to supplement logs, All cached cursors will become invalid. A large amount of unexpected hard parsing will affect the database performance. Starting the database to supplement the log will wait until all the current transactions are completed, so that all the Redo data recorded after this operation has the attribute of supplementing the log, if the database has long transactions or the transaction is too frequent, this operation will occur Hang. Therefore, we can see that the supplemental logs of the database should be avoided as much as possible during the database peak period.

-- End --