After deploying CAS one problem that cropped up was that after 2 hours Oracle would close the database connection while Apache's DBCP would hold on the connection in its pool unaware what the database has done. Then next time a database query CAS would through the following exception
java.sql.SQLException: ORA-03135: connection lost contact
Our dba suggested we use OCI instead of thin client and he configured ORACLE configuration on the system. However it didn't work. It seems this scenario is not part of the failover, probably because DB responds with a RESET instead of a timeout, so at the connectivity is not lost with the server. I tried looking for a RECONNECT setting for Oracle JDBC but couldn't find it; however, DBCP provides testing of the connection in the pool before using it. So I configured the testing as follows
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<value>oracle.jdbc.driver.OracleDriver</value>
</property>
<property name="url">
<value>jdbc:oracle:oci:@<DB></value>
</property>
<property name="username">
<value>USERNAME</value>
</property>
<property name="password">
<value>PASSWORD</value>
</property>
<property name="validationQuery" value="select <something> from <table>" />
<property name="testOnBorrow" value="true"/>
</bean>
validationQuery is a quick query that is used to test the connection whenever the connection is borrowed from the pool. If the connection fails then it is removed from the pool and DBCP creates a new connection if it can't find any valid connection. This has been working so far without error. Only overhead is the execution of validationQuery. I would actually prefer to have a RECONNECT option as it would probably be with lower overhead. And for that the search continues.
Subscribe to:
Post Comments (Atom)
2 comments:
I'm trying this at work now ... I think it's going to work. Just wanted to say thanks. I try to blog solutions to problems I solve, too, to help out other devs! upthescala @ blogspot in case your interested. Once again, thanks!
Hi I would like to make use of the solution that you have provided here. But not sure where to place the code ... If you could tell me where exactly to make use of this code it will be appreciable.
Post a Comment