Supplemental Logging in Oracle Database

Supplemental Logging

Supplemental logging is a pre-requisite for redo log based database replication or recovery after instance crash.
By default, there is not enough information maintained in redo logs by oracle to reconstruct SQL statements so that they can be executed in multiple databases (Replica of some database table or entire database can be maintained at different, unrelated databases).

For example - If any update happens on database table which is replicated on another database, and all the changes are replicated in real time (as and when they happen at original database) redo logs having only ROWID (by default) are not sufficient to update the same record on replicated database table because ROWID of one database will be different on another database.
Supplemental logging allows us to add extra information in redo logs.

Supplemental logging can be enabled at database level or at table level.

Supplemental logging at table level -
1. Supplemental Log Groups :
    Allows to specify list of columns to be logged in redo logs.
    'ALWAYS' keyword is used for unconditional log groups. All specified columns are logged into redo log file every time a row is updated whether or not any of the specified column is updated.
Ex. ALTER TABLE table_name ADD SUPPLEMENTAL LOG GROUP GROUP_NAME (COL1, COL2, COL3,..) ALWAYS

2. Supplemental Key Column Logging :
    Allows to specify specific columns i.e. primary key/ unique/ foreign key or even ALL columns to be logged every time table row is updated.
Ex. i) ALTER TABLE table_name ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS
   ii) ALTER TABLE table_name ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS
  iii) ALTER TABLE table_name ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS
   iv) ALTER TABLE table_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
 
Supplemental logging is enabled at both the levels mentioned above in below table -

CREATE TABLE XXRVBD.XXRB_ORCL_INV_ONHAND_DTLS
(
  INV_ORG_CODE          VARCHAR2(20 BYTE),
  SUBINV_ORG_CODE       VARCHAR2(20 BYTE),
  PART_NUMBER           VARCHAR2(100 BYTE),
  SERIAL_NUMBER         VARCHAR2(100 BYTE),
  QUANTITY              NUMBER,
  CREATION_DATE         DATE,
  CREATED_BY            VARCHAR2(100 BYTE),
  PICK_FLAG             VARCHAR2(1 BYTE),
  SPLUS_ACCOUNT         VARCHAR2(50 BYTE),
  UPS_WAREHOUSE         VARCHAR2(50 BYTE),
  SUPPLEMENTAL LOG GROUP GGS_11869075_2 (INV_ORG_CODE,SUBINV_ORG_CODE,PART_NUMBER,SERIAL_NUMBER,QUANTITY,CREATION_DATE,CREATED_BY,PICK_FLAG,SPLUS_ACCOUNT,UPS_WAREHOUSE) ALWAYS,
  SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,
  SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS,
  SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS,
  SUPPLEMENTAL LOG DATA (ALL) COLUMNS
)