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

[Suman_7]

I have to test for a condition whether the 3 oracle databases are up or not. Can you please suggest me an easy way to do it

Thanks

[Jeff_Traigle]

What database (Oracle, DB2, MySQL, Sybase, etc.)? Trying to check from the database server itself or from another system?

[Jeff_Traigle]

Nevermind... you said Oracle.

Can do it this way...

DBS="inst1 inst2 inst3"

#!/usr/bin/sh

for DATABASE in $DBS
do
tnsping inst1
done

(You'll want to check the syntax of that tnsping command... we don't run Oracle at my new job. It also assumes you have ${ORACLE_HOME} in your path.)

[Mobeen_1]

Suman,
It depends on how and from where you would like to check your database instances. For example, would you like to check from the host level or database level or ???

In any case, the following are some ways in which you can check

1. Ping the instance name (if there is a host entry for the instance)

2. Write a simple script that would log into
your database instance and check for the
dB availability

3. You could also monitor for the database
processes to determine the availability
of each of the database instance

regards
Mobeen

[Jeff_Traigle]

Gee... I'm having a difficult time... the DBS definition shou,d of course, be after the shell specification.

[Suman_7]

The TNSPING gives :

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1546))) (CONNECT_DATA = (SID = )))
OK (40 msec)

DO I need to grep for OK?

Thanks

[Sanjay_6]

Hi,

How about,

ps -ef |grep pmon |grep -v grep |wc -l

This should give "3" as the return value if the three oracle databases are running on the system.

Hope this helps.

Regds

[Sundar_7]

Hi Suman,

You can try this

DBS="inst1 inst2 inst3"
for INST in $DBS
do
tnsping $INST 1>/dev/null 2>&1
if [[ $? -eq 0 ]]
then
echo "Instance $INST is running"
else
echo "Instance $INST is not running"
fi
done

The above example will work fine as long as tnsping is not failing for any other reason.

-- Sundar.

[Anil C. Sedha]

Suman,

Just grep for your oracle DB name by doing a ps -ef
edit a file in /etc/db and specify your dbnames in it. Save the file

for dbname in $(cat -v /etc/db)
ps -ef | grep dbname
if [ $? = 0 ]; then
echo "dbname database is up"
else
echo "dbname database is not running"
fi
done


-Anil

[Anil C. Sedha]

Hit the submit button too quick

for dbname in $(cat -v /etc/db)
ps -ef | grep $dbname
if [ $? = 0 ]; then
echo "$dbname database is up"
else
echo "$dbname database is not running"
fi
done

-Anil

[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.