Hi,

Is there a way to log what information Oracle client components are passed
when a connection-attempt is made?

The background to this question is that I am building an application in
Borland Delphi 5 using ODAC as access components. I can connect at designtime
but at runtime the program gives me an ORA-12154 error. "TNS: could not
resolve service name". But the information used _should_ be the same as when
I connect at designtime and it is accepted then.

So I would like to check from the other end to verify what my application
is trying to do.

Oracle is 9.2 on Windows.

Ciao, MM

On Mon, 31 Oct 2005 11:55:18 +0100, Marian Aldenhövel
<marian@mba-software.de> wrote:

>Hi,
>
>Is there a way to log what information Oracle client components are passed
>when a connection-attempt is made?
>
>The background to this question is that I am building an application in
>Borland Delphi 5 using ODAC as access components. I can connect at designtime
>but at runtime the program gives me an ORA-12154 error. "TNS: could not
>resolve service name". But the information used _should_ be the same as when
>I connect at designtime and it is accepted then.
>
>So I would like to check from the other end to verify what my application
>is trying to do.
>
>Oracle is 9.2 on Windows.
>
>Ciao, MM

ORA-12514 is a common error. When you search the archives of this
group you will see numerous people posting the same question, and
always been pointed to the same cause (which you can also look up in
the Oracle Error Messages manual) 'You are using a service name that
doesn't occur in your tnsnames.ora. The cause *might* be that you have
multiple tnsnames.ora floating around, and you didn't set TNS_ADMIN to
point to one of them'
Check from the other end in your case also wouldn't help as the client
connection doesn't even get to the server.
If you verify your tnsnames.ora and that doesn't resolve it, you can
enable sqlnet tracing on the client side. How to do that is explained
in the documentation, and has also been posted in this forum
*numerous* times.
Could you *please* try do some more research of your own, instead of
posting this FAQ *yet again* *rightaway*???

--
Sybrand Bakker, Senior Oracle DBA

Hi,

> ORA-12514 is a common error. When you search the archives of this
> group you will see numerous people posting the same question

Yes, I did see that. Unfortunately none of the answers applies to
my case. My tnsnames.ora is OK. I _can_ connect using the settings.

I realize that this group is not about Borland Delphi but the fact
that I can connect at designtime and not at runtime is very strange
as it is the same code trying the connect and it uses the same
parameters.

> If you verify your tnsnames.ora and that doesn't resolve it, you can
> enable sqlnet tracing on the client side.

Thank you for mentioning that keyword. It helped me find a few bits
of information and yields exactly the kind of logging I was after. Even
if it does not make any sense to me yet :-).

> Could you *please* try do some more research of your own, instead of
> posting this FAQ *yet again* *rightaway*???

What exactly makes you think I didn't?

> Sybrand Bakker, Senior Oracle DBA

Marian Aldenhövel, Non-Oracle-DBA-but-still-forced-to-act-like-one :-)

Hi,

Well, I added a few lines to my sqlnet.ora to enable tracing:

> trace_level_client=admin
> trace_unique_client=yes
> TNSPING.TRACE_LEVEL = admin
> TNSPING.TRACE_DIRECTORY = d:\projekte\oralog
> trace_file_client = cli.trc
> trace_directory_client = d:\projekte\oralog
> log_file_client = sqlnet.log
> log_directory_client = d:\projekte\oralog

I clicked my connection online at designtime - which works - and got two trc
files. I then ran my program up to the point of failure and got two
other trace files. To compare them I concatenated those with the same
client numbers in order of suffix.

The first difference is that in the non-working-case there is a number in
braces before the timestamp in the trace file. That is it goes like this:

(1236) [date time] Text

whereas the trace of the working connection does not have that first
part and starts like:

[date time] Text

For comparison purposes I removed the timestamp. I then put the files through
a diff. They start out the same except for a few lines with - I believe -
inconsequential differences:

> New trace stream is d:\projekte\oralog\cli_636.trc
> nncpcin_maybe_init: first request sent to name server will have ID 8874

vs.

> New trace stream is d:\projekte\oralog\cli_1292.trc
> nncpcin_maybe_init: first request sent to name server will have ID 0

I don't think there is something wrong with that, is it? Then about
150 lines into the traces the working sample goes:

> nrigbni: Unable to get data from navigation file tnsnav.ora
> nrigbni: exit
> nrigbi: exit
> nigini: exit
> niqname: Hst is already an NVstring.
> niqname: Inserting CID.
> niotns: entry
> niotns: niotns: setting up interrupt handler...
> niotns: Not trying to enable dead connection detection.
> niotns: Calling address: (
>
DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)( HOST=dodo-mm)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(S ERVICE_NAME=CMS)(CID=(PROGRAM=D:\prg\Delphi5\Delph i5\Bin\delphi32.exe)(HOST=DODO-MM)(USER=marian))))
> nsmal: 164 bytes at 0xbae35f0
> nscall: connecting...
> nladini: entry
> nladini: exit
> nladget: entry
> nladget: exit
> nttbnd2addr: entry
> nttgetport: entry
> nttgetport: port resolved to 1521
> nttgetport: exit
> nttbnd2addr: looking up IP addr for host: dodo-mm
> nttbnd2addr: exit
> nsmal: 492 bytes at 0xbae3ff8
> nsmal: 2348 bytes at 0xbb19040
> nsopen: opening transport...

And then goes on for about a thousand lines full of interesting information.
The only part making sense to me is the one about DESCRIPTION. This is my
login information.

The non-working sample stops after "Inserting CID".

Does that yield any useful information as to what might cause the failure?

Yes, I did google some of the keywords here, but to no avail.

Ciao, MM

Marian Aldenhövel wrote:
....
>
> > niqname: Inserting CID.
> > niotns: entry
> > niotns: niotns: setting up interrupt handler...
> > niotns: Not trying to enable dead connection detection.
> > niotns: Calling address: (
> >
> DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)( HOST=dodo-mm)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(S ERVICE_NAME=CMS)(CID=(PROGRAM=D:\prg\Delphi5\Delph i5\Bin\delphi32.exe)(HOST=DODO-MM)(USER=marian))))
....
>
> The non-working sample stops after "Inserting CID".

I think it simply means can't find the Oracle TNS entry. Not very
informative. In fact, solving ORA-12154 is very easy in most cases and
rarely needs SQL*Net tracing. On Windows, just launch Filemon (from
sysinternals.com) and filter on your app name or "tns" or "ldap" (if
you use LDAP). Run your application and check the Filemon output. In
most cases, the application tries to find the file(s) in a directory
you didn't expect.

Yong Huang

Hi,

> In fact, solving ORA-12154 is very easy in most cases

It looks that way from what I've found but I can't seem to pinpoint
the problem anyway. What's easy to one person is hard to another ;-).

> Run your application and check the Filemon output.

I did that.

It first loads oci.dll followed by a lot of other oracle-libraries,
Oraclient9, Oracore9, Oranls9. All successful. It fails to find
MSVCIRT.DLL in the oracle bin-directory but locates and loads it
from System32. Then more Oracle-DLLs. Then ORACLE.KEY.

It fails to find ldap.ora, I do not use LDAP, so this is to be expected
I guess. It then reads my sqlnet.ora, the file I decorated with the
tracing-entries.

It fails to find intchg.ora, tnsnav.ora and sdns.ora in network\admin
and network\names respectively. It tries to read tnsnames.ora from my
application directory and does not find it there.

It then finds and opens network\admin\tnsnames.ora. It reads from it.
It tries intchg.ora and tnsnav.ora again, and does not find them.

By that time the error-message is on the screen and I have stopped
the monitoring.

That all looks good to me. I can provide complete information for
download should someone care.

Ciao, MM

Hi,

> By that time the error-message is on the screen and I have stopped
> the monitoring.

The working version goes on to load oran9.dll and orantcp9.dll. A lot
of windows-stuff. Then orank59.dll, orangss9.dll and more oracle-DLLs.
I assume oran* to constitute the networking layer. So all that tells
me is that the name has been resolved correctly and it has been decided
to connect using TCP/IP (which is correct).

So in both cases tnsnames.ora is found, opened and read. In one case
the resolution of the name works and in the other fails. My tnsnames.ora
is 814 bytes long. Filemon says:

Non-Working:
> QUERY INFORMATION O:\oracle\ora92\network\admin\tnsnames.ora SUCCESS
Attributes: A
> DIRECTORY O:\oracle\ora92\network\admin\ SUCCESS
FileBothDirectoryInformation: tnsnames.ora
> OPEN O:\oracle\ora92\network\admin\tnsnames.ora SUCCESS Options: Open
Access: All
> READ O:\oracle\ora92\network\admin\tnsnames.ora SUCCESS Offset: 0
Length: 4096
> READ O:\oracle\ora92\network\admin\tnsnames.ora SUCCESS Offset: 0
Length: 4096
> READ O:\oracle\ora92\network\admin\tnsnames.ora END OF FILE Offset: 812
Length: 4096
> CLOSE O:\oracle\ora92\network\admin\tnsnames.ora SUCCESS

Working:
> QUERY INFORMATION O:\oracle\ora92\network\admin\tnsnames.ora SUCCESS
Attributes: A
> DIRECTORY O:\oracle\ora92\network\admin\ SUCCESS
FileBothDirectoryInformation: tnsnames.ora
> OPEN O:\oracle\ora92\network\admin\tnsnames.ora SUCCESS Options: Open
Access: All
> READ O:\oracle\ora92\network\admin\tnsnames.ora SUCCESS Offset: 0
Length: 4096
> READ O:\oracle\ora92\network\admin\tnsnames.ora END OF FILE Offset: 812
Length: 4096
> CLOSE O:\oracle\ora92\network\admin\tnsnames.ora SUCCESS

Identical. I think tnsnames.ora is out of it. What can I check now?

Ciao, MM

Marian Aldenhövel wrote:
[Snip file access nonsense]
> Identical. I think tnsnames.ora is out of it. What can I check now?
>

I may have missed (I am wearing spectacles...), but I have not
seen any of your configuration files.
It might help to post sqlnet.ora, tnsnames.ora from both environments,
as well as explaining what tns alias you try to connect to.

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Well my problem is similar and I have traced it down to the sam
thing. my application is reading tnsnames.ora etc and everythin
works with sqlplus, tnsping, etc. all the diagnostics you can spend
week on google checking

I also get 2 files

FILE 1 contains

ShadowAssassin wrote:
> Well my problem is similar and I have traced it down to the same
> thing. my application is reading tnsnames.ora etc and everything
> works with sqlplus, tnsping, etc. all the diagnostics you can spend a
> week on google checking!
>

I asked for configuration files, not trace files.
I am willing to help out here, but if you expect me
to spend precious time in reconstructing config files
from trace files, think again.
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...