Supplemental logging when using Oracle Golden Gate with Oracle EBS 12.2.3

          Supplemental logging when using Oracle Golden Gate with Oracle EBS 12.2.3

Source Oracle EBS : 11.5.10.2

Target Oracle EBS : 12.2.3

Database version: 11.2.0.4

Tables: Custom Stage Tables

Oracle Golden gate : 12.1.2.0.1

Problem Statement:

We need to set up Oracle EBS database as a Golden Gate publisher. In order to do so, Supplemental Logging enablement at the DB level is required.

Evaluating concerns around performance impacts that we might see if we enable supplemental logging.

Golden Gate enablement was required in order to support data migration from Oracle EBS 11.5.10.2 on one data center to Oracle EBS 12.2.3 applications on a different secure data center.

We had to enable supplemental logging only on 12 Custom Staging tables, from where the Oracle Golden gate will perform the data migration. This data is migrated to Oracle EBS 12.2.3 database having 11.2.0.4 database version.

Evaluation details:

Few things to be considered in terms of enabling supplemental logging when using Oracle Golden Gate:

1. Enabling supplemental logging to source database will increase the size of the redo logs. This might increase more, depending on the amount of updates done on source database. Tuning the source database by configuring appropriate number of redo groups and redo size can help.

2. There are options to enable only required level of supplemental logging on the database while using golden gate. Enabling only primary key/Unique key might help and if it’s not present on tables where supplemental logging is required, then we can follow Oracle document for alternative:

How to Handle Tables Without Primary Keys or Unique Indexes With Oracle GoldenGate (Doc ID 1271578.1)

However without a key on a table with multiple columns can definitely increase the redo log generation. 

3. We also might need to understand how the EBS application connections will be handled while the supplemental logging will be enabled. If any of the application process is doing any updates to the database table, then there are good chance that it might create locks and logging can be enabled only after the transaction is completed.

TESTING and Results:

I did some tests for 1 EBS process that uses a lot of updates to see the impact of enabling the minimum supplemental logging at database level.

I executed the job Receipt Accruals - Period-End. The job has a loop of 2M records and it updates a lot of other records inside the loop.

The job normally takes more than 10h to complete and below are the statistics for the execution with Supplemental Logging and without it.

Mode: Without supplemental Logging

Time to complete: 626 minutes

Size of archive logs: 24.48GB

Number of archive logs: 36

Mode: With supplemental Logging (ALTER DATABASE ADD SUPPLEMENTAL LOG DATA)

Time to complete: 643 minutes

Size of archive logs: 25.62

Number of archive logs: 37

So as we can see the minimum supplemental logging has a very little impact in the performance and in the log generation.

In this test, the job took 2.7% more to complete and the volume of archive increase 4.65% and this is one of the worst case scenarios (the job does a lot of updates and supplemental logging does not affect insert and delete statements).