This content has been marked as final. Show 12 replies
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.
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
How about this:
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
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.
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.
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:
To execute a SQL that returns a QUERY
ResultSet rset = stmt.executeQuery ("select ENAME from EMP");
To open a connection using thin driver :
The following may help you who make a jdbc connection as sysdba
Jython JDBC connect as sysdba