If Oracle DB Connection goes down,it should reconnect the DB again
Hi All,
I have a serious problem while running java process all the day. Sometimes I get following errors:
1. (***ERROR) java.sql.SQLException: OALL8 is in an inconsistent state.
2. (***ERROR) java.sql.SQLException: Io exception: Broken pipe
(***ERROR) java.sql.SQLException: Io exception: Broken pipe
(***ERROR) java.sql.SQLException: Closed Connection
3. (***ERROR) java.sql.SQLException: No more data to read from socket.
The above exceptions are not coming everyday.But sometimes it gives exceptions and because of that my java process will not run until it restarts the program.
I do know, because of oracle server down some of exceptions are coming. But what I wanted to do is if once the oracle server is up , my java application has to reconnect to the DB.
My idea is when java throws SQLException because of oracle server down, in the exception block it has to reconnect to the DB again by checking for right SQLERROR.
I am using JRE 1.4.2_10, JDBC Thin driver and Oracle 10g.
About Oracle DataSource am using JDBC 3.0 concepts which includes
OracleConnectionCacheManager and
Oracle DataSource
Can anybody help me on this to resolve the above exceptions.
Thanks in advance.
[1288 byte] By [
OPMa] at [2007-11-27 6:46:41]
# 2
Hi ,
Thanks for the reply.
As my understanding, I use Implicit connection Cache which will keep number of required connections in the Pool. Whenever Java requires connection, it will take it from connection pool. Connection pool will give a wrapper connection(wrapper around physical connection). After finishing the work java will close the connection which means giving connection back to the Pool. But all the time physical connections will be available.
Actually, we are implementing Fast Connection Failover in our application. Wwhenever one server instance goes down, java has to map to another instance by taking all the required connections and when server backs up then oracle has to do load balancing with all the server instances.
In the Java application, it sends array of hundred records to oracle all the time. Is it because of sending huge arrays it is giving exception
java.sql.SQLException: OALL8 is in an inconsistent state.
Please help me out in solving the exceptions am getting.
Thank you.
OPMa at 2007-7-12 18:19:05 >
# 3
> Actually, we are implementing Fast Connection Failover in our
> application. Wwhenever one server instance goes down, java has to
> map to another instance by taking all the required connections and
> when server backs up then oracle has to do load balancing with all
> the server instances.
So you do already solve connection problem, right?
> In the Java application, it sends array of hundred records to oracle all
> the time. Is it because of sending huge arrays it is giving exception
> java.sql.SQLException: OALL8 is in an inconsistent state.
> Please help me out in solving the exceptions am getting.
i think the exception raise not because you sending a huge arrays,
but because the connection broken in the middle of sending array.
you can get another connection when that exception occurs.
i'm not sure... i hope this can help you about handling that exception :
boolean success = false;
// will repeatly get available any connection until your transfer successfull
// or datasource does not has connection available anymore.
while (!success && isConnectionAvailable()) {
Connection c = null;
// Statement s = null;
// ResultSet r = null;
try {
c = ds.getConnection();
c.setAutoCommit(false);
// send your array here
// only commit when all data sent
c.commit();
c.setAutoCommit(true);
success = true;
} catch (SQLException sqlEx) {
if (sqlEx.getErrorCode() == SERVER_DOWN) {
// not sure this is needed...
// when connection broken your partial sent data will not saved in db
// because we set off auto-commit.
// & actually calling rollback when connection is broken only raise
// another sql exception ;P
if (c.isValid(0)) c.rollback();
// log exception information
}
} finally {
// close result set
// close statement
DbUtils.closeQuietly(c);
}
}
