Strange ORA-12154

We have an NT4.0 server running as a test web server (using IIS) with two versions of Oracle client software to allow web apps to access our Oracle databases. The two versions of Oracle installed are 8.0.5.0.0 (installed in d:\orant) and 8.1.5.0.0 (installed in d:\orant8I). The databases are on other NT servers. The applications are ASP’s that access the Oracle db’s via the Microsoft ODBC driver for Oracle.

Since we also have around 1000 desktop Oracle clients in addition to the web servers, we maintain a single TNSNAMES file for use by all clients, be they desktops, web servers, or dedicated application devices. This TNSNAMES is kept in its own directory on one of the DB servers, and all clients have TNS_ADMIN entry in their registry to point to this central TNSNAMES.

All of this is working fine for all desktop apps and all but one web app. This one app continues to get an ORA-12154 unless it can resolve connect strings from a TNSNAMES file located in its own oracle_home\net80\admin directory. It’s as if it is completely ignoring the TNS_ADMIN entry in the registry. Other web apps, executing on the same web server and using the same ODBC driver, are having no problems.

As part of my testing I sat at the web server in question and tried connecting with SQLPlus. I put a unique entry in the central TNSNAMES file, so that I would know for sure that it was that file being used to resolve the names. I found that if I used the version from the 8.0 directory it was able to connect using the central TNSNAMES, but if I used the version from the 8I drectory it was unable to connect. This held true regardless of which home was selected with the “home selector” utility.

We also observed that if the local TNSNAMES was removed, the application would continue to work for a few minutes before failing with the ORA-12154. After failures, if we restored the local TNSNAMES, the app would begin working immediately.

Given all these clues, does anyone have any explanation for the behavior we’re seeing?

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 20 1999 - 10:43:09 CDT