How to find if 3 databases are up and running from UNIX shell

[Sundar_7]

Also the presence of daemons ora_pmon_ and ora_smon_ indicates the instance is up and running.

[T G Manikandan]

you can grep for any oracle system process like

x=`ps -ef|grep pmon|grep -v grep|wc -l`
if [ -x -eq 3 ]
then
..
else
..

fi

[Hein van den Heuvel]

Suman,

IMHO you need to define for yourself what 'up' means precisely.

1) The instance started? That is readily seen by checking for a known system process like smon or pmon for the given name as prior replies indicate.
But strictly speaking that will NOT tell you whether the DB is open for business!
Granted, mostly the presence of those processes indicates a full start... but it might not.

2) OK, open and mounted? Well if it is in read-only / maintanance mode do you consider that up (for business) or down (for backup)?

3) Does TNSping react? Great, but I believe it will cheerfully return 'OK' even if the DB is down. It just indicated willingness to listen.
Granted, mostly the presence of an active listener indicateds a full start... but it might not.

4) Can you execute a local query? Now we are getting closer to indicating it is really up.
But if your access is all remote and the network is down, do you consider that 'up' or 'down'? At least you would have to execute a test query through tnsnames, invoking the listener and (parts of) the network stack.

5) 'up' for who, the oracle user or a production user?

I would encourage you to investigate a safe/repeatable business query, preferable an insert + commit form a 'normal' users over TNS ( user/pass@sid ).
Or at the very least a query like:
"select STATUS,STARTUP_TIME from v$instance;"


STATUS STARTUP_T
------------ ---------
OPEN 29-APR-04

(try a SELECT * from V$INSTANCE for other interesting fields)

hth,
Hein.

[Leon Allen]

This is how I do it (my 2 bobs worth :-)

Points to note:
- I know the names of my databases
- Lots of bells and whistles is a database is down
- executed every time the oracle account logs on (part of .profile)

# echo Database Check for "`uname -a ; date`"

for DATABASE in CHRISP F1PROD
do
# echo Checking $DATABASE - \\c

if [ "`ps -ef | awk '/'co_$DATABASE'/ && $8 !~ /awk/ {print $8}' -`" = "ora_re
co_$DATABASE" ] ; then
continue
# echo $DATABASE is up and running.
elif [ "`ps -ef | awk '/'co_$DATABASE'/ && $8 !~ /awk/ {print $9}' -`" = "ora_
reco_$DATABASE" ] ; then
# echo $DATABASE is up and running.
continue
else
echo **DATABASE ALERT! \\a \\a \\a
echo **DATABASE ALERT! The $DATABASE Database is DOWN! \\a \\a \\a
echo **DATABASE ALERT! \\a \\a \\a
fi
done

[Kartik Gajjar]

Put following into a shell script...

----------- Cut ----------
sh << ++++
echo '\n\nFollowing Database are running:\n'
ps -ef|grep pmon|cut -c 58-
exit
----------- Cut ----------

[Hein van den Heuvel]

Despite two more replies (Katrik, Leon)suggesting the opposite I still dissagree with just testing for a known DB process.
pmon, dbw0,lgwr,ckpt,smon,reco,arc0... They are all there after a 'startup nomount' while the DB is still useless for the application.
They are also already there when certain filesystem/IO errors fail to allow Oracle to Mount and Open (the next phases in startup).
These are exactly the cases where you would want to help the end user as: 'it looks like it is up, but it is not'. (you know, those case where you needed a shutdown abort to get thinkgs moving again)

btw... You may want to consider to tail/grep the alert log(s) for succesfull start. Depending on whether you log checkpoint, just look in the last (few) hunderd lines of the alert log checking for "Completed: ALTER DATABASE". Maybe
tail -500 alert-softlink | grep "Completed: ALTER DATABASE" | tail -1

But again the above does not protect against real interesting failure (node crash, kill -9)

Cheers,
Hein.