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)

Tuning WebLogic's Prepared Statement Cache

In [10], the top 9 tuning recommendations for WebLogic Server includes

Use the Prepared Statement Cache

The primary utility of a cached prepared statement is its association with a compiled query plan in the DBMS.  In this article, we will show how to tune Prepared Statement Cache in Oracle WebLogic Server for better web application performance.

Caching Prepared Statements

There are two steps to complete a SQL request: 

• Compiling the SQL statement
• Executing the SQL statement

By using prepared statements (java.sql.PreparedStatement), you can reduce unnecessary compilation, saving time.  A prepared statement contains SQL statements that have already been compiled, thus making their execution faster. If you’re going to use a SQL statement more than once, you should use a prepared statement.

However, when you use a prepared statement or a callable statement (a callable statement object provides a way to call stored procedures in a standard way for all RDBMs) in an application, there’s additional overhead due to the need for communication between WebLogic Server and the database.

To minimize the processing costs, WebLogic Server can cache prepared and callable statements used in your applications. When an application or EJB calls any of the statements stored in the cache, WebLogic Server reuses the statement stored in the cache. Reusing prepared and callable statements can:

• Reduce CPU usage on the database server 
• Improve the statement’s performance on the application server

Statement Cache

The statement cache caches statements from a specific physical connection.  Each connection in a data source has its own individual cache of prepared and callable statements used on the connection. However, you configure statement cache options per data source. That is, the statement cache for each connection in a data source uses the statement cache options specified for the data source, but each connection caches it's own statements. Statement cache configuration options include:

• Statement Cache Type—The algorithm that determines which statements to store in the statement cache. See Statement Cache Algorithms.
• Statement Cache Size—The number of statements to store in the cache for each connection.

Configuring Statement Cache Size

You can configure the size of the statement cache from the WebLogic Server Administration Console by navigating to:

• Services -> Data Sources -> ApplicationDB -> Configuration -> Connection Pool

The value can be from 0 to 1024 (default: 10).  If you set the size of the statement cache to 0, it runs off statement caching.

JDBC DataSource Runtime Statistics

Each connection in the connection pool has its own cache of statements. JDBC DataSource Runtime Statistics shown in the table are the sum of the number of cached statements for all connections in the connection pool.

• Prep Stmt Cache Access Count 
• The total number of times the statement cache was accessed
• Prep Stmt Cache Add Count
• The total number of statements added to the statement cache for all connections
• Prep Stmt Cache Current Size
• The number of prepared and callable statements currently cached in the statement cache
• Prep Stmt Cache Hit Count 
• The running count of the number of times the server used a statement from the cache
• Prep Stmt Cache Miss Count
• The number of times that a statement request could not be satisfied with a statement from the cache

Access Count is the sum of Hit Count and Miss Count.  When you tune for the cache performance, you want to reduce the miss ratio (i.e., Miss Count / Access Count) based on the guideline below.

Tuning Guideline

By increasing the statement cache size, you can increase your system performance. However, you must consider how your DBMS handles open prepared and callable statements. In many cases, the DBMS will maintain a cursor for each open statement. This applies to prepared and callable statements in the statement cache.

If you cache too many statements, you may exceed the limit of open cursors on your database server. If the DBMS is Oracle, you will get ORA-1000 error.  To avoid exceeding the limit of open cursors for a connection, you can change the limit in your database management system or you can reduce the statement cache size for the data source.

References

1. Oracle WebLogic Server 11g Administration Handbook
2. JDBC statement cache
3. Why Prepared Statements are important and how to use them "properly"
4. Statement Cache Algorithms
5. Monitoring and Tuning Oracle Fusion Applications
6. Monitoring WebLogic JDBC Connection Pool at Runtime (XML and More)
7. Oracle JDBC Memory Management (Oracle Database 12c)
8. Fusion Middleware Performance and Tuning for Oracle WebLogic Server
9. Oracle® Fusion Middleware Tuning Performance of Oracle WebLogic Server 12c (12.2.1)
10. Top Tuning Recommendations for WebLogic Server

Configuring JDBC Data Sources in JDeveloper and Oracle WebLogic Server

In a previous article, we have introduced different ways of creating and managing database connections in JDeveloper. Those connections (i.e. using JDBC URL) are good for the testing of JDeveloper Design-time, Business Components Tester, and Integrated WebLogic Server.

