This post covers about Supplemental Logging and its importance in Oracle GoldenGate. If you are new to Oracle GoldenGate then I highly recommend you to look at Oracle GoldenGate 12c Overview & Components and Oracle GoldenGate 12c Download & Installation
If you wish to learn GoldenGate systematically then look at Activity Guides (tasks) you must perform from our Step by Step Guide to Learn Oracle GoldenGate or if you are already working/using Oracle GoldenGate then look at Oracle GoldenGate 12c Troubleshooting using Logdump Utility
What & Why Supplemental Logging?
As many trainees in our GoldenGate Training asking What is Supplemental Logging and Why is it required for GoldenGate so we thought to blog it too (apart from covering dedicated lesson and activity guide on this topic).
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. When a row is updated/deleted from the source, GoldenGate Replicat Process searches for the same row on a target based on column supplementally logged before the transaction could be applied.
Supplemental Logging Levels
There is two levels of supplemental logging :
1. Database level
2. Table level
Why is Supplemental Logging Required?
Supplemental logging is required by each Change Data Capture (CDC) tool. GoldenGate being CDC tool hence it also requires supplemental logging to be enabled so that rows updated on Source can be uniquely identified at Target and updated.
One of the reasons to enable supplemental logging is to generate changelogs for the update and delete statements. Supplemental logging makes sure that enough information is captured during these updates and deletes that can be used by any method based on log miner technology. Since GoldenGate is a SQL Apply hence once a row is updated or deleted from the source, GoldenGate Replicat Process searches for the same row on target on which update/delete needs to be performed.
A minimum level of supplemental logging at the database level is required for GoldenGate and can be achieved by issuing below command:
1. Enabling Supplemental Logging at DB Level:
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
OR
SQL>alter database add supplemental log data (all) columns;
To ensure the same has been enabled query V$Database view.
By default, only changed column is logged into transaction logs for any operations. Normally, this means that primary key columns are not logged during an update/delete operation. However, Replicat requires the extra column(primary key/Unique Key columns) in order to apply the update/delete on the target system. The ADD TRANDATA command in GGSCI is used to cause Oracle to log extra column in logs for all transactions on a table.
For enabling Table level Supplemental logging, Database level supplemental logging is required. Consider DB level supplemental logging as a gateway to enable supplemental logging at the table level.
2. Enabling Supplemental Logging at Table Level:
GGSCI>dblogin userid c##gguser@pdb1,password *****
Note: For enabling supplemental logging at table level dblogin to the pluggable database is required if database architecture is Multitenant.
GGSCI>ADD TRANDATA pdb1.ggtraining1.dept1
The above command at GGSCI level is equivalent at the database level as below depending on supplemental logging has to be enabled on primary key/Unique key for all columns
SQL> ALTER TABLE “ggtraining1”.”DEPT1″ ADD SUPPLEMENTAL LOG DATA(PK|UK|ALL) columns;
ADD TRANDATA automatically adds supplemental logging for the table based on the table’s primary key or if that’s missing, using any unique key constraints defined for that table.
Below is the order of columns in which Supplemental logging is enabled at table level:
- Primary Key
- Unique Key
- All columns
Once supplemental logging is enabled, along with a column which has been updated/changed an extra column will be logged in to redo logs so that a row could be uniquely identified. Here it could be either of the one mentioned as above.
If you enable only table-level supplemental logging without database level then Oracle will not capture all changes.
What If Table doesn’t have Primary Key/Unique Key?
If the table does not have either a primary key or a unique key constraint defined, then ADD TRANDATA will automatically add supplemental logging based on sequence mentioned above. Hence in absence of both Supplemental logging will be enabled on all columns of a table.
Please note it will increase the size of the redo logs and the trail files as well. More importantly, if you are applying a change on the target database using all the columns as the key then the DML ( be it an update or a delete operation) will not be efficient and will cause your REPLICAT to lag behind.
You might need to sit with the application team to find out what key could be defined for that table.
You can enable supplemental logging based on columns defined by Application team on a particular object: You can use the following syntax to ensure only those columns are used to add supplemental logging.
ADD TRANDATA <table name>, COLS (list of column names), NOKEY
Please note the usage of “NOKEY” usually if you don’t use “NOKEY” and you try using only “COLS” then you will get an error saying that the columns you define in “COLS” are already used because by default ADD TRANDATA adds supplemental logging for all the columns if there is no primary key defined as stated earlier.
Also, ensure the target database has indexes defined on the columns in question.
Reference:
This post is from our Oracle GoldenGate 12c Administration Training, in which we cover Architecture, Installation, Configuring & Preparing the Environment, DML Replication – Online Change Synchronization, Initial Load, Zero Downtime Migration & Upgrading using GoldenGate, Oracle GoldenGate Security, Performance of Oracle GoldenGate and Troubleshooting and much more.
