java.sql.SQLRecoverableException:Closed Connection

Question

I am using jboss7.0.2 final with Oracle 11. Currently I am facing one issue which happens randomly. But during heavy load this error trace keeps growing. Tried so many approaches couldn't succeed.

MyDatasource Config:

<?xml version="1.0" encoding="UTF-8"?>
<datasource jndi-name="sportPool" pool-name="sportPool" enabled="true" jta="true" use-java-context="true" use-ccm="true">
    <connection-url>jdbc:oracle:thin:@10.11.252.200:1521:sportsdb</connection-url>
    <driver>oracleDriver</driver>
    <pool>
        <min-pool-size>1</min-pool-size>
        <max-pool-size>75</max-pool-size>
        <prefill>false</prefill>
        <use-strict-min>false</use-strict-min>
        <flush-strategy>FailingConnectionOnly</flush-strategy>
    </pool>
    <security>
        <user-name>mportal</user-name>
        <password>mobile</password>
    </security>
    <timeout>
        <idle-timeout-minutes>60000</idle-timeout-minutes>
    </timeout>
</datasource>

StackTrace:

nested exception is java.sql.SQLRecoverableException: Closed Connection
        at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.translate(SQLExceptionSubclassTranslator.java:82)
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:237)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:604)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:638)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:667)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:683)

        ... 50 more
Caused by: java.sql.SQLRecoverableException: Closed Connection
        at oracle.jdbc.driver.OracleStatement.ensureOpen(OracleStatement.java:4051)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3563)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493)
        at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:111)
        at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
        at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:645)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:588)
        ... 56 more

Answer 1

1. Take a look at your database settings - it's possible, that the timeout set in database is shorter than the timeout set in datasource deployment descriptor, and the database closes the connections.

2. Check your network - it's possible that there is something wrong with your firewall / routing settings, and the connections are closed/dropped somewhere in the middle of the route.

3. Take a look at this link JBoss 7 Datasource Configuration. There is a way of configuring a datasource descriptor, forcing the JBoss to check the connection when checking it out from the pool. Use the valid-connection-checker (faster) or check-valid-connection-sql (slower) setting and set the

   <valid-connection-checker>
       org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker
   </valid-connection-checker>
   

   or

   <check-valid-connection-sql>
       SELECT 1 FROM DUAL
   </check-valid-connection-sql>