Article

Use Oracle's Universal Connection Pool with Red Hat JBoss Enterprise Application Platform 7.3 and Oracle RAC

December 7, 2020
DevOps Java Security
Shaaf, Syed
Technical Marketing - Cloud Native Runtimes @ Red Hat
Pablo Silberkasten
Guest Author
Featured image: JBoss EAP and Oracle RAC

Data is a critical business application component, but ensuring consistent, reliable data access can be challenging. Adding distributed services and high availability to your application requirements makes data access even more complicated. You can now use Oracle's Universal Connection Pool (UCP) together with Oracle Real Application Clusters (RAC) and Red Hat JBoss Enterprise Application Platform (JBoss EAP) 7.3. This article introduces connection pooling with Oracle Universal Connection Pool and demonstrates how to integrate UCP with an Oracle RAC database in a JBoss EAP 7.3 deployment.

What is connection pooling?

A connection pool is a group of connection objects that represent physical database connections. At runtime, an application requests a connection from the pool. When the application is finished with the connection, it releases it back to the pool.

Opening a physical connection to a database involves establishing a TCP/IP connection, negotiating session parameters (from the protocol), and authenticating the user. User authentication can require considerable processing for heavy cryptographic key generation. Worse, each of these steps requires a remote procedure call (RPC), which entails a network round-trip to the database with the implied network latency.

Achieving maximum application uptime without interruptions requires outage detection, transparent planned maintenance, and balancing the workload. All of these factors critically influence application availability and performance. Connection pooling is an effective means of handling these concerns. Just as critically, connection pooling lets us re-use connections rather than starting a new one each time a request is made.

Connection pooling minimizes the use of resources in both the client and the database. The client only needs to access a pool of active connections, which is shared by a larger number of clients. In the database, each active connection allocates a set of resources—both in memory and in the CPU—that can be minimized by using a pool in the client.

When using Oracle Universal Connection Pool (UCP), a Java developer opens and closes connections only at the logical level. Connections are kept active in the pool and borrowed and returned to the pool as needed.

In the next sections, we will demonstrate a couple of ways to integrate UCP with an Oracle RAC database in a JBoss EAP deployment.

Integrating Universal Connection Pool with an Oracle RAC database

Beyond the intrinsic benefits of connection pooling, developers implementing UCP with an Oracle RAC database can leverage additional features in the connection pool. These features—addressing high availability, scalability, and performance—are as follows:

  • Runtime connection load balancing allows UCP to acquire connections based on the load of the nodes in the cluster.
  • Fast connection failover supports unplanned outages and rapidly replaces dead connections in the pool. Failover also supports planned outages and helps ensure that connections are not interrupted until their work is done.
  • Transaction affinity allows an ongoing transaction to stick to a specific node in the cluster, thus improving performance.
  • Built-in support for database-resident connection pooling and application continuity provide seamless integration with server-side pooling and mask outages to support in-flight transaction recovery.
  • Explicit request boundaries begin when a connection is borrowed from UCP and ends when the connection is returned to the connection pool. The JDBC driver provides the explicit request boundary declaration APIs beginRequest and endRequest.

Oracle UCP is automatically configured to listen for Oracle notification service events in a RAC instance. Listener events include:

  • Service up/down
  • Node up/down
  • Load balancing and affinity advice

Configuring Oracle UCP with JBoss EAP

There are two possible approaches to configuring Oracle Universal Connection Pool with JBoss EAP. We will consider them both.

Option 1: Configure UCP with a web application descriptor (web.xml)

This approach lets you configure the connection pool using the standard web application descriptor of a web.xml file and <context-param> name-value pairs. Each name uses the prefix "ucp." to identify as a UCP argument. The following web.xml sets the jndiName, URL, connectionFactoryClassName, and dataSourceName, as well as identifying the user of a connection in the pool:

<web-app>

...

<context-param>

<param-name>ucp.jndiName</param-name>

<param-value>java:/datasources/mypool_usingwl</param-value>

</context-param>

<context-param>

<param-name>ucp.URL</param-name>

