Connecting to an Oracle Cluster in Java

Question

We have a couple of Oracle servers that are configured as nodes in the cluster (apologies if my terminology is disabled). 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 to programs that use the tnsnames.ora file. However, I also have a Java program that 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 connects directly to one of the nodes. I would like to use the tnsnames.ora load balancing method tnsnames.ora , where it uses load balancing or whatever to connect to one of the nodes, so if one of them is turned off, it will automatically connect to the other.

Since I only have two nodes, I would just try to open a connection to the first node, and then if this does not work, open a connection to the second. However, I am wondering if there is a better way to do this.

I see that there is a setTNSEntryName parameter, but since my tnsnames.ora is in a non-standard place, I need to set the TNS_ADMIN environment TNS_ADMIN , which I'm not sure what I can even do from inside Java. I'm also not sure if this will work anyway.

Does anyone know how to connect to a cluster of Oracle nodes from a Java program?

Answer 1

For the thin Oracle JDBC driver, I think this can 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 setUrl method to set the URL. If the URL is set, then all other properties, such as database_name, server_name, port number, network protocol, tnsentry, and driver type will be ignored.

Hope this helps!