Re: Strange ORA-12154

On Wed, 20 Oct 1999 15:43:09 GMT, Ed Stevens <Ed.Stevens_at_nmm.nissan-usa.com> wrote:

>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.

I would recommend making sure the trace_level_client of both your sqlnet.ora's (ie in TNS_ADMIN and locally is set to 16). also make sure trace_directory_client is set to something meaningful and trace_unique_client to true.
This will provide you with an exact dump of all net8 activity, including the location and contents of tnsnames.ora

Sybrand Bakker, Oracle DBA Received on Wed Oct 20 1999 - 13:10:22 CDT