Monday, August 23, 2010

GOLDEN GATE LAG MONITORING

Hi

There are two script i have created
1) Linux
2) solaris and other platform.

First one i just wrote with modified logic and less processing using files.
Second script is specially for other Unix OS ,due to tac command does not work there.

Please inform me on jonyjt@gmail.com , if you find problem regarding it.


1 script

#!/bin/ksh
###############################
# monitor gg process
###############################
export ORACLE_HOME=/u00/app/oracle/product/10.2.0/DB
export GG_HOME=/ORAGG/product/11.2.1/gghome10
export PATH=$GG_HOME:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME:$LD_LIBRARY_PATH
export LOGFILE=/tmp/testoutput.log
export DT=`date +%d/%m/%Y\ %k:%M:%S`
export BOX=`hostname -s`
export COLUMNS=130
#echo "############################################################"
#echo `date +%d/%m/%Y\ %k:%M:%S`
################################
echo $DT
rm -rf /tmp/testoutput.log
###############################
#check if process has abended.
${GG_HOME}/ggsci <<!| while read;do printf '%s\n' ;egrep -i '(EXTRACT|REPLICAT)'|tr ":" " " | tr -s '[:space:]' | awk '{if ($2=="ABENDED" || $5>=05 || $7 >=05 ) {print $1" "$3" is "$2" with lag of " $4"HR:"$5"MI and checkpoint of "$7"HR:"$8"MI" "\n">> "/tmp/testoutput.log"} else {print "No lag for"$3 > "/tmp/nolag.log"} }' ;  done
info all
exit
!
###############################
if [ -f ${LOGFILE} ]; then
        x=$( grep -c ABENDED /tmp/testoutput.log )
        y=$( grep -c RUNNING  /tmp/testoutput.log )
        if [ $x -gt 0 ]; then
                echo "please refer below logs" >> $LOGFILE
                echo "##################################################################" >> $LOGFILE
                tac ${GG_HOME}/ggserr.log | grep -m 1 -A 2 -B 2 ABEND >> $LOGFILE
                mailx -s "Alert GG Abended on $BOX" fistname.lastname@company.com < $LOGFILE
        fi
        if [ $y -gt 0 ]; then
                ##echo "process running "
                mailx -s "Lag Found on $BOX" fistname.lastname@company.com < $LOGFILE
        fi
else
        echo "NO LAG FOUND"
fi
###################################
2 script It was created on Solaris platform for ksh shell. ggs_lag.ksh is main script which calls ggs.ksh , you can also merge both into one script also.
#!/bin/ksh
############################################
# Name: ggs_lag.ksh                             #
# PURPOSE: TO MONITOR LAG OF GOLDEN GATE        #
# NOTE: THIS SCRIPT CALLS ggs.ksh               #
# THIS SCRIPT NOTIFY IF LAG IS MORE THEN 30 MIN #
# ONLY FOR FOR EXT AND PMP PROCESS GROUP #
###########################################
export GGATE=/opt/oracle/u01/app/oracle/ggs
alias gate='clear;cd $GGATE;./ggsci'
export PATH=/opt/oracle/u01/app/oracle/ggs:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/oracle/u01/app/oracle/ggs
LOGDIR=/export/home/oracle/dba_scripts/ggs/logs
EMAILFile=$LOGDIR/ggs_email.log
BOX=$(uname -a | awk '{print $2}')

##########################################################################
# RUNNING SCRIPT TO GET GOLDEN GATE INFORMATION #
##########################################################################

/export/home/oracle/dba_scripts/ggs/ggs.ksh > $LOGDIR/ggs_1.log

#to check when script was running

echo "script ggsksh completed from ggs_lag at `date`" >> /tmp/ggs_check.log

##################################################################################
## FORMATING INFORMATION: change cut -d":" -f 1,4 TO cut -d":" -f 1,2 ##
## to getinformation about lag instead of checkpoint ##
## this command grep only EXT_ and PMP_ if you need more pattern ##
## if you need more pattern to be greped please add to '(EXT_|PMP_|pattern)' ##
##################################################################################


cat $LOGDIR/ggs_1.log|egrep -i '(EXT_|PMP_)'|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 > $LOGDIR/ggs_2.log

# uncomment below command if you want to get lag and checkpoint both information #

#cat $LOGDIR/ggs_1.log|egrep -i '(EXT_|PMP_|DART)'|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 > $LOGDIR/ggs_2.log

# uncomment below command if you want to get lag information about running process #

