Re: How to check whether the Oracle database is up?

From: bards <bards1888_at_yahoo.com.au.au>
Date: Fri, 30 Aug 2002 10:50:18 GMT
Message-ID: <newscache$h3kn1h$8u8$1@elise.onthenet.com.au>

Billy Verreynne wrote:
> Sri wrote:
>
>

>>Using a script, I want to check whether my database instance has been
>>started and mounted or not.
>>What is the best way to do this?

>
>
> Platform?
>
> On Unix, you can do a ps and grep for all processes for the Oracle user and
> all processes for the applicable SID.
>
> Or you can try a SQL*Plus connection and check STDERR/STDOUT to see if the
> connection fails, something like:
> # sqlplus xxx/xxx < /dev/null | grep ORA
>
> If the result is not a ORA-01017 (invalid username/password) then the
> database is likely down, in restricted mode, hanging because archive logs
> are full, busy with a shutdown, or whatever. Whatever it is, it is
> definately not publicly available for client connections.
>
> Or you can pipe the output of (ex-)server manager to log when the db start
> is done and then inspech the log with a few well places awk's and grep's.
>
>
> --
> Billy

How about something like for unix, bear in mind 'garbage in = garbage out' ;

#!/bin/ksh
#
# Oracle status script
#
# Joe Bloggs
# A company,
# 30/08/2002
#

# OVERVIEW
# ========
# Checks for the existance of a 'pmon' process for the given ORACLE_SID
# this is oracle's recommended way of determining whether an instance
# is up.
#
# Rule being, if there is no pmon there *cannot* be a running instance !
#
# Return Codes :        0       selected instance is UP
#                       1       selected instance is DOWN

#

#
# Functions
#

usage()
{

         echo Usage : `basename $0` ORACLE_SID
         exit 1

}

#
# Main
#

# we require a valid ORACLE_SID
if [ $# -ne 1 ]; then

         usage
fi

ORACLE_SID=$1 ps -ef | grep [o]ra_pmon_$ORACLE_SID$ > /dev/null 2>&1 ret=$?

if [ $ret -eq 0 ]; then

         # Oracle instance $ORACLE_SID appears to be up
         exit 0
else
         # Oracle instance $ORACLE_SID appears to be down
         exit 1

fi Received on Fri Aug 30 2002 - 05:50:18 CDT