<param-value>jdbc:oracle:thin:@myhost:5521/myservice</param-value>

</context-param>

<context-param>

<param-name>ucp.connectionFactoryClassName</param-name>

<param-value>oracle.jdbc.replay.OracleDataSourceImpl</param-value>

</context-param>

<context-param>

<param-name>ucp.dataSourceName</param-name>

<param-value>myDataSource</param-value>

</context-param>

<context-param>

<param-name>ucp.user</param-name>

<param-value>scott</param-value>

</context-param>

<listener>
    <listener-class>oracle.ucp.jdbc.UCPServletContextListener</listener-class>
</listener>

</web-app>

Note that the name of the attribute in <param-name> should be formed with the ucp. prefix and the name of the setter without the set prefix. As an example, ucp.initialPoolSize maps to setInitialPoolSize(int).

Note: For a complete list of Universal Connection Pool configuration values, please visit the Oracle UCP documentation.

Option 2: Configure UCP with an XML configuration file

To use this configuration option, you must first set the JVM system property to oracle.ucp.jdbc.xmlConfigFile. In the web.xml, ensure that the ucp.dataSourceNameFromXMLConfig parameter matches a value for the data-source-attribute from /ucp-properties/connection-pool/data-source/data-source-name.

For this web.xml:

-Doracle.ucp.jdbc.xmlConfigFile=file:/Users/scott/conf/ucp_config.xml

Pass the following values:

<web-app>

...

<context-param>

<param-name>ucp.dataSourceNameFromXMLConfig</param-name>

<param-value>myDataSourceInXml</param-value>

</context-param>

Then /Users/scott/conf/ucp_config.xml

<ucp-properties>

    <connection-pool

    connection-factory-class-name="oracle.jdbc.replay.OracleDataSourceImpl"

    connection-pool-name="pool1"

    initial-pool-size="10"

    max-connections-per-service="15"

    max-pool-size="30"

    min-pool-size="2"

    password="*****"

    url="jdbc:oracle:thin:@myhost:5521/myservice”

    user="scott">

    <connection-property name="autoCommit" value="false"></connection-property>

    <connection-property name="oracle.net.OUTBOUND_CONNECT_TIMEOUT" value="2000">

    </connection-property>

    <data-source data-source-name="myDataSourceInXml" description="pdb1" service="ac">

    </data-source>

    </connection-pool>

</ucp-properties>

Using Oracle UCP in a JBoss EAP deployment

UCP creates a connection pool when the application server is started or deployed. When this event occurs, the object reads the configuration or description file and create a UCP data source with the configured values. It then uses the Java Naming and Directory Interface (JNDI) to bind the data source to the configured address. It also binds the data source to an application-scoped object, which is injected using Context and Dependency Injection (CDI). Contained objects can use the JNDI address and application-scoped object to retrieve the data source and its connections. The application-scoped object and the JNDI-retrieved data source are in the same pool, so this mechanism creates no duplication.

There are two possible approaches to using UCP in a JBoss EAP deployment.

Option 1: Using Context and Dependency Injection (CDI)

First, here is an example of using CDI for this purpose:

@Inject

@UCPResource

private DataSource ds;

In this case, the data source is ready to use. No additional code is needed.

Option 2: Using the Java Naming and Directory Interface (JNDI)

This second option is to use the Java Naming and Directory Interface. To use JNDI, you will need to perform the additional step of retrieving the pool and associating it to the data source:

public class OracleUcp extends HttpServlet { // sample usage in a servlet

  private DataSource ds = null;

  // Retrieve Datasource reference using JNDI

  @Override

  public void init() throws ServletException { // association must occur after init

    Context initContext;

    try {

      initContext = new InitialContext();

      ds = (DataSource) initContext.lookup("java:/datasources/mypool_usingwl");

Conclusion

We hope the discussion and examples we have shared in this article have helped you understand how to use Oracle UCP and Oracle RAC in a JBoss EAP deployment. Using these technologies together is a great way to develop resilient, highly available applications. We recommend the following additional resources for exploring more about JBoss EAP:

Last updated: January 27, 2023

Comments