how to check database status

[Paul_481]

Hi,

I have a script that starts the oracle database. I want to enhanced this by checking first the database if it is up or down.

If it is up, the script executes. If not the script ends.

I was thinking of the background oracle process. Are there any effective way of checking the status that can be compare to and incorporated on the scrit?

Regds,
Paul

[Ranjith_5 · Accepted solution]

HI Paul,

Use

#ps -ef|grep pmon

This will give a similar output.

oradb 2835 1 0 Feb 12 ? 0:22 ora_pmon_SPICE


Where "SPICE" is the instance ORACLE_SID at my place.


Regards,
Syam

[Paul_481]

so if the pmon process is not present then i can safely assume that the database is down?

rgds,
paul

[Ranjith_5]

Yes.

That's all what I used to do when I need to shutdown the server for a cold backup.

Regards,
Syam

[Ravi_8]

Hi,

Not only pmon represent oracle DB, other processes like dbwr,logwr,smon also represent. check for these process also.
We prefer looking into listerner
#ps -aef|grep -i lsnr

[Ranjith_5]

Hi Paul/Ravi,

I am mentioning a scenerio where you have a proper db shutdown. Log writer, data base writer and SMON will be stopped after issuing "shutdown immediate" in the 'SQL>" propmt. This case checking of PMON only also will do the purpose. Also you can check the processes related to your ORACLE_SID.
Paul, If you can share the purpose of the script, we can help you in a better way.

regards,
Syam

[Paul_481]

Hi Sya,

Actually this is the pre-exec of our DP5 backup. We have a failed backup before due to script error. The current pre-exec script shutdowns the database and the post-exec restart the database.

What we found out was when database is down and the backup starts and the pre-exec script that shutdown the database runs our DP5 backup fails.

Rgds,
Paul

[Paul_481]

Hi,

Actually this is the pre-exec of our DP5 backup. We have a failed backup before due to script error. The current pre-exec script shutdowns the database and the post-exec restart the database.

What we found out was when database is down and the backup starts and the pre-exec script that shutdown the database runs our DP5 backup fails.

Rgds,
Paul

[Ranjith_5]

Hi paul,

That means , the script is for backup related activity. In that case checking of only PMON will do your purpose.You can also check SMON but not necessory in an ideal condition.

All the data base related processes ( LOGWR,DBWR, and SMON) will be down in a normal scenerio, if the Process MONitor( PMON) is down. Also LOGWR and DBWR are not running as a daemon in unix.ie; you cant find it running/not using ps comand. At the same time SMON status cn be viewed using

#ps -ef|grep -i smon

regards,
Syam

[Paul_481]

Ok, thanks everyone

[Jean-Luc Oudart]

Hi Paul,

The listener won't tell you if the database is up and down.
ususally I would use (like others) the pmon process

ps -ef | grep [p]mon | grep
where is the oracle SID for the database.

I had in the past issues with pre-exec script as they never finished and the backup timed out. The script does a "shutdown immediate" but I had some long operation(s) still running , hence the issue.
One possibility is to kill all the client processes beforehand, but again if this is a long operation the rollback may take sometimes.

Regards
Jean-Luc

[Rick Garland]

All of the above works fine.

What I do, check for the 'pmon'
ps -ef | grep pmon

I will also SQL intop the database and do a short, simple select to ensure that I can get info from the database.
Example
SQL> select * from dba_users;

Use a table that you know is going to be short.

Do a login from a remote terminal to check the accessibility of database via the listener.

[Geoff Wild]

Also, you can use the oracle utility called tnsping.

Also check out:

http://www.oracleutilities.com/OSUtil/ping.html

Rgds...Geoff