Bookmark and Share
Supplemental Logging in a Streams Environment
(Publish Date: 2008-8-28 5:50pm, Total Visits: 2058, Today: 1, This Week: 3, This Month: 3)

Supplemental logging places additional column data into a redo log whenever an UPDATE operation is performed. Such updates include piecewise updates to LOBs. The capture process captures this additional information and places it in LCRs.

Supplemental logging places additional column data into a redo log whenever an UPDATE operation is performed. Such updates include piecewise updates to LOBs. The capture process captures this additional information and places it in LCRs.

There are two types of supplemental logging: database supplemental logging and table supplemental logging. Database supplemental logging specifies supplemental logging for an entire database, while table supplemental logging enables you to specify log groups for supplemental logging for a particular table. If you use table supplemental logging, then you can choose between unconditional and conditional log groups.

Unconditional log groups log the before images of specified columns any time the table is updated, regardless of whether the update affected any of the specified columns. This is sometimes referred to as an ALWAYS log group. Conditional log groups log the before images of all specified columns only if at least one of the columns in the log group is updated.

Supplementing logging at the database level, unconditional log groups at the table level, and conditional log groups at the table level together determine which old values are logged in an update statement or piecewise LOB update.

===Turn on the supplemental log on database level===

SQL>ALTER DATABASE ADD SUPPLEMENT LOG DATA;

===Enable table level supplemental logging===


The following adds columns to the conditional supplemental log group for the emp and dept tables:

alter table dept add supplemental log group loggrp_dept (deptno,dname,site);
alter table emp add supplemental log group loggrp_emp (empno,ename,site,job,deptno);

SQL> select log_group_name, table_name
  2  from dba_log_groups
  3  where owner='APPS';

LOG_GROUP_NAME                 TABLE_NAME
------------------------------ ------------------------------
LOGGRP_EMP                     EMP
LOGGRP_DEPT                    DEPT