Datasource autoreconnect in Java Application Servers

Many times we face issues like the database will be down due to some reason and even after the database issue has been fixed the application data source wont be able to reconnect to DB properly or it will run on some stale DB connections and cause transaction failure and unexpected behaviors. To avoid this we need to configure the JNDI pool to autoreconnect to a valid connection to database after recovering from shutdown/failure so that application can run smoothly.

In this article we will discuss the JNDI/DataSource autoreconnect configuration steps for different application servers.

Tomcat jdbc connection pool reconnect:

i. Login to Tomcat server.
ii. Navigate to ‘\conf’ and edit context.xml file
iii. Add parameters ‘validationQuery=”select 1 from dual”’ and ‘testOnBorrow=”true”’ under the Resource tag.

<Resource auth="Container"
driverClassName="oracle.jdbc.OracleDriver"
initialSize="5"
maxActive="120"
maxIdle="5"
maxWait="5000"
name="jdbc/oracle/myds"
password="secret"
poolPreparedStatements="true"
type="javax.sql.DataSource"
url="jdbc:oracle:thin:@DBHOSTNAME:1521/ServiceName"
username="testuser"
validationQuery="select 1 from tab"
testOnBorrow="true"/>

iv. Restart tomcat to reflect the changes.

Jboss datasource autoreconnect configuration:

For Jboss 6.1 EAP:

i. Login to JBOSS server.
ii. Navigate to ‘\standalone\configuration’ for standalone mode and edit ‘standalone-full.xml’
iii. Navigate to ‘\domain\configuration’ for domain mode and edit domain.xml
iv. Edit ‘standalone-full.xml’ or ‘domain.xml’ file as per the application server configuration and add the following property under ‘<datasource>’ tags.

<Validation>
<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
<validate-on-match>true</validate-on-match>
</validation>

v. Save changes and restart the JBOSS server.

For Jboss 5.1 :

i. Login to Jboss Server.
ii. Navigate to ‘\ server\default\deploy’ and edit ‘oracle-ds.xml’ file.
iii. Add below properties in datasource configuration.

<validate-on-match>true</validate-on-match>
<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
<failOverReadOnly>false</failOverReadOnly>

iv. Save changes and restart JBOSS server.

Websphere datasource autoreconnect configuration:

i. Login to WAS console.
ii. Navigate to Data sources>Oracle JDBC Driver Datasource>WebSphere Application Server data source properties
iii. Enable ‘Validate new connections’ and enter values for ‘Number of retries’ and ‘Retry interval’ as shown in screenshot.

iv. Restart the application server to activate the changes.

Weblogic datasource auto reconnect configuration:

Follow the below steps to configure data source auto connect configuration with WebLogic:

i. Login to WebLogic console.
ii. Navigate to Datasource  Datasource name  Configuration  connection pool  Advanced
iii. Enable ‘Test Connections on Reserve’ and set ‘Test Frequency’ to 3 seconds (Picture 2 and Picture 3)
iv. Specify ‘Test Table Name’ as ‘SELECT 1 FROM DUAL’
v. Save the changes and restart the managed server.

Credits: Anshul Tiwari. One of my colleague who compiled the information for all servers.

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.