JDBC getConnection timeout issue

Question

Hi,

I have an application that uses connection pooling to get database connection from oracle9i release 9.2.0.4 database.Application is hosted in SJSAS 8.1 and the driver is ojdbc14.jar version 10.1.0.4. The problem I am having is datasource.getConnection() method is taking about 40 secs to throw an exception when the DB is down! This is causing the application to get overloaded during a DB outage.I tried setting LoginTimeout=8 with the datasource but it has no effect.Please see below the datasource I am using

Can any one please help? I need the getConnection() method to timeout with in the timeout given.

Thanks

Answer 1

Here's a few things to try:

From "Oracle® Database JDBC Developer's Guide and Reference".

Using JDBC with Firewalls

Firewall timeout for idle-connections may sever a connection. This can cause JDBC applications to hang while waiting for a connection. You can perform one or more of the following actions to avoid connections from being severed due to firewall timeout:

• If you are using connection caching or connection pooling, then always set the inactivity timeout value on the connection cache to be shorter than the firewall idle timeout value.
• Pass oracle.net.READ_TIMEOUT as connection property to enable read timeout on socket. The timeout value is in milliseconds.
• For both JDBC OCI and JDBC Thin drivers, use net descriptor to connect to the database and specify the ENABLE=BROKEN parameter in the DESCRIPTION clause in the connect descriptor. Also, set a lower value for tcp_keepalive_interval.
• Enable Oracle Net DCD by setting SQLNET.EXPIRE_TIME=1 in the sqlnet.ora file on the server-side.