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

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sat, 31 Aug 2002 18:02:46 +1000
Message-ID: <IB_b9.19620$g9.60491@newsfeeds.bigpond.com>

Hi Pete,

Quick way is to just check STATUS in V$INSTANCE (available since Oracle8):

STARTED - Nomounted

MOUNTED - Mounted

OPEN - Open

Cheers

Richard
"Pete Sharman" <peter.sharman_at_oracle.com> wrote in message news:ako5ef0kbu_at_drn.newsguy.com...
> In article <newscache$h3kn1h$8u8$1_at_elise.onthenet.com.au>, bards says...
> >
> >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
> >
> >

>
> All well and good if the code works (I'm not a Unix afficiniado if that's
spelt
> correctly!), but the problem with this and some of Billy's suggestions are
they
> don't actually test the state of the database, just the instance.
Remember the
> sequence with which things are started:
>
> 1. STARTUP NOMOUNT - Parameter file read, SGA allocated, background
processes
> started.
> 2. STARTUP MOUNT - Same as above, plus the control file is opened to read
the
> lcoations of the remaining database files (not verified yet)
> 3. STARTUP OPEN - Same as 2, but now we touch all the files to make sure
> they're there and allow users to connect.

>

> The only way to check each individual stage that I'm aware of is:
>
> 1. STARTUP NOMOUNT - look for a fixed view that's available then (like
V$SGA)
> and another one that's not until the database is mounted (can't remember
the
> list here - maybe V$CONTROLFILE? Check the doc to be sure). Trap the
error on
> the second fixed view.
> 2. STARTUP MOUNT - look for the second fixed view and any DBA_ view.
Trap the
> error returned on the DBA view not being available yet.
> 3. STARTUP OPEN - check fo connectivity of a non-privileged user account
i.e.
> one without SYSDBA or restricted session privilege.

>

> HTH. Additions and corrections welcome.

>

> Pete

>

> SELECT standard_disclaimer, witty_remark FROM company_requirements;
> Received on Sat Aug 31 2002 - 03:02:46 CDT