1

In my application which uses Apache JDBC connectivity to connect to a database, I am occasionally getting "java.sql.SQLRecoverableException". After searching on the root cause, I was able to learn it might be caused due the termination of connection from the oracle server end. I am using oracle 11G version.

Now my requirement is that I have to make sure the connection is still valid and not terminated before executing any query. I need to establish a new connection if the connection was terminated from oracle end.

I am not sure how I can achieve this, or to test it. I have tried getting the session from below query:

select * from v$session where username is not null;

It displays a single result when I have SQL Developer open and the applications (2 applications using same user credentials) are open too. I want to know how to terminate the connection from SQL and reproduce the "SQLRecoverableException" for testing and also reconnect to the database after the issue occurs.

Since I am new to database connections, I am not sure what to do or what to look into in order to achieve this. Kindly help me out with this.

My JDBC connectivity in spring-servlet.xml is given below:

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource" />
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="${myjdbc.driverClassName}" />
    <property name="url" value="${myjdbc.url}" />
    <property name="username" value="${myjdbc.username}" />
    <property name="password" value="${myjdbc.password}" />
</bean>
<context:property-placeholder location="classpath:myjdbc.properties" />
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Deepak Ramakrishnan Kalidass
  • 1,582
  • 10
  • 33
  • 57
  • Can't use use `validationQuery` parameter from the CommonsDBCP. This behavior is supposed to be done by the connection pool. As a user of the connection object from the pool, you should be least worried about tit. https://stackoverflow.com/questions/2548137/auto-reconnect-of-database-connection You could use this link as a reference – Abhijith Nagarajan May 13 '19 at 11:23
  • @AbhijithNagarajan : how do i test it ? how can i disconnect the application's session from oracle so that i can see if it reconnects through connection pool automatically – Deepak Ramakrishnan Kalidass May 13 '19 at 11:49
  • If your database is clustered (Oracle RAC) then you need to query `gv$session` instead. – peekay May 13 '19 at 12:50
  • Please post the full exception stacktrace – Mark Rotteveel May 13 '19 at 14:41
  • 1. Use a validation query to confirm connectivity. Using the suggestion from Abhijith, this can be something simple like "select 1 from dual". It will always return 1, unless the connection is dropped. – 1991DBA May 13 '19 at 14:48
  • Sorry, meant to add this to the first comment: 2. If this is a RAC database, you need to use gv$session like Peekay suggests. v$session will only return sessions on that particular instance. For example, if you connect to node 2 and are bounced to node 4, querying node 2 will not show the session 3. Check your Firewall & Database settings. If idle_time parameter has been set, maybe extend the timeout. Does your firewall drop idle connections? Things to look out for – 1991DBA May 13 '19 at 14:54

1 Answers1

1

It seems the connection is being dropped by some firewall or other activity. We have faced similar issue where the database was terminating the connections which were idle for 30 mins.

To Overcome the issue we have tuned database pool by specifying the following properties

testOnBorrow:-Setting it true will force the pooling provider to run the validation query while handing out the connection to the application.
testWhileIdle:-Setting it true will enable the validation when the connection is sitting idle in the pool.
timeBetweenEvictionRunsMillis:- Setting this property to non-zero will allow the evictor thread to run,which will test the idle connections.

Reproducing the issue will require to kill the connection on the database side.We have performed a small test using mssql wherein we can terminate the connection using the Server Tools and the pool was establishing the connection again.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="${myjdbc.driverClassName}" />
    <property name="url" value="${myjdbc.url}" />
    <property name="username" value="${myjdbc.username}" />
    <property name="password" value="${myjdbc.password}" />
    <property name="testOnBorrow" value="true" />
    <property name="testWhileIdle" value="true" />
    <property name="timeBetweenEvictionRunsMillis" value="3000" />
</bean>

Note the timeBetweenEvictionRunsMillis is in millisonds.

The above configuration will checks the invalid connection and drops them from the pool if they are abruptly closed by the database or the firewall.

HarPal
  • 31
  • 4