Checks the Status of the Oracle Database

#!/bin/ksh  
#
# Scriptname  : db_ora_status.ksh
#
# Description : Lists the status of all databases on a given installation 
#               and checks the availability.  Checks that the listener is up.
#               Works with 8i and 9i.  Does not pick up new 10g or 11g processes.
#
# Parameters  : None.
#
# Author      : M S Ramsay 26 October 1999
#
# History       Date      Name      Reason
#               ----      ----      ------
#               dd/mm/yy  xxx       xxxxxxxxxxxxxxxxxxxxxxxx
#
# Copyright   : www.markramsay.com
#
############################################################################
#
#Parameter validation and set-up.
#
set +A SDS_dbinfo   #Array variable
SDS_scriptname="`basename $0`" 
SDS_runtype=${1:-}

if [[ $SDS_runtype != '' ]] && [[ $SDS_runtype != 'h' ]]
then echo "" 
     echo "ERROR in script $SDS_scriptname - Variable \$1 is invalid.  Should not be $1."
     echo ""
     echo "Try:-"
     echo ""
     echo "h - Help"
     echo ""
     exit
fi

if [[ $SDS_runtype = 'h' ]] 
then echo
     echo "SCRIPT DB_STATUS.KSH."
     echo "====================="
     echo 
     echo
     echo "Description:-"
     echo ""
     echo "This script checks the status of the oracle servers  registered"
     echo "in /var/opt/oracle/oratab.  It checks the number of pmon processes"
     echo "with the number  of oratab registrations and  it checks"
     echo "the availability of each database."
     echo ""
     echo "Parameters:- "
     echo ""
     echo "h - Help"
     echo ""
     exit
fi

#
#Determine the number of Oratab registrations.
#

SDS_servers=$(cat /var/opt/oracle/oratab |egrep -v '(^#|^\*)' |grep \: |awk -F\: '{print $1}')
SDS_server_count=0
echo "The following database instances are registered in /var/opt/oracle...."
echo

for f in $SDS_servers
do
    SDS_server_count=$((SDS_server_count + 1))
    echo $f
done
echo

#
#Check the oratab entries against the pmon processes.
#

SDS_proc_count=$((`ps -ef |grep pmon |grep -v grep |wc -l`))

if [[ $SDS_server_count != $SDS_proc_count ]]
    then echo "***********************************************************"
         echo "***** The number of PMON processes running does not   *****"
         echo "***** match the number of entries in the oartab file. *****"
         echo "***** It is likely that one of the above the          *****"
         echo "***** datebases is down.                              *****"
         echo "***********************************************************"
         echo
         echo "Continuing with database availability check....."
         echo
fi

#
#For each Database check for the basic oracle processes
#

echo
echo "Checking the instance processes for database...."
for f in $SDS_servers
do
    echo
    echo $f
    echo "--------"
    echo 

# 
#Set-up Headings
#

    printf "%-30s" "Process"
    printf "%-32s" "Status"
    print  "Count"
    echo

#
#Display Output for Each Process
#

    SDS_proc_name="pmon smon ckpt reco arc lgwr dbw"

    for n in $SDS_proc_name
    do

        if [[ $n = "arc" ]] || [[ $n = "dbw" ]]
        then

        #
        #Process 3 chars in length
        #

        printf "$n                           "
        SDS_proc_avail=$((`ps -fuoracle |grep ora_$n |grep $f|grep -v grep |wc -l` ))
         if [[ $SDS_proc_avail > 0 ]]
                 then print "Available                         $SDS_proc_avail"
              else print "Not Responding. Contact DBA.      $SDS_proc_avail"
         fi

        else

        #
        #Processes 4 chars in length
        #

        printf "$n                          "
        SDS_proc_avail=$((`ps -fuoracle |grep ora_$n |grep $f|grep -v grep |wc -l` ))
         if [[ $SDS_proc_avail > 0 ]]
              then print "Available                         $SDS_proc_avail"
              else print "Not Responding. Contact DBA.      $SDS_proc_avail"
             fi
        fi
    done
done

#
# Save current value of $ORACLE_SID, $ORACLE_HOME and $ORACLE_BASE
#

SDS_old_SID=`echo $ORACLE_SID`
SDS_old_HOME=`echo $ORACLE_HOME`
SDS_old_BASE=`echo $ORACLE_BASE`

#
# Check status of each DBMS Server.
#

echo
echo "Starting Database availability check...."
echo

for f in $SDS_servers
do
printf "Checking status of instance $f.........."
export ORACLE_SID=$f
export ORACLE_HOME=$(grep $f /var/opt/oracle/oratab |awk -F\: '{print $2}')
sqlplus / <<EOT 1>/dev/null           

EOT
        if [[ $? = 0 ]]
        then print "Available"
        else print "Not Responding. Contact DBA."
        fi
done

#
#Check to see if the listener is running
#

echo
echo "++++++++++++++++++++++++++++++++++++++++++++++"
echo
echo "Starting Listener availability check...."
echo
printf "The Listener is......................................."

SDS_lsnr_up=$((`ps -ef |grep LIST |grep -v grep|wc -l`))
if [[ $SDS_lsnr_up < 1 ]]
then echo "Not Running"
else echo "Running"
fi

#
#Check the services registered with the listener
#

for m in $SDS_servers
do
printf "Checking status of listener service $f.........."
SDS_lsnr_status=$((`lsnrctl status |grep service |grep $m |grep -v grep |wc -l`))
if [[ $SDS_lsnr_status > 0 ]]
   then print "Available"
   else print "Not Responding. Contact DBA."
fi
done

#
#Resetting ORACLE_SID
#

export $ORACLE_SID=$SDS_old_value

#
#Exit Script
#

exit