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:

• Monitoring WebLogic JDBC Connection Pool at Runtime

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

1. Monitoring WebLogic JDBC Connection Pool at Runtime
2. Monitoring WebLogic Server Thread Pool at Runtime 
3. Professional Oracle WebLogic Server by Robert Patrick, Gregory Nyberg, and Philip Aston

Dynamically Sizing JDBC Connection Pool in WebLogic Server

A data source in WebLogic Server has a set of properties that define the initial, minimum, and maximum number of connections in the pool. A data source automatically adds one connection to the pool when all connections are in use. When the pool reaches maxCapacity, the maximum number of connections are opened, and they remain opened unless you enable automatic shrinking on the data source or manually shrink the data source.

In this article, we will discuss the trade-offs between memory footprint and CPU utilization in the task of JDBC connection pool sizing. Before you start, you may want to read this companion article first:

• Monitoring WebLogic JDBC Connection Pool at Runtime

Fixed-Sized vs Dynamically-Sized Pool

Sometimes you would like to set the initial capacity to the same value as the maximum capacity—this way, the connection pool will have all the physical connections ready when the pool is initialized. However, sometimes it's not possible to estimate what your run-time workloads (either average or peak load) would be in advance and it could become wasteful to over-allocate connection instances. Then dynamically-sized pool may be the better approach.

Monitoring JDBC Connection Statistics

As shown above, you can navigate to:

• Services -> Data Sources -> ApplicationDB -> Monitoring -> Statistics

and monitor the connection statistics of a specific data source (i.e., "ApplicationDB").

In our case, ApplicationDB was deployed to multiple servers. As you can see, the active connections on each server is low (i.e., maximum is 6). However, we have set its Initial Capacity to be 20 and all five pools inherit the setting and have a current capacity of 20.

Also, in our case, only SalesServer_1 will ever need over 20 connections concurrently and allocating 20 connections for all pools can be wasteful. So, based on your own situation, you may want to reduce ApplicationDB's initial capacity appropriately.

After you estimate your peak load, you can choose a Maximum Capacity for the data source. In this case, initial and maximum capacity will be different. Then you can configure the way the pool can shrink and grow by using two additional properties:

• Shrink Frequency
• The number of seconds to wait before shrinking a connection pool that has incrementally increased to meet demand.
• When set to 0, shrinking is disabled.
• Minimum Capacity
• The minimum number of physical connections that this connection pool can contain after it is initialized.

You may want to drop some connections from the data source when a peak usage period has ended, freeing up WebLogic Server and DBMS resources. When you shrink a data source, WebLogic Server reduces the number of connections in the pool to the greater of either the Minimum Capacity or the number of connections currently in use.

For best performance, you should always tune pool sizes based on DataSource statistics.

References

1. Monitoring WebLogic JDBC Connection Pool at Runtime
2. Oracle® Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server 11g Release 1 (10.3.4)
3. Configuring JDBC Data Sources in JDeveloper and Oracle WebLogic Server
4. Monitoring and Tuning Oracle Fusion Applications
5. Why My WebLogic Managed Server is in ADMIN State?
• Read this for a good example of when to set Initial Capacity to be zero.
6. JBO-26061: Error while opening JDBC connection
7. Tuning Data Sources (12.2.1.3.0) 
8. Top Tuning Recommendations for WebLogic Server (12.2.1.3.0)

Monitoring WebLogic JDBC Connection Pool at Runtime

Before you start any performance tuning, you need to monitor your application runtime behavior using default application server settings first.

In this article, we will show you how to monitor the health of WebLogic JDBC connection pool. In a companion article^[7], we also show you how to tune Prepared Statement Cache in WebLogic Server for better web application performance.

Data Sources & JDBC Connection Pool

WebLogic Server maintains a pool of reusable physical database connections to minimize the overhead involved in connecting to a database. All the connections in a pool connect to the same database and use the same username and password for the connections.

WebLogic Server also manage your database connectivity through JDBC data sources. WebLogic Server data sources help separate database connection information from your application code.  Each data source that you configure contains a pool of database connections that are created when the data source instance is created—when it is deployed or targeted, or at server startup. The connection pool can grow or shrink dynamically to accommodate the demand.

At runtime, Java applications perform a lookup of the JNDI tree to find the data source and request database connections using the getConnectionMethod. Once the application completes using that connection, the connection goes back to the data source’s connection pool.

DataSource Runtime Monitoring

Using WebLogic Server Administration Console, you can monitor JDBC DataSource statistics by navigating to:

• Servers --> SalesServer_1 --> Monitoring --> JDBC

There are many KPIs that you can monitor with and you can customize which ones to be displayed in the table.  We have listed some important KPIs here:

• Waiting For Connection High Count
• Highest number of application requests concurrently waiting for a connection from this instance of the data source
• Wait Seconds High Count
• The highest number of seconds that an application waited for a connection (the longest connection reserve wait time) from this instance of the connection pool since the connection pool was instantiated
• Connection Delay Time
• The average amount of time, in milliseconds, that it takes to create a physical connection to the database
• The value is calculated as summary of all times to connect divided by the total number of connections

Configuring the Connection Pool

When the WebLogic Server starts up or when you deploy a data source to a new target, the connection pool is registered with the server, meaning that the connection pool and its connections are created at that time. You can configure various settings to control the connection pool size and the way the pool can shrink and grow.  You should tune pool sizes based on DataSource statistics. For example, you should ensure connection wait time is not high.

We have listed some settings here that you may want to tune for your applications:

• Initial Capacity
• Number of connections created when pool is initialized
• Minimum Capacity
• Minimum number of connections that will be maintained in the pool
• Should be tuned for steady load
• Maximum Capacity
• Maximum number of connections that pool can have
• Should be tuned to peak load
• Shrink Frequency 
• Should be enabled to drop some connections from the data source when a peak usage period has ended, freeing up WebLogic Server and DBMS resources

It is common to set the initial capacity to a value that handles your estimated average, but not necessarily the maximum number of connections to the database. Ideally, you want to make sure that you have enough initial connections to match the number of concurrent requests that you expect to have running on any given server instance.

To be on the safe side, you can set the initial capacity to the same value as the maximum capacity—this way, the connection pool will have all the physical connections ready when the pool is initialized.  However, sometimes you do want to dynamically adjust pool size at run-time, see [5].

The rule of the thumb for pool sizing is simply to make sure that the pool is large enough for all server threads to get access to the pooled resources they need concurrently.  In previous versions of WebLogic Server,this was usually simple.  For example,each execute thread needs access to one database connection from each pool, so you always make sure that the maximum capacity of the database connection pool was greater than or equal to the number of execute threads. With the introduction of server self-tuning, the number of execute threads isn't necessarily well defined.  Then, the tips provided here may be helpful to you.

Acknowledgement

Some writings here are based on the feedback from Sandeep Mahajan and Stevan Malesevic. However, the author would assume the full responsibility for the content himself.

References

1. Professional Oracle WebLogic Server
2. Oracle WebLogic Server 11gR1 PS2: Administration Essentials
3. The WebLogic Server Administration Console
4. Managing WebLogic JDBC Resources
5. Dynamically Sizing JDBC Connection Pool in WebLogic Server
6. Configuring JDBC Data Sources in JDeveloper and Oracle WebLogic Server
7. Tuning WebLogic's Prepared Statement Cache
8. Data Source Connection Pool Sizing