What is supplemental logging

Supplemental logging only affect UPDATE type operation.  It forces additional columns into the redo logs. When you perform an update on a table rows, only the change vector for the affected columns of the row are written to redo log. The address in the database  where this changes occurs is given by the rowid. The rowid is a structured number that uniquely identify the block id and the file id of the table row. So, the change vector comprises the mutation and the physical address where the change took place. 

The change vector comprise the mutation and the physical address of the row where the change occurred.

 Unhappily if you want to transfer this mutation to another database, the physical address will most probably not correspond to the same row on the remote DB. Event the object id of the replicated table will differ on the two DB. So if on the source DB, you rely on the physical placement of the object to locate the changed row, how do you locate this row on the remote DB when you cannot count anymore on the address ? 

The answer brought by Oracle is that if you cannot locate the row if you rely on the physical address. And they concludes that if you cannot do it physically, then do it logically. Do not code the mutation using the physical address, but code the mutation under the form of an SQL and leave the resolution of the remote physical address to the remote DB. Problem is that to know which row exactly is affected you need all the columns that will uniquely identify the row.  If a change vector as enough information's to locate the object with the rowid, a replicated change vector will need to have all the columns that uniquely identify the row added in order to uniquely identify the row remotely. The act of adding those extra columns is called 'supplemental logging'.

Supplemental logging in 10gR2

In 10gR2, supplemental logging is automatically configured for tables with primary, unique, or foreign keys , providing that they are defined when the database object is prepared for Streams capture. It is implemented as a constraints and a system generated name is created. In 10gR2, you need to activate the supplemental logging at database level before activate it at Table level.  All target site indexed columns, including the primary key, unique index, and foreign key columns of a replicated table or database must be logged at the source site. Primary Key logging must be unconditionally logged, while  unique index and foreign keys may be only conditionally logged.

Views 

When dealing with supplemental logging there are 3 main views

1. v$database                            -- you will find database level logging
2. dbal_log_groups                     -- that hold information on log_group
3. dba_log_groups_columns        -- that hold information on columns in log_groups

DBA_LOG_GROUPS describes the log group definitions on the tables

Name                             Null?    Type
-------------------------------- -------- -----------------
OWNER                            NOT NULL VARCHAR2(30)
LOG_GROUP_NAME                   NOT NULL VARCHAR2(30)
TABLE_NAME                       NOT NULL VARCHAR2(30)
LOG_GROUP_TYPE                            VARCHAR2(19)
ALWAYS                                    VARCHAR2(11)         /* possible values are ALWAYS or CONDITIONAL */
GENERATED                                 VARCHAR2(14)

Column LOG_GROUP_TYPE may have the following values:

• PRIMARY KEY LOGGING
• UNIQUE KEY LOGGING
• FOREIGN KEY LOGGING
• ALL COLUMN KEY LOGGING
• USER LOG GROUP

 

DBA_LOG_GROUP_COLUMNS describes columns that are part into a log group

View : SYS.DBA_LOG_GROUP_COLUMNS

Name                              Null?    Type
--------------------------------  -------- ----------------------------
OWNER                             NOT NULL VARCHAR2(30)
LOG_GROUP_NAME                    NOT NULL VARCHAR2(30)
TABLE_NAME                        NOT NULL VARCHAR2(30)
COLUMN_NAME                                VARCHAR2(4000)
POSITION                          NUMBER
LOGGING_PROPERTY                           VARCHAR2(6)

References

Conditional logging

The before images of all specified columns are logged only if at least one of the columns in the log group is updated. Conditional logging must be set explicitly using the ALTER TABLE command and comes in 2 variations:

• LOG DATA:  The log data option is used to add  conditional logging only for UNIQUE KEY columns  and FOREIGN KEY columns.
  

  Alter table scott.EMP add supplemental logging log DATA (UNIQUE, FOREIGN KEY) columns

  This command will add into the Streams change vector the all the columns that are part of a unique index or are part of a Foreign Key.

• LOG  GROUP : you can named any columns you need. And you will these columns if they are part of a conflict resolution DML_HANDLER

When to use conditional logging: 

When to set conditional logging	Who
Parallelism of apply process > 1	All columns that are part of an unique constraint
Parallelism of apply process > 1	All columns that are part of a Foreign Key
Conflict update resolution	The columns specified in a column list
DML handler or error handler (for update)	All columns at source DB that are used into the procedure

Note :

The following exists         ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG DATA (ALL) Columns ;    but this is in fact the same as an unconditional supplemental logging

Unconditional logging

Whatever columns of the row is modified, include the before image of columns specified as unconditionally logged. You do this by adding the  key work 'ALWYAS'

Alter table scott.emp add supplemental logging log group (NAME, JOB) ALWAYS ;

When to use conditional logging: 

When to set unconditional logging	Who
Primary Key	all columns that are part of PK
substitute keys using the SET KEY COLUMNS	Any columns at the source database that are used in substitute key columns
rule base transformation (using dbms_transform)	Any columns at the source database that are used in the transformation
rule	Any columns at the source database that are used by a rule
row subsetting	Any columns at the source database that are in the dest table or dest subset