A GoldenGate profile & environment script

I have been working a lot with Oracle’s GoldenGate the last year or so and i am loving the product more and more. I really enjoy it’s modulair format where you can actually form it into exactly the thing that you need. However one thing about working with GoldenGate is something that always annoyed me a bit: There is no good way of finding out where the binaries are if the GoldenGate processes are not running. Coming from an Oracle database background, i am used to an oratab file or a cluster registry to see where a database is running (or not running).

I often have to logon to a server with GoldenGate installed on it, i forget where it is running and find myself doing something like a ps -ef | grep mgr to see what the $OGG_HOME is. It becomes a little bit more of a challenge when all processes from GoldenGate are down and you can’t really remember the $OGG_HOME anymore. One option would be to take a look into the inventory.xml:

<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>11.2.0.3.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraGI12Home1" LOC="/u01/app/12.1.0.2/grid" TYPE="O" IDX="1">
   <NODE_LIST>
      <NODE NAME="rac1"/>
      <NODE NAME="rac2"/>
   </NODE_LIST>
</HOME>
<HOME NAME="OraDB12Home1" LOC="/u01/app/oracle/product/12.1.0.2/dbhome_1" TYPE="O" IDX="2">
   <NODE_LIST>
      <NODE NAME="rac1"/>
      <NODE NAME="rac2"/>
   </NODE_LIST>
</HOME>
<HOME NAME="OraHome1" LOC="/u01/app/oracle/product/12.1.2/oggcore_12c" TYPE="O" IDX="3"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>

In the example above it is obvious that the GoldenGate software was installed in the 3rd home by looking at the name of the directory, there is of course nothing that stops someone from installing GoldenGate in an a completely anonymous path. The Oracle Inventory does not specify what type of software is installed in a specific home, it just records that a home is installed in that location.

So i decided to create a small script that keeps track of your GoldenGate environment on a server. After setting up this script you will see the following when you have logged on to your server:

=Alias MgrUp OGG_HOME
ogg1 No /u01/app/oracle/product/12.1.2/oggcore_11g
ogg2 No /u01/app/oracle/product/12.1.2/oggcore_12c

The script has created 2 aliases ogg1 and ogg2, each alias will setup the GoldenGate environment for that specific home. The setup of the environment consists of a couple things:

  • It sets $OGG_HOME to the home that was just selected
  • Expands $PATH with $OGG_HOME so you can run the tools directly
  • Does an check if it can find the shared libraries of the Oracle RDBMS and immediately warns if no RDBMS home has been set. It specifically checks if the libnnz*.so libraries (GoldenGate needs this for cipher/wallet support) are there.
  • Setting up a bunch of handy aliases:
    • ggstart: Starts the MGR process
    • gg-startall: Starts all processes that are configured
    • ggstop: Stops the MGR process
    • gg-stopall: Stops all processes that are configured
    • gginfo: Shows the output of info all from ggsci
    • ggversion: Shows some version information of the system
    • ggcom: Generic wrapper for ggsci, handy for scripting. Usage is like this: ggcom “info all”
    • ggreprofile: Reloads the oggprofile script for you add an additional home or made some modifications to the script.
    • rgg: Wraps ggsci with rlwrap so you have command line history in ggsci (please oracle update ggsci)
    • rlogdump: rlwrap alias for logdump, same as with rgg.
    • gglog: Opens the Golden Gate logfile for viewing
    • gghome: Goes to the $OGG_HOME

How does this script work? After it is copied to a directory on your server, change variable OGG_TOOLS_HOME in the top of the script to correct path:

### OGG Tools home location - CHANGE THIS ###
OGG_TOOLS_HOME=/u01/tools/ogg

Then add the full location of the script to the user profile that runs GoldenGate like this: . /u01/tools/ogg/oggprofile (note it is: point[space]{path]) and log out and in again. As soon as you have logged in again the script will check in the $OGG_TOOLS_HOME location if a file called oggtab exists, if it doesn’t exists it will create the file:

if ! [[ -f $OGG_TOOLS_HOME/oggtab ]] ; then touch $OGG_TOOLS_HOME/oggtab ; fi;

In the oggtab the profile script will keep track of which GoldenGate home it is has detected. It does this by comparing the contents of the oggtab with the running MGR processes. If it finds a new home it will add it to the oggtab:

V_OGG_LOC=`ps -ef | grep [\.]\/mgr | awk '{print $10}' | sed 's/\/dirprm\/mgr\.prm//'`

if ! [[ -z $V_OGG_LOC ]]; then 
	for OGG_HOME in $V_OGG_LOC;
	do
		grep -q -F  "$OGG_HOME" $OGG_TOOLS_HOME/oggtab || echo $OGG_HOME >> $OGG_TOOLS_HOME/oggtab
	done
fi;

Be aware that the script has now way of knowing if a home is deleted, if you remove an $OGG_HOME you must also manually remove the home from the oggtab file. With the oggtab file the script can loop through each of the homes and check if the MGR is up and build up the alias for the $OGG_HOME:

