RE: db up script

From: Thomas Jeff <ThomasJe_at_tce.com>
Date: Fri, 12 May 2000 08:54:25 -0500
Message-Id: <10495.105497@fatcity.com>

This is a slightly changed version of a script that Steve Adams posted a while back,
I have an AUTOSYS job that runs this every 15 minutes. It checks for both a
hung/db not up, and excessive TNSPING times.

#! /usr/bin/ksh
export PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/sbin:/export/home/oracle:. export ORACLE_HOME=/oracle/app/oracle/product/7.3.4 export PATH=$ORACLE_HOME/bin:$PATH
export THRESHOLD=1000
export STATUS=0;
export TWO_TASK=db1.world
export SPOOL=/tmp/$$.SPOOL
export PING=/tmp/$$.PING
export TNS_ADMIN=/etc

echo ' ' >> /tmp/$TWO_TASK.dbcSUCCESS
tnsping $TWO_TASK | tail -1 > $PING &

sleep 1
((timeout = 20))
while ((timeout -= 1)) && ! grep -c OK $PING >/dev/null do

   sleep 1
done

if [[ `grep -c OK $PING` = 1 ]]
then

   echo `date` $TWO_TASK 'is responding to TNSPING' >>
/tmp/$TWO_TASK.dbcSUCCESS

   TIME=$(sed -n 's/.* (\([0-9]*\) .*/\1/p' $PING)    if (($TIME > $THRESHOLD))
   then

      echo `date` 'TNSPING' $TIME 'msec is excessive' >
/tmp/$TWO_TASK.dbcFAILED.$$

      STATUS=1
      exit $STATUS
   else
      echo `date` 'TNSPING is' $TIME 'msec' >> /tmp/$TWO_TASK.dbcSUCCESS

  fi
else

   kill $!
   echo `date` $TWO_TASK 'is not responding to TNSPING' >
/tmp/$TWO_TASK.dbcFAILED.$$

   STATUS=1
   exit $STATUS
fi

sqlplus -s << EOF > $SPOOL
nobody/important
exit;
EOF
egrep 'ORA-122|ORA-121|ORA-01034' $SPOOL > /dev/null if [[ $? = 0 ]]
then

   echo `date` $TWO_TASK 'is NOT accessible' > /tmp/$TWO_TASK.dbcFAILED.$$    STATUS=1
   exit $STATUS
else

   grep 'ORA-01017' $SPOOL > /dev/null
   if [[ $? = 0 ]]
   then
     echo `date` $TWO_TASK 'is UP and accessible' >>
/tmp/$TWO_TASK.dbcSUCCESS

   fi
fi

rm $SPOOL
rm $PING

-----Original Message-----
From: Lisa_Koivu_at_gelco.com [mailto:Lisa_Koivu_at_gelco.com] Sent: Thursday, May 11, 2000 1:29 AM
To: Multiple recipients of list ORACLE-L Subject: db up script

Hello -

Does anyone have a script that checks if a series of databases are up and available that they would be willing to share? I set out to write a ksh script
to do this and found that I would have to write C to do what I needed to do, since trying to connect to a hung or down instance will result in a hung process. There wasn't an easy way to fire off the connect process, sleep, and
kill it if it hadn't come back yet. I really don't want to spend my day writing
C code, it will exacerbate the headache my job gave me today.

Any suggestions / script snippets are appreciated Thanks
Lisa

-- 
Author: 
  INET: Lisa_Koivu_at_gelco.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Fri May 12 2000 - 08:54:25 CDT