Shell Programming and Scripting

cd ~
. $HOME/.profile

# Orasid.lst has the DB name which I want to Monitor
# Oratab has all the database names on the srver

cat $ORA_SCRIPT/orasid.lst | while read LINE1
do
  cat /etc/oratab | while read LINE
  do
  case $LINE in
  \#*)            ;;      #comment-line in oratab
  *)
  #       Proceed only if third field is 'Y'.
 if [ "`echo $LINE | awk -F: '{print $1}' -`" = $LINE1 ] ; then
 export ORACLE_SID=$LINE1
 export ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`

#
# CHECK FOR ALL DB if Up/Down
#

  if [ -f $ORA_LOG/pmon_process_exist.txt ] ; then /bin/rm $ORA_LOG/pmon_process_exist.txt; fi
  ps -ef | grep ora_pmon_$ORACLE_SID | grep -v grep  > $ORA_LOG/pmon_process_exist.txt
  if [ -s $ORA_LOG/pmon_process_exist.txt ]
  then
        if [ -f $ORA_SCRIPT/select.lst ] ; then /bin/rm $ORA_SCRIPT/select.lst; fi
        $ORACLE_HOME/bin/sqlplus -s ora_connect/ora_connect@$ORACLE_SID @$ORA_SCRIPT/select.sql
        if [ -s $ORA_SCRIPT/select.lst ]
        then
                echo "DB ALL OK"
        else
        for dba in `cat $ORA_SCRIPT/DBA_EMAIL_LIST`
        do
                /usr/bin/mailx -s "PMON is up but DATABASE HAS CONNECTIVITY ISSUES: $ORACLE_SID" $dba < /dev/null
        done
        fi
  else
    for dba in `cat $ORA_SCRIPT/DBA_EMAIL_LIST`
    do
        /usr/bin/mailx -s "DATABASE IS DOWN: $ORACLE_SID" $dba < /dev/null
    done
  rm $ORA_LOG/pmon_process_exist.txt
  fi
 fi
esac
done
done

if [ -s $ORA_LOG/pmon_process_exist.txt ]
  then
        if [ -s $ORA_SCRIPT/select.lst ]
        then
                echo "DB ALL OK"
        else
        for dba in `cat $ORA_SCRIPT/DBA_EMAIL_LIST`
        do
                /usr/bin/mailx -s "PMON is up but DATABASE HAS CONNECTIVITY ISSUES: $ORACLE_SID" $dba < /dev/null
        done
        fi
else
    for dba in `cat $ORA_SCRIPT/DBA_EMAIL_LIST`
    do
        /usr/bin/mailx -s "DATABASE IS DOWN: $ORACLE_SID" $dba < /dev/null
    done
  rm $ORA_LOG/pmon_process_exist.txt
  fi