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).
