Best Practices from Oracle Development's A‑Team

Oracle GoldenGate: Parameter Substitution


Oracle GoldenGate (OGG) provides a mechanism for assigning values to parameters at run time. In this article we shall discuss this feature and show you how to reference operating system variables from within an OGG parameter file.

Main Article

Typically when we create OGG Groups we edit a parameter file and define configuration options. This requires that we edit the parameter file whenever a change occurs that affects the operating environment. Instead of setting static options in the parameter file, we can use parameter substitution to assign values automatically when the Group is started. This allows us to have a single set of parameter files in what may be a fluid operating environment.

Consider a typical Extract Data Pump parameter file:

extract p_ieast rmthost oelora114, mgrport 15051 rmttrail ./dirdat/pe passthru table east.*;

In this file we typically "hard code" the target host name, OGG Manager Listener Port, and Remote Trail location. But what if I want to set up change control for OGG parameter files with the requirement that no modifications occur as the file is migrated through development, quality assurance, and production release environments? This is a case where parameter substitution maybe of help.

To implement parameter substitution, lets change our Extract Data Pump parameter file as follows:

extract p_ieast rmthost ?OGGTarget, mgrport ?OGGListener rmttrail ?TrailPIEAST passthru table ?PIEASTList;

The ? specifies that when the GGSCI start command is executed for this Group, the variables OGGTarget, OGGListener, TrailPIEAST, and PIEASTList will be obtained from the operating system environment and those values will be used as parameter options. If the OS variables do not exist, the Group will ABEND.

In my OS environment, I have the variables defined as:

[lpenton@oelora114 ~]$ echo $OGGTarget $OGGListener $TrailPIEAST $PIEASTList oelora114 15051 ./dirdat/pe east.*

To enable substitution, I then start GGSCI. If the OGG Manager is running, I must restart the process so the environment variables can be passed to the Extract Data Pump when its process is being spawned (Manager spawns all OGG processes).

GGSCI (oelora114) 3> stop mgr ! Sending STOP request to MANAGER ... Request processed. Manager stopped. GGSCI (oelora114) 4> start mgr Manager started. GGSCI (oelora114) 5> start p_ieast Sending START request to MANAGER ... EXTRACT P_IEAST starting GGSCI (oelora114) 6> status p_ieast EXTRACT P_IEAST: RUNNING

When we view the Group report file, we see the substitution elements:

GGSCI (oelora114) 7> view report p_ieast ***********************************************************************                  Oracle GoldenGate Capture for Oracle  Version 17185003 OGGCORE_12.    Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:44:16 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.                     Starting at 2014-04-08 10:00:41 *********************************************************************** Operating System Version: Linux Version #1 SMP Wed Jul 17 10:58:36 PDT 2013, Release 2.6.32-400.29.2.el5uek Node: oelora114 Machine: x86_64                          soft limit   hard limit Address Space Size   :    unlimited    unlimited Heap Size            :    unlimited    unlimited File Size            :    unlimited    unlimited CPU Time             :    unlimited    unlimited Process id: 11136 Description: *********************************************************************** **            Running with the following parameters                  ** *********************************************************************** 2014-04-08 10:00:41  INFO    OGG-03059  Operating system character set identified as UTF-8. 2014-04-08 10:00:41  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing. extract p_ieast rmthost oelora114, mgrport 15051 ?OGGTarget = oelora114 ?OGGListener = 15051 rmttrail ./dirdat/pe ?TrailPIEAST = ./dirdat/pe passthru table east.*; ?PIEASTList = east.*

As you can see, using parameter substitution to configure an OGG environment can help streamline the change control process; which is a positive when setting up large environments. The negative is that an additional error layer may be introduced into the environment because the operating system environment variables must be controlled and maintained.


Substituting Oracle GoldenGate parameter options with operating system environment variables allows us to create standardized parameter files for change control purposes. In this article we presented the concept of parameter substitution and showed an example of how this could be implemented.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha