#!/bin/ksh
#
# Verify if all oracle instances are up - tnsnames.ora entries should be uniform
#
# Biju Thomas
. /etc/profile
tempfile=/tmp/sidfile.tmp
sidfile=/tmp/sidfile.out
TNS_DIR=$ORACLE_HOME/network/admin
cat $TNS_DIR/tnsnames.ora | grep SID > $tempfile
sort -u -o$sidfile $tempfile
awk '{ print $5 }' $sidfile > $tempfile
grep 'OR)' $tempfile | tr -d \) > $sidfile
#
# Now $sidfile has all the SIDNAMES to be verified
#
ORA_BIN=$ORACLE_HOME/bin
cat $sidfile | while read SIDNAME
do
$ORA_BIN/tnsping $SIDNAME > /dev/null 2>&1
STATUS=$?
if test $STATUS -ne 0
then
# Check if this database down information was reported earlier
# If yes, don't send page again
if test ! -f /home/oracle/.alerts/$SIDNAME
then
# New alert, Page DBA, set marker file
WMESSAGE="$SIDNAME is not responding... Please Verify."
#echo $WMESSAGE
echo $WMESSAGE | mailx  dba_on_call_pager
touch /home/oracle/.alerts/$SIDNAME
# Log pages sent
date >> /home/oracle/.alerts/page.log
echo $WMESSAGE >> /home/oracle/.alerts/page.log
fi
else
# Check if the database was down earlier and is up now
# If yes, remove marker file
if test -f /home/oracle/.alerts/$SIDNAME
then
rm -f /home/oracle/.alerts/$SIDNAME
fi
fi
done
#
# End of Script