HikariCP Best Practices for Oracle Database and Spring Boot

January 25, 2024 | 12 minute read
Kuassi Mensah
Director Product Management
Richard Exley
Consulting Member of Technical Staff, Oracle Database
Text Size 100%:

HikariCP is a popular Java connection pool, commonly used with Spring Boot. This blog post furnishes the best practices for configuring HikariCP with Spring Boot for the Oracle Database.

You will learn how to configure HikariCP to leverage the Oracle Databases’ High Availability (HA) capabilities for continuous service during planned and unplanned downtime.
All the recommendations in this blog post have been tested using the latest Long Term Support (LTS) Oracle Database release i.e., 19c (19.21).

About UCP

The Oracle Database furnishes the Universal Connection Pool (UCP), a feature-rich Java connection pool — one of the Spring Boot choices — that supports out-of-the-box, all Oracle database configurations, and mission-critical capabilities such as 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/

You may have standardized on HikariCP and do not necessarily need the advanced and mission-critical UCP features. However, if you’d like to migrate from HikariCP to UCP, here is the blog post for you.

The Basics

The basic steps for using HikariCP with the Oracle Database consist of (i) configuring the Oracle JDBC driver, and (ii) configuring HikariCP.

Configure the Oracle JDBC Driver

Add Oracle Driver as a dependency in Spring Initializr or manually add it to your project’s pom.xml.

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

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

Configure HikariCP

1. Starting with version 2, Spring Boot uses HikariCP as the default connection pool and it is transitively imported with the following Spring Boot starters: spring-boot-starter-jdbc or spring-boot-starter-data-jpa.

<parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>3.0.4</version>
  <relativePath/> <!-- lookup parent from repository -->
 </parent>

<dependencies>
...
<dependency>
<! -- Assume Spring Data JDBC -->
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
...
</dependencies>

2. Then configure the application properties using one of the following methods: (i) using aapplication.properties file (Spring Boot automatically reads these files and applies the configuration), (ii) creating a configuration class annotated with @Configuration and using @Bean to define the DataSource bean with HikariCP settings — we are using this method hereafter, (iii) using aapplication.yaml file, and (iv) using a Kubernetes secret, for a more secure method. See this UCP-related blog post for more details about the approaches. Pick the approach that best fits your project and preferences. The first method might be simpler and is often sufficient for basic setups, while the second method provides more flexibility and control over the configuration in a Java class.

HikariCP has default settings that perform well in most deployments without additional tweaking. Spring Boot exposes Hikari-specific properties using thespring.datasource.hikarinamespace. Every property is optional except for the few essential ones that must be set. HikariCP’s official GitHub page explains various configuration options. In addition, you can review the Common Application Properties page listing all Spring Boot application.properties options.

Here are the examples of application.properties file and DataSourceConfig class:

i. An application.properties file with the minimum set of properties required for using the Oracle Database.

# Oracle DataSource Configuration
spring.datasource.url=${JDBC_URL}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

# HikariCP settings
spring.datasource.hikari.maximum-pool-size=4
spring.datasource.hikari.data-source-properties.oracle.jdbc.defaultConnectionValidation=LOCAL

Setting the oracle.jdbc.defaultConnectionValidation property set to LOCAL or SOCKET enables lightweight connection validation by the JDBC driver when you call the isValid(timeout) method. We strongly recommend setting up such a property.

You may add other HikariCP properties based on your application requirements however, when setting multiple data source properties beware of the syntax and side effects.
The following setting will erase the first data source property setting and preserve only the last one.

spring.datasource.hikari.data-source-properties=oracle.jdbc.implicitStatementCacheSize=10
spring.datasource.hikari.data-source-properties=oracle.jdbc.defaultConnectionValidation=LOCAL

To successfully set both data source properties, you need to use the following syntax.

spring.datasource.hikari.data-source-properties.oracle.jdbc.implicitStatementCacheSize=10
spring.datasource.hikari.data-source-properties.oracle.jdbc.defaultConnectionValidation=LOCAL

