HikariCP Best Practices for Oracle Database and Spring Boot — V2
Irina Granat — Richard Exley— Kuassi Mensah
This previously published blog now covers the new Oracle Database 23ai release.
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) and 23ai.
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, this blog post is for you.
The Basics
The basic steps for using HikariCP with the Oracle Database are (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>For Oracle database 23ai, the version of JDBC 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>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 are examples of an application.properties file and DataSourceConfig.java class with the minimum set of properties required to use the Oracle Database.
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=LOCALSetting 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.
We also recommend enabling additional logging to verify that your properties were correctly registered:
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG
logging.level.com.zaxxer.hikari=TRACEPlease 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 Database’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 TRUEThe -notification TRUE enables FAN for this service. Your application will connect to that 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 DataSourceConfig.java 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 a setting like this will affect all HikariCP pools in that JVM instance.
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.
props.setProperty("oracle.jdbc.defaultConnectionValidation", "LOCAL");Setting the data source class name using the application.properties or the applications.yaml files throw the following error message: “Caused by: java.lang.IllegalStateException: both driverClassName and dataSourceClassName.” We need to set it programmatically as follows (at this point, we recommend using only the DataSourceConfig class file for any additional settings and not combining it with the 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.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);
}
}For Oracle database 23ai, change config.setDataSourceClassName value for data source creation to oracle.jdbc.datasource.impl.OracleDataSource:
config.setDataSourceClassName("oracle.jdbc.datasource.impl.OracleDataSource");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
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;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(*)
----------------------------------- --------- ---------- ----------
24-JUN-24 04.44.50.330975 PM +00:00 app2 2 2
24-JUN-24 04.44.50.330975 PM +00:00 app1 2 1
24-JUN-24 04.44.50.330975 PM +00:00 app1 1 3
24-JUN-24 04.44.50.330975 PM +00:00 app2 1 2To 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 -failoverFast 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(*)
----------------------------------- --------- ---------- ----------
24-JUN-24 04.44.58.313179 PM +00:00 app1 2 4
24-JUN-24 04.44.58.313179 PM +00:00 app2 2 2
24-JUN-24 04.44.58.313179 PM +00:00 app2 1 2If 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(*)
----------------------------------- --------- ---------- ----------
24-JUN-24 04.45.04.310911 PM +00:00 app1 2 4
24-JUN-24 04.45.04.310911 PM +00:00 app2 2 4Configuring 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);
}
}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.
For Oracle database 23ai, change the value of config.setDataSourceClassName for the data source creation to
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 out this technical brief—What’s in ‘Oracle DB 23c Free Developer Release’ for Java Developers—for more information about the new JDBC Self-Driven Diagnosability in Oracle database 23ai.
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.
Thank you for reading!
