Re: is instance up???

From: Gary Hubbard <hubbardgd_at_phibred.com>
Date: Wed, 23 Jun 1999 13:47:09 -0500
Message-ID: <930163633.879.65@news.remarQ.com>

This is pretty elaborate, but it will truly tell of the instance is up or down by actually attempting to connect. We've used this for a long time and it's worked very well. I fire it up as a background daemon and all instances that are specified in the INSTFILE file are checked. Doing this means that if another instance needs to be monitored, I simply add it to the file and don't have to bring down the instance monitor daemon.

Enjoy...

Gary Hubbard
Sr. Database Administrator
Pioneer Hi-Bred International, Inc.
(515) 270-3287
,hubbardgd_at_phibred.com,

#***************************************************************************
**
#
# i n s t a n c e u p
#
#***************************************************************************
**
#
# Modifications:
#
# Change made by Date change made Description of change
# -------------- ---------------- ---------------------
# Gary Hubbard 01-MAY-1997 Created.
#
# Gary Hubbard 12-MAR-1999 Made a change in the
# logic so that if an
# instance is down, then
# the DBAs are notified
# once and not again
# until it is back up.
#
#***************************************************************************
**
#
# Description:
#
# This script will routinely check, at a predetermined rate of time,
# whether or not an Oracle instance us up or not. It will check all
instances
# that are listed the instances input file.
#
# If an instance is down at any time, a beeper is dialed and a DBA is
# notifed. A mail message is also sent confirming the information.
#
# The purpose of this procedure is to notify the DBA staff in the event that
# and Oracle instance is down.
#
#***************************************************************************
**
#
#---------------------------------------------------------------------------
---
# At least one parameter (the name file that contains the names of the
# instances that will be checked) must be passed into the script.
#---------------------------------------------------------------------------
---
#

if [ $# -eq 0 ]
then
echo "Invalid number of parameters - specify file that contains instance names"
exit 254
fi
#
#---------------------------------------------------------------------------
---
# If only one parameter is specified, then it will be the name of the file
# that contains the names of the Oracle instances to be checked. If more
# than one is passed, then the second one will be the rate at which to
monitor
# for down instances.
#---------------------------------------------------------------------------
---
#

if [ $# -eq 1 ]
then
INSTFILE=$1
MONRATE=600
else
INSTFILE=$1
MONRATE=$2
fi
#

if [ $# -gt 2 ]
then
echo "Too many parameter specified - specify only file name for instance names"
exit 254
fi
#
#---------------------------------------------------------------------------
---
# Define the needed environment variables...
#---------------------------------------------------------------------------
---
#

unset noclobber          # allow for overwriting existing files...
alias rm='rm -f'         # temporarily remove alias

alias sqld=svrmgrl
#

SCRIPT_NAME=instanceup
SCRIPTDIR=${ORACLE_HOME}/phi/scripts
LOGDIR=${ORACLE_HOME}/phi/logs
MAILID=${SCRIPTDIR}/oranotify_cell.mld
COMMONDIR=${LOGDIR}
#

export SCRIPT_NAME SCRIPTDIR LOGDIR MAILID COMMONDIR
#
#---------------------------------------------------------------------------
---
# Show that the script has begun...
#---------------------------------------------------------------------------
---
#

echo " "
echo "** Beginning" ${SCRIPT_NAME} "on" `date` " **"
#
#---------------------------------------------------------------------------
---
# Open the file that contains the schema names and passwords and loop
through
# them until all are processed.
#---------------------------------------------------------------------------
---
#

LOOP=1
#

while [ "${LOOP}" = 1 ]
do

for INREC in `cat ${INSTFILE}`

     do
       SID=`echo ${INREC}|awk -F* '{print $1}'`
       SYSPASS=`echo ${INREC}|awk -F* '{print $2}'`

#
#---------------------------------------------------------------------------
---
# Define where to put temporary files that are used...
#---------------------------------------------------------------------------
---
#

       SQLOUT=${LOGDIR}/${SCRIPT_NAME}_${SID}.out
       ERROUT=${LOGDIR}/${SCRIPT_NAME}_${SID}.err
       TEMPSQL=${LOGDIR}/${SCRIPT_NAME}_${SID}.sql
       DOWNFILE=${COMMONDIR}/${SID}down

#
#---------------------------------------------------------------------------
---
# Build a temporary SQLPlus script that will be used to make a valid
connection
# to the specified instance.
#---------------------------------------------------------------------------
---
#

       echo "connect SYS/${SYSPASS}@${SID}" > ${TEMPSQL}
       echo "select * from DUAL;" >> ${TEMPSQL}

#
#---------------------------------------------------------------------------
---
# Now execute the SQLPlus script just created and attempt to connect to the
# instance...
#---------------------------------------------------------------------------
---
#

sqld < ${TEMPSQL} > ${SQLOUT}
#
#---------------------------------------------------------------------------
---
# Check to see if the instance read from the file is up or not. If not, then
# notifiy DBA via the beeper and a mail message.
#---------------------------------------------------------------------------
---
#

grep ORA- ${SQLOUT} > ${ERROUT}
#

       if [ -s ${ERROUT} ]
         then
           if [ ! -s ${DOWNFILE} ]
             then
               echo "${SID} Oracle instance down, notifying DBAs!" >
${DOWNFILE}
               for MAIL in `cat ${MAILID}`
                 do
                   SUBJECT_TEXT="Oracle_instance_down!"
                   echo "${SID} Oracle instance down!" | mail -s \
                       ${SUBJECT_TEXT} ${MAIL}
                 done
           fi
         else
           if [ -s ${DOWNFILE} ]
             then
                 rm -f ${DOWNFILE}
                 for MAIL in `cat ${MAILID}`
                   do
                     SUBJECT_TEXT="Oracle_instance_up!"
                     echo "${SID} Oracle instance is back up!" | mail -s \
                         ${SUBJECT_TEXT} ${MAIL}
                   done
           fi
       fi


#


      done # End FOR do...

echo ""
echo "Pausing...will check instances again in ${MONRATE} seconds..."
echo ""

sleep ${MONRATE}

done # End WHILE do...
#
#

echo "** Finished" ${SCRIPT_NAME} "on" `date` " **" echo " "
#

exit 0 Received on Wed Jun 23 1999 - 13:47:09 CDT