Database level supplemental logging is an Oracle requirement that ensures that the Oracle redo log on the source database contains the information required to describe all data changes completely. Enabling supplemental logging is a requirement for all redo log mining replication solutions and not specific to Dbvisit Replicate.
Turning on supplemental logging ensures additional information is inserted into redo stream in order to facilitate replication. The extra level of overhead of adding this information into the redo log is generally less than 1%.
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
- DATABASE LEVEL
- TABLE LEVEL
Supplemental logging is also required in order to ensure the following items are replicated succesfullyreplicated successfully:
- chained rows
- clustered tables
- index organized tables (IOTs)
...
| Note |
|---|
Dbvisit Replicate automatically turns on supplemental logging and sets the minimal logging level based on the replication requirement. No manual supplemental logging steps are required. |
| Note |
|---|
Supplemental logging is only required for the source database. |
The following SQL is run by Dbvisit Replicate to turn on supplemental logging:
| Sectionnoformat | |||||
|---|---|---|---|---|---|
| Column | ||
|---|---|---|
| ||
|
| width | 99% |
|---|
| bgColor | CCC |
|---|
SQL> alter database add supplemental log data;
If
...
DDL
...
is
...
enabled,
...
then
...
the
...
following
...
is
...
run
...
| Sectionnoformat | |||||
|---|---|---|---|---|---|
| Column | ||
|---|---|---|
| ||
|
| width | 99% |
|---|
| bgColor | CCC |
|---|
SQL> alter database add supplemental log data (primary key) columns;
For
...
each
...
table
...
to
...
be
...
replicated
...
the
...
following
...
SQL
...
is
...
run
| Sectionnoformat | ||
|---|---|---|
| ||
| ||
| ||
| Column | ||
|
| |
SQL> alter table '||owner||'.'||table_name||' add supplemental log data (primary key) columns; |
To check and monitor what supplemental logging has been turned on, the following query can be run:| Section | |||||||
|---|---|---|---|---|---|---|---|
Column | | width | 99%
The supplemental log groups created by Dbvisit Replicate start with a G and then a random hex string: G575B4B0CC2E511E2A20CB8ADB3813
| Note |
|---|
|
| Info |
|---|
In Oracle 9i, there is a limit of 33 columns in each supplemental log group. See support.oracle.com note ID 466439.1 So in the case of a table with more than 33 columns you create as many distinct supplemental log groups as required for the same table, e.g., ALTER TABLE <table_name> ADD SUPPLEMENTAL LOG GROUP <log_grp1> |
Verify supplemental logging
To check and monitor what supplemental logging has been turned on, the following query can be run
| No Format | ||
|---|---|---|
| ||
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" FROMv$databasel 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
- If there is an enabled PK, it will add supplemental logging to only the columns of the PK
- 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
- As last resort, log all columns (except those that can never be logged - LOBs, LONGs, nested tables etc.)
Here is an example of a conditional user-defined supplemental log group for jobcode and sdate columns where ID is primary key.
| No Format | ||
|---|---|---|
| ||
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.