Introduction

Learn how to configure the Universal Connection Pool (UCP) to leverage the Oracle Databases’ High Availability (HA) capabilities for continuous service during planned and unplanned downtime.

All the recommendations have been tested using the latest Long Term Support (LTS) Oracle Database releases, i.e., 19c (19.21) and 23ai.

About UCP

Oracle Database furnishes the Universal Connection Pool (UCP), a feature-rich Java connection pool—one of the Spring Boot choices—that supports all Oracle database configurations and mission-critical capabilities (availability, scalability, and load balancing), out-of-the-box, including Oracle Real Application Cluster (RAC), Data Guard, Oracle Sharding, Asynchronous API, Runtime Load Balancing, XA, and front-end to Database Resident Connection Pool (DRCP).

UCP is released quarterly and fully supported by Oracle.

See more details @ https://www.oracle.com/jdbc/.

The Basics

With Spring Boot v2.4.0 and later, configuring UCP is easier and does not require any extra code. Spring identifies UCP as a data source like any other from the application.properties file. Spring retrieves the configuration from the application.properties file and autowires (injects) the values to the datasource.

The basic steps for using the UCP pool with the Oracle Database are (1) configuring the Oracle JDBC driver and (2) configuring UCP.

Configuring the Oracle JDBC Driver

Add Oracle JDBC driver as a dependency in Spring Initializer or manually add it to your project pom.xml file.

For Oracle database 19c, ojdbc driver version is 19.21.0.0:

<properties>
    <oracle.jdbc.version>19.21.0.0</<oracle.jdbc.version>
</properties>

<dependencies>
...
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>${oracle.jdbc.version}</version>
</dependency>
...
</dependencies>

For Oracle database 23ai, ojdbc driver version is 23.4.0.24.05:

 
<properties>
    <oracle.jdbc.version>23.4.0.24.05</<oracle.jdbc.version>
</properties>

<dependencies>
...
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>${oracle.jdbc.version}</version>
</dependency>
...
</dependencies>

Checkout page for more information on Oracle RDBMS and JDK Version Compatibility for Oracle JDBC Drivers.

Configuring UCP

Pull the latest UCP release by adding the following dependency to your project pom.xml file:

<dependencies>
...
<dependency>
<groupid>com.oracle.database.jdbc</groupId>
    <artifactId>ucp</artifactId>
</dependency>
...
</dependencies>

Then, add the following properties to the application.properties file. Here is an example of the application.properties file with the minimum set of properties required for using the Oracle database 19c:

#Oracle Database - Connection Details
spring.datasource.url=${JDBC_URL}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}

#Oracle Universal Connection Pool (UCP) Properties
spring.datasource.type=oracle.ucp.jdbc.PoolDataSource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.pool.OracleDataSource
spring.datasource.oracleucp.connection-pool-name=${POOL_NAME}
spring.datasource.oracleucp.initial-pool-size=4
spring.datasource.oracleucp.min-pool-size=4
spring.datasource.oracleucp.max-pool-size=4

Oracle UCP-specific settings bound to an instance of Oracle UCP’s PoolDataSource can be configured as follows:

  • spring.datasource.type=oracle.ucp.jdbc.PoolDataSource
  • spring.datasource.oracleucp.connection-factory-class-name  – provides a connection factory class for the pool-enabled data source to get an actual physical connection.
  • spring.datasource.oracleucp.initial-pool-size – specifies the number of available connections created after the pool is initiated
  • spring.datasource.oracleucp.min-pool-size specifies the minimum number of available and borrowed connections that our pool maintains and
  • spring.datasource.oracleucp.max-pool-size – specifies the maximum number of available and borrowed connections that our pool maintains

New in Oracle database 23ai: For data source creation using Oracle JDBC driver 23ai, we strongly recommend to use this data source oracle.jdbc.datasource.impl.OracleDataSource instead of the older ones, like oracle.jdbc.pool.OracleDataSource, or oracle.jdbc.replay.OracleDataSourceImpl. Data source classes in packages oracle.jdbc.pool and oracle.jdbc.replay continue to function, but will be gradually phased out.  Oracle® Database JDBC Java API Reference, Release 23ai documentation covers the details of oracle.jdbc.datasource.impl package.

#Oracle Database - Connection Details
spring.datasource.url=${JDBC_URL}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}

#Oracle Universal Connection Pool (UCP) Properties
spring.datasource.type=oracle.ucp.jdbc.PoolDataSource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.datasource.impl.OracleDataSource
spring.datasource.oracleucp.connection-pool-name=${POOL_NAME}
spring.datasource.oracleucp.initial-pool-size=4
spring.datasource.oracleucp.min-pool-size=4
spring.datasource.oracleucp.max-pool-size=4

