JDBC programming tips with Oracle RAC databases

You might be using jdbc:oracle:driver:@database format in your java code to establish connection to Oracle instances.

Database can be of the format {host:port:sid | net_service_name | connect_descriptor}. Driver specifies the type of JDBC driver to use for connection. The choices are

thin – for the Oracle thin driver (No SQL net needed)
oci7 – For Oracle 7.3.4 OCI driver
oci8 – For Oracle 8.x.x OCI driver
oci – For an Oracle 9.x.x or higher driver
kprb – For the oracle internal driver used in Java Stored Procedures

Thin driver will understand only SID and Service names won’t work with thin driver. In case of a need to use Service names, we need to use oci driver. But oci needs SQL Client to be installed on the same host where we execute the java calls. It is claimed that OCI calls performs 400% faster than thin driver calls and with a small modification in the definition in the code, we can migrate code to work with RAC instances.

Another question I hear all the time, why there are different ojdbcX.jar files. Ojdbc4.jar is for JDK 1.4, ojdbc5.jar is for JDK 1.5(also known as JDK 5) and ojdbc6.jar is for JDK 6.

The oracle.jdbc.driver.* classes, the ojdbc4.jar file, and the OracleConnectionCacheImpl class are no longer supported or available.

I tested jdbc thin driver connection string with different combinations.

1. Simple method where we specify server name, port and sid
String URL = "jdbc:oracle:thin:@" + serverName + ":" + port + ":" + SID;
2. More complicated setup which is needed for RAC or when using service names. As jdbc thin driver does not understand tns alias or as thin client is used in applet configuration which works from a web brower, it is isolated from oracle client tools. So calling tns alias directly is not supported. But you can take the contents from tnsalias and set in connection string as follows.
String URL = "jdbc:oracle:thin:@" +
"(DESCRIPTION =" +
"(ADDRESS_LIST =" +
"(ADDRESS = (PROTOCOL = TCP)(HOST = node1.somedomain.com)(PORT = 7737))" +
"(ADDRESS = (PROTOCOL = TCP)(HOST = node2.somedomain.com)(PORT = 7737))" +
"(LOAD_BALANCE = yes)" +
")" +
"(CONNECT_DATA =" +
"(SERVER = DEDICATED)" +
"(SERVICE_NAME = node.somedomain.com)" +
")" +
")";

From 10.2 onwards calling tnsalias is supported in following way.
In 10.2, JDBC-Thin supports TNSNAMES entries lookup in tnsnames.ora, resulting in a much simplified and driver type independent Oracle Net descriptor. You must set the system property oracle.net.tns_admin to the directory that contains your tnsnames.ora file. See the Oracle JDBC 10.2 document for detailed description.

java -Doracle.net.tns_admin=$ORACLE_HOME/network/admin
or:
System.setProperty("oracle.net.tns_admin", "c:\oracle\net\admin");
OracleDataSource ods = new OracleDataSource();
ods.setTNSEntryName("tns_alias");
ods.setUser("scott");
ods.setPassword("tiger");
ods.setDriverType("thin");
Connection conn = ods.getConnection();

Here is my test program which used both options. I tested this connection in my RAC instance and is working well with service names.

/* This program is to test different connection options for jdbc thin driver
Written by Madhu Kangara
*/
import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;

class DbConnectService {
  public static void main(String args[]) throws SQLException {
    DriverManager.registerDriver(
       new oracle.jdbc.driver.OracleDriver()
    );
    String serverName = "node2.somedomain.com";
    int port = 7737;
    String user = "mkangar";
    String password = "Change_me";
    String SID = "NODE2";
    // First way to do it (simple way). Uncomment following line if you wish to use this method.
    //String URL = "jdbc:oracle:thin:@" + serverName + ":" + port + ":" + SID;
    // Second way to do with full tnsnames entry
    String URL = "jdbc:oracle:thin:@" +
    "(DESCRIPTION =" +
    "(ADDRESS_LIST =" +
      "(ADDRESS = (PROTOCOL = TCP)(HOST = node1.somedomain.com)  (PORT = 7737))" +
      "(ADDRESS = (PROTOCOL = TCP)(HOST = node2.somedomain.com)(PORT = 7737))" +
      "(LOAD_BALANCE = yes)" +
    ")" +
    "(CONNECT_DATA =" +
      "(SERVER = DEDICATED)" +
      "(SERVICE_NAME = node.somedomain.com)" +
      ")" +
    ")";
    Connection conn = DriverManager.getConnection(URL, user, password);
    String SQL = "SELECT INSTANCE_NAME FROM V$INSTANCE";
    Statement stat = conn.createStatement();
    ResultSet rs = stat.executeQuery(SQL);
    while (rs.next()) {
       System.out.println(rs.getString(1)
       );
    }
    stat.close();
    conn.close();
  }
}

Please don’t forget to set your CLASSPATH with right path to your java class files and ojdbcX.jar file. (Need to add the settings line)

Here is the output of compilation and execution
C:\Projects\java\scripts>javac DbConnectService.java
C:\Projects\java\scripts>java DbConnectService
node1

Due to above compilcations with programming and abilility to scale with more nodes added a RAC cluster, Oracle added a new feature in Oracle 11gR2 which is called SCAN. This is a DNS name which does name resolution management behind the scene with several rules and end user will be dealing with a single domain name like they done with non RAC databases. More about this new feature in another blog.