Part 33 of series - Script to monitor Goldengate extract/replicat process status & get the email alert or the remedy ticket if any process is ABENDED

O Destroyer of the pains and the suffering of the poor, O Master of each and every heart, O Masterless One: I have come seeking Your Sanctuary. O God, please be with Nanak!

Where there is no mother, father, children, friends or siblings - O my
mind, there, only the Naam, the Name of the Lord, shall be with you as your help and support. Where the great and terrible
Messenger of Death shall try to crush you, there, only the Naam shall go along with you. Where the obstacles are so very
heavy, the Name of the Lord shall rescue you in an instant. By performing countless religious rituals, you shall not be saved.
The Name of the Lord washes off millions of sins. As Gurmukh, chant the Naam, O my mind.



Index of all the posts of Gurpartap Singh's Blog



Here is the script that can be used to monitor the goldengate extract/replicat process. It will email you to the specified
email address or you can configure remedy to cut the ticket by letting its agent read the logfile.

This script will send the email for every extract that has a status of "ABENDED". Then you should go in and look at the
problem that caused it and fix it and restart it. You can schedule this script in crontab for every 30 minutes or as
much frequency as you want.

If you are using it on a home network this script will send email only if you have configured email. On my home network
I have configured email using the smtp of gmail.com, if you have not setup email on your server you can use the following note
to get it configured.

Post 27 of series, Setup email on unix server using SMTP of gmail.com(Google Mail) to get alerts from scripts.


Create the following script at your scripts directory. I have it at:

/u01/app/oracle/scripts

Create a log directory where you want to place your logs. I am placing them here:
/u01/app/oracle/product/log


You just need to fix the following parameter of this script as per your environment:

===========================================================
GG_HOME=/u01/app/oracle/product/goldengate; export GG_HOME
GG_BASE="/u01/app/oracle/product"
NOTIFY="xxxxx@yahoo.com"
LOG="${GG_BASE}/log/ggs_monitor_${DT}.log"
===========================================================

Here starts the script:

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/scripts
$ cat goldengate_monitor.sh
#!/bin/ksh

#
# Created by : Gurpartap Singh
# Date: March 03, 2011
#
# Purpose: Monitoring script for Golden Gate processes
#
# Notes: At the very least, the "Setup variables" section will need to be modified to
# reflect your environment.
#

if test "${1}" = "RBA"
then
CHECK_RBA="YES"
else
CHECK_RBA="NO"
fi

# Setup variables:
DT=`date +%d%H%M%S`
GG_HOME=/u01/app/oracle/product/goldengate; export GG_HOME
GG_BASE="/u01/app/oracle/product"
NOTIFY="xxxxxxxx@yahoo.com"
LOG="${GG_BASE}/log/ggs_monitor_${DT}.log"

echo "======================================================================" >> $LOG 2>&1
echo "+ Date: $(date)" >> $LOG 2>&1
echo "+ Script: $0" >> $LOG 2>&1

# Setup sub-routines here.
notfyMe() {
MESG=${1} # argument passed to sub-routine
}

checkReplicat() {
cd $GG_HOME
./ggsci <> $LOG 2>&1
echo "Type: ${TYPE[$x]}" >> $LOG 2>&1
echo "Name: ${NAME[$x]}" >> $LOG 2>&1
echo "Status: ${STATUS[$x]}" >> $LOG 2>&1
echo "Lag: ${LAG[$x]}" >> $LOG 2>&1
echo "Checkpoint: ${CHKPT[$x]}" >> $LOG 2>&1

MYSTATUS="${STATUS[$x]}"

case ${STATUS[$x]} in
"ABENDED") echo "Action: ${NAME[$x]} ABENDED - Sending mail" >> $LOG 2>&1
echo "Group Name = ${NAME[$x]} has ABENDED" /bin/mailx -s "OGG Abend" ${NOTIFY}
;;
"STOPPED") echo "Action: ${NAME[$x]} STOPPED - Sending mail" >> $LOG 2>&1
echo "Group Name = ${NAME[$x]} has been STOPPED" /bin/mailx -s "OGG Stopped" ${NOTIFY}
;;
"RUNNING") echo "Action: None required!" >> $LOG 2>&1
;;
*) echo "Optional case" >> $LOG 2>&1
esac

# Next check for lag
# Continue, even if the process is STOPPED or ABENDED!!!
MYLAG="${LAG[$x]}"

if [[ $MYLAG > "01:00:00" ]]
then
echo "Group Name = ${NAME[$x]} is LAGGING" /bin/mailx -s "OGG Lag" ${NOTIFY}
fi

