Script to verify that Oracle database is running Oracle Database Tips by Donald Burleson

Question:  I'm a UNIX shop and I need a shell script to constantly monitor my database.  If the database is down, I want to send an alert e-mail.

Answer:  I use a Korn shell script to monitor the Oracle background process and scripts to check of Oracle is accepting connections.  For more details, see the great book "Oracle Linux Commands", and get the Free Oracle Linux command poster:

This UNIX command will show the number of processes for your instance:

ps -ef|grep $ORACLE_SID|grep -v grep|grep -v ora_|wc ?l

Here is how to check if your database is running:

check_stat=`ps -ef|grep ${ORACLE_SID}|grep pmon|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -lt 1 ]
then
exit 0
fi

#*************************************************************
# Test to see if Oracle is accepting connections
#*************************************************************
$ORACLE_HOME/bin/sqlplus -s /<<! > /tmp/check_$ORACLE_SID.ora
  select * from v\$database;
exit
!

#*************************************************************
# If not, exit and e-mail . . .
#*************************************************************
check_stat=`cat /tmp/check_$ORACLE_SID.ora|grep -i error|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -ne 0 ]
then
  mailx -s "$ORACLE_SID is down!" larry@us.oracle.com
exit 16
fi

This is just a snippet from Oracle database monitoring scripts that you can schedule with a cron.  More advanced Oracle monitoring scripts can also automatically attempt to re-start the failed instance or listener processes, but we recommend that you use well-tested script like those in the Oracle script collection.