What is Oracle Supplemental Logging?

Here is a quick synopsis on supplemental logging. I have simply distilled the information gathered from The "Oracle Database Utilities" Manual, Chapter 17 for 10g R2. Because Streams uses logminer and logminer required supplemental logging, streams requires supplemental logging.

Types of Supplemental Logging:
You can add supplemental logging at either the table level, or the database level.

Database Level Supplemental Logging:
There are two types of database level supplemental logging: minimal logging, and identification key logging. Identification key logging can impose significant overhead. Minimal Supplemental Logging "logs the minimal amount of information needed for LogMiner to identify, group, and merge the redo operations associated with DML changes." You turn it on with:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database Level Identification Key logging is necessary when the redo logs will not be mined at the source database instance. Here are the options:

• ALL system-generated uncondititional supplemental log group
  

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


• PRIMARY KEY system-generated uncondititional supplemental log group
  

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


• UNIQUE system-generated conditional supplemental log group
  

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


• FOREIGN KEY system-generated conditional supplemental log group
  

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

Table-level Supplemental Logging
Table-level Identification key Logging:
This configuration offers the same options as those provided at the database level and "whenever a column in any database table is changed, the entire row containing that column (except columns for LOBs, LONGs, and ADTs) will be placed in the redo log file".

ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Table-level User-Defined Supplemental Log Groups.
This option gives maximum control over the columns that are logged. You get to specify what columns' before-images you wish to add to the redo record. Not only that, but there are unconditional and conditional log groups. here is an example of an unconditional log group:

ALTER TABLE HR.EMPLOYEES
   ADD SUPPLEMENTAL LOG GROUP emp_parttime (EMPLOYEE_ID, LAST_NAME, 
   DEPARTMENT_ID) ALWAYS;

This will add EMPLOYEE_ID, LAST_NAME, and DEPARTMENT_ID to the redo record *even* if none of those columns were modified - this is a result of the ALWAYS parameter. In contrast, here is an example of a conditional user-defined supplemental log group:

ALTER TABLE HR.EMPLOYEES
   ADD SUPPLEMENTAL LOG GROUP emp_fulltime (EMPLOYEE_ID, LAST_NAME, 
   DEPARTMENT_ID);

In this case, only when one of the three listed columns is updated will the before image of those columns be logged...and finally, here is how you "associate this column with other columns in the named supplemental log group such that any modification to the NO LOG column causes the other columns in the supplemental log group to be placed in the redo log file. This might be useful, for example, if you want to log certain columns in a group if a LONG column changes. You cannot supplementally log the LONG column itself; however, you can use changes to that column to trigger supplemental logging of other columns in the same row"

ALTER TABLE HR.EMPLOYEES
   ADD SUPPLEMENTAL LOG GROUP emp_parttime(
   DEPARTMENT_ID NO LOG, EMPLOYEE_ID);

Note the use of the "NO LOG" clause. This means that the DEPARTMENT_ID column will not be logged, but if it changes, be sure to log the EMPLOYEE_ID.

For more detail on Supplemental Logging, see the Oracle Docs Here.