Oracle Application Server 10.1.3 / JDBC connections based on tnsnames.ora

The customer wants to use DataGuard as backend for the applications that are hosted on the Oracle Application Server 10.1.3. To implement this solution it is necessary to configure the JDBC connections with the tnsnames.ora, because with this solution it is possible to put more databases (on different servers) in one JDBC connection.

Oracle has an java program to check if this connection is working:

// -- begin : cut here : ThinTnsnames.java -- cut there
import java.sql.*;
import oracle.jdbc.pool.*;

public class ThinTnsnames {
  // private static OracleConnection connection = null;
  static String sql = null;

  public static void main(String[] args) {
    String entry_name = args [0];
    String userId = args [1];
    String password = args [2];
    test (entry_name, userId, password);
  }

  public static void test (String entry_name, String userId, String password) {
    Connection pconnection = null;
    try {
      String l_url = "jdbc:oracle:thin:@" + entry_name;
      System.out.println( " Connection string = " + l_url );

      OracleDataSource ods = new OracleDataSource();
      ods.setUser(userId);
      ods.setPassword(password);
      ods.setURL(l_url);
      pconnection = ods.getConnection ();
      version (pconnection );
    }
    catch(SQLException e) {
      e.printStackTrace();
    }
    finally {
      try {
        if (pconnection != null )pconnection .close();
      }
      catch(Exception e) {
        e.printStackTrace();
      }
    }
  }

  public static void version (Connection pconnection) {
    try {
      DatabaseMetaData dmd = pconnection.getMetaData();
      System.out.println("DriverVersion: ["+dmd.getDriverVersion()+"]");
      System.out.println("DriverMajorVersion: ["+dmd.getDriverMajorVersion()+"]");
      System.out.println("DriverMinorVersion: ["+dmd.getDriverMinorVersion()+"]");
      System.out.println("DriverName: ["+dmd.getDriverName()+"]");
      System.out.println("URL: ["+dmd.getURL()+"]");
      System.out.println("UserName: ["+dmd.getUserName()+"]");
      System.out.println(dmd.getDatabaseProductName() );
      System.out.println(dmd.getDatabaseProductVersion() );

      String ver;
      ver = System.getProperty("java.version");
      System.out.println("The JDK version is " + ver);
    }
    catch(SQLException e) {
      e.printStackTrace();
    }

  }
}

// --end : cut here : ThinTnsnames.java -- cut there

compile the program:
javac -classpath ojdbc14.jar:. ThinTnsnames.java

Execute the program:
java -Doracle.net.tns_admin=$ORACLE_HOME/network/admin -classpath .:ojdbc14.jar ThinTnsnames [DATABASE] [USER] [PASSWORD]

Output:

 Connection string = jdbc:oracle:thin:@[DATABASE]
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 221
        at oracle.net.nl.NVTokens.parseTokens(Unknown Source)
        at oracle.net.nl.NVFactory.createNVPair(Unknown Source)
        at oracle.net.nl.NLParamParser.addNLPListElement(Unknown Source)
        at oracle.net.nl.NLParamParser.initializeNlpa(Unknown Source)
        at oracle.net.nl.NLParamParser.(Unknown Source)
        at oracle.net.resolver.TNSNamesNamingAdapter.loadFile(Unknown Source)
        at oracle.net.resolver.TNSNamesNamingAdapter.checkAndReload(Unknown Source)
        at oracle.net.resolver.TNSNamesNamingAdapter.resolve(Unknown Source)
        at oracle.net.resolver.NameResolver.resolveName(Unknown Source)
        at oracle.net.resolver.AddrResolution.resolveAndExecute(Unknown Source)
        at oracle.net.ns.NSProtocol.establishConnection(Unknown Source)
        at oracle.net.ns.NSProtocol.connect(Unknown Source)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:858)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:268)
        at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:441)
        at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:165)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
        at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:297)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:221)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:165)
        at ThinTnsnames.test(ThinTnsnames.java:26)
        at ThinTnsnames.main(ThinTnsnames.java:13)

I could not find the problem for this issue. But after logging an SR on metalink the problem was the tnsnames.ora file.

We had one database entry in the tnsnames.ora:

[DATABASE] =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = [SERVERNAME] l)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = [DATABASE] )(SERVER = DEDICATED)))

But this entry was not correct.

We changed the tnsnames.ora:

[DATABASE] =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [SERVERNAME] )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = [DATABASE])
)
)

And now execute the program:

java -Doracle.net.tns_admin=$ORACLE_HOME/network/admin -classpath .:ojdbc14.jar ThinTnsnames [DATABASE] [USER] [PASSWORD]

Connection string = jdbc:oracle:thin:@[DATABASE]
DriverVersion: [10.2.0.4.0]
DriverMajorVersion: [10]
DriverMinorVersion: [2]
DriverName: [Oracle JDBC driver]
URL: [jdbc:oracle:thin:@[database]]
UserName: [USER]
Oracle
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
The JDK version is 1.5.0_06

If this program is working it is also possible to make a JDBC connection in enterprise managers like this:
jdbc:oracle:thin:@[DATABASE]

In the start parameters of the OC4J container there should be an extra option:
-Doracle.net.tns_admin=[TNSNAMES LOCATION]

Note:
It is also possible to configure the JDBC connection like an tnsnames.ora entry in the enterprise manager:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(failover=on)(ADDRESS=(PROTOCOL=TCP)(HOST=[SERVERNAME1])(PORT=1521))(address=(protocol=tcp)(host=[SERVERNAME2])(port=1521)))(CONNECT_DATA=(SERVICE_NAME=[DATABASE])))

Did this post help you in any way can you please leave a comment? This will motivate me writing more posts.