How to check if db instance is working? 1

[tomecki24]

Hello,

I've got to start a script after db instance is started. How to check if db instance, for example Mydb, is working?

Thanks
Tomasz

 

[lweng]

From svrmgrl or sqlplus
select instance, status from v$thread;

 

[carp]

From a Unix (and possibly Linux) operating system, try
ps -ef|grep your_instance_name

If the instance is up, you will get the instance background processes that contain your instance name returned.

 

[tomecki24]

hello,

ok, thanks. But how to check it inside a script? I mean, when I start computer(Solaris) I start the db instance and then I start a script, which is based on just started database. So, I wonder if there is something like:
if (db instance run)
then (do my stuff)

Thanks
Tomasz

 

[SeattleDBA]

If you are looking for a simple way to have some assurance that the database started and is functioning I would suggest two things.
1.) ps -ef | grep smon
(the database wont run without this process)

2.) actively monitor the alert_log file for the instance.

 

[THoey]

I have been following this thread, since I need the same answer. I have a stored procedure in one Oracle database that truncates some static tables and then refreshes them with the data from 2nd database. Obviously, if the 2nd database is not available, I do not want to truncate the tables in the first database.

There has to be some way in code (SQL) to connect to and check to see if an instance is up and running. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[SeattleDBA]

Within pl/sql you will need to do some error handling by using SQLERRM and/or SQLCODE... since I am not a developer I will leave the actual syntax in your hands but they are well documented.

select status from v$thread
(should return OPEN)

good luck.

 

[tomecki24]

Hello,

I found on other forum script, that matches my request:
ps -ef | grep "ora_smon_$ORACLE_SID" |grep -v "grep" > /dev/null
if [ "$?" -ne 0 ]
then
echo "`date '+%T %m/%d/%Y'` : Database $ORACLE_SID not running on `uname -
n`."
exit 1
fi

Tomasz

 

[raygg]

What would the equivalent for NT be?

 

[carp]

I don't think there is an NT equivilent. This is because Unix has the individual background processes (SMON, PMON, DBWR, LGWR, ARCH, etc) running separately. On NT, these processes are being run under the umbrella of the Oracle service. Unfortunately, just because the service is running does not mean that the instance is up or the database is open.
One possibility would be to have a nested block of code that tries to select * from dual. If you get any error out of this query, there's a really GOOD chance that the database isn't available!