for TAB_HOME in `cat $OGG_TOOLS_HOME/oggtab`;
do
	if [[ " ${V_OGG_LOC[*]} " == *" $TAB_HOME "* ]]; then
		MGR_UP="Yes"
	else
		MGR_UP="No"
	fi
	
	printf " $(tput bold)%-16s$(tput sgr0) %-11s %-16s \n" $(echo ogg$HOME_COUNT) $(echo $MGR_UP) $(echo $TAB_HOME)
	alias ogg$HOME_COUNT="export OGG_HOME=$TAB_HOME; export PATH=$PATH:$OGG_HOME; export JAGENT_HOME=$V_JAGENT; echo \"GoldenGate home set to: $TAB_HOME\"; f_dbhome_set"
		if [ -n "$JAGENT_HOME" ]; then
			echo "Jagent location set to: $JAGENT_HOME"
		fi;
		((HOME_COUNT++))
done

The script prints out all the information and immediately shows you in the output (see the top of the post) with the alias name is, if the manager is up-and-running and what the path location is. Personally this scripts solves a bunch of small nuisances i have with GoldenGate and makes management a lot easier for me. I find myself making additional aliases for specific repeating tasks at customers with ggcom function quiet regularly.

You can find the GoldenGate environment script here

Golden Gate monitoring in OEM

I have migrated several database to Exadata using Golden Gate and i really liked the Golden Gate tool. The only thing i never really understood is why there was not a simple Enterprise Manager plug-in to monitor the Golden Gate status. So i decided to create a small script that can be used as a ‘User Defined Metric’ in OEM. For monitoring purposes i am only interested in knowing if my process is abended (status stopped means that i have deliberately stopped it, so i don’t need to know that) and what the lag time is. So i have created that small script i called ogg_status.sh, which i placed in my $GG_HOME directory:

#!/bin/bash
# set -x
########################################
#
# Usage ./ogg_status.sh -n GG_PROCES_NAME -t status|lagtime
# Klaas-Jan Jongsma 2011
#
# v.01
#
########################################
#Function to get info all from GGSCI
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/db_1/lib
infoall() {
cd /u01/app/oracle/product/ogg
(
./ggsci << eof
info all
exit
eof
)}
########################################
# Commandline options:
while getopts n:t: opt
do
  case "$opt" in
    n) NAME="$OPTARG";;
    t) TYPE="$OPTARG";;
  esac
done
#########################################
# Check status of Golden Gate processes
status()
{
# infoall |grep "$NAME" | awk '{print $2}'
cSTATUS="`infoall |grep "$NAME" | awk '{print $2}'`"
if [ "${cSTATUS}" = "ABENDED" ]
  then
  echo "em_result=ABENDED"
  echo "em_message=Golden Gate process ${NAME} status: ${cSTATUS}"
else
    if [ "${cSTATUS}" = "STOPPED" ]
      then
      echo "em_result=STOPPED"
    else echo "em_result=RUNNING"
    fi
    unset cSTATUS
fi
}
#########################################
# Check lagtime of Golden Gate processes
lagtime()
{
cLAGTIME="`infoall | grep $NAME | awk '{print $4}'`"
# echo $cLAGTIME
cLAGTIME_HOURS=`echo $cLAGTIME | awk -F: '{print $1}'`
CLAGTIME_MINUTES=`echo $cLAGTIME | awk -F: '{print $2}'`
cLAGTIME_SECONDS=`echo $cLAGTIME | awk -F: '{print $3}'`
cLAGTIME_SEC_TOTAL=$(($cLAGTIME_HOURS*3600+$CLAGTIME_MINUTES*60+$cLAGTIME_SECONDS))
echo "em_result=${cLAGTIME_SEC_TOTAL}"
echo "em_message=Golden Gate process ${NAME} lagtime is: ${cLAGTIME} (${cLAGTIME_SEC_TOTAL} seconds), check Golden Gate infrastructure."
unset cLAGTIME cLAGTIME_HOURS CLAGTIME_MINUTES cLAGTIME_SECONDS cLAGTIME_SEC_TOTAL
}
#########################################
# MAIN
case "$TYPE" in
  status)
     status
     ;;
  lagtime)
     lagtime
     ;;
esac
# set +x

Now we have a script that tells something about our Golden Gate status we can create an UDM in OEM, we do this at the host target level. Now go to the top right corner and click on create, to make a new UDM:

If you want to create an UDM that monitors the lag of a process fill it in somewhat similar like below. It will create an UDM whenever the extract/datapump process gets a lag bigger then 5 seconds:

For monitoring the status of an extract or manager process create the following. It monitors if a process gets the status abended

We now have our UDM in place, next step would be to create a notification rule. For this go to your OEM preferences, this is located at the top right most place in your OEM screen. In the preferences screen click on rules (located under notifications):

This will brings in the notification rules window, add a new rule here and add you host as a target for the rule:

Next go the the metrics tab and add two metrics, one UDM String metric and UDM number metric. We need the string UDM to monitor process status, the number UDM is needed for lag monitoring:

Next check the critical severity status:

We are done on the Metrics tab, final step is to tell OEM to send us an e-mail. Go to the last tab, actions, do so and check ‘Send me an e-mail’:

If you have OEM alerting set up properly, you will now get mail if process gets abended or if the lag gets bigger then 5 seconds.