Re: Strange ORA-12154 - solution found!

Problem solved -- sort of!

The apps that worked were going thru MS Transaction Server, so the effective user was MTSUSER, with whatever authority is given to that user -- pretty much the same as any user. When not going thru MTX, the effective user coming out of IIS is the NT user ‘SYSTEM’, which has no connectivity authority at all. This restrictive control on user SYSTEM (which I'm sure is a security issue) prevented the access to a file being shared from another machine.

Two other things APPEAR to be happening as well. First, the trace of the failed transaction contains a reference to sid=ORCL. We can find no place in the system configuration nor the application that makes this reference. And we have never created this default, out of the box, database. Therefore, I would assume that this is hardcoded into a SQLNet module to use as a final default if no other address resolution can be made.

Second, It would appear that something (probably IIS) is doing some connection pooling. One of the behaviors we observed was that when the local TNSNAMES was added or removed, the application would continue to behave as before the change for several minutes before changing behavior to be consistent with the state of TNSNAMES. Further, with rapid-fire iterations of the app, behavior could be indeterminate for several minutes after a change of state for TNSNAMES. We believe this indetermanancy to be based on which of the pooled connections the app got on any particular iteration.

In article <7uknu9$ark$1_at_nnrp1.deja.com>,   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.
>

--
Ed Stevens
(Opinions are not necessarily those of my employer)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 21 1999 - 14:44:44 CDT