Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Suman_7
Frequent Advisor

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

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
15 REPLIES
Jeff_Traigle
Honored Contributor

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

What database (Oracle, DB2, MySQL, Sybase, etc.)? Trying to check from the database server itself or from another system?
--
Jeff Traigle
Jeff_Traigle
Honored Contributor

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

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.)
--
Jeff Traigle
Mobeen_1
Esteemed Contributor

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

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
Honored Contributor

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

Gee... I'm having a difficult time... the DBS definition shou,d of course, be after the shell specification.
--
Jeff Traigle
Suman_7
Frequent Advisor

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

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
Honored Contributor

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

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
Honored Contributor

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

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.
Learn What to do ,How to do and more importantly When to do ?
Anil C. Sedha
Trusted Contributor

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

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
If you need to learn, now is the best opportunity
Anil C. Sedha
Trusted Contributor

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

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
If you need to learn, now is the best opportunity
Sundar_7
Honored Contributor

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

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

Learn What to do ,How to do and more importantly When to do ?
T G Manikandan
Honored Contributor

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

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
Honored Contributor

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


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
Regular Advisor

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

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
Time's fun when your having flys (ancient frog saying)
Kartik Gajjar
Occasional Visitor

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

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
Honored Contributor

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

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.