Single JDBC OracleDataSource/HikariCP with primary/backup DB

Question

I'm trying to set up a single connection pool which references our primary database until said becomes unhealthy and after which the pool fails over, filling up against our backup. Until now I've been taking advantage of an undocumented feature of our application server's JNDI datasources which allows me to specify 2 JDBC connection URL strings thusly:

jdbc:oracle:thin:@primary:1521:DB|jdbc:oracle:thin:@backup:1521:DB

I have the following code, no doubt partially cribbed from some Hikari/Spring documentation months ago.

@Bean(name = "dataSource")
public DataSource dataSource() throws SQLException {
    String userName = "user";
    String password = "pass";
    String server = "primary";
    String database = "DB";

    OracleDataSource ods = new OracleDataSource();
    ods.setServerName(server);
    ods.setDatabaseName(database);
    ods.setNetworkProtocol("tcp");
    ods.setUser(userName);
    ods.setPassword(password);
    ods.setPortNumber(1521);
    ods.setDriverType("thin");

    HikariConfig hkConfig = new HikariConfig();
    hkConfig.setDataSource(ods);
    hkConfig.setDataSourceClassName("oracle.jdbc.pool.OracleDataSource");
    hkConfig.setPoolName("springHikariRECPool");
    hkConfig.setMaximumPoolSize(15);
    hkConfig.setMinimumIdle(3);
    hkConfig.setMaxLifetime(1800000); // 30 minutes

    return new HikariDataSource(hkConfig);
}

My Google-Fu has failed me. Does anyone have any ideas on how to achieve the failover functionality?

Edit - re. @M. Deinum "Remove the construction of the OracleDataSource and just set the url on the HikariConfig."

HikariConfig hkConfig = new HikariConfig();
hkConfig.setUsername(userName);
hkConfig.setPassword(password);
hkConfig.setJdbcUrl("jdbc:oracle:thin:@primary:1521:DB|jdbc:oracle:thin:@backup:1521:DB");
hkConfig.setDataSourceClassName("oracle.jdbc.pool.OracleDataSource");
hkConfig.setPoolName("springHikariRECPool");
hkConfig.setMaximumPoolSize(15);
hkConfig.setMinimumIdle(3);
hkConfig.setMaxLifetime(1800000);

Unfortunately, this yields a fairly long stack, the base of which is this:

Caused by: java.sql.SQLException: Invalid Oracle URL specified: OracleDataSource.makeURL
    at oracle.jdbc.pool.OracleDataSource.makeURL(OracleDataSource.java:1277)
    at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:185)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:356)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:199)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:444)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:515)

Investigation of that here - Hikaricp Oracle connection issue and here - Invalid Oracle URL specified: OracleDataSource.makeURL causes me to add some additional properties.

hkConfig.addDataSourceProperty("portNumber", "1521");
hkConfig.addDataSourceProperty("driverType", "thin");

Which now bombs with:

Caused by: java.net.UnknownHostException: null: Name or service not known
    at java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method)
    at java.net.InetAddress$2.lookupAllHostAddr(InetAddress.java:928)
    at java.net.InetAddress.getAddressesFromNameService(InetAddress.java:1323)
    at java.net.InetAddress.getAllByName0(InetAddress.java:1276)
    at java.net.InetAddress.getAllByName(InetAddress.java:1192)
    at java.net.InetAddress.getAllByName(InetAddress.java:1126)
    at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:117)
    at oracle.net.nt.ConnOption.connect(ConnOption.java:133)
    at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:370)

The JDBC URL is no longer being referenced, it would appear. . . and, confirmed - I took the backup connection string out of the URL and reached the same exception with a standard, single server connection. So it appears the ODS demands to be configured as originally done (or mimicked with Properties).

As a last gasp for this method, I tried setting the serverName property to "primary|standby" and, as expected, that blew up as well:

Caused by: java.net.UnknownHostException: primary|backup: Name or service not known
    at java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method)
    at java.net.InetAddress$2.lookupAllHostAddr(InetAddress.java:928)
    at java.net.InetAddress.getAddressesFromNameService(InetAddress.java:1323)
    at java.net.InetAddress.getAllByName0(InetAddress.java:1276)
    at java.net.InetAddress.getAllByName(InetAddress.java:1192)
    at java.net.InetAddress.getAllByName(InetAddress.java:1126)
    at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:117)
    at oracle.net.nt.ConnOption.connect(ConnOption.java:133)
    at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:411)
    ... 56 more

I have failed to note thus far that I am using ojdbc7.jar.

Remove the construction of the `OracleDataSource` and just set the url on the `HikariConfig`. — M. Deinum, Apr 21 '17 at 20:03
@M.Deinum question updated in response. Thanks for your input. — Jon Sampson, Apr 24 '17 at 15:16

score 2 · Accepted Answer · edited May 23 '17 at 11:54

Use standard way. Support for DataGuard, failover, RAC is native feature of Oracle JDBC drivers.

1st use tnsnames.ora as described here "How to connect JDBC to tns oracle"

2nd use multiple hosts in tnsnames.ora:

DB =
(DESCRIPTION=
(ADDRESS_LIST=
  (LOAD_BALANCE=off)
  (FAILOVER=ON)
  (ADDRESS=(PROTOCOL=TCP)( HOST=primary)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)( HOST=backup)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=DB)))

Oracle JDBC driver will connect to the host, where database is "OPEN" and the service named "DB" is present.

PS: you can also pass the whole tns connection string to the jdbc driver directly as a parameter.

url="jdbc:oracle:thin:@(DESCRIPTION=
  (LOAD_BALANCE=on)
   (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=TCP)(HOST=primary)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=secondary)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=DB)))"

I would have loved a 'simple' version that didn't include installing Oracle Client etc but that does seem to be the other path. It's middleware or Client. Thanks! — Jon Sampson, Apr 24 '17 at 15:37

Single JDBC OracleDataSource/HikariCP with primary/backup DB

1 Answers1