4

While migrating from Oracle 11g to Oracle 12 c, we are getting below error using UCP(12.1.0.2.0) 

    java.sql.SQLException: The connection is closed: The connection is closed
            at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:464)
            at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:448)
            at oracle.ucp.jdbc.proxy.JDBCConnectionProxyFactory.invoke(JDBCConnectionProxyFactory.java:307)
            at oracle.ucp.jdbc.proxy.ConnectionProxyFactory.invoke(ConnectionProxyFactory.java:50)
            at com.sun.proxy.$Proxy24.prepareStatement(Unknown Source)

This is multithreaded application where we take connection from UCP pool, use it and then close the connection. However for some of the operation we are getting the above error on the line - connection.prepareStatement(statement) We are using PoolDataSource

Few things tried 1) Using default values for connection pool and only setting minimum, maximum pool size. 2) Setting setValidateConnectionOnBorrow as true, 3) Setting setValidateConnectionOnBorrow as true and setValidateConnectionOnBorrow as "SELECT 1 FROM DUAL" 4) Using Labelled Connection 5) The db doesn't disconnect the connection after a particular time and also there is no firewall which blocks connection. 6) Using setMaxConnectionReuseTime

I have referred few links which shows similar issues. However it doesn't show any resolution https://community.oracle.com/thread/2183613

https://community.oracle.com/thread/2298699

The connection is closed when using Oracle UCP

Has anyone faced similar issue on Oracle 12C with UCP?? Is there some bug in latest UCP??

Community
  • 1
  • 1
Jay Sangoi
  • 41
  • 1
  • 2
  • This issue should be fixed in 12.2.0.1 which can be downloaded here: http://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html – Jean de Lavarene Apr 19 '17 at 14:44

2 Answers2

0

I do not have any problems when using Weblogic's connection pool.

I think the problem is exactly what is described here for question oracle- db-java-sql-sqlexception-closed-connection.

Be really sure that the connection is NOT closed before calling prepareStatement() (pay attention to multi threading).

Maybe add some logging to see if close() is really not closed before prepareStatement() and try logging value for connection.isValid() right before prepareStatement() call.

Can you provide some example code?

Community
  • 1
  • 1
Michael
  • 189
  • 1
  • 17
0

I faced an issue similar to this. I was using ucp-11.2.0.3.jar. I logged my process in detail and observed the connection from pool were

  1. Not null

  2. not closed

  3. Also not valid(con.isValid() is returning false)

    I have gone through so many forums but of not much help. I finally found this Link related to similar issue. Hence i updated to ucp-11.2.0.4 and everything worked well.

Not sure if this is a solution but it solved problems in my case.

Community
  • 1
  • 1
varun teja A
  • 31
  • 4