We also recommend to enable additional logging to verify that your properties where correctly registered:

logging.level.com.zaxxer.hikari.HikariConfig=DEBUG 
logging.level.com.zaxxer.hikari=TRACE

Please refer to the HikariCP documentation for more details.

ii. An example of a DataSourceConfig class defining the minimum set of properties that the DataSource requires to work with the Oracle Database.

package maa.spring;
import org.springframework.context.annotation.Bean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariConfig;

import java.util.Properties;
import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {
    @Bean
    public HikariDataSource getDataSource () {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(System.getenv("JDBC_URL"));
        config.setUsername(System.getenv("DB_USER"));
        config.setPassword(System.getenv("DB_PASSWORD"));
        config.setMaximumPoolSize(4);
        config.addDataSourceProperty("oracle.jdbc.defaultConnectionValidation", "LOCAL");
        return new HikariDataSource(config);
    }
}

At this stage, you have successfully configured your Spring Boot application to work with the Oracle Database using HikariCP.

The next sections cover the steps for configuring HikariCP 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: using a database service, configuring a URL or a connection string for High Availability, enabling Fast Application Notification (FAN), implementing draining, and enabling continuous database availability for Java applications.
You may choose the level of HA 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 HikariCP for sustaining planned and unplanned outages. The detailed steps and explanations for the continuous availability of your applications are covered in Oracle’s “High Availability Overview and Best Practices” documentation.

Configuring for Basic High Availability — Level 1

Implement a level of high availability that allows applications to immediately react to instance, node, or database failures, and quickly establish new connections to surviving database instances.

With application HA Level 1, downtime is minimized for unplanned and planned outages.

Configure High Availability Database Services

With Oracle RAC, the Oracle database service may be deployed over several nodes in the cluster. Planned outage allows performing maintenance operations on a subset of the service nodes/hosts. The database admin issues a command that generates a planned down event. As HikariCP is not instrumented to handle such event type, it will be handled by the Oracle JDBC. The driver will drain i.e., transparently and smoothly close all active connections thereby allowing the nodes scheduled for maintenance, to be shut down without impacting the applications (the remaining active nodes will absorb the workload).

Create a dedicated service to support HA features as follows.

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

The -notification TRUE enables FAN for this service. Your application will connect to such a service.

Configure the JDBC Connection String for High Availability

Configure your Java application to use the following connect string.

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

Enable Fast Application Notification (FAN) at the Driver Level

As HikariCP has not been instrumented to handle FAN events, the planned maintenance will be managed by the Oracle JDBC driver. To configure the driver to handle FAN events, add simplefan and ons jar files to your project’s pom.xml.

<dependencies>

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

...
  </dependencies>

At this stage, the basic High Availability has been configured and 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 is failing 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, then you don’t need anything else.

Configuring for Planned Maintenance — Level 2

To prepare applications for planned maintenance, add the following connection validation settings to your application code.

Enabling Predictable Connection Validation

By design, HikariCP validates connections that have been idle for more than 500ms (in the latest version). If a connection is constantly being checked out and checked in it won’t be validated, and hence won’t be drained by the Oracle JDBC driver.
Set the following property either as a JVM system property or programmatically in your DataSourceConfig class (as shown below) to force connection validation at checkout.

  • -Dcom.zaxxer.hikari.aliveBypassWindowMs=-1
  • System.property("com.zaxxer.hikari.aliveBypassWindowsMs","-1");

Please be aware that a system property has JVM scope, so all HikariCP pools in that JVM instance will be affected by such a setting.

A systematic connection validation on borrow may have a slight performance impact; Oracle recommends setting a lightweight connection validation property oracle.jdbc.defaultConnectionValidation to SOCKET or LOCAL(SOCKET has a small performance penalty while LOCAL might limit the driver's ability to detect the outage) to mitigate the impact.

Setting the data source class name using the application.properties or the applications.yaml files, throws the following error message: “Caused by: java.lang.IllegalStateException: both driverClassName and dataSourceClassName”. We need to set it programmatically using DataSourceConfig class as follows (at this point, we recommend to use only DataSourceConfig class file for any additional settings and not to combine it with application.properties file).

package maa.spring;
import org.springframework.context.annotation.Bean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariConfig;

import java.util.Properties;
import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    @Bean
    public HikariDataSource getDataSource () {
        System.setProperty("com.zaxxer.hikari.aliveBypassWindowMs", "-1");
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(System.getenv("JDBC_URL"));
        config.setUsername(System.getenv("DB_USER"));
        config.setPassword(System.getenv("DB_PASSWORD"));
        config.setMaximumPoolSize(4);
        config.addDataSourceProperty("oracle.jdbc.defaultConnectionValidation", "LOCAL");
        return new HikariDataSource(config);
    }
}

The above example demonstrates data source configuration for Oracle JDBC driver. For using oracle.jdbc.pool.OracleDataSource, the configuration is slightly different (see an example below):

package maa.spring;
import org.springframework.context.annotation.Bean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariConfig;

import java.util.Properties;
import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    @Bean
    public HikariDataSource getDataSource () {
        System.setProperty("com.zaxxer.hikari.aliveBypassWindowMs", "-1");
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(System.getenv("JDBC_URL"));
        config.addDataSourceProperty("url",System.getenv("JDBC_URL"));
        config.setUsername(System.getenv("DB_USER"));
        config.setPassword(System.getenv("DB_PASSWORD"));
        config.setMaximumPoolSize(4);
        config.setDataSourceClassName("oracle.jdbc.pool.OracleDataSource"); 
        Properties props = new Properties();
        props.setProperty("oracle.jdbc.defaultConnectionValidation", "LOCAL");
		config.addDataSourceProperty("connectionProperties", props);
        return new HikariDataSource(config);
    }
}

Performing Planned Maintenance

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

Under load (i.e., database activities), observe that the connections drain away from that instance and that the instance/node can be shut down.
In this example, we are monitoring the draining via v$session.

... 
SQL> 
TIME                               MACHINE   INST_ID   COUNT(*)
----------------------------------- --------- ---------- ----------
2023-12-21 17:06:04 
22-DEC-23 01.06.04.504891 AM +00:00 app1        2         3
2023-12-21 17:06:04 
22-DEC-23 01.06.04.504891 AM +00:00 app2        1         2
2023-12-21 17:06:04 
22-DEC-23 01.06.04.504891 AM +00:00 app2        2         2
2023-12-21 17:06:04 
22-DEC-23 01.06.04.504891 AM +00:00 app1        1         1
...
SQL> 
TIME                                MACHINE  INST_ID   COUNT(*)
----------------------------------- --------- ---------- ----------
2023-12-21 17:06:05 
22-DEC-23 01.06.05.685695 AM +00:00 app1        1         1
2023-12-21 17:06:05 
22-DEC-23 01.06.05.685695 AM +00:00 app1        2         3
2023-12-21 17:06:05 
22-DEC-23 01.06.05.685695 AM +00:00 app2        2         2
2023-12-21 17:06:05 
22-DEC-23 01.06.05.685695 AM +00:00 app2        1         2
....SQL> 
TIME                                 MACHINE  INST_ID   COUNT(*)
----------------------------------- --------- ---------- ----------
2023-12-21 17:06:07 
22-DEC-23 01.06.07.486856 AM +00:00 app1        2         4
2023-12-21 17:06:07 
22-DEC-23 01.06.07.486856 AM +00:00 app2        2         4

Configuring for Unplanned Outages — Level 3

For planned maintenance, draining is used to allow active sessions to finish their work (drain) before the database instance is shut down. However, in unplanned events such as an instance, node, or database service failures, all sessions connected to the faulty node/instance will be terminated immediately — there is no time for draining. The Fast Application Notification (FAN) mechanism will detect those unplanned events and notify the JDBC driver or any connection pool such as UCP that has been instrumented to receive and process the FAN messages.

