Versions Compared

Old Version 14

changes.mady.by.user Arjen Visser

Saved on

compared with

New Version Current

changes.mady.by.user Chris Lawless (Deactivated)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

 Supplemental logging is not the default behaviour of the oracle database. It has to be enabled manually after the database is created. It can be enabled at two levels

  1.  DATABASE LEVEL
  2. TABLE LEVEL

...

The supplemental log groups created by Dbvisit Replicate start with a G and then a random hex string: G575B4B0CC2E511E2A20CB8ADB3813

Note
  • For Oracle 10g, 11g and 12c if there is no primary key defined on the table, then Oracle automatically adds additional columns to the supplemental log data to satisfy either the unique key, or if there is no unique key, then it adds all the columns in the table.
  • For Oracle 9i, Oracle does not automatically adds additional columns. The specific supplemental log level has to be defined. Dbvisit Replicate does this automatically.

...

No Format
bgColorCCC
SQL> SELECT supplemental_log_data_min MIN, 
supplemental_log_data_pk PK, 
supplemental_log_data_ui UI, 
supplemental_log_data_fk FK,
supplemental_log_data_all "ALL" 
FROM v$database;

...

Modifying supplemental log behaviour
Please see variable _ADD_SUPLOG in Internal Variables on the default supplemental log behaviour and how to modify this. 
How Dbvisit Replicate works with supplemental logging
Dbvisit Replicate does not use primary key definition directly. It uses all the columns that are supplementally logged by Oracle.
When a table is prepared, Dbvisit Replicate issues: alter table xx.xx add supplemental logging (primary key) columns.
This enables the following Oracle behaviour:
  1. If there is an enabled PK, it will add supplemental logging to only the columns of the PK
  2. If there is no PK, but there is unique key that is guaranteed to be unique (none of the columns are NULL) - use columns of this UK
  3. As last resort, log all columns (except those that can never be logged - LOBs, LONGs, nested tables etc.)
This means that as long as there is a way on how to uniquely identify rows, replication will work as expected. Oracle will supplementally log all the columns needed. 
In certain cases such as filtering, you must ensure that that column is always present in the redo logs.
Here is an example of a conditional user-defined supplemental log group for jobcode and sdate columns where ID is primary key. 
 No Format
bgColorCCC
ALTER TABLE AVI.SAMPLE
ADD SUPPLEMENTAL LOG GROUP sample_jobcode (ID,JOBCODE,SDATE) ALWAYS;
The ALWAYS keyword is required to create an unconditional supplemental log group to ensure the column(s) in the filter condition are always logged in the redo log, regardless of whether they appear in the DML statement.