GoldenGate Parameter Files – Format and Logic

As we have done multiple engagements with Oracle GoldenGate and helped clients get the most out of their investment; we have realized that 90% of the parameter files we deal will are not structured in a way that makes sense.  Traditionally, Oracle GoldenGate parameter files are text files that are read and loaded into memory for execution.  The order in which these files are read have an impact on how Oracle GoldenGate will operate.  In this post, we will highlight the basics of how a parameter file should be structured for readability as well as operational practicality.

With newer versions of Oracle GoldenGate, there are two architectures – Classic and Microservices.  Depending on the architecture you are using will determine the number of parameter files you have to review.  For the purpose of this post, we will look at parameter files that are associated with the Microservices architecture; meaning only looking at the extract and replicat parameter files.

Parameter File Formats

Every parameter file starts off with the basic first line of either Extract or Replicat followed by the name of the process.  After that many clients randomly throw parameters throughout the file, leading to either missing data or unexpected errors.  As basic format of the parameter file, a skeleton key is below as an example.  After reviewing this structure, we’ll show you some examples of do’s and don’ts.

[EXTRACT || REPLICAT] <process_name>
[MACRO SETTINGS]
[LOGIN SETTINGS]
[MEMORY MANAGEMENT]
[ENVIRONMENT SETTINGS]
[REPORTING]
[DDL]
[DATABASE OPTIONS]
[TRANSACTION LOG OPTIONS]
[MISC.]
[TABLE || MAP]

The above skeleton key is designed to provide a guidance as to how the parameter file should be laid out.   Not all categories will be used in all parameter file; please use it as a guide to write better parameter files.

Capture Process

With in Oracle GoldenGate the capture process is also know as the Extract.  The extract comes in two vesions – Non-Integrated and Integrated.  In both cases, a single parameter file is used.  At the same time, many clients get the formatting of a parameter file crossed up. In this example, everything is set at random in the file.  There is no logic or reason for where items are placed.  The end goal is to have a working extract.  At the same time, if you take the stand point of the parameter file is read from top-down, then items are being set before they need to be.  Although this parameter file works, it is messy and difficult to follow what is going on.

Note: Values of the parameter file has been modified to protect the client

Extract Bad Format:

EXTRACT <extract_name>
DBOPTIONS ALLOWUNUSEDCOLUMN
DBOPTIONS LOBBUFSIZE 2097152
USERID ggate@<connect_string>, PASSWORD <password>
SETENV (ORACLE_HOME=“$ORACLE_HOME")
SETENV (ORACLE_SID=“$ORACLE_SID")
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TRANLOGOPTIONS MININGUSER ggate@<mining_server>, MININGPASSWORD <password>
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 2048,parallelism 4, downstream_real_time_mine Y)
TRANLOGOPTIONS BUFSIZE 4096000
TRANLOGOPTIONS EXCLUDEUSERID 9
CACHEMGR CACHESIZE 16GB, CACHEDIRECTORY /gg/dirtmp 300GB
RMTHOST <host_name>, MGRPORT 7809
WARNLONGTRANS 2h, CHECKINTERVAL 10m
TABLEEXCLUDE MIP.TPC_EN*
TABLEEXCLUDE MIP.SIB*
TABLE MIP.*;

When we run this through the skeleton key format that is provided above it becomes a lot easier to read and work with.

Extract Skeleton Format:

EXTRACT <extract_name>
USERID ggate@<connect_string>, PASSWORD <password>
--CACHEMGR CACHESIZE 16GB, CACHEDIRECTORY /gg/dirtmp 300GB
SETENV (ORACLE_HOME=“$ORACLE_HOME")
SETENV (ORACLE_SID=“$ORACLE_SID")
WARNLONGTRANS 2h, CHECKINTERVAL 10m
DBOPTIONS ALLOWUNUSEDCOLUMN
DBOPTIONS LOBBUFSIZE 2097152
TRANLOGOPTIONS MININGUSER ggate@<mining_server>, MININGPASSWORD <password>
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 2048,parallelism 4, downstream_real_time_mine Y)
TRANLOGOPTIONS BUFSIZE 4096000
TRANLOGOPTIONS EXCLUDEUSERID 9
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLEEXCLUDE MIP.TPC_EN*
TABLEEXCLUDE MIP.SIB*
TABLE MIP.*;

After rewriting the extract parameter file, you can quickly see items that should be removed or adjusted.  Example of this is the setting for CACHEMGR.  Although this parameter is correct from a syntax point-of-view, this setting is turned on by default with newer versions of Oracle GoldenGate.  In the skeleton format above, notice that it is proceeded with a double dash.  This means that the parameter has been commented out of parameter file and will not be ran upon starting up.  After that you can quickly scan other parameters as needed to define what is going to happen.

This same formatting can be used in other parameters files as well, especially the Apply process (i.e. Replicat).  By using this organized flow to a parameter file, the Oracle GoldenGate Administrator or the DBA can quickly identify what is happening in the file or if anything has changed.  The added bonus of this format is it can be used in all versions of Oracle GoldenGate – on-premises and cloud.

If you need help with your Oracle GoldenGate implementation, feel free to drop us a line at [email protected]