As HikarCPi is not capable of handling FAN events, the Oracle JDBC driver transparently cleans up the orphan connections and replays in-flight requests on a surviving database instance (using new connections); the failures will therefore be transparent to the applications that were using those connections.

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 upon which the replay will not be performed.

$ srvctl add service -db mydb -service MYSERVICE -pdb mypdb
-notification TRUE -failover_restore AUTO -failovertype AUTO -replay_init_time 600

2. To enable continuous service during unplanned outages, include the replay data source oracle.jdbc.replay.OracleDataSourceImpl in your DataSourceConfig class.

package maa.spring;

import org.springframework.context.annotation.Bean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariConfig;

import java.util.Properties;
import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    @Bean
    public HikariDataSource getDataSource () {
        System.setProperty("com.zaxxer.hikari.aliveBypassWindowMs", "-1");
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(System.getenv("JDBC_URL"));
        config.addDataSourceProperty("url",System.getenv("JDBC_URL"));
        config.setUsername(System.getenv("DB_USER"));
        config.setPassword(System.getenv("DB_PASSWORD"));
        config.setDataSourceClassName("oracle.jdbc.replay.OracleDataSourceImpl");
        config.setMaximumPoolSize(4);
        Properties props = new Properties();
        props.setProperty("oracle.jdbc.defaultConnectionValidation", "LOCAL");
        config.addDataSourceProperty("connectionProperties", props);
        return new HikariDataSource(config);
    }
}

You may notice that we added config.addDataSourceProperty("url",System.getenv("JDBC_URL")) setting to HikariDataSource in addition to config.setJdbcUrl(System.getenv("JDBC_URL")) (this setting will be ignored in this case) - it is required to resolve java.sql.SQLException: Invalid Oracle URL specified: OracleDataSource.makeURL exception.

Unplanned Outage Success Test

Under load (i.e., database activities), emulate failure using ALTER SYSTEM KILL SESSION <sid>.

To determine, if TAC is enabled successfully in your environment, you can use ACCHK. This database feature shows you the level of protection for your applications for failover. Review the statistics to determine the extent of protected calls or If the protected call count or protected time decreases. You can review ACCHK utility usage details in this document Application Continuity Protection Check.

Wrap-up

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

Further Readings

Kuassi Mensah

Director Product Management

Kuassi is Director of Product Management at Oracle, in charge of

•Java database access, performance , scalability, availability and frameworks for the Oracle database (OJVM, JDBC, UCP, App Cont, TG, etc)

•Hadoop, Spark and Flink integration with the Oracle database

•JavaScript with Oracle database (Nashorn with JDK, JRE and OJVM)

Graduate MS in CS from the Programming Institute of University of Paris VI (Jussieu)

Frequent speaker @ JavaOne, Oracle Open World, Data Summit, Node Summit, Collaborate/IOUG, RMOUG, BIWA , UKOUG, DOAG, OUGN, BGOUG, OUGF, OTN LAD, OTN APAC.

Author: Oracle Database Programming using Java and Web Services

Social network: @kmensah, http://db360.blogspot.com/, https://www.linkedin.com/in/kmensah

Richard Exley

Consulting Member of Technical Staff, Oracle Database

Irina Granat

Irina Granat is a member of MAA group in Oracle Database Development organization focusing on Microservices and SaaS applications.

Irina (Rena) has over 20 years of experience in the Software Industry with Leadership, Solution Consultant and Management abilities that span ERP, CRM, HCM, SaaS, PaaS, Applications, and Technology Platforms.


Previous Post

The topmost requested Oracle-related books of 2023

Lisa Goldstein | 2 min read

Next Post


Deploy and Scale Spring Boot Microservices with Confidence.

Sanjay Goil | 10 min read