Qlik Replicate: Defining Supplemental Logging and its importance in Data Replication

[SushilKumar]

In this article, we cover Supplemental Logging and its importance in Data Replication via any tool which uses redo or archive log to capture the CDC changes and propagate them to the Target database (Endpoint).

What & Why Supplemental Logging?

Redo log files are generally used for instance recovery and media recovery. The data needed for such operations is automatically recorded in the redo log files. However, a redo-based application or replication tool may require few additional columns to be logged in the redo log to maintain the database Acid properties. The process of logging these additional columns or info is called supplemental logging.

Supplemental logging as the name suggests is extra logging, required to uniquely identify a row on Target Database when a row is updated/deleted from Source Database. This additional information allows rows to be located when the ROWID is unavailable. Also, these additional columns help the replication tools to identify the rows which need to be updated on the on the destination side.

By default, Oracle Database does not provide any supplemental logging, which means that it is not enabled by default. Therefore, you must enable at least minimal supplemental logging before generating log files which will be analyzed or mined by the configured replication tool.

The following are examples of situations in which additional columns may be needed by the replication tool.

• An application that applies reconstructed SQL statements to a different database must identify the update statement by a set of columns that uniquely identify the row (for example, a primary key), not by the ROWID because the ROWID of one database will be different and therefore meaningless in another database.
• An application may require that the before-image of the whole row be logged, not just the modified columns, so that tracking of row changes will be more efficient.

A supplemental log group is the set of additional columns to be logged when supplemental logging is enabled. There are two types of supplemental log groups that determine when columns in the log group are logged:

• Unconditional supplemental log groups: The before-images (mostly require in term of update and Delete) of specified columns are logged any time a row is updated, regardless of whether the update affected any of the specified columns. This is sometimes referred to as an ALWAYS log group.
• 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.

Supplemental log groups can be system-generated or user-defined.

In addition to the two types of supplemental logging, there are two levels of supplemental logging, as described in the following sections:

• Database-Level Supplemental Logging
• Table-Level Supplemental Logging

Database-Level Supplemental Logging

There are two types of database-level supplemental logging:

1. minimal supplemental logging
2. Database-Level identification key logging,

Minimal supplemental logging does not impose significant overhead on the database generating the redo log files. Normally we use this when using Replication Via Golden gate or using Dataguard.

enabling database-wide identification key logging can impose overhead on the database generating the redo log files. Oracle recommends that you at least enable minimal supplemental logging.

Minimal Supplemental Logging

Minimal supplemental logging logs the minimal amount of information needed for LogMiner to identify, group, and merge the redo operations associated with DML changes. It ensures that LogMiner (and any product building on LogMiner technology) has sufficient information to support chained rows and various storage arrangements, such as cluster tables and index-organized tables. To enable minimal supplemental logging, execute the following SQL statement:

• LogMiner/or Log mining is a term or internal Functionality where any replication tool process mine the LCR(Logical Change records Stored in Log buffer Cache inside redo entries)  to extract the Sql Statement

Database-Level Identification Key Logging

Identification key logging is necessary when redo log files will not be mined at the source database instance, for example, when the redo log files will be mined at a logical standby database when using oracle DR solution.

Using database identification key logging, you can enable database-wide before-image logging for all updates by specifying one or more of the following options.

SQL> sqlplus / as sysdba

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

This option specifies that whenever a row is updated, all columns of that row (except for LOBs, LONGS, and ADTs) are placed in the redo log file.

PRIMARY KEY system-generated unconditional supplemental log group

This option causes the database to place all columns of a row's primary key in the redo log file whenever a row containing a primary key is updated (even if no value in the primary key has changed).

If a table does not have a primary key but has one or more non-null unique index key constraints or index keys, then one of the unique index keys is chosen for logging as a means of uniquely identifying the row being updated.

If the table has neither a primary key nor a non-null unique index key, then all columns except LONG and LOB are supplementally logged; this is equivalent to specifying ALL supplemental logging for that row. Therefore, Oracle recommends that when you use database-level primary key supplemental logging, all or most tables be defined to have primary or unique index keys.

UNIQUE system-generated conditional supplemental log group

This option causes the database to place all columns of a row's composite unique key or bitmap index in the redo log file if any column belonging to the composite unique key or bitmap index is modified. The unique key can be due to either a unique constraint or a unique index.

