How to test oracle is up and running in remote machine using shell script

user4469127 Jul 11, 2010 7:00 AM

Currently Being Moderated

Hello,

I need help to write an linux script.

I have a java application server running on machine 192.168.5.20 and our oracle 10g standard 1.0.2 is running on machine 192.168.5.21.
I have found script to start oracle when computer is turned on.

But I cannot start my application server automatically since it depends on whether oracle is up and running or not on the remote machine.

Therefore, in case of power failure or maintenance, our client has to call us to start application server.

I would be grateful if someone can help me "How can I write script to check oracle is up and running in machine 192.168.5.21 from machine 192.168.5.20.

For your information, I have no oracle installed in application server. So tnsping won't work.

What I want is when power is back, application server script automatically and tesing for oracle server is up and running. If it is up, script will start application server.

Thanking you.

1. Re: How to test oracle is up and running in remote machine using shell script

fritshoogland Jul 12, 2010 12:48 AM (in response to user4469127)

Currently Being Moderated

your problem can be solved in multiple ways.

In my opinion the best way would be to have the application server try to build up a connection if it needs one, and retry whenever it needs one. if so, this would mean that you can startup the application server on startup, and just need the database to be up. If the database is down, the application will not work, but once you startup the database, the application will come up.

The most easy way to do make a script to test if a database is_really_ up, is installing the oracle client, and make a script which connects and issues a small query (select * from dual). tnsping is not sufficient, tnsping assures you the SID you want to connect to is in the listener list.

Another way is a small java program which uses jdbc to test database connectivity.
Report Abuse

Like (0)
2. Re: How to test oracle is up and running in remote machine using shell script

user4469127 Jul 12, 2010 12:57 AM (in response to fritshoogland)

Currently Being Moderated

Thank you for your reply.

Actually, I would like to know the code snipped in my script how I can connect and test the database is up and running. If you can provide me any help link, that would also be helpful.

Best regards.
Report Abuse

Like (0)
3. Re: How to test oracle is up and running in remote machine using shell script

fritshoogland Jul 12, 2010 1:01 AM (in response to user4469127)
Currently Being Moderated

I am not a java programmer, so I can't help you with that.

If you install an oracle client, and setup tnsnames.ora, a script would be:
sqlplus -S username/password@sid << _END select * from dual; exit; _END RC=$? if [ $RC -eq 0 ]; then echo "Database connection OK" else echo "Unable to reach database" fi
Report Abuse

Like (0)
4. Re: How to test oracle is up and running in remote machine using shell script

Dude! Jul 12, 2010 1:27 AM (in response to user4469127)
Currently Being Moderated

How about this:

ORACLE_SID=my_instance_name ORACLE_HOME=/my_path_to_oracle_home_directory LD_LIBRARY_PATH=$ORACLE_HOME/lib PATH=$ORACLE_HOME/bin:$PATH export PATH export ORACLE_HOME export ORACLE_SID export LD_LIBRARY_PATH ora_status=`echo 'set heading off select status from v$instance; exit' | sqlplus -s / as sysdba` if [ $ora_status = "OPEN" ]; then echo "Database is up and running..." fi
If you need to connect to another machine, then you need to change the sqlplus connect string to e.g. sqlplus -s "sys/password@servicename as sysdba". For this to work you need to have a password file on the remote computer and REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE. Please be aware that anybody listing process on your host computer can see the connect string including the password while the procedure is running.

Edited by: waldorfm on Jul 12, 2010 1:07 AM

Edited by: waldorfm on Jul 12, 2010 1:24 AM

corrected connect string as sysdba
Report Abuse

Like (0)
5. Re: How to test oracle is up and running in remote machine using shell script

fritshoogland Jul 12, 2010 1:07 AM (in response to Dude!)

Currently Being Moderated

Please mind "/ as sysdba" will only work locally, unless you grant remote sysdba access.
Report Abuse

Like (0)
6. Re: How to test oracle is up and running in remote machine using shell script

Dude! Jul 12, 2010 1:11 AM (in response to fritshoogland)

Currently Being Moderated

just noticed... thanks.
Report Abuse

Like (0)
7. Re: How to test oracle is up and running in remote machine using shell script

MiroslavK Jul 12, 2010 1:54 AM (in response to user4469127)

Currently Being Moderated

Try NAGIOS. You have a lot things to monitor, even from remote machine, you can even send alerts via email and SMS and the best of all, the software is open source..
Report Abuse

Like (0)
8. Re: How to test oracle is up and running in remote machine using shell script

user4469127 Jul 12, 2010 2:28 AM (in response to Dude!)

Currently Being Moderated

Thank you for the script. I already tested. it works.

But as I mentioned earlier, my oracle is running in another machine and i have two oracle instance running in the same network and Both have service name ORCL.

So in the script, can I mention host name anywhere?

Best regards.
Report Abuse

Like (0)
9. Re: How to test oracle is up and running in remote machine using shell script

Dude! Jul 12, 2010 7:57 AM (in response to user4469127)

Currently Being Moderated

From what I understand, that's where your local TNSNAMES.ORA file comes into play, which sqlplus will use to decode your @servicename connect string. You can define any local name pointing to the correct remote machine. Check out $ORACLE_HOME/network/admin/tnsnames.ora. Is this some kind of a fail-over setup, or experimental? Your host computers, each should actually have their own host name and the instance service names should be like orcl.host1.net and orcl.host2.net.
Report Abuse

Like (0)
10. Re: How to test oracle is up and running in remote machine using shell script

user4469127 Jul 13, 2010 12:57 AM (in response to Dude!)

Currently Being Moderated

Hi,

It's not failover or experimental. My application server uses JBOSS and JBOSS needs database name to start up. My enviroment is LAN. My database is in different machine as I mentioned before. So In case of power failure, I need to start my application server (JBoss) manually, because I need to confirm that oracle is already running before start app server.

I want an automated script (when power is just on) to start my application server which will wait to check if the datbase server is up I will start application server.

The scripts given above It works if appserver and db server in the same machine (my development machine).

But when I test the script in my production applicaiton server, It shows sqlplus command not found (my app server does not have any oracle client installed).

About hostname yes My applicaiton server hostname is hostapp1 and dabase server hostname is hostdat1.

Do you think Is it possible to test the script without oracle client not installed. or It has to be done writing code like java program and test using jdbc connection.

Best regards.
Report Abuse

Like (0)
11. Re: How to test oracle is up and running in remote machine using shell script

Dude! Jul 13, 2010 4:06 PM (in response to user4469127)

Currently Being Moderated

You will have to install Oracle software to be able to use the sqlplus client.

Or, you can use Java and JDBC to connect and query the oracle database. There are many examples, just google for "connect to oracle using jdbc".

Oracle also provides code examples:
http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/htdocs/templates.htm

To execute a SQL that returns a QUERY
ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

To open a connection using thin driver :
DriverManager.getConnection("jdbc:oracle:thin:@<mc-name>:<port-no>:<sid>",
"scott", "tiger");

The following may help you who make a jdbc connection as sysdba
Jython JDBC connect as sysdba
Report Abuse

Like (0)
12. Re: How to test oracle is up and running in remote machine using shell script

user4469127 Jul 14, 2010 4:34 AM (in response to Dude!)

Currently Being Moderated

Thanks for your posting. I will go for jdbc for connection test and then pass the result to the script and start application server.

Thank you all who have responded this thread.
Report Abuse

Like (0)

Go to original post

Actions

Legend

Correct Answers - 10 points
Helpful Answers - 5 points