Connect to an Oracle cluster in Java
cluster-computing, java, jdbc, oracle
Solution
For Oracle JDBC thin driver I think this could work as the connection url:
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=TCP)(HOST=1.2.3.4) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=1.2.3.5) (PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=example.gov)))
Use the method setUrl to set the url. If URL is set all other properties like databasename, servername, portNumber, network protocol, tnsentry, and driver type will be ignored.
Hope this helps!
Problem
We have a pair of Oracle servers which are set up as nodes in a cluster (apologies if my terminology is way off). In my `tnsnames.ora` file, we have an entry that looks like ``` EXAMPLE.GOV = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.4)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.5)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = example.gov) ) ) ``` and this works when I connect with programs which use the `tnsnames.ora` file. However, I also have a Java program which uses the oracle.jdbc.pool.OracleDataSource class to establish a connection ``` public static Connection connect() throws Exception { OracleDataSource ods = new OracleDataSource(); ods.setDriverType("thin"); ods.setServerName("1.2.3.4"); ods.setDatabaseName("example"); ods.setPortNumber(1521); ods.setUser("scott"); ods.setPassword("tiger"); return ods.getConnection(); } ``` which just connects to one of the nodes directly. I'd like to instead use the load-balancing `tnsnames.ora` approach, where it uses load balancing or whatever to connect to either one of the nodes, so that if one of them is down then it'll automatically connect to the other. Since I only have two nodes, I could easily just try opening a connection to the first node, then if that doesn't work open a connection to the second one. However, I'm wondering if there's a more correct way to do this. I see that there's a `setTNSEntryName` parameter, but since my `tnsnames.ora` is in a nonstandard place, I'd need to set the `TNS_ADMIN` environment variable, which I'm not sure that I can even do from within Java. Nor am I certain that this would work in any case. Does anyone know how to connect to a cluster of Oracle nodes from a Java program?