#!/usr/bin/ksh
# -----------------------------------------------------------------------------
# File:      monitor_db.sh
#
# -----------------------------------------------------------------------------
cd ~
. .profile

# Parameters used
# ORA_LOG,ORA_SCRIPT,DBA_EMAIL_LIST   -- (set in .profile)
#

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_LOG/select.lst ] ; then /bin/rm $ORA_LOG/select.lst; fi
        $ORACLE_HOME/bin/sqlplus -s ora_connect/ora_connect@$ORACLE_SID @$ORA_SCRIPT/select.sql
        if [ -s $ORA_LOG/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
  fi
  rm $ORA_LOG/pmon_process_exist.txt
 fi
esac
done
echo $LINE1
echo $ORACLE_SID
echo $ORACLE_HOME
done

ps -ef | grep ora_pmon_$ORACLE_SID | grep -v grep  > $ORA_LOG/pmon_process_exist.txt