This previously published blog now covers the new Oracle Database 23ai release and HikariCP 6.0.0 updates. The earlier version of the blog can be reviewed here.
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. In this blog, we will cover HikariCP ver. 6.0.0 which includes support for request boundaries (beginRequest and endRequest API support) that are part of the JDBC 4.3 standard. The request boundaries enable HikariCP to transparently inject BeginRequest and EndRequest, similarly to UCP, thereby enabling the JDBC driver to perform replay as part of Transparent Application Continuity (TAC).
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 and HikariCP version 6.0.0
Before zooming into HikariCP, a few words about the Universal Connection Pool (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 basic steps for using HikariCP with the Oracle Database consist of (i) configuring the Oracle JDBC driver, and (ii) configuring HikariCP.
Add Oracle Driver as a dependency in Spring Initializer or manually add it to your project’s pom.xml.
For Oracle database 23ai, the version of JDBC is 23.6.0.24.10:
<properties>
<oracle.jdbc.version>23.6.0.24.10</<oracle.jdbc.version>
</properties>
<dependencies>
...
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc11</artifactId>
<version>${oracle.jdbc.version}</version>
</dependency>
...
</dependencies>
For Oracle database 19c, the version of JDBC 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>
Use this page for more information on Oracle RDBMS and JDK Version Compatibility for Oracle JDBC Drivers.
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. However, in order to use the latest HikariCP release (i.e., version 6.0.0 as of this writing) which implements request boundaries option, we also need to add the Hikari dependency in the pom.xml explicitly:
<dependencies>
...
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>6.0.0</version>
</dependency>
...
</dependencies>
3. Then configure the application properties using one of the following methods: (i) using application.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 application.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 the spring.datasource.hikari
namespace. 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 Database - Connection Details
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.minimum-idle=4
spring.datasource.hikari.data-source-properties.oracle.jdbc.defaultConnectionValidation=LOCAL
In this example we enabled the following HikariCP properties:
Validation comes at a cost, and you should decide what value works best for your application. Here is an analysis showcasing the processing time associated with each value:
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 where correctly registered:
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG
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.setMinimumIdle(4);
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.
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.
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.
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. 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
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)))
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:
If your application has been designed to handle planned and unplanned outages via retry logic, then you don’t need anything else.
To prepare applications for planned maintenance, enable the request boundaries and add the following connection validation settings to your application code.
Please note that the major change in Hikari 6.0.0 is a support for request boundaries and this feature is disabled by default. Enabling the request boundaries will make draining more efficient - drain can occur at both checkout's (i.e., at connection-validation) and checkin's (i.e., at endRequest). It also enables a complete support for both AC-manual and TAC. Request boundaries should be enabled by setting the following system property:
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.
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 system property either as a JVM system property or programmatically in your DataSourceConfig class (as shown below) to force connection validation at checkout.
A systematic connection validation on borrow may have a slight performance impact; Oracle recommends setting a lightweight connection validation property oracle.jdbc.defaultConnectionValidation t
o "LOCAL" or "SOCKET" (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");
System.setProperty("com.zaxxer.hikari.enableRequestBoundaries", "true");
HikariConfig config = new HikariConfig();
config.addDataSourceProperty("url",System.getenv("JDBC_URL"));
config.setUsername(System.getenv("DB_USER"));
config.setPassword(System.getenv("DB_PASSWORD"));
config.setDataSourceClassName("oracle.jdbc.datasource.impl.OracleDataSource");
config.setMinimumIdle(4);
config.setMaximumPoolSize(4);
Properties props = new Properties();
props.setProperty("oracle.jdbc.defaultConnectionValidation", "SOCKET");
config.addDataSourceProperty("connectionProperties", props);
return new HikariDataSource(config);
}
}
For Oracle database 19c, change the value of config.setDataSourceClassName for the data source creation to oracle.jdbc.datasource.impl.OracleDataSource:
config.setDataSourceClassName("oracle.jdbc.pool.OracleDataSource");
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
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 gv$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(*)
----------------------------------- --------- ---------- ----------
04-NOV-24 12.33.23.964298 AM +00:00 app1 2 3
04-NOV-24 12.33.23.964298 AM +00:00 app2 1 4
04-NOV-24 12.33.23.964298 AM +00:00 app2 1 1
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(*)
----------------------------------- --------- ---------- ----------
04-NOV-24 12.33.29.945024 AM +00:00 app1 2 3
04-NOV-24 12.33.29.945024 AM +00:00 app2 2 4
04-NOV-24 12.33.29.945024 AM +00:00 app2 1 1
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(*)
----------------------------------- --------- ---------- ----------
04-NOV-24 12.33.31.964371 AM +00:00 app1 2 4
04-NOV-24 12.33.31.964371 AM +00:00 app2 2 4
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. We recommend setting "oracle.jdbc.defaultConnectionValidation" to "SOCKET" to have a better coverage for unplanned outages.
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");
System.setProperty("com.zaxxer.hikari.enableRequestBoundaries", "true");
HikariConfig config = new HikariConfig();
config.addDataSourceProperty("url",System.getenv("JDBC_URL"));
config.setUsername(System.getenv("DB_USER"));
config.setPassword(System.getenv("DB_PASSWORD"));
config.setDataSourceClassName("oracle.jdbc.datasource.impl.OracleDataSource");
config.setMinimumIdle(4);
config.setMaximumPoolSize(4);
Properties props = new Properties();
props.setProperty("oracle.jdbc.defaultConnectionValidation", "SOCKET");
config.addDataSourceProperty("connectionProperties", props);
return new HikariDataSource(config);
}
}
For Oracle database 19c, change the value of config.setDataSourceClassName for the data source creation to oracle.jdbc.replay.OracleDataSourceImpl:
config.setDataSourceClassName("oracle.jdbc.replay.OracleDataSourceImpl");
Depending on your application functionality and performance needs, you might also consider adding the following properties to the HikariCP configuration and/or enable Oracle's Database Resident Connection Pooling (DRCP):
1. The spring.datasource.hikari.connection-timeout property → controls the maximum number of milliseconds an application is willing to wait for a connection from the pool. If pool is exhausted and this time is reached, then an exception is thrown with a message “connection acquisition timed out”. Lowest acceptable connection timeout is 250 ms (Oracle's UCP supports much lower minimum). Default: 30000 (30 seconds).
Note: If the maximum number of connections exhausted from the pool and a new request comes in, it will behave depending on the connection-timeout. The new request will wait until the connection is available or it is timed out.
2. The oracle.jdbc.implicitStatementCacheSize property→ by design HikariCP doesn’t implement a statement cache as explained by the author. Oracle JDBC thin driver provides an implicit statement cache that will take care of optimizing how statements and cursors are allocated and freed. To enable it just set the property "oracle.jdbc.implicitStatementCacheSize" to a value for example "10" which is usually a good size for the statement cache.
Here are the examples of application.properties file and DataSourceConfig class including these properties.
i. application.properties
# Oracle Database DataSource Configuration
spring.datasource.url=${HATEST_JDBC_URL}
spring.datasource.username=${HATEST_DB_MAIN_USER}
spring.datasource.password=${HATEST_DB_MAIN_PASSWORD}
# HikariCP settings
spring.datasource.hikari.maximum-pool-size=4
spring.datasource.hikari.minimum-idle=4
spring.datasource.hikari.connection-timeout=250
spring.datasource.hikari.data-source-properties.oracle.jdbc.defaultConnectionValidation=LOCAL
spring.datasource.hikari.data-source-properties.oracle.jdbc.implicitStatementCacheSize=10
ii. 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 oracle.jdbc.OracleConnection;
import java.util.Properties;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
@Bean
public HikariDataSource getDataSource () {
System.setProperty("com.zaxxer.hikari.aliveBypassWindowMs", "-1");
System.setProperty("com.zaxxer.hikari.enableRequestBoundaries", "true");
HikariConfig config = new HikariConfig();
config.addDataSourceProperty("url",System.getenv("JDBC_URL"));
config.setUsername(System.getenv("DB_USER"));
config.setPassword(System.getenv("DB_PASSWORD"));
config.setDataSourceClassName("oracle.jdbc.datasource.impl.OracleDataSource");
config.setMinimumIdle(4);
config.setMaximumPoolSize(4);
config.setConnectionTimeout(250);
Properties props = new Properties();
props.setProperty("oracle.jdbc.defaultConnectionValidation", "SOCKET");
props.setProperty("oracle.jdbc.implicitStatementCacheSize", "10");
config.addDataSourceProperty("connectionProperties", props);
return new HikariDataSource(config);
}
}
3. To enable Database Resident Connection Pooling (DRCP), you can refer to Oracle’s “Database Resident Connection Pooling” documentation for detailed configuration steps. DRCP is a feature of Oracle Database that allows applications and mid-tier services to efficiently manage multiple data requests while optimizing database resource utilization.
Along with enabling DRCP on the server side, you also need to configure it on the client side. This involves setting a new connection string and enabling the required properties in the DataSourceConfig class.
System.setProperty("com.zaxxer.hikari.enableRequestBoundaries", "true");
Here are the examples of DataSourceConfig class including these properties:
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 oracle.jdbc.OracleConnection;
import java.util.Properties;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
@Bean
public HikariDataSource getDataSource () {
System.setProperty("com.zaxxer.hikari.aliveBypassWindowMs", "-1");
System.setProperty("com.zaxxer.hikari.enableRequestBoundaries", "true");
HikariConfig config = new HikariConfig();
config.addDataSourceProperty("url",System.getenv("JDBC_POOLED_URL"));
config.setUsername(System.getenv("DB_USER"));
config.setPassword(System.getenv("DB_PASSWORD"));
config.setDataSourceClassName("oracle.jdbc.datasource.impl.OracleDataSource");
config.setMinimumIdle(4);
config.setMaximumPoolSize(4);
config.setConnectionTimeout(250);
Properties props = new Properties();
props.setProperty("oracle.jdbc.defaultConnectionValidation", "SOCKET");
props.setProperty("oracle.jdbc.implicitStatementCacheSize", "10");
props.setProperty("oracle.jdbc.DRCPMultiplexingInRequestAPIs", "true");
props.setProperty("oracle.jdbc.DRCPConnectionClass", "DRCP_POOL_NAME");
config.addDataSourceProperty("connectionProperties", props);
return new HikariDataSource(config);
}
}
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 databse 23ai.
The steps described in this blog post are based on an Oracle RAC system and Oracle JDBC Drivers v19.21.0.0 and 23.6.0.24.10. We hope you found this blog post helpful. Thanks for reading!
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
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
Next Post