oracle status etc..

From: Petter Henrik Hansen <petter_at_fdmetd.UUCP>
Date: 25 May 94 16:44:30 GMT
Message-ID: <4253_at_fdmetd.UUCP>

Just a little unix bsh script in which you may check the database status.

The following will be checked.

1. Does at least 4 (5) oracle processes exist?
2. Does the sgadef file exist?

########### CUT HERE ###############
# Function name ora_stat
#
# Written by: Petter Henrik Hansen (DBA)
#
# E-Mail: P.H.Hansen_at_fdsw.no
#
# Compuserve: 100121,2076
#
# Address FD Software AS
# Hovfaret 11
# Postboks 145 Skoyen
# 0212 Oslo
# Norway
#
# If you decide to use this script, please notify the author. If there
# is enough interest I may send out some of my other little
# solutions. 8-)
#
# Please return to the author suggestions for improvements and
# extended functionality. Contributors will be credited.
#
# Please give the author the credit if you decide to use this
# script. It may be freely distributed etc......
#
# Functions: -a $ORACLE_SID Show status of selected database
# -u $ORACLE_SID -p $PASSWD Show logged on users
# You must enter the password for the
# oracle user system
# -e $ORACLE_SID Show info about the status
# -h $ORACLE_HOME Show oracle home
#
# Return values: 0 Database running
# 1 Database not running
# 2 sgadef file exists but no processes is
# running
# 3 Sgadef file does NOT exist but processes is
# still running
#
# Problems: The ps -ef option does not exist on all unix platforms.
# Please edit this to your liking.
#
# Please configure the following 3 lines if needed

ORATAB=/etc/oratab	# Name of the oratab file
ORACLE_OWNER=oracle	# Owner of the oracle database
NR_PROCS=4		# Number of oracle processes (pmon,smon,arch ....)

# Some definitions

DVN=/dev/null
TMP=/tmp/ora$$
trap 'rm -f ${TMP}?; exit ${EXVAL}' 0 1 3 15 DATABASE=""
for OPT in $*
do

        case ${OPT} in
        -a)     DB_CHECK=Y
                DATABASE=$2
		shift 2
                ;;
        -e)     EXAMINE=Y
                DATABASE=$2
		shift 2
                ;;
        -h)     ORHOME=Y
                DATABASE=$2
		shift 2
                ;;
        -u)     SHOWUSER=Y
                DATABASE=$2
		shift 2
                ;;
	-p)	# Password for the oracle system user
		SECC_WORD=$2
		shift 2
		;;
        esac

done

set ${DB_CHECK:=N}
set ${EXAMINE:=N}
set ${ORHOME:=N}
set ${SHOWUSER:=N}

ORACLE_HOME=`awk -F: "/^${DATABASE}:/ {print \\$2; exit}" $ORATAB 2>$DVN`

if [ "$DB_CHECK" = "Y" ]
then

	# Show database status
	. $ORACLE_HOME/.profile > $DVN 2>&1
	ORACLE_SID=$DATABASE
	# Check if the sgadef file exists
	if test -f $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.dbf
	then
		# Sgadef file exists. Does any processes exist?
		ps -ef > ${TMP}1
		set -- `cat ${TMP}1 | grep 'ora_...._'${ORACLE_SID} | wc -l`
		if [ "$1" -ge "$NR_PROCS" ]
		then
			# Database procs running (OK)
			EXVAL=0
			exit $EXVAL
		else
			# SGA file exists but no procs are running
			EXVAL=2
			exit $EXVAL
		fi
	else
		# sga file does not exist. Does any processes exist?
		ps -ef > ${TMP}1
		set -- `cat ${TMP}1 | grep 'ora_...._'${ORACLE_SID} | wc -l`
		if [ "$1" -ge "$NR_PROCS" ]
		then
			# Database procs still running (ERROR)
			EXVAL=3
			exit $EXVAL
		else
			# Database is down
			EXVAL=1
			exit $EXVAL
		fi
	fi

fi
if [ "$ORHOME" = "Y" ]
then

	# Show the ORACLE_HOME variable defined in oratab
	echo $ORACLE_HOME

fi
if [ "$SHOWUSER" = "Y" ]
then

	# Show active oracle users
        . $ORACLE_HOME/.profile > $DVN 2>&1
        ORACLE_SID=$DATABASE
        export ORACLE_SID
        # Show all Oracle users

$ORACLE_HOME/bin/sqlplus -silent <<-!
system/$SECC_WORD
set pagesize 300
set termout off
set heading off
set feedback off
select spid,terminal,username,program from sys.v_\$process ;
exit
!

        exit 0
fi

if [ "$EXAMINE" = "Y" ]
then

        # Examine database closer
        ORACLE_SID=${DATABASE}
        # Get ipcs status
        echo "Shared memory belonging to Oracle:"
        ipcs -m | grep ${ORACLE_OWNER}
        echo "Semaphores belonging to oracle:"
        ipcs -s | grep ${ORACLE_OWNER}
        echo
        echo "sgadef status"
        # "Oracle sgadef file:"
        ls -l $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.d*
        # MACHINE DEPENDENT
        #"Oracle processes:"
        ps -ef | grep ${ORACLE_OWNER} | grep ${ORACLE_SID}

fi

########### CUT HERE ###############
Petter Henrik Hansen Received on Wed May 25 1994 - 18:44:30 CEST