You have successfully configured your Spring Boot application to work with the Oracle Database using UCP. 

The next sections cover the steps for configuring UCP to leverage Oracle Databases’ high availability capabilities.

Oracle’s High Availability

With the Oracle Real Application Cluster system (RAC), any Oracle database service is accessible equally from several server nodes. If a node or a subset of the RAC cluster fails or is taken offline for maintenance, the database is still accessible through the remaining active nodes.

The building blocks for implementing Application High Availability with the Oracle database are: (1) using a database service, (2) configuring a URL or a connection string for High Availability, (3) enabling Fast Application Notification (FAN), (4) implementing draining and enabling continuous database availability for Java applications.
You may choose the HA protection level that suits your requirements. A detailed explanation of HA protection levels can be found in Oracle’s “High Availability Overview and Best Practices” under the Application High Availability Levels chapter.

The following steps allow you to configure the Oracle RAC system, the Oracle JDBC driver, and Oracle UCP to sustain planned and unplanned outages. Oracle’s “High Availability Overview and Best Practices” documentation covers the detailed steps and explanations for maintaining your applications’ continuous availability.

Configuring for Basic High Availability – Level 1

This HA level allows applications to immediately react to instance, node, or database failures and quickly establish new connections to surviving database instances. With the application’s High Availability Level 1, downtime is minimized for both unplanned and planned outages.

The steps for implementing High Availability Level 1 are:

  1. Configure High Availability Database Services
  2. Setup the Connect String for High Availability
  3. Enable Fast Application Notification

Configuring High Availability Database Services

Create a dedicated service to support HA features as follows. A detailed explanation of how to create a dedicated service and a connect string can be found in Oracle’s “High Availability Overview and Best Practices” under the Application High Availability Levels chapter.

$ srvctl add service -db mydb -service MYSERVICE -pdb mypdb
-notification TRUE

Setting Up the Connection String for High Availability

Use the connect string shown here to connect to the dedicated database service, created above.

jdbc:oracle:thin:@(DESCRIPTION =
  (CONNECT_TIMEOUT= 3)(RETRY_COUNT=4)(RETRY_DELAY=2)
  (TRANSPORT_CONNECT_TIMEOUT=3) (ADDRESS_LIST = (LOAD_BALANCE=on)
  (ADDRESS = (PROTOCOL = TCP)(HOST=clu_site_scan)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME = my_service)))

Enabling Fast Application Notification (FAN) 

FAN is a high-availability notification mechanism that allows an active transaction to be immediately terminated when an instance or server fails. FAN is auto-configured and is enabled out of the box; no application code changes. FAN uses Oracle Clusterware’s Oracle Notification Service (ONS) to receive events from the cluster. ONS requires some ports to be available between the client and the servers.

The database uses the URL specified above to auto-configure FAN on the client side. To enable Fast Application Notification (FAN), add ons jar file to your project’s pom.xml file.

<dependencies>
...
<dependency>
<groupid>com.oracle.database.ha</groupId>
    <artifactId>ons</artifactId>
    <version>${oracle.jdbc.version}</version>
</dependency>
...
</dependencies>

Configuring HA Level 1 for Spring

To support planned and unplanned outages, set spring.datasource.oracleucp.fast-connection-failover-enabled=true. Configure the Oracle database 19c and UCP in your project’s application.properties file as follows.

#Oracle Database - Connection Details
spring.datasource.url=${JDBC_URL}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}

#Oracle Universal Connection Pool (UCP) Properties
spring.datasource.type=oracle.ucp.jdbc.PoolDataSource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.pool.OracleDataSource
spring.datasource.oracleucp.connection-pool-name=${POOL_NAME}
spring.datasource.oracleucp.fast-connection-failover-enabled=true
spring.datasource.oracleucp.initial-pool-size=4
spring.datasource.oracleucp.min-pool-size=4
spring.datasource.oracleucp.max-pool-size=4
spring.datasource.oracleucp.validate-connection-on-borrow=false

For Oracle database 23ai, change the value of spring.datasource.oracleucp.connection-factory-class-name property for data source creation to oracle.jdbc.datasource.impl.OracleDataSource:

spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.datasource.impl.OracleDataSource

You may notice that we’ve added spring.datasource.oracleucp.validate-connection-on-borrow=false to disable connection validation. For more details on connection validation, see the section titled Session Validation of this blog.

At this stage, the basic High Availability has been configured for Level 1. If your application has been designed to handle planned and unplanned outages via retry logic, you don’t need anything else.

Next, we will walk you through HA Level 2 below which furnishes the following benefits:

  1. FAN event notification of unplanned connection failures. This is especially useful when the failure may have resulted in an application hang, such as when a database node fails, and the TCP/IP sockets are not cleaned up.
  2. A connection string that results in connection requests being load-balanced across multiple RAC nodes using Single Client Access Name (SCAN) with retry on failure.

If your application has been designed to handle planned and unplanned outages via retry logic, you don’t need anything else.

Preparing for Planned Maintenance – Level 2

Building on application HA Level 1, Level 2 adds session draining for minimal application impact during planned maintenance. You can use planned operations to relocate, stop services, or switch over, allowing for graceful completion of the users’ work.

UCP responds to FAN events and takes immediate action, either balancing connections or establishing new connections and routing work to the available database instances or where the service was started. When a DOWN event (Reason = User) occurs, connections to the databases scheduled for maintenance will be cleaned up gracefully. When a UP event occurs, FAN informs the connection pool that the new instance is available, allowing sessions to be created on this instance.

Performing Planned Maintenance

Draining database sessions is the safest way to migrate work without interrupting applications. The following command initiates the draining of active sessions.
The -drain_timeout 60 parameter, allows active sessions to finish their requests within a predefined timeout (60 sec).

srvctl stop instance -db mydb -node node1 -stopoption immediate
drain_timeout 60 -force -failover

Planned Outage Success Test

For the planned outage test, we will be using a two-node Oracle RAC environment. During normal operation, both RAC nodes are up and running and serving the application. Depending on your load-balancing strategy, the number of sessions might be different or equally distributed across both nodes. Under load (i.e., database activities), observe that the connections drain away from that instance and that the instance/node can be shut down.

We will monitor the draining process using the following SQL statement:

select systimestamp time, machine, inst_id, count(*) from v$session where username='XXXX' group by systimestamp, machine, inst_id;

In our test run, we perform the following steps:

  • Start the applications — app1 and app2 against nodes db1 and db 2.
  • Run the workload.
  • Drain the instance on node db1.
  • Monitor the draining.
  • Shut down the applications.

During normal operation, both RAC nodes db1 and db2 (inst_id 1 and inst_id2) are up and running. The following SQL output demonstrates how sessions for app1 and app2 are being distributed (count * column). Each application is using a connection pool that is configured to hold 4 sessions.

 
SQL>
TIME				                MACHINE	   INST_ID   COUNT(*)
----------------------------------- --------- ---------- ----------
17-MAY-24 08.47.55.092067 PM +00:00  app1	       1	  1
17-MAY-24 08.47.55.092067 PM +00:00  app1	       2	  3
17-MAY-24 08.47.55.092067 PM +00:00  app2	       2	  1
17-MAY-24 08.47.55.092067 PM +00:00  app2	       1	  3

To begin the planned maintenance test, we first stop the database services on node 1 using the following command:

srvctl stop instance -db $(srvctl config database) -node db1 -stopoption immediate -drain_timeout 10 -force -failover

Fast Application Notification (FAN) sends a stop event. The connection pool reacts to FAN and starts closing connections on node 1. At the same time, new connections are being opened on node 2. The active session on node 1 is still executing its work. If it finishes within the drain timeout (10 sec) and returns the connection to the pool, then the session is closed and a new one is established on node 2 when requested.

SQL>
TIME                                MACHINE    INST_ID   COUNT(*)
----------------------------------- --------- ---------- ----------
17-MAY-24 08.47.58.090673 PM +00:00  app2          2        3
17-MAY-24 08.47.58.090673 PM +00:00  app2          1        1
17-MAY-24 08.47.58.090673 PM +00:00  app1          2        4

If the drain timeout completes but the remaining session is still connected to node 1, it will be disconnected. With Application Continuity in place, the session reconnects to node 2, and the interrupted work will be replayed transparently to the end user. See the next section for replay configuration details.

SQL>
TIME				                MACHINE	   INST_ID   COUNT(*)
----------------------------------- --------- ---------- ----------
17-MAY-24 08.48.08.111763 PM +00:00  app1	       2	  4
17-MAY-24 08.48.08.111763 PM +00:00  app2	       2	  4

Configuring for Unplanned Outages – Level 3

For planned maintenance (Level 2), draining allows active sessions to finish their work (drain) before the database instance is shut down. However, in the case of unplanned events such as an instance, node, or database service failure, all sessions belonging to the faulty node/instance will be terminated immediately, with no time for draining. The Fast Application Notification (FAN) mechanism will detect those unplanned events and notify the UCP that has been instrumented to receive and process the FAN messages.

The primary difference between unplanned (Level 3) and planned shutdown (Level 2) scenarios is how borrowed connections are handled. In the unplanned shutdown scenario, stale connections idle in the pool (not borrowed) are removed.

Starting with Oracle Database19c, Transparent Application Continuity (TAC) automatically tracks and records the session and transactional states to recover and rebuild the database session and its states following recoverable errors (i.e., node, server, outages; SQL exceptions requiring application or database schema changes are classified as unrecoverable errors). TAC protects your work without application knowledge or code changes.

To configure TAC, perform the following steps: (1) add extra failover parameters to the database service definition and (2) add a replay data source to the application.properties file.

1. Add the following parameters to the database service definition

  • -failovertype AUTO — enables Transparent Application Continuity (TAC).
  • -failover_restore AUTO — restores client states automatically
  • -replay_init_time 600 — specifies the time in seconds when the replay will not be performed.

2. Add the replay data source spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.replay.OracleDataSourceImpl to enable continuous service during unplanned outages to your application.properties file.

#Oracle Database - Connection Details
spring.datasource.url=${JDBC_URL}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}

#Oracle Universal Connection Pool (UCP) Properties
spring.datasource.type=oracle.ucp.jdbc.PoolDataSource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.replay.OracleDataSourceImpl
spring.datasource.oracleucp.connection-pool-name=${POOL_NAME}
spring.datasource.oracleucp.fast-connection-failover-enabled=true
spring.datasource.oracleucp.initial-pool-size=4
spring.datasource.oracleucp.min-pool-size=4
spring.datasource.oracleucp.max-pool-size=4
spring.datasource.oracleucp.validate-connection-on-borrow=false

For Oracle database 23ai, change the value of spring.datasource.oracleucp.connection-factory-class-name property for data source creation to oracle.jdbc.datasource.impl.OracleDataSource:

spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.datasource.impl.OracleDataSource

JDBC Diagnosability

New in Oracle Database 23ai: A single ojdbc jar (e.g., ojdbc8.jar, ojdbc11.jar) for all use cases (production, debug, metrics). In other words, no more ojdbc8_g.jar or ojdbc11_g.jar for debugging.

Check this blog post — What’s in ‘Oracle DB 23c Free Developer Release’ for Java Developers — to get more information about the new JDBC Self-Driven Diagnosability in Oracle DB 23ai.

Session Validation

Connection validation on connection borrow in 19c and 23ai UCP is configured by enabling or disabling spring.datasource.oracleucp.validate-connection-on-borrow property in the application.properties file.

  • If spring.datasource.oracleucp.validate-connection-on-borrow is set to false, no connection validation is performed in 19c.
    • New in 23ai: UCP still does lightest validation on its connections on borrow, by reading the inband down event: (“INBAND_DOWN”) mode.
  • If spring.datasource.oracleucp.validate-connection-on-borrow set to true, the level of validation is controlled by the oracle.jdbc.defaultConnectionValidation connection property. The possible values are documented below. Validation will be performed on every borrow unless the setSecondsToTrustIdleConnection() method is set to a positive value (in seconds); in which case, validation will only be performed if a connection has not been used for that period.

Connection property oracle.jdbc.defaultConnectionValidation controls what connection.isValid() does under the covers. This connection property specifies the level of connection validation. The possible values for this property are::

  • “SERVER” (or “COMPLETE”) makes the driver execute a basic SQL query “SELECT ‘x’ FROM DUAL”,
  • “NETWORK” causes the driver to issue an OPING TTC function. It’s the default.
  • “SOCKET” causes the driver to write a zero-length NS data packet on the socket, which the server ignores.
  • New in 23ai: “INBAND_DOWN” makes the do a non-blocking socket read call on the socket
  • “LOCAL” same as above
  • “NONE” checks the connection’s lifecycle (variable check)

Here is an example for setting validation properties in the application.properties file:

#Oracle Database - Connection Details
spring.datasource.url=${JDBC_URL}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}

#Oracle Universal Connection Pool (UCP) Properties
spring.datasource.type=oracle.ucp.jdbc.PoolDataSource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.datasource.impl.OracleDataSource
spring.datasource.oracleucp.connection-pool-name=${POOL_NAME}
spring.datasource.oracleucp.fast-connection-failover-enabled=true
spring.datasource.oracleucp.initial-pool-size=4
spring.datasource.oracleucp.min-pool-size=4
spring.datasource.oracleucp.max-pool-size=4

#Session Validation
spring.datasource.oracleucp.validate-connection-on-borrow=true
spring.datasource.oracleucp.connection-properties=oracle.jdbc.defaultConnectionValidation=SOCKET
spring.datasource.oracleucp.seconds-to-trust-idle-connection=1

Wrap-up

The steps described in this blog post are based on an Oracle RAC system and Oracle JDBC Drivers v19.21.0.0 and 23.4.0.24.05. We hope you found this blog post helpful. Thanks for reading!

Further Readings