###############################################################################
#
# Synopsis:	db_check.sh [-b] [sid ...]
# Purpose:	to check the health of database instances
#
# Author:	Steve Adams (based on various similar scripts)
#
# Description:  This script checks the named instances, or all instances
#		listed in the oratab file with a Y in the third field.
#		The -b option should be used during the backup window.
#
#		It may be used in 3 ways:
#		* interactively
#		* from cron
#		* from other scripts
#
#		The following checks are performed:
#		* a listener must be running
#		* each instance must be available
#		* instances must not be in resticted session mode
#		* auto-archiving must be enabled in archivelog mode
#		* archiver processes must not be stuck
#		* tablespaces must not be in hot backup mode
#		During the backup window, instances may be down, or in
#		restricted session mode, and tablespaces may be in hot
#		backup mode.
#		
#		If any checks fail, they are reported:
#		* to the terminal, if running interactively
#		* to the system log via logger (syslog)
#		* with a non-zero exit status
#
###############################################################################

# set variables
#
export PROGRAM=${0##*/}			# program name
export ORATAB				# path to the oratab file
export INTERACTIVE=:			# whether to print messages to stdout
export DEBUG=				# whether to actually log messages
# export DEBUG=print			# (uncomment this line while debugging)
export FACILITY=oracle			# change to "user" if logger complains
export BACKUP=false			# are we in the backup window
export ORACLE_SID=			# the instance being checked
export SPOOL=/tmp/$$.spool		# the spool file for output
export READY=/tmp/$$.ready		# file to flag that output is ready
export STATUS=0				# exit status


# find the oratab file
# if none, assume no Oracle and quit
#
{ ORATAB=/etc/opt/oracle/oratab && [[ -r $ORATAB ]] ; } ||
{ ORATAB=/var/opt/oracle/oratab && [[ -r $ORATAB ]] ; } ||
{ ORATAB=/etc/oratab            && [[ -r $ORATAB ]] ; } ||
exit 0


# are we running interactively?
# if not, we must have logger
#
tty -s && INTERACTIVE=print || whence logger > /dev/null || exit 1


# check that a listener is running
#
if ps -fu oracle | grep -v grep | grep -c tnslsnr >/dev/null 
then
    $INTERACTIVE "Listener is running"
else
    msg="$PROGRAM: Listener not running - no network access to Oracle"
    $DEBUG logger -p $FACILITY.err "$msg"
    STATUS=1
    $INTERACTIVE $msg
fi


# check for oraenv
#
whence oraenv > /dev/null ||
{
    msg="$PROGRAM: Cannot check Oracle - oraenv not in PATH"
    $DEBUG logger -p $FACILITY.warning "$msg"
    $INTERACTIVE $msg
    exit 1
}


# are we in the backup window
#
[[ $1 = -b ]] && { BACKUP=true; shift; }


# check specified instances or all auto-started instances
#
for ORACLE_SID in ${*-$(awk -F: '!/^#/ && $3=="Y" {print $1}' $ORATAB)}
do                           
    # set environment and check for sqlplus
    #
    ORAENV_ASK=NO . oraenv                   
    whence sqlplus > /dev/null ||
    {
	msg="$PROGRAM: Cannot check Oracle - sqlplus not in PATH"
	$DEBUG logger -p $FACILITY.warning "$msg"
	STATUS=1
	$INTERACTIVE $msg
	continue
    }


    # check connectivity for an ordinary user
    # (we expect an ORA-01017 error; most others mean something)
    #
    rm -f $READY
    print "
	connect nobody/really
	host touch $READY
	exit " |
    sqlplus /nolog > $SPOOL &

    # wait for up to 59 seconds
    #
    ((timeout = 60))
    while ((timeout -= 1)) && [[ ! -r $READY ]]
    do
	sleep 1
    done

    # check for hang
    #
    [[ -r $READY ]] ||
    {
	kill $!
	msg="$PROGRAM: Oracle instance $ORACLE_SID is not responding"
	$DEBUG logger -p $FACILITY.err "$msg"
	STATUS=1
	$INTERACTIVE $msg
	continue
    }

    # check for other problems
    #
    ERROR=$(sed -n 's/.*ORA-\([0-9]*\):.*/\1/p' $SPOOL|head -1)
    case $ERROR in
	    #
	    # this is what we expect
	    #
    01017)  # invalid username/password
	    ;;
	    #
	    # this group are not expected, but OK anyway
	    #
    "")     # no error (connected OK)
	    ;;
    01035)  # instance in restricted session mode
	    ;;
    01040)  # invalid password
	    ;;
    01045)  # no create session priv
	    ;;
	    #
	    # any other error is a problem
	    #
    00257)  # archiver stuck
	    #
	    msg="$PROGRAM: Oracle archiver for $ORACLE_SID is stuck"
	    $DEBUG logger -p $FACILITY.alert "$msg"
	    STATUS=1
	    $INTERACTIVE $msg
	    ;;
    01034)  # Oracle not available
	    #
	    msg="$PROGRAM: Oracle instance $ORACLE_SID is not up"
	    $BACKUP || $DEBUG logger -p $FACILITY.err "$msg"
	    $BACKUP || STATUS=1
	    $BACKUP || $INTERACTIVE $msg
	    continue
	    ;;
    *)      # something else
	    #
	    msg="$PROGRAM: Got Oracle error ORA-$ERROR from $ORACLE_SID"
	    $DEBUG logger -p $FACILITY.err "$msg"
	    STATUS=1
	    $INTERACTIVE $msg
	    continue
	    ;;
    esac
    $INTERACTIVE "Oracle instance $ORACLE_SID is up"

    # now connect internal and check everything else
    #
    { SVRMGRL=svrmgrl		&& whence $SVRMGRL > /dev/null ; } ||
    { SVRMGRL=sqldba		&& whence $SVRMGRL > /dev/null ; } ||
    {
	msg="$PROGRAM: Cannot check Oracle - svrmgrl not in PATH"
	$DEBUG logger -p $FACILITY.warning "$msg"
	STATUS=1
	$INTERACTIVE $msg
	continue
    }
    rm -f $READY
    print "
	connect internal
	select '/actives=' || count(*) from v\$backup where status = 'ACTIVE';
	select '/restrict=' || value from v\$instance where key like 'RESTR%';
	select '/restrict=' || logins from v\$instance;
	archive log list;
	host touch $READY
	exit " |
    $SVRMGRL > $SPOOL &

    # wait for up to 89 seconds
    #
    ((timeout = 90))
    while ((timeout -= 1)) && [[ ! -r $READY ]]
    do
	sleep 1
    done

    # check for hang (most unlikely at this point)
    #
    [[ -r $READY ]] ||
    {
	kill $!
	msg="$PROGRAM: Oracle instance $ORACLE_SID is not responding"
	$DEBUG logger -p $FACILITY.err "$msg"
	STATUS=1
	$INTERACTIVE $msg
	continue
    }

    # check for active backups, archiver and restricted session
    #
    eval $(sed -n 's:^/\(.*=.*\):\1:p' $SPOOL)
    [[ $actives -eq 0 ]] ||
    {
	msg="$PROGRAM: Database $ORACLE_SID has files in hot backup mode"
	$BACKUP || $DEBUG logger -p $FACILITY.warning "$msg"
	$BACKUP || STATUS=1
	$BACKUP || $INTERACTIVE $msg
    }
    eval $(awk '/Database log mode/ {print "mode=" $(NF-2)}
		/Automatic archival/ {print "auto=" $NF}' $SPOOL)
    [[ $auto = Enabled || $mode = No ]] ||
    {
	msg="$PROGRAM: Oracle archiver for $ORACLE_SID is not running"
	$DEBUG logger -p $FACILITY.err "$msg"
	STATUS=1
	$INTERACTIVE $msg
    }
    [[ $restrict = 0 || $restrict = ALLOWED ]] ||
    {
	msg="$PROGRAM: Oracle instance $ORACLE_SID is in restricted mode"
	$BACKUP || $DEBUG logger -p $FACILITY.warning "$msg"
	$BACKUP || STATUS=1
	$BACKUP || $INTERACTIVE $msg
    }
done

# cleanup
#
$DEBUG rm -f $SPOOL
$DEBUG rm -f $READY

exit $STATUS