-
1. Re: How to test oracle is up and running in remote machine using shell script
your problem can be solved in multiple ways.1 person found this helpful
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. -
2. Re: How to test oracle is up and running in remote machine using shell script
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. -
3. Re: How to test oracle is up and running in remote machine using shell script
I am not a java programmer, so I can't help you with that.1 person found this helpful
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 -
4. Re: How to test oracle is up and running in remote machine using shell script
How about this:1 person found this helpful
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.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
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 -
5. Re: How to test oracle is up and running in remote machine using shell script
Please mind "/ as sysdba" will only work locally, unless you grant remote sysdba access. -
6. Re: How to test oracle is up and running in remote machine using shell script
just noticed... thanks. -
7. Re: How to test oracle is up and running in remote machine using shell script
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.. -
8. Re: How to test oracle is up and running in remote machine using shell script
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. -
9. Re: How to test oracle is up and running in remote machine using shell script
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.1 person found this helpful -
10. Re: How to test oracle is up and running in remote machine using shell script
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. -
11. Re: How to test oracle is up and running in remote machine using shell script
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 -
12. Re: How to test oracle is up and running in remote machine using shell script
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.