I was playing around with using Oracle Internet Directory to resolve database service names recently, and it turned into an adventure. Not exactly a memoir-inspiring adventure, but at least one worth blogging about. Maybe my little voyage of discovery here will help someone else who gets a TNS-03505 when using LDAP to look up database service names.
The Setup
My "lab" in this case consisted of two VM servers, running Oracle Enterprise Linux 5. Both Oracle installations are "plain vanilla," no additional patching other than what was required to get the non-OID database server from 10.2.0.1 to 10.2.0.4.
| OID server | "normal" database server | |
| Hostname | garibaldi | vir |
| Software & version | 10gAS 10.1.2.0.2 (OIM 10.1.4) | RDBMS 10.2.0.4 |
| SID | OIM | JPTEST |
The tests
First, I registered the JPTEST database with OID in the custom Oracle Context dc=jep,dc=com. Based on that statement alone, you may already know where this is going. 
In order to test the registration, I confirmed that that the client software on the database server was set up to consult OID first for name resolution. Then, I confirmed that I could ping the JPTEST database from its own host:
oracle@vir ~]$ grep NAMES $TNS_ADMIN/sqlnet.ora NAMES.DIRECTORY_PATH= (LDAP,TNSNAMES,EZCONNECT) [oracle@vir ~]$ tnsping JPTEST TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 05-OCT-2008 15:12:11 Copyright (c) 1997, 2007, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/rdbms/10gR2/network/admin/sqlnet.ora Used LDAP adapter to resolve the alias Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vir.local)(PORT=1563))(CONNECT_DATA=(SERVICE_NAME=JPTEST.dbdomain))) OK (0 msec)
So far, so good. Next, I checked to see if I could resolve other database service names. I only had 1 other database server running, the OID repository, so:
[oracle@vir ~]$ tnsping OIM TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 05-OCT-2008 15:14:02 Copyright (c) 1997, 2007, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/rdbms/10gR2/network/admin/sqlnet.ora TNS-03505: Failed to resolve name
...Huh.
Hopping over to the OID server, I saw the following:
[oracle@garibaldi ~]$ tnsping OIM TNS Ping Utility for Linux: Version 10.1.0.5.0 - Production on 05-OCT-2008 15:28:22 Copyright (c) 1997, 2003, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/OIM/network/admin/sqlnet.ora Used LDAP adapter to resolve the alias Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=garibaldi.local)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=OIM.local))) OK (10 msec)
Okay, that looks good, but:
[oracle@garibaldi ~]$ tnsping JPTEST TNS Ping Utility for Linux: Version 10.1.0.5.0 - Production on 05-OCT-2008 15:28:29 Copyright (c) 1997, 2003, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/OIM/network/admin/sqlnet.ora TNS-03505: Failed to resolve name
Okay, so these servers can see themselves, but not each other. Neato!
The explanation
The error message implies a lookup problem, not a connectivity problem. Clearly, both clients can connect to the OID server...they're just not finding their targets. That suggests the targets are not defined similarly in the LDAP directory. What does ldapsearch say?
[oracle@vir ~]$ ldapsearch -h garibaldi.local -p 389 -s sub orclservicetype=DB | egrep ^cn cn=OIM,cn=OracleContext cn=OIM cn=JPTEST,cn=OracleContext,dc=jep,dc=com cn=JPTEST
Aha. The entries are in different Oracle contexts. Had I not decided to create a custom Oracle context, and just registered my JPTEST database in the default context, I wouldn't have this situation (or this blog post). Just for fun, let's confirm that the clients on the two servers are configured to look in different places.
On the database server:
[oracle@vir ~]$ grep CONTEXT $TNS_ADMIN/ldap.ora DEFAULT_ADMIN_CONTEXT = "dc=jep,dc=com"
And the OID server:
[oracle@garibaldi ~]$ grep CONTEXT $TNS_ADMIN/ldap.ora grep: /u01/app/oracle/product/OIM/network/admin/ldap.ora: No such file or directory
Oops. That's not where ldap.ora lives in the default OID install...
[oracle@garibaldi ~]$ grep CONTEXT $ORACLE_HOME/ldap/admin/ldap.ora DEFAULT_ADMIN_CONTEXT = ""
There we go. The client on the database server is searching in my custom context, and lookups on the OID server start at the root context.
Solutions and closing rambling
Here's my quick and dirty solution to my problem. Note that I'm working in a test system, and this may be too dirty for use in a production configuration.
Since I plan to register the rest of my databases in the custom context, rather than dumping them in the root context, an easy way to make it possible to connect to the OIM database via LDAP naming from non-local clients is to create an alias to the OIM entry in my custom context:
[oracle@garibaldi ~]$ ldapadd -p 389 -h garibaldi.local -f OIM_alias.ldif -q -v -D cn=orcladmin Please enter bind password: add objectclass: alias add aliasedObjectName: cn=OIM, cn=OracleContext adding new entry cn=OIM, cn=OracleContext, dc=jep, dc=com modify complete
Now, from my database server:
[oracle@vir ~]$ tnsping OIM TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 06-OCT-2008 10:36:57 Copyright (c) 1997, 2007, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/rdbms/10gR2/network/admin/sqlnet.ora Used LDAP adapter to resolve the alias Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=garibaldi.local)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=OIM.local))) OK (0 msec)
Yay, half a problem solved! Still, I'm more or less stuck when performing searches from the OID server. One option is to use the full DN to access the server:
[oracle@garibaldi ~]$ tnsping cn=JPTEST,cn=OracleContext,dc=jep,dc=com TNS Ping Utility for Linux: Version 10.1.0.5.0 - Production on 07-OCT-2008 14:33:52 Copyright (c) 1997, 2003, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/OIM/network/admin/sqlnet.ora Used LDAP adapter to resolve the alias Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vir.local)(PORT=1563))(CONNECT_DATA=(SERVICE_NAME=JPTEST.dbdomain))) OK (20 msec)
That works, but if I wanted to type that much, I'd just use EZConnect. I could also create a second ldap.ora in $ORACLE_HOME/network/admin on the OID server, with a non-root value (dc=jep,dc=com) for DEFAULT_ADMIN_CONTEXT, and set the LDAP_ADMIN environment variable to point to the alternate file as needed. That would be okay (barely) for a one-user test setup like this, but in the real world it's a recipe for unacceptable levels of confusion.
It seemed reasonable to expect (to me, anyway...maybe I just expect too much) that specifying the root context as the default for LDAP searches would make it possible to find a database further down the tree, but apparently not. After some looking around on Metalink My Oracle Support, I found that I may be hitting a bug that's fixed in the 11.1.0.7 RDBMS patchset. But either I'm reading the note wrong, or I don't have the problem as described. Here's lookup attempt from a different server with a patched 11gR1 client...still no joy:
[oracle@lyta ~]$ grep CONTEXT $TNS_ADMIN/ldap.ora DEFAULT_ADMIN_CONTEXT = "" [oracle@lyta ~]$ tnsping JPTEST TNS Ping Utility for Linux: Version 11.1.0.7.0 - Production on 07-OCT-2008 14:55:18 Copyright (c) 1997, 2008, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/client/11gR1/network/admin/sqlnet.ora TNS-03505: Failed to resolve name
I'd welcome any comments with alternate solutions, explanations, or a gentle "Hey, idiot, you're doing it wrong!" for what's going on here. I can live with the setup I have now in my test environment, but I'm always ready to learn something new.
