Friday, February 8, 2013

JBO-26061: Error while opening JDBC connection

If you see something similar to the following messages in the WebLogic Server console output or log files:

at oracle.jbo.server.DBTransactionImpl.establishNewConnection(DBTransactionImpl.java:1045) 
at oracle.jbo.server.DBTransactionImpl.getInternalConnection(DBTransactionImpl.java:1433) 
at oracle.jbo.server.DBSerializer.setup(DBSerializer.java:147) 
at oracle.jbo.server.DBSerializer.passivateRootAM(DBSerializer.java:287)
at oracle.jbo.server.DBSerializer.passivateRootAM(DBSerializer.java:268) Truncated. see log file for complete stacktrace Caused By: weblogic.jdbc.extensions.PoolLimitSQLException: weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool ApplicationDB to allocate to applications, please increase the size of the pool and retry.. 

You have a need to tune your JDBC Connection Pool. In this article, we show you how to validate the issue and how to fix it.

JDBC Connection Pools


Connections to a database are expensive to create, which it involves creating a process on the database.  A connection pool can be maintained so that the connections can be reused when future requests to the database are required.

In Java programming paradigm, a Java DataBase Connectivity (JDBC) resource (or data source) provides applications with the means of connecting to a database. A JDBC connection pool contains a group of JDBC connections that are created when the connection pool is registered.
  • J2EE
    • All JDBC connections come from the application server’s pool
  • J2SE with JPA
    • JPA providers transparently creates a connection pool for Java SE programs and you can configure the connection pool within the persistence.xml file.

How to Validate the Issue?


As suggested in the message, you should tune JDBC Connection Pool used by JDBC Data Source named "ApplicationDB".  In this article:
it shows you how to validate (or montior)  JDBC Connection Pool at Runtime.  For example, if you find the following statistics:
  • Waiting For Connection Failure Total
has a non-zero entry, you know you need to tune the JDBC Connection Pool with associated data source.

How to Fix the Issue?


You can tune JDBC Connection Pool from the WebLogic Server Administration Console or you can modify JDBC configuration file used by data source "ApplicationDB" directly.  But, before you modify the configuration file, you need to shut down servers first.

JDBC configuration files can be located here:
  • DOMAIN_NAME/config/jdbc

For example, the one for the ApplicationDB data source is named ApplicationDB-8936-jdbc.xml in our application (ATG CRMDemo).

After trial-and-errors, we have found the following settings are good for our application (note that each application has specific needs and depends on the hardware too):

  <jdbc-connection-pool-params>
    <initial-capacity>0</initial-capacity>
    <max-capacity>500</max-capacity>
    <capacity-increment>2</capacity-increment>
    <connection-creation-retry-frequency-seconds>10</connection-creation-retry-frequency-seconds>
    <test-frequency-seconds>300</test-frequency-seconds>
    <test-connections-on-reserve>true</test-connections-on-reserve>
    <test-table-name>SQL SELECT 1 FROM FND_DUAL</test-table-name>
    <statement-cache-size>5</statement-cache-size>
    <statement-cache-type>LRU</statement-cache-type>
    <seconds-to-trust-an-idle-pool-connection>0</seconds-to-trust-an-idle-pool-connection>
  </jdbc-connection-pool-params>

References

No comments: