UCP Best Practices for Oracle Database 19c and Spring Boot

Published in

Oracle Developers

9 min readJun 17, 2024

Irina Granat — Richard Exley — Kuassi Mensah

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 release, i.e., 19c (19.21).

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:

<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>

Check out this 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:

#Oracle Database DataSource Configuration
spring.datasource.url=${JDBC_URL}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}

#UCP Settings
spring.datasource.type=oracle.ucp.jdbc.PoolDataSource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.pool.OracleDataSource
spring.datasource.oracleucp.initial-pool-size=4
spring.datasource.oracleucp.min-pool-size=4
spring.datasource.oracleucp.max-pool-size=4

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

Oracle Database 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 furnishes fast dead connection detection thereby allowing applications to immediately react to instance, node, or database failures and quickly establish new connections to surviving database instances. With this level, downtime is minimized for both unplanned and planned outages.

The steps for implementing High Availability Level 1 are:

Configure High Availability Database Services
Setup the Connect String for High Availability
Enable Fast Application Notification

Configuring High Availability Database Services

Create a dedicated service to support HA features as follows.

$ 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

Configure the Oracle database and UCP in your project’s application.properties file as follows.

#Oracle Database DataSource Configuration
spring.datasource.url=${JDBC_URL}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}

#UCP Settings
spring.datasource.type=oracle.ucp.jdbc.PoolDataSource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.pool.OracleDataSource
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

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 and Level 3 which furnish the following benefits:

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.
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.

Configuring for High-Availability — 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 (i.e., new instance up), 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 seconds).

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 kicks in but some sessions are still connected to node 1, these will be disconnected. With Application Continuity in place, the sessions reconnect 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 High Availability — 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 (i.e., 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.

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

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 DataSource Configuration
spring.datasource.url=${JDBC_URL}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}

#UCP Settings
spring.datasource.type=oracle.ucp.jdbc.PoolDataSource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.replay.OracleDataSourceImpl
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

Session Validation

Connection validation on borrow in 19c 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.
If spring.datasource.oracleucp.validate-connection-on-borrow set to true, the level of validation is controlled by the oracle.jdbc.defaultConnectionValidation connection property (see more details in the next paragraph). The connection validation will be performed on every borrow unless the setSecondsToTrustIdleConnection() method specifies a positive value (in seconds), in which case, validation will only be performed if a connection has not been used for that period.

The connection property oracle.jdbc.defaultConnectionValidation controls what connection.isValid() method call 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.
“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 DataSource Configuration
spring.datasource.url=${JDBC_URL}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}

#UCP Settings
spring.datasource.type=oracle.ucp.jdbc.PoolDataSource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.pool.OracleDataSource
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=Wrap

Wrap-up

The steps described in this blog post are based on a RAC system and Oracle JDBC Drivers v19.21.0.0. A follow-up blog will describe the steps for planned and unplanned outages using the Autonomous Databases and a more recent Oracle JDBC driver release.