Thursday, March 3, 2011

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


41 comments:

  1. Thanks for a great script. Although it seems to be missing the code that loads the ggsci data into the arrays (TYPE, NAME, STATUS, LAG, etc). Is this the complete script or is there something else that needs to be run before this script may work correctly?

    ReplyDelete
  2. Hello John,

    Sorry for the late reply, I was away on vacations. This is a complete script and works fine and sends email alerts.

    Regards
    Gurpartap Singh

    ReplyDelete
  3. Hi Pratap,
    I am getting error:

    line 88: syntax error: unexpected end of file

    ReplyDelete
  4. Hello Sajid,

    Please chech that while copy paste you have copied everything properly and have not missed any character. I would recomend you to copy to a notepad and check for all the single and double quotes.

    Have fun !

    Regards
    Gurpartap Singh

    ReplyDelete
  5. Hi Pratap,
    I getting same error
    ./goldengate_monitor.sh: line 79: syntax error near unexpected token `done'
    ./goldengate_monitor.sh: line 79: `done'
    please check it again, thanks!

    ReplyDelete
  6. Hello Software,

    Line 79 is:
    echo "Group Name = ${NAME[$x]} RBA is stuck" |/bin/mailx -s "OGG RBA" ${NOTIFY}

    Please check while copy paste the quotes and brackets are picked properly.

    Script works fine and should work for you as well.

    Regards
    Gurpartap Singh

    ReplyDelete
  7. Dear,

    Thank you for reply, I have try check per row of it but it still occur that error!. I upload this sh file to box.net and i wanna you help check this issues, its really important with me! Thank you again!

    http://www.box.net/shared/nzd8zdkpcpisxslkx0vl

    ReplyDelete
  8. Please look again Gurpartap, your script is cut off on this blog. The subroutine "checkReplicat" never has its ending } even listed, and the main routine that calls the subroutines is never listed.

    ReplyDelete
  9. Note that $delay is never defined as well. There appears to be quite a bit of the script that got chopped off, perhaps by the Blog software.

    ReplyDelete
  10. Hello JC,

    I copy pasted it again from my server. Give it a try and see how it looks.

    Regards
    Gurpartap Singh

    ReplyDelete
  11. Nope, still looks like its getting truncated by this blog.

    Can I ask you to send it via email to shammaframma@mailinator.com

    ReplyDelete
  12. Hi Gurpratap,
    This is really good script. Unfortunately the blog software is messing up with it. I tried copy paste and could not make it work. would you mind sending it to my mail ID. ravindra958@yahoo.com?

    Regards,
    Ravi

    ReplyDelete
  13. could you please email to me also I have the same issue jp@wantnotneed.co.nz

    Looks good thought.

    Cheers,

    ReplyDelete
  14. Hi,

    Could you send me the script to my email ? I vave the same issue.

    wellprado@hotmail.com

    Very nice article.

    Congratulations

    Cheers

    ReplyDelete
  15. Hi,

    Could you send me the script to my email ? I vave the same issue.

    dba.shailendra@gmail.com



    Cheers

    ReplyDelete
  16. do you mind sending it to me also please

    sridharrdba@gmail.com

    ReplyDelete
  17. Hi Gurpartap,

    This seems to be useful script but unfortunately, while up-loading script to this page, some sections or lines were left out. Can you pl check on this and correct.

    If uploading is still a issue, can you pl send to email nspatel1960@gmail.com

    Thanks
    Nilesh

    ReplyDelete
  18. Hello Gurpartap,

    I'd like to use your script for goldengate monitoring,

    Do you mind send it to me by mail.

    Regards

    Thanks for sharing ;o)

    God bless

    My mail : hichtelga@gmail.com

    ReplyDelete
    Replies
    1. Hello Gurpartap,

      I'd like to use your script for goldengate monitoring,

      Do you mind send it to me by mail

      dbaedge@yahoo.com

      Delete
  19. Hi Gurprathab, Can you send me the script to my email id pandiva@gmail.com I would like to test as well.

    ReplyDelete
  20. HI gurucharan,,

    Can you please mail me copy of your script to mohith2009@gmail.com

    ReplyDelete
  21. Hi GurPratap,

    Could you please send me the script to my mailid?

    subbarao.yadavalli@gmail.com

    ReplyDelete
  22. Hi GurPratap,

    is it possible to send the script by email?
    ravi.kap@gmail.com

    Thanks and regards
    Ravi

    ReplyDelete
  23. This comment has been removed by the author.

    ReplyDelete
  24. Hi GurPratap - Would you mind to send to rakeshkumarkanaka@gmail.com

    ReplyDelete
    Replies
    1. Hi GurPartap

      Please send to me at - jaigaurav1@gmail.com.

      Thank !!

      Delete
  25. This comment has been removed by the author.

    ReplyDelete
  26. This script emails the .rpt file

    #!/bin/ksh
    GG_HOME=/u01/app/oracle/ogg; export GG_HOME
    NOTIFY="golden_one@mycompany.com"

    OIFS=$IFS
    IFS="
    "
    NIFS=$IFS

    OUTPUT=`$GG_HOME/ggsci << EOF
    info all
    exit
    EOF`

    for line in $OUTPUT
    do
    if [[ $(echo "${line}"|egrep 'RUNNING|STOP|ABEND' >/dev/null;echo $?) = 0 ]]
    then
    GRPNAME=$(echo "${line}" | awk -F" " '{print $3}')
    GRPSTAT=$(echo "${line}" | awk -F" " '{print $2}')
    GRPTYPE=$(echo "${line}" | awk -F" " '{print $1}')
    case $GRPTYPE in
    "MANAGER")
    cat $GG_HOME/dirrpt/MGR.rpt | mailx -s "$HOSTNAME - GoldenGate $GRPTYPE $GRPSTAT" $NOTIFY ;;
    "JAGENT")
    cat $GG_HOME/dirrpt/JAGENT.rpt | mailx -s "$HOSTNAME - GoldenGate $GRPTYPE $GRPSTAT" $NOTIFY ;;
    "EXTRACT"|"REPLICAT")
    tail -100 $GG_HOME/dirrpt/"${GRPNAME}".rpt |mailx -s "$HOSTNAME - GoldenGate $GRPTYPE $GRPSTAT" $NOTIFY ;;
    esac
    fi
    done

    ReplyDelete
  27. http://oracleabout.blogspot.com/2010/08/golden-gate-lag-monitoring.html
    RBA stuck idea is good ....

    ReplyDelete
  28. Good script to monitor application logs on unix servers.

    http://www.buggybread.com/2012/03/log-monitoring-shell-script-to-send.html

    ReplyDelete
  29. You got a really useful blog I have been here reading for about an hour. I am a newbie and your success is very much an inspiration for me.
    Oracle SQL

    ReplyDelete
  30. Line 86 or what ever the line is with - done. is missing an associated do clause. Not sure if this script ever worked. Keep searching guys.

    ReplyDelete
  31. Hi! I am a digital marketer. The previous seo guy working for my client left a spammy comment at your blog with the username Rosemarie rana which links to my client's site.
    Such comments are or might cause serious damage to my client's site with respect to SEO which is why I am request you here by to remove it asap.
    If you don't remove it by 11:59 pm, Pacific Standard Time, 3rd January 2014, we will have use Google's Disavow Tool to get back-link removed and, sorry to say this, but Google may not look too nicely upon you either for not having removed the comment. Thanks in advance for your cooperation.

    ReplyDelete
  32. Hi guru
    As ppl said the script has issues. As soon as login to ggsci what command u r using and I see you have done at end which should have a while loop or for loop can you copy paste the correct script or else upload to google docs or drop box. Appreciate your help

    ReplyDelete
  33. I am experiencing Unexpected error. can you please send me the script to mail id vinodraj.dba@gmail.com

    ReplyDelete
  34. can someone who has the please forward it to my mail id vinodraj.dba@gmail.com

    ReplyDelete
  35. I am getting the error 'gg_monitor.sh[83]: 0403-057 Syntax error at line 84 : `done' is not expected.' could anyone please forward the complete script to id ismailshariff.a@gmail.com

    ReplyDelete
  36. Please email me as well Thank you.
    hsachin@gmail.com

    ReplyDelete
  37. Hi
    I am getting the error 'gg_monitor.sh[83]: 0403-057 Syntax error at line 84 : `done' is not expected.' could anyone please forward the complete script to id tareq976@gmail.com

    ReplyDelete
  38. This comment has been removed by the author.

    ReplyDelete
  39. Can you send me the script to my email id xiaochuan717@gmail.com I would like to test as well.

    ReplyDelete