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

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 Fri Aug 30 2002 - 11:10:55 CDT