Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to check whether the Oracle database is up?
In article <IB_b9.19620$g9.60491_at_newsfeeds.bigpond.com>, "Richard says...
>
>Hi Pete,
>
>Quick way is to just check STATUS in V$INSTANCE (available since Oracle8):
>
>STARTED - Nomounted
>
>MOUNTED - Mounted
>
>OPEN - Open
>
>Cheers
>
>Richard
Nah, too simple. I like my much more complicated way of doing things! :)
Pete
>"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;
>>
>
>
HTH. Additions and corrections welcome.
Pete
SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Sat Aug 31 2002 - 11:26:03 CDT
![]() |
![]() |