Oracle GoldenGate: Parameter Substitution

Introduction

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 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
   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.

Summary

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.

Add Your Comment