Oracle Supplemental Logging

Supplemental Logging enhancements are aimed at improving streams and other data sharing facilities.(e.g Logminer) .Includes additional information in redo stream

A-Database Supplemental Logging

Minimal supplemental logging can be enabled using:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Minimal supplemental logging can be disabled using:
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;


Database supplement logging can be enabled :

1-For all columns :This option specifies that when a row is updated, all the columns of that row (except for columns of type LOB, LONG, LONG RAW, and user-defined types) are placed in the redo log file.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

All columns are included with the exception of:LONG,LOB,LONG RAW,Abstract Data Types,Collections

2-For primary key columns
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

3-For unique columns:This option causes the Oracle database to place all columns of a row's foreign key in the redo log file, if any column belonging to the foreign key is modified

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

4-For foreign key columns
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;


When you enable identification key logging at the database level, minimal supplemental logging is enabled implicitly.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


Database supplement logging can be disabled
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

To monitor database level supplemental logging:
SELECT
supplemental_log_data_min,
supplemental_log_data_pk,
supplemental_log_data_ui,
supplemental_log_data_fk,
supplemental_log_data_all
FROM v$database;


B-Log Groups:Table supplemental logging specifies, at the table level, which columns are to be supplementally logged. You can use identification key logging or user-defined conditional and unconditional supplemental log groups to log supplemental information.


Implemented as constraints.If no name specified for log group then system constraint name will be allocated e.g SYS_C005223
Log Groups can be

1-Unconditional Supplemental Log Groups : The before-images of specified columns are logged any time a row is updated, regardless of whether the update affected any of the specified columns. This can be referred to as an ALWAYS log group

To specify an unconditional supplemental log group for primary key column(s):
ALTER TABLE t1 ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
To specify an unconditional supplemental log group that includes all table columns:
ALTER TABLE t1 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
To specify an unconditional supplemental log group that includes selected columns:
ALTER TABLE t1 ADD SUPPLEMENTAL LOG GROUP t1_g1 (c1,c3) ALWAYS;




2-Conditional Supplemental Log Groups - The before-images of all specified columns are logged only if at least one of the columns in the log group is updated

To specify a conditional supplemental log group for unique key column(s) and/or bitmap index column(s):
ALTER TABLE t1 ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
To specify a conditional supplemental log group that includes all foreign key columns:
ALTER TABLE t1 ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
To specify a conditional supplemental log group that includes selected columns:
ALTER TABLE t1 ADD SUPPLEMENTAL LOG GROUP t1_g1 (c1,c3);



In Oracle 10.2, minimal supplemental logging must be enabled at database level before supplemental logging can be enabled at table level




To drop a supplemental log group:
ALTER TABLE t1 DROP SUPPLEMENTAL LOG GROUP t1_g1;
To drop supplemental logging of data use:
ALTER TABLE t1 DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE t1 DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE t1 DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER TABLE t1 DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Supplemental Logging views: DBA_LOG_GROUPS,DBA_LOG_GROUP_COLUMNS