What to do when an Oracle connection dies?

[davidnash]

I've been developing a small web app in jBoss with Oracle as the DB. When the database goes down for backups at night, the DB connection in the jBoss connection pool becomes invalid. Then the next morning, the first person using the application gets a javax.transaction.HeuristicRollbackException (root cause: Connection reset by peer).

Possible solutions:

(1) For every transaction which accesses the database I can check for this exception and try a second time.

(2) I can take down jBoss before the database backups start and bring it back up when they complete.

I'm not thrilled with either solution. Can anyone suggest a better way to deal with this?

Thanks,

-David

[nuanda]

You'd kind of hope that the pooling manager would ensure that the connections in the pool were valid before handing them out... One of the primary purposes of a connection pool is to clean up stale connections after all...

What version of JBoss are you running and are you using Oracle's default Type 4 driver ?

Dave

[davidjencks]

Try setting the idletimeout parameters in the pool settings.
Neither the jdbc spec nor the jca spec includes support for the db telling everyone "bye now" nor a jini-lease type mechanism for removing broken stuff. I have yet to see a proposed mechanism that appears to work better than the idle timeout. Any ideas?

[davidnash]

Version of jBoss - 2.2.2

I think I'm using the default Type 4 driver.

Here is the JDBC driver:

<!-- JDBC -->

<!-- org.hsql.jdbcDriver,org.enhydra.instantdb.jdbc.idbDri
ver,oracle.jdbc.driver.OracleDriver -->
org.enhydra.instantdb.jdbc.idbDriver,oracle.jdbc.driver.Ora
cleDriver


And here is my datasource mbean:


OracleDS
org.opentools.minerva.jdbc.xa.wrapper.XADataSourceIm
pl

jdbc:oracle:thin:@oncsgid4.onc.michelin.com:1521:tmtimna3</attri
bute>
1200000
xxxx
10
yyyy
false
false
false
true
120000
1800000
false
false
1.0
0


-David

[davidnash]

I tried the following parameters in my datasource Mbean:

1200
true

...but no change in behavior. The first person who tries to access the web app after the DB backup still causes an exception.

-David

[davidjencks]

Ok, I learned a litle more-
try setting GCInterval to a smaller value also (maybe half the length of time of your backup process)

I admit I haven't actually tried this myself to see if there are other problems ;-)

Remember these values are in milliseconds - do you really want connections idle for 1.2 sec to be disposed of?

[davidnash]

Hmmm. 1.2 sec *may* be a bit short :|

I'll try the following:

900000
900000
true

-David

[davidnash]

The above mod did not work either.

-David

[davidjencks]

Well, I finally resorted to looking at the code. I think you also need to set GCEnabled=true. I'm fixing this up in jboss 3.0 with the resource adapters so it makes a little more sense. I'll probably add something like

CheckConnectionsEnabled=[true, false]
CheckConnectionsInterval=[-1, millis] ( -1 means check each time before handing out)
CheckConnectionsSQL=sqlstring

might be done by monday, but I anticipate only putting it in 3.0.

[davidjencks]

Well, I should avoid proposing new features late at night. Simplifying getting the idle timeout is no problem for resource adapters, but my proposal for testing connections is sql-dependent which makes it unsuitable for the jca framework. I suppose you could supply a class that knew how to test a particular kind of connection, but I think this is getting too complicated compared to its usefulness.

[rogert]

Hi:
I have a similar problem with mysql. It's not a backup, I think it's only inactivity in the sql connection, but I must restart the jboss when happens (every night).
Any solution?
Thanks.

[davidjencks]

Ok, I think in pre 3.0 jboss you set


900000
true
900000
true

and all connections idled for more than 15 min (900,000 millisec) will be disposed of (and replaced if minSize > 0).

If this doesn't work please let us know.

[vipul_bscity]

I have also got the similar problem of JBOSS Sleeping".
It happens when I leave the JBOSS 2.4.3 idle for 2-3 hrs, and then try to retrieve soem data through my BMP Entity bean, it gives me exception in ejbCreate().

I have tried the following settings in my JDBC mbean, suggested by you, BUT till now I am not sure whether it has been rectified completely, since I need to make the JBOSS idle for few hrs and then test.

************************************************

oracle.jdbc.driver.OracleDriver



DefaultDS
org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
jdbc:oracle:thin:@dds_india_106:1526:ts2
SYSADMIN
123456
1200000
20
true
false
false
true
900000
900000
true
false
1.0
1


*******************************

BUT one thing is sure that once this happens, JBOSS needs to be restarted,to work correctly.

Pl suggest me if you have fixed anything new to get this going.

[earlgrey]

I do not think the oracle thin driver supports reconnect (oci calls do).
Two conditions cause an exception with (root cause: Connection reset by peer).

1) the db went down
2) high network latency

The entire connection pool and jdbc driver should be recreated if this kind
of root exception occurs (since all connections would be invalid).
Where does this kind of code go? the sql-layer should catch the exception and might trigger a pool restart?


Lars