CODEWITHSUNDEEP
javaspringoracle-databasejdbcucp
Raghu
Raghu

Reputation: 41

Oracle UCP reconnect option

We have the following spring bean config for Oracle UCP. For some reason if DB is bounced then pool should automatically re-establish the connection back without restarting my webserver. It looks like the below bean config didn't work. I mean, connection pools' connection is not validated with new connection. Can some one please review the bean config and provide suggestion/option to reconnect?

<bean id="jpaDataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory"
    factory-method="getPoolDataSource">
    <property name="URL" value="${app.jdbc.url}" />
    <property name="user" value="${app.jdbc.username}" />
    <property name="password" ref="password"/>
    <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource" />
    <property name="connectionPoolName" value="SVC_POOL" />
    <property name="minPoolSize" value="1" />
    <property name="maxPoolSize" value="5" />
    <property name="initialPoolSize" value="1" />
    <property name="validateConnectionOnBorrow" value="true"/>
    <property name="inactiveConnectionTimeout" value="120"/>
</bean>

With above config, I get the following exception after DB restart:

Caused by: org.hibernate.TransactionException: JDBC rollback failed
        at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:200)
        at org.hibernate.ejb.TransactionImpl.rollback(TransactionImpl.java:107)
        ... 61 more
Caused by: java.sql.SQLRecoverableException: Closed Connection
        at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:3921)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at oracle.ucp.jdbc.proxy.JDBCConnectionProxyFactory.invoke(JDBCConnectionProxyFactory.java:274)
        at com.sun.proxy.$Proxy24.rollback(Unknown Source)
        at org.hibernate.transaction.JDBCTransaction.rollbackAndResetAutoCommit(JDBCTransaction.java:213)
        at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:192)

Edit:

I tried the same configuration in new box, it worked flawlessly. Not sure about this exception in one of my machine (where I was using maven tomcat:run goal to run the job).

Upvotes: 2

Views: 2907

Answers (1)

TeeJ
TeeJ

Reputation: 208

Although the documentation here http://docs.oracle.com/cd/E11882_01/java.112/e12265/connect.htm#CHDIDJGH says that oracle does a internal ping to check if the database, did you try by setting an sql setSQLForValidateConnection(String)

Upvotes: 1

Related Questions