Main Note - Oracle GoldenGate - Supplemental Logging

The query below will tell you if Supplemental Logging is turned on at the database level:




select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, 
SUPPLEMENTAL_LOG_DATA_UI, FORCE_LOGGING from v$database;
 


For a particular table, you can find if a Supplemental Log group has been created for a particular table with the query below. If it returns a row, Supplemental Logging is turned on. If it returns "no rows selected", then Supplemental Logging is not turned on.




select * from dba_log_groups
where OWNER='<schema_name_in_upper_case>' and 
TABLE_NAME='<table_name_in_upper_case>';
 


For a particular table, you can find which columns are part of the Supplemental Log group with the query below:




select LOG_GROUP_NAME, COLUMN_NAME, POSITION from 
dba_log_group_columns
where OWNER='<schema_name_in_upper_case>' and 
TABLE_NAME='<table_name_in_upper_case>'
order by position;
 


For a particular table, you can find out if Supplemental Logging is turned on through GGSCI with the commands below:




GGSCI> dblogin userid <user>, password <pw>
GGSCI> info trandata <schema>.<table>




Question
Can I turn on the Oracle Supplemental Log at the DB Level only, without doing it at the Table Level?




Answer
GoldenGate requires adding Supplemental Logging at the Table Level, regardless of the Database Supplemental setting, due to issues with Multiple Unique Keys and/or lack of Keys.


It is highly recommended to use "ADD TRANDATA" under the GGSCI interface.  If using the SQL command to add Supplemental Logging at the Table Level, ALL the keys should be included (ex.  2 separate Unique Keys are all required).


Question
Why does GoldenGate for Oracle require Supplemental Logging at the Database Level for Capture?


Answer
GoldenGate version 7.3.1 for Oracle 9i and above has enhanced the Log Based Capture. In the process, it became necessary to enable supplemental logging at the database level. By enabling this feature, Oracle adds in a few extra bytes of information into the redo logs for some DML operations.


How To Check The Supplemental Log Information In Oracle


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.


To check whether one or more log groups are specified for the table at the source database, run the following query:


COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20


COLUMN TABLE_NAME HEADING 'Table' FORMAT A20


COLUMN ALWAYS HEADING 'Type of Log Group' FORMAT A30


SELECT


LOG_GROUP_NAME,


TABLE_NAME,


DECODE(ALWAYS,


'ALWAYS', 'Unconditional',


NULL, 'Conditional') ALWAYS


FROM DBA_LOG_GROUPS;




Your output looks similar to the following:
Log Group Table Type of Log Group


-------------------- -------------------- ------------------------------


LOG_GROUP_DEP_PK DEPARTMENTS Unconditional


LOG_GROUP_JOBS_CR JOBS Conditional


To list the columns in a particular log group, query the DBA_LOG_GROUP_COLUMNS data dictionary view. You can also query the V$DATABASE dynamic performance view to display supplemental logging specified at the database level.


The GoldenGate ADD TRANDATA command is equivelant to the following SQL statement,


alter table [TABLE_NAME] add supplemental log group [GROUP_NAME] ([COLUMN_LIST]) always;


The Group_name will usually be GGS_TABLENAME, the COLUMN_LIST is either the primary key columns or unique index columns, or if there is no primary key or unique index, it will be all columns.


The GoldenGate DELETE TRANDATA command is equivelant to the following SQL statement


alter table [TABLE_NAME] drop supplemental log group [GROUP_NAME]


To check the database level supplemental logging info


select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, force_logging from v$database;


GoldenGate needs at least "supplemental_log_data_min" to be YES to Extract chained row correctly from the Oracle redo log. See attached doc for the detail explain of what other options are.


Example:
SQL> SELECT LOG_GROUP_NAME,TABLE_NAME,DECODE(ALWAYS,'ALWAYS', 'Unconditional',NULL, 'Conditional') ALWAYS FROM DBA_LOG_GROUPS;


LOG_GROUP_NAME TABLE_NAME ALWAYS
------------------------------ ------------------------------ -------------
ENC$_LOG_GRP ENC$ Unconditional
GGS_TCUSTMER_74729 TCUSTMER Unconditional


SQL> select * from dba_log_group_columns where table_name='TCUSTMER';


OWNER LOG_GROUP_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
POSITION LOGGIN
---------- ------
SYSTEM GGS_TCUSTMER_74729
TCUSTMER
CUST_CODE
1 LOG
SQL>