In the production environment, the web application would typically reference a JDBC datasource, since it simplifies maintenance of the connection credentials by allowing them to be centrally managed in the Java EE container. In this article, we will show you how to:

• Switch from JDBC URL to JDBC DataSource in JDeveloper.
• Setup global JDBC DataSource for database connection in the WebLogic Server
• Deploy a Web Application to the WebLogic Server

JDBC URL vs. JDBC DataSource

If you use JDBC URL connection type, you provide settings as shown below:
However, if you use JDBC DataSource connection type, its settings are done on the server. To avoid passwords being present in plain text in deployed files, JDeveloper uses password indirection, which means that passwords for the data sources must be set on the server before the application will run correctly.

A data source object enables a Java Database Connectivity (JDBC) client to obtain a DBMS connection from a JDBC pool. A data source is a vendor-independent encapsulation of a database server connection. The data source offers advantages over a JDBC URL connection because the data source can be tuned, reconfigured, or remapped without changing the deployed application.

In WebLogic Server, you configure database connectivity by adding data sources to your WebLogic domain. WebLogic JDBC data sources provide database access and database connection management. Each data source contains a pool of database connections that are created when the data source is created and at server startup.

Choosing JDBC DataSource

After you finish testing your application using Integrated WebLogic Server and decide to deploy it to a standalone WebLogic Server, then it's time to configure JDBC DataSource as your application's connection type.

This configuration step is done on the Application Module in the Business Layer.
Edit Business Components Configuration dialog is used to create a new application module configuration or edit an existing one. To open it, do:

Application Navigator context menu for application modules > Configurations > Edit > Application Module tab

You choose to connect to a JDBC DataSource from the dropdown list. The JDeveloper helps create the data source name based on available resource connections. This data source naming convention follows the Java EE standard which specifies that resource connections appear in the application-specific name space java:comp/env/jdbc/. For example, for a connection named 'SmmApp', the JDBC Datasource name would be 'java:comp/env/jdbc/SmmAppDS'.

When you deploy the application and run it in standalone Oracle WebLogic Server, the specified data source connection in the application module configuration will be used.

Setting up Global JDBC DataSource

You can create a global data source on Oracle WebLogic Server Administration Console:

• http://xbox.mycompany.com:7001/console/

To set up a global data source:

1. Login to the Oracle WebLogic Server Administration Console. Click on the Data Sources link under JDBC. On the Summary of JDBC Data Sources page, click New. In the Create a New JDBC Data Source page, enter details of the data source.
2. The name can be anything.
3. The JNDI name must be of the form jdbc/connection-nameDS. For example, if the application has a connection name SmmApp, the JNDI name is jdbc/SmmAppDS. Ensure that the database type is Oracle and that the driver is Oracle’s Driver (Thin) for Service Connections;Version 9.0.1,9.2.0,10,11. Click Next twice to navigate to the Create a New JDBC Data Source page, where you enter the connection details.
4. The database name is the Oracle SID.
5. The host name is the the name of the machine the database is on.
6. The default port is 1521.
7. Enter the user name and password, for example hr/hr. Click Next and click Test Configuration. Click Next to navigate to the Select Targets page, where you select a target for this data source. If you fail to select a target, the data source is created but not deployed. Click Finish.

Steps to Deploy to WebLogic Server

Application Properties Dialog is used to to define the deployment profiles that will be available to an application. To open Deployment page, do:

Application menu > Application Properties > Deployment page

Makre sure "Auto Generate and Synchronize weblogic-jdbc.xml Descriptors During Deployment" is unchecked. When this field is not selected, this means that your deployed application will use global data sources on the server.
You can also edit the default deployment profile (i.e. SmmTest_application1--EAR File) or create a new one if needed.

There are two options for the deployment:

1. Deployed to Ear
2. Deployed to Application Server

If you want to deploy your application directly to the server, configure Resource Palette with a new Application Server Connection (i.e., xbox) as follows:
You then deploy your application using context menu. Right select your application and select Deploy. Choose the destination to be your application server (i.e., xbox).

References

1. Database Connections in JDeveloper 11
2. JDBC Datasources Work in the Business Components Browser in 11g
3. Configuring JDBC Data Sources
4. Configuring JDBC Data Sources (WebLogic)
5. What You May Need to Know About JDBC DataSource
6. Oracle WebLogic Server Downloads 
7. Oracle® Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server 11g Release 1 (10.3.6)