FOREIGN KEY system-generated conditional supplemental log group

This option causes the 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.

Keep the following in mind when you use identification key logging:

• When you enable identification key logging at the database level, minimal supplemental logging is enabled implicitly.
• Supplemental logging statements are cumulative. If you issue the following SQL statements, then both primary key and unique key supplemental logging is enabled:

Table-Level Identification Key Logging

Identification key logging at the table level offers the same options as those provided at the database level: all, primary key, foreign key, and unique key. However, when you specify identification key logging at the table level, only the specified table is affected. For example, if you enter the following SQL statement (specifying database-level supplemental logging), then 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:

Use Case: (database-level supplemental logging) This is done when we want to migrate or process most of Application table as part of replication to another database.

Use Case: (table-level supplemental logging) This is done when we want to migrate or process less number or small Subset of Application table as a part of replication to another database.

The Main Difference between logging at DB and Table is that if you (specifying table-level supplemental logging) then only when a column in the participating table is changed then the entire row (except for LOB, LONGs, and ADTs) of the table be placed in the redo log file. If a column changes in the participating table, then only the changed column will be placed in the redo log file.

Check the Supplemental logging enabled at Database level

SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;

SUPPLEME SUP SUP

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

NO       NO  NO

Enabling Supplemental Logging at Database Level   *  if you enter the following SQL statement (specifying database-level supplemental logging), then 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:

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

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

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

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

Disable the Supplemental Logging at Database Level

Note: To disable the supplemental logging at database level, run the following sequence order:

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

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

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Enabling Supplemental Logging at table Level  *  With the following SQL statement, only when a column in the participating table  is changed then the entire row (except for LOB, LONGs, and ADTs) of the table is placed in the redo log file. If a column changes in the participating table, then only the changed column will be placed in the redo log file.

SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

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

 Drop Supplemental Logging at table Level  *   

SQL> ALTER TABLE HR.EMPLOYEES  DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

SQL> ALTER TABLE HR.EMPLOYEES  DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

SQL> ALTER TABLE HR.EMPLOYEES  DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

SQL> ALTER TABLE HR.EMPLOYEES  DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Oracle supports user-defined supplemental log groups also. With user-defined supplemental log groups, you can specify which columns are supplementally logged.

We can specify conditional or unconditional log group

What is conditional Group

The before image of all the columns are logged into the redo log file even if at least one of the columns in the supplemental log group is updated.

What is unconditional log groups or Always log Group

The before image of the column to be logged in to the redo log file even if there is no changes happen that column and which have supplemental logging enabled

Unconditional log group

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

Conditional log group

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

Drop user-defined supplemental log groups

SQL> ALTER TABLE HR.EMPLOYEES  DROP SUPPLEMENTAL LOG GROUP emp_fulltime;

Check the Supplemental logging for Table

SQL> select count(*) from ALL_LOG_GROUPS where LOG_GROUP_TYPE='ALL COLUMN LOGGING' and OWNER= 'HR' and TABLE_NAME='EMPLOYEES';

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.

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

where OWNER='<table owner>' and

TABLE_NAME='<table name>';

We can find which columns are part of the Supplemental Log group with the query

select LOG_GROUP_NAME, COLUMN_NAME, POSITION from

dba_log_group_columns

where OWNER='<table owner>' and

TABLE_NAME='<table name> '

order by position;

How Qlik Replicate handles or implements Supplemental Logging

If Supplemental logging is enabled at database level then you find below info message in Task Log.

[SOURCE_CAPTURE] I:  SUPPLEMENTAL_LOG_DATA_ALL is set (oracle_endpoint_conn.c:138)

FOR table level.

Qlik replicate provide below option to enable supplemental logging at table level. By enable the Check box

Whenever a new table is added to the configured task Qlik replicate execute below command

If table have Primary key defined then below command will be executed if no primary key is defined then you may see Warning in task log. It does not pose any issue to configured task however you mey exepreince perfomrance issue for the task.  

ALTER TABLE TABLE.NAME ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS.

if Above option is not selected then DBA team need to Manage Supplimental logging for participating table as per their business Requirment and below info message will be in the task logs.

[METADATA_MANAGE] I:  Supplemental logging automatic definition is disabled (oracle_endpoint_imp.c:871)