HikariCP Best Practices for Oracle Database and Spring Boot
Irina Granat — Richard Exley— Kuassi Mensah
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 at: 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
- 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-jdbcorspring-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 is an example of 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=LOCALSetting the oracle.jdbc.defaultConnectionValidation property set to LOCAL 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=LOCALTo 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
Please refer to the HikariCP documentation for more details.
Here follows 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.addDataSourceProperty("url",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.
Configuring for Basic High Availability — Level 1
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.
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 TRUEThe -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:
- 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.
- 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 DataSourceConfiguration class (as shown above) to force connection validation at checkout.
-Dcom.zaxxer.hikari.aliveBypassWindowMs=-1System.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 LOCAL to mitigate the impact. You have already set such a property above.
props.setProperty("oracle.jdbc.defaultConnectionValidation", "LOCAL");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 as follows.
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);
Properties props = new Properties();
props.setProperty("oracle.jdbc.defaultConnectionValidation", "LOCAL");
config.addDataSourceProperty("connectionProperties", props);
return new HikariDataSource(config);
}
}You may notice in the Java class that we are using both config.setJdbcUrl(System.getenv(“JDBC_URL”)) and
config.addDataSourceProperty(“url”,System.getenv(“JDBC_URL”)).
With setJdbcUrl, Hikari creates the driver based on the specified URL.
When a data source class name is specified, Hikari creates an instance of that class and calls the setter methods that match the data source.
Performing Planned Maintenance
The following command initiates the draining of active sessions.
The -drain_timeout 60parameter, 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 -failoverPlanned 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.
- 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 6002. To enable continuous service during unplanned outages, include the replay data source oracle.jdbc.replay.OracleDataSourceImplin 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;
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);
}
}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.
