Problems resolving Oracle SID via LDAP/OID lookup with Thin JDBC Client

Question

We are using OID/LDAP lookup for our connection configuration. Both the TNS_ADMIN environment variable and the oracle.net.tns_admin Java property are set correctly and points to a directory that contains sqlnet.ora and ldap.ora. However, the JDBC connection URL: jdbc:oracle:thin:@ourtnsalias throws exception when attempting to connect:

java.sql.SQLRecoverableException: IO Error: could not resolve the connect identifier  "ourtnsalias"
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:185)
        at TnsTest.main(TnsTest.java:29)
Caused by: oracle.net.ns.NetException: could not resolve the connect identifier  "tnsalias"
        at oracle.net.resolver.NameResolver.resolveName(NameResolver.java:181)
        at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:416)
        at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:687)
        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:247)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
        ... 7 more
Exception in thread "main" java.lang.NullPointerException
        at TnsTest.main(TnsTest.java:46)

The following flavors of the connection string connect successfully:

• Full TNS string:
  jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host.company.com)(PORT=####))(CONNECT_DATA=(SERVICE_NAME=service_name)))
• Full LDAP url:
  jdbc:oracle:thin:@ldap://oid.company.net:####/ourtnsname,CN=OracleContext,dc=company,dc=net

This would seem to imply that the problem is with the LDAP/OID lookup. However, running tnsping ourtnsname also connects successfully.

Is there any way to use only a TNS Alias in the connection string when doing OID/LDAP lookup?

For completeness sake, here is the relevant Java:

String connectionURL = "jdbc:oracle:thin:@ourtnsalias";
System.setProperty("oracle.net.tns_admin", "c:/oracle/network/admin");
Class.forName("oracle.jdbc.OracleDriver");
Connection c = DriverManager.getConnection(connectionURL, userid, password);

Answer 1

I am not a programmer but a dba and had helped a colleague of mine some time back with the same issue. so let me know if this helps

System.setProperty("oracle.net.tns_admin", "c:/oracle/network/admin"); 

should be

System.setProperty("oracle.net.tns_admin", "c://oracle//network//admin"); 

or

System.setProperty("oracle.net.tns_admin", "c:\\oracle\\network\\admin");

Answer 2

After beating my head against a wall with this, it turned out the problem for us was that the TNS Names entry had invalid tabs/spacing across multiple lines, causing it not to parse correctly.

In ourt case it came as a surprise that TNSPING was able to correctly parse the TNSNAMES.Ora file, but jdbc Oracle Driver was not.

Deleting all spacing in the TNSNAmes entry, and then carefully adding it back in, paying attention to the rules described here:

http://docs.oracle.com/cd/A57673_01/DOC/net/doc/NWUS233/apb.htm

Helped us to the resolve the issue.

What lead us to the answer was that we would get a different error when changing the connection identifier i.e. switching from this:

jdbc:oracle:thin:@ourtnsalias

To:

jdbc:oracle:thin:@unknown

Was giving a different error (along with checking in sysinternals process monitor to ensure the TNSNAmes.ora file was actually being read) suggested that it was parsing the TNS file, but that there was something wrong with the entry that was tripping things up.

I can't help but think all this could be easily resolved if the JDBC Oracle client returned a more meaningful error i.e. "unable to parse TNS Entry for connection identifier"!

Hope this helps somebody else out :)

Answer 3

Look for invalid characters in your tnsnames.ora.

I had

test, test.WORLD=...

in my file. Changing it to

test.WORLD=

resolved the issue.