#cat $LOGDIR/ggs_1.log|grep RUNNING|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 >
$LOGDIR/ggs_2.log

##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################

##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################


awk '{if ( $4 > 00 || $5 >=30 ) {print $1 " " $3 " HAS LAG of " $4" hour " $5 " min -- at -- " d "\n"} else {print "NO LAG FOR " $3 " " d >> "/tmp/ggs_lag_fine.log" }}' d="$(date)" $LOGDIR/ggs_2.log > $LOGDIR/ggs_email.log

# uncomment below command if you want to get lag and checkpoint both information #

#awk '{if ($4 >=30 || $5>=30 ) {print $1 " " $3 " has lag of "$4" min with checkpoint of "$5" min -- at -- " d "\n"} else {print "NO LAG FOR " $3 " "d > "/tmp/ggs_lag_fine.log" }}' d="$(date)" $LOGDIR/ggs_2.log > $LOGDIR/ggs_email.log

##########################################################
## SENDING EMAIL IF ERRORS ARE IN LOGFILE ###
##########################################################

if [ -s $EMAILFile ]; then
#echo "ERRORS FOUND"
mailx -s "GG LAG FOUND ON: $BOX" your.email@gmail.com team.email@gmail.com < $EMAILFile else cat /dev/null > $EMAILFile
#echo "ERRORS NOT FOUND"
fi

################# SCRIPT END ######################
This is ggs.ksh which calls by above ggs_lag.ksh
#!/bin/ksh
#########################################
#Name: ggs.ksh #
#THIS SCRIPT WILL CALLED BY ggs_lag.ksh #
#########################################
#alias gate='clear;cd $GGATE;./ggsci'
echo "ggsksh started `date`" >> /tmp/ggs_check.log
export GGATE=/opt/oracle/u01/app/oracle/ggs
alias gate='clear;cd $GGATE;ggsci'
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/oracle/u01/app/oracle/ggs
export PATH=/opt/oracle/u01/app/oracle/ggs:$PATH
#cd $GGATE
/opt/oracle/u01/app/oracle/ggs/ggsci <<>
info all
exit
EOF
echo "ggsksh completed at `date` " >> /tmp/ggs_check.log

12 comments:

  1. hi Jignesh, i copied the script and modified, but i got an error message on line 20..
    "line 17: syntax error at line 20: `<<' unmatched"

    could you help me on this..

    Regards,
    Burghan

    ReplyDelete
    Replies
    1. Which script you copied. 1 or 2 ??

      Delete
    2. Fixed it,I modified script again , script 1 had below lines, that may have generated while editing Post.

      ${GG_HOME}/ggsci <<! -- | while read;

      Delete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
    Replies
    1. Thanks , Soon I will be posting Automation of Goldengate Implementation and configuration.

      Delete
  3. Hi Jignesh!

    Thanks a ton for a very nice post!! Will you please answer my below doubts?

    1) How script will send mail? there is no internet connection on my production server.
    2) My source is AIX and target is windows can I implement same script on AIX?
    3) Can you please provide me the similar script for windows?

    Thanks once again for super awesome script!!

    Mayur

    ReplyDelete
    Replies
    1. Thanks to appreciate my efforts.

      1) It use smtp to send email.
      2) You can use 2 script for AIX.
      3) I dont have not yet written script on windows but you can use powershell to monitor GG.

      Delete
    2. Hi jignesh,

      Thanks again for your time for answering my questions!!

      Could you please explain how we can use powershell? I need to configure alerts for my target windows machine..

      Can you please help me in understanding?

      Delete
  4. Hi Sir, I have taken 1st scripts and made required changes.

    Now my question is how to execute this script ? do I need to put it in cron job ? how this script automatically executed ?

    ReplyDelete
  5. after executing this script mannually, I'm getting this..
    10/01/2017 10:45:20
    Usage: ggsci PARAMFILE param file> [CD ]
    LOG NOLOG PORT ]

    NO LAG FOUND


    Not received any mail so far...? In addition to this, there no logfile created in /tmp/ directory. why?

    Pls reply urgent

    ReplyDelete
  6. Hi Jignesh,
    Really good and useful one !
    Could you just please share the print of your 'info all' ? I just wonder why you grep by "egrep -i '(EXT_|PMP_|DART)'"...
    Thanks again !
    -Felipe

    ReplyDelete
  7. Hi Jignesh ,


    I am not receiving mails.
    i have internet & i am receiving mail alerts from other shell scripts

    ReplyDelete