I have a complaint. After seven years of Oracle experience, ORA-12154: TNS:could not resolve the connect identifier specified ought to be in my past.
I installed a standard Oracle 10.2 client on a fresh, new machine, only to find that the sqlplus.exe in {ORACLE_HOME}/bin was not looking in {ORACLE_HOME}/network/admin for its TNSNAMES.ORA. I don't know where it was looking, or why. I searched for any of those annoying stray TNSNAMES.ORA files, and there weren't any, but that doesn't mean that Oracle Net wasn't looking for TNSNAMES in all the wrong places. I eventually gave up and set the TNS_ADMIN environment variable, but that was an unsatisfying brute-force solution; I want to know why SQLPLUS wasn't looking in the standard place in its own home for TNSNAMES.ORA, but apparently I'll never know.
The problem is that Oracle Net gives you no feedback about what went wrong when something goes wrong. Did it find a TNSNAMES.ORA, but hit a syntax error in it? Did it find a TNSNAMES.ORA, but not the one you expected it to? Did it not find a TNSNAMES.ORA at all? Well, you'll just have to guess. Yes, you can trace Oracle Net; you need to insert directives like TRACE_LEVEL_CLIENT=user and TRACE_DIRECTORY_CLIENT into SQLNET.ORA. Ah, but which SQLNET.ORA? Well, that's the problem - if Oracle Net isn't looking where you expect it to for TNSNAMES.ORA, it won't see your SQLNET.ORA either.
It was a big improvement when TNSPING was upgraded to report on which SQLNET.ORA it was using. (And, indeed, in my case, TNSPING reported that it was using {ORACLE_HOME}/network/admin/sqlnet.ora, as you would expect, which is probably why TNSPING could resolve my service names just fine.) We really need a similar improvement in Oracle Net in general - some troubleshooting information that's transmitted in the error message every time TNS resolution fails.
Have I simply missed the memo on some good way to troubleshoot these problems? If you know of one, please let me know.
While researching it, I did find a pretty nice resource - ora-code.com. Their ORA-12154 page is a more concise and relevant checklist than anything I know of on MetaLink. Unfortunately, they lack a search box, so the best way to find their pages is simply to Google for "ora-code 12154".
There. Now if Oracle magazine ever asks me "what one improvement I'd like to see in Oracle", I have my answer ready. That and PL/Python, of course.
3 comments:
My next port of call when I can't resolve which TNSNAMES.ora file is being used is to take a look at the registry.
Sometimes I've seen weird values in there, especially if I've installed and de-installed Oracle software on that machine.
Of course, YMMV.
Hi Catherine,
you can look at where oracle is trying to find sqlnet.ora and other files using strace (ex: strace sqlplus aa/bb@myalilas).
..and I agree on PL/Python ! ;)
Luca.
I encountered similar symptoms to this. Tnsping worked, programs using JDBC drivers worked, but anything based on sql*net couldn't resolve the TNS alias because it was looking for the tnsnames.ora and sqlnet.ora in the wrong directories (confirmed using filemon). Turns out that the 10.2 client installer didn't create the neccessary registry entries under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\. In particular, I manually created two string values, ORACLE_HOME and ORACLE_HOME_NAME, to point to (in my instance) C:\oracle\product\10.2.0\client_1 and OraClient10g_home1 respectively. After reopening the sql*net-based clients it could correctly find the .ora files and resolve the TNS aliases. YMMV.
Post a Comment