Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm configuring the boot sequence on a Linux box, and one of the startup applications is dependent upon an Oracle instance being available on a different box. I would like a bash scriptable tool to check the availability of the Oracle instance. Likely, the power button on both boxes will have been hit within seconds of each other, and I need the longer Oracle process to finish before my application attempts to connect.

share|improve this question

1 Answer

up vote 2 down vote

The only way to be sure that the remote database is up and that the listener is up and that the database is registered with the listener properly would be to actually make a connection. You could use the SQL*Plus utility (assuming the Oracle client is installed on the linux box your application runs on) to attempt to make a connection. Something like

Create a file check_db_up.sql

whenever sqlerror exit 1;
connect username/password@<<TNS alias>>
select 1 from dual;
exit 0;

Then invoke this script in your bash shell script and look at the return code

sqlplus /nolog @check_db_up.sql

If that returns 1, there was an error and the database isn't up. If it returns a 0, the database is up and accepting connections.

share|improve this answer
Why not just use tnsping? docs.oracle.com/cd/B19306_01/network.102/b14212/… – FrustratedWithFormsDesigner Feb 15 '12 at 14:44
2  
@FrustratedWithFormsDesigner - tnsping doesn't actually tell you whether the remote database is up and accepting connections. It tells you that you can reach the listener and that the listener would be prepared to pass you off to the database if you tried to connect but it's possible that the listener would be up and running while the database is not accepting connections. – Justin Cave Feb 15 '12 at 14:59
Ah, I didn't know that. Thanks for the info! :) – FrustratedWithFormsDesigner Feb 15 '12 at 15:02
@FrustratedWithFormsDesigner - Now, I should note that in this particular instance, assuming that the listener is running on the same machine as the database, using tnsping would work the vast majority of the time. But I don't think tnsping returns different status codes for success or failure so you'd probably have to parse the output in the shell script which would be more of a pain. – Justin Cave Feb 15 '12 at 15:05
1  
@FrustratedWithFormsDesigner - You can certainly use grep. It's just more logic to add to the shell script and more ways for something to get screwed up (the simplest approach would be to grep for the string "OK" without accounting for the fact that the TNS alias that gets echoed back might occasionally have the string "OK" in it). And there is no need (necessarily) to store the password-- you can always use operating system authentication in your CONNECT (assuming you set up an appropriate user on the remote database). – Justin Cave Feb 15 '12 at 15:11
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.