if [ $CHECK_RBA == "YES" ]
then

# Now check to make sure the RBA numbers are changing... stupid pos!!!
curr=$(checkReplicat ${NAME[$x]}grep -v grepgrep RBAawk {'print $4}')
sleep $delay
next=$(checkReplicat ${NAME[$x]}grep -v grepgrep RBAawk {'print $4}')

if [ $curr -eq $next ]
then
echo "RBA has not change in $delay minutes"
echo "Group Name = ${NAME[$x]} RBA is stuck" /bin/mailx -s "OGG RBA" ${NOTIFY}
fi
fi


let x=x+1

done

exit 0;








oracle : rac1.rac.meditate.com : @crs : /u01/app/oracle/scripts
$



After creating it just change its permisssins to 755 like:

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/scripts
$ chmod 755 goldengate_monitor.sh

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/scripts
$ ls -lart
total 12
drwxr-xr-x 11 oracle oinstall 4096 Mar 3 21:57 ..
-rwxr-xr-x 1 oracle oinstall 3157 Mar 3 22:09 goldengate_monitor.sh
drwxr-xr-x 2 oracle oinstall 4096 Mar 3 22:09 .

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/scripts
$


Now lets execute it as :

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/scripts
$ goldengate_monitor.sh

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/scripts
$


You will have an email for each process that the status of "ABENDED".
You can also see the logfiles generated at following location for your each run with time stamp.

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/log
$ ls -lart
total 32
drwxr-xr-x 6 oracle oinstall 4096 Mar 3 21:24 ..
-rw-r--r-- 1 oracle oinstall 1418 Mar 3 22:04 ggs_monitor_03220438.log
-rw-r--r-- 1 oracle oinstall 1418 Mar 3 22:06 ggs_monitor_03220650.log
-rw-r--r-- 1 oracle oinstall 1403 Mar 3 22:18 ggs_monitor_03221828.log
-rw-r--r-- 1 oracle oinstall 1371 Mar 3 22:19 ggs_monitor_03221901.log
-rw-r--r-- 1 oracle oinstall 1418 Mar 3 22:29 ggs_monitor_03222951.log
drwxr-xr-x 2 oracle oinstall 4096 Mar 3 22:29 .

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/log
$

cat on the logfile will look as:

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/log
$ cat ggs_monitor_03222951.log
======================================================================
+ Date: Thu Mar 3 22:29:51 PST 2011
+ Script: ./goldengate_monitor.sh
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Type: EXTRACT
Name: ECREATOR
Status: ABENDED
Lag: 00:00:00
Checkpoint: 1708:36:18
Action: ECREATOR ABENDED - Sending mail
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Type: EXTRACT
Name: ELORD1
Status: ABENDED
Lag: 00:00:00
Checkpoint: 2663:42:41
Action: ELORD1 ABENDED - Sending mail
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Type: EXTRACT
Name: EPLACE
Status: ABENDED
Lag: 00:00:00
Checkpoint: 1708:36:27
Action: EPLACE ABENDED - Sending mail
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Type: EXTRACT
Name: ETRUTH
Status: ABENDED
Lag: 00:00:00
Checkpoint: 1708:36:27
Action: ETRUTH ABENDED - Sending mail
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Type: EXTRACT
Name: PCREATOR
Status: ABENDED
Lag: 00:00:00
Checkpoint: 1832:31:42
Action: PCREATOR ABENDED - Sending mail
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Type: EXTRACT
Name: PPLACE
Status: ABENDED
Lag: 00:00:00
Checkpoint: 1832:31:41
Action: PPLACE ABENDED - Sending mail
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Type: EXTRACT
Name: PTRUTH
Status: ABENDED
Lag: 00:00:00
Checkpoint: 1832:31:43
Action: PTRUTH ABENDED - Sending mail

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/log
$


Have fun and don't forget to do meditation in the name of GOD as thats the only savior in Kaliyuga and pray for me that I do more and more meditation in rememberance of all mighty.



On that path where the miles cannot be
counted, there, the Name of the Lord shall be your sustenance. On that journey of total, pitch-black darkness, the Name of the
Lord shall be the Light with you. On that journey where no one knows you, with the Name of the Lord, you shall be
recognized. Where there is awesome and terrible heat and blazing sunshine, there, the Name of the Lord will give you shade.
Where thirst, O my mind, torments you to cry out, there, O Nanak, the Ambrosial Name, Har, Har, shall rain down upon you