Single JDBC OracleDataSource/HikariCP with primary/backup DB

Question

我正在尝试设置一个引用我们的主数据库的单个连接池，直到所述变得不正常，之后池故障转移，填满我们的备份。到目前为止，我一直在利用应用程序服务器的JNDI数据源的未记录功能，该功能允许我指定2个JDBC连接URL字符串：

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

我有以下代码，毫无疑问，部分是几个月前从一些Hikari / Spring文档中抄录下来的。

@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);
}

我的Google-Fu让我失望了。有人对如何实现故障转移功能有什么想法吗？

Edit-re.@M. Deina"删除OracleDataSource的构造，只需在HikariConfig上设置url。"

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

不幸的是，这会产生一个相当长的堆栈，其基础是这样的：

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)

在这里调查-Hikaricp Oracle连接问题和这里-指定的无效Oracle URL：OracleDataSource.makeURL导致我添加一些额外的属性。

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

现在炸弹爆炸了:

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)

JDBC网址似乎不再被引用了。。。并且，确认了——我从URL中取出了备份连接字符串，并使用标准的单一服务器连接获得了相同的异常。所以看起来ODS要求像最初那样配置(或者用属性模仿)。

作为使用这种方法的最后一招，我尝试将serverName属性设置为“primary|standby ”,不出所料，它也失败了:

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

到目前为止，我还没有注意到我正在使用ojdbc7.jar。

Answer 1

使用标准方式。支持 DataGuard、failover、RAC 是 Oracle JDBC 驱动程序的原生功能。

首先使用tnsnames.ora，如“如何将JDBC连接到tns oracle”中所述

2nd 在 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驱动程序将连接到主机，其中数据库为“OPEN”并且存在名为“DB”的服务。

PS:也可以将整个tns连接字符串直接作为参数传递给jdbc驱动。

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