Oracle supplemental logging and logging rules

SupplementalLog is a complementary type of log. It is mainly used for technologies such as logical Standby Database Synchronization, LogMiner, Streams, and Flashback. For logical Stand

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 logical Stand

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;