#!/bin/ksh
#============================================================================
# File:		chk_db_up.sh
# Type:		UNIX korn-shell script
# Author:	Tim Gorman (Evergreen Database Technologies, Inc.)
# Date:		30jun98
#
# Description:
#	Check whether the database instance specified is "up" or "down".
#
# Syntax:
#	chk_db_up.sh <env-ORACLE_SID> <TNS-connect-string>
#
# Exit statuses:
#	0	- instance is "up" and available
#	1	- database instance not specified - user error
#	2	- database instance not valid - user error or ORATAB error
#	3	- database instance "down" or not responding
#	4	- SQL*Net Listener not responding
#	5	- some other database error
#
# Modifications:
#	TGorman	11apr01	changed query from DUAL to V$THREAD so that standby
#			databases can be checked also...
#	TGorman	16aug04	changed login to SQL*Plus to allow for detection
#			of "hung" database...
#============================================================================
_Pgm=chk_db_up.sh
_OutFile=/tmp/chk_db_up_$$.out
_ErrFile=/tmp/chk_db_up_$$.err
#
#----------------------------------------------------------------------------
# Korn-shell function to be called multiple times in the script...
#----------------------------------------------------------------------------
_Echo() ### ...define shell-function "_Echo()"...
{
if [[ "$1" = "failure" ]]
then
	if [ -r ${HOME}/.dbapage ]
	then
		sed '/^#/d' ${HOME}/.dbapage |
		while read _PageRcpt
		do
			date | mailx -s "`hostname`: ${_Pgm} $1" ${_PageRcpt}
		done
	fi
fi
if [ -r ${HOME}/.dbamail ]
then
	sed '/^#/d' ${HOME}/.dbamail |
	while read _EmailRcpt
	do
		echo "$2" | mailx -s "`hostname`: ${_Pgm} $1" ${_EmailRcpt}
	done
else
	echo "${_Pgm} $1: $2"
fi
}	### ...end of definition of shell-function "_Echo()"...
#
#----------------------------------------------------------------------------
# Verify that the ORACLE_SID has been specified on the UNIX command-line...
#----------------------------------------------------------------------------
if (( $# != 2 ))
then
	_Echo warning "Usage: \"${_Pgm} <env-ORACLE_SID> <TNS-connect-string>\""
	exit 1 > /dev/null 2>&1
fi
_EnvOraSid=$1
_TnsString=$2
#
#----------------------------------------------------------------------------
# Verify that the ORACLE_SID is registered in the ORATAB file...
#----------------------------------------------------------------------------
if [ ! -x /usr/local/bin/dbhome ]
then
	_Echo warning "${_Pgm}: \"/usr/local/bin/dbhome\" does not exist"
	exit 2 > /dev/null 2>&1
fi
/usr/local/bin/dbhome ${_EnvOraSid} > /dev/null 2>&1
if (( $? != 0 ))
then
	_Echo warning "${_Pgm}: \"${_EnvOraSid}\" not local to this host"
	exit 2 > /dev/null 2>&1
fi
#
#----------------------------------------------------------------------------
# Set the Oracle environment variables for this database instance and then
# verify that they are all set "sanely"...
#----------------------------------------------------------------------------
if [ ! -x /usr/local/bin/oraenv ]
then
	_Echo warning "${_Pgm}: \"/usr/local/bin/oraenv\" does not exist"
	exit 2 > /dev/null 2>&1
fi
#
export ORACLE_SID=${_EnvOraSid}
export ORAENV_ASK=NO
. /usr/local/bin/oraenv > /dev/null 2>&1
if (( $? != 0 ))
then
	unset ORAENV_ASK
	_Echo warning "${_Pgm}: \"/usr/local/bin/oraenv ${_EnvOraSid}\" failed"
	exit 2 > /dev/null 2>&1
fi
unset ORAENV_ASK
#
if [[ "${ORACLE_HOME}" = "" ]]
then
	_Echo warning "${_Pgm}: \"ORACLE_HOME\" not set"
	exit 2 > /dev/null 2>&1
fi
if [ ! -d ${ORACLE_HOME} ]
then
	_Echo warning "${_Pgm}: directory \"${ORACLE_HOME}\" not found"
	exit 2 > /dev/null 2>&1
fi
if [ ! -d ${ORACLE_HOME}/bin ]
then
	_Echo warning "${_Pgm}: directory \"${ORACLE_HOME}/bin\" not found"
	exit 2 > /dev/null 2>&1
fi
if [ ! -x ${ORACLE_HOME}/bin/sqlplus ]
then
	_Echo warning "${_Pgm}: program \"${ORACLE_HOME}/bin/sqlplus\" not found"
	exit 2 > /dev/null 2>&1
fi
#
#----------------------------------------------------------------------------
# Connect to SQL*Plus via an impossible username (expected result is
# ORA-01017 error) and wait for a maximum of 10 seconds for the ORA-01017
# error to emerge...
#----------------------------------------------------------------------------
${ORACLE_HOME}/bin/sqlplus -s /nolog << __EOF__ > ${_OutFile} 2> ${_ErrFile} &
whenever oserror exit failure
whenever sqlerror exit failure
connect sfkjasdfljsdfasdf0/fdsafdsjlfdsajkfs0@{_TnsString}
exit success
__EOF__
#
#----------------------------------------------------------------------------
# After SQL*Plus is sent to the "background", poll the spooled output file
# for the expected error messages.  An "ORA-01017" represents a successful
# database connection, an "ORA-12541" represents TNS Listener down.  Any
#----------------------------------------------------------------------------
integer _SecsWaited=0
integer _Rtn=3
while (( ${_SecsWaited} <= 10 ))
do
	sleep 1
	integer _SecsWaited=${_SecsWaited}+1
	if grep "ORA-01017:" ${_OutFile} > /dev/null 2>&1
	then
		integer _SecsWaited=99999
		integer _Rtn=0
	else
		if grep "ORA-12541:" ${_OutFile} > /dev/null 2>&1
		then
			integer _SecsWaited=99999
			integer _Rtn=4
		else
			if grep "ORA-" ${_OutFile} > /dev/null 2>&1
			then
				integer _SecsWaited=99999
				integer _Rtn=5
			fi
		fi
	fi
done
#
#----------------------------------------------------------------------------
# Interpret the results of the SQL*Plus connection attempt...
#----------------------------------------------------------------------------
case ${_Rtn} in
	0)	rm -f ${_OutFile} ${_ErrFile} ;;
	3)	_Echo failure "SQL*Plus not responding" ;;
	4)	_Echo failure "TNS Listener not responding" ;;
	5)	_Echo failure "Other database error - check \"/tmp/chk_db_up_$$.*\"" ;;
esac
#
#----------------------------------------------------------------------------
# Return the exit status...
#----------------------------------------------------------------------------
exit ${_Rtn} > /dev/null 2>&1