Mysteriously dropped connection and deciphering the trace

Hi all,

I have an application that uses Oracle's JDBC thin driver 9.0.x to
have a nice, friendly chat with 8.1.x database. During this exchange
the server rudely interrupts the conversation and causes the
application to fubar. The driver reports this as 'Connection reset'
(IOException) without any error numbers. The server is hosted by a 3rd
party in one of those "controlled" environments so my first guess is
network/tx timeout but wouldn't it say so? This is 100% reproducible
and always fails in the same place. The line of code corresponding to
that reported in JDBC driver stack trace does nothing but unset the
auto commit. If this is indeed a case of one particular transaction
timing out on commit, how do I tell if this is indeed the case? I
can't do a client-side SQL*Net trace with the thin driver and
server-side SQL trace doesn't tell me anything interesting.

Here's the relevant part of the server-side SQL*Net trace (IP address
changed to protect the innocent):


nigini: Count in NI global area now: 1
nigini: Count in NI global area now: 1
nrigbni: Unable to get data from navigation file tnsnav.ora
niotns: Not enabling dead connection detection.
nlpcaini: No process parameters set
nsinherit: connecting...
nsinherit: doing connect handshake...
nsinherit:
ADR="(ADDRESS=(PROTOCOL=tcp)(DEV=18)(HOST=x.y.z.f) (PORT=1521))"
nsinherit: bufl=257
nttbnd2addr: port resolved to 1521
nttbnd2addr: using host IP address: x.y.z.f
nsopen: opening transport...
nttcnp: Validnode Table IN use; err 0x0
nsopen: transport is open
nsinherit: hoff=0
nsinherit: handshake is complete
nttbnd2addr: port resolved to 1521
nttbnd2addr: using host IP address: x.y.z.f
nsinherit: inheriting the connection...
nsopen: opening transport...
nttcnp: Validnode Table IN use; err 0x0
nttcnp: getting sockname
nttcnp: getting peername
nttcon: set TCP_NODELAY on 18
nsopen: transport is open
nsnainit: inf->nsinfflg[0]: 0x8 inf->nsinfflg[1]: 0x8
nsopen: global context check-in (to slot 0) complete
nscon: doing connect handshake...
nscon: sending NSPTRS packet
nscon: doing connect handshake...
nscon: got NSPTCN packet
nsinherit: connection inherited
nsinherit: connected
nscon: sending NSPTAC packet
nscon: doing connect handshake...
nscon: nsctxinf[0]=0x49, [1]=0x8
nsconbrok: asking transport to enable NTOBROKEN
nsnainconn: inf->nsinfflg[0]: 0x49 inf->nsinfflg[1]: 0x8
niotns: No broken-connection function available.
nazsgunm: failed with error 12630
nioqbr: state = normal (0)
nsdo: sending NSPTMK packet
nioqbr: calling rdbms ast handler...
nioqbr: ...done.
nioqrs: state = interrupted (1)
nsdo: sending NSPTMK packet
nsrdr: got NSPTMK packet
nioqbr: state = normal (0)
nsdo: sending NSPTMK packet
nioqbr: calling rdbms ast handler...
nioqbr: ...done.
nioqrs: state = interrupted (1)
nsdo: sending NSPTMK packet
nsrdr: got NSPTMK packet

ORA-12630 (if that's what nazsgunm is saying) is like HTTP 500: no way
to tell what went wrong..

Any help is appreciated.

Alex Smith
Insight LLC

re: Mysteriously dropped connection and deciphering the trace

First try the 9.2.0.3 driver and see if the problem goes away. JDBC
thin drivers are backward compatible.

If the problem is due to connection timeout, you can try setting
SQLNET.EXPIRE_TIME in sqlnet.ora so that the Oracle server will send a
ping to the client every n seconds. This might keep the connection from
timing out.

If there's a firewall in front of the server, see if you can run the
same Java code behind the firewall. If this solves the problem, then
you have isolated the problem to the firewall. I think there's a list
of firewalls certified by Oracle, and this includes Checkpoint, Cisco,
Sun and some other vendors. You might want to check Metalink for details.

Cheers,
Dave






Alex Smith wrote:[color=blue]
> Hi all,
>
> I have an application that uses Oracle's JDBC thin driver 9.0.x to
> have a nice, friendly chat with 8.1.x database. During this exchange
> the server rudely interrupts the conversation and causes the
> application to fubar. The driver reports this as 'Connection reset'
> (IOException) without any error numbers. The server is hosted by a 3rd
> party in one of those "controlled" environments so my first guess is
> network/tx timeout but wouldn't it say so? This is 100% reproducible
> and always fails in the same place. The line of code corresponding to
> that reported in JDBC driver stack trace does nothing but unset the
> auto commit. If this is indeed a case of one particular transaction
> timing out on commit, how do I tell if this is indeed the case? I
> can't do a client-side SQL*Net trace with the thin driver and
> server-side SQL trace doesn't tell me anything interesting.
>
> Here's the relevant part of the server-side SQL*Net trace (IP address
> changed to protect the innocent):
>
>
> nigini: Count in NI global area now: 1
> nigini: Count in NI global area now: 1
> nrigbni: Unable to get data from navigation file tnsnav.ora
> niotns: Not enabling dead connection detection.
> nlpcaini: No process parameters set
> nsinherit: connecting...
> nsinherit: doing connect handshake...
> nsinherit:
> ADR="(ADDRESS=(PROTOCOL=tcp)(DEV=18)(HOST=x.y.z.f) (PORT=1521))"
> nsinherit: bufl=257
> nttbnd2addr: port resolved to 1521
> nttbnd2addr: using host IP address: x.y.z.f
> nsopen: opening transport...
> nttcnp: Validnode Table IN use; err 0x0
> nsopen: transport is open
> nsinherit: hoff=0
> nsinherit: handshake is complete
> nttbnd2addr: port resolved to 1521
> nttbnd2addr: using host IP address: x.y.z.f
> nsinherit: inheriting the connection...
> nsopen: opening transport...
> nttcnp: Validnode Table IN use; err 0x0
> nttcnp: getting sockname
> nttcnp: getting peername
> nttcon: set TCP_NODELAY on 18
> nsopen: transport is open
> nsnainit: inf->nsinfflg[0]: 0x8 inf->nsinfflg[1]: 0x8
> nsopen: global context check-in (to slot 0) complete
> nscon: doing connect handshake...
> nscon: sending NSPTRS packet
> nscon: doing connect handshake...
> nscon: got NSPTCN packet
> nsinherit: connection inherited
> nsinherit: connected
> nscon: sending NSPTAC packet
> nscon: doing connect handshake...
> nscon: nsctxinf[0]=0x49, [1]=0x8
> nsconbrok: asking transport to enable NTOBROKEN
> nsnainconn: inf->nsinfflg[0]: 0x49 inf->nsinfflg[1]: 0x8
> niotns: No broken-connection function available.
> nazsgunm: failed with error 12630
> nioqbr: state = normal (0)
> nsdo: sending NSPTMK packet
> nioqbr: calling rdbms ast handler...
> nioqbr: ...done.
> nioqrs: state = interrupted (1)
> nsdo: sending NSPTMK packet
> nsrdr: got NSPTMK packet
> nioqbr: state = normal (0)
> nsdo: sending NSPTMK packet
> nioqbr: calling rdbms ast handler...
> nioqbr: ...done.
> nioqrs: state = interrupted (1)
> nsdo: sending NSPTMK packet
> nsrdr: got NSPTMK packet
>
> ORA-12630 (if that's what nazsgunm is saying) is like HTTP 500: no way
> to tell what went wrong..
>
> Any help is appreciated.
>
> Alex Smith
> Insight LLC[/color]

re: Mysteriously dropped connection and deciphering the trace

asmith42@hotmail.com (Alex Smith) wrote
[color=blue]
> I have an application that uses Oracle's JDBC thin driver 9.0.x to
> have a nice, friendly chat with 8.1.x database. During this exchange
> the server rudely interrupts the conversation and causes the
> application to fubar.[/color]

Don't you find it weird how software get more and more human-like?
These days even talking trash to one another.. <sigh>
[color=blue]
> nsconbrok: asking transport to enable NTOBROKEN
> nsnainconn: inf->nsinfflg[0]: 0x49 inf->nsinfflg[1]: 0x8
> niotns: No broken-connection function available.
> nazsgunm: failed with error 12630[/color]

12630, 00000, "Native service operation not supported"
// *Cause: An operation requested by a user is not supported by the
native
// services component.
// *Action: This may be an internal error if the operation should have
been
// supported.


My guess is that you have the thin JDBC drivers wired for some option
that Net8 on the back-end can not support. Or, this option is
hardwired (but then this should have been in readme's hopefully).

I did a google on http://www.google.com/search?q=oracl...ction+function

It turned up quite a few pages. Hmm.. seems like this option that
fails is akin to an IP OOB at OraNet level to deal with a CTRL-BREAK
from the client. You may make more sense out of that google set than
I..

--
Billy