sql net trace file
614497Nov 10 2009 — edited Nov 10 2009Hi All,
Our prod db is 2 nodes RAC 10g in MS window 2003 servers. It is located in a remote place. Recently one of our local app servers lost connection to this db although we can tnsping it but got ORA3113 error when we tried to logon using sql plus. Most of our local PCs did not have problem logon at all. We enabled the sql net trace on this app server and network administrators spent a lot of time on this and finally shutdown one of the app servers in the same remote location which seemed cause a lot of network traffic with a local app server. So the problem went away and the app server can logon to the db now. I (I know nothing about network) tried to read the sql net trace file generated during the trouble time using the help outlined in the “Examining Oracle Net Trace Files” written by Kevin Reardon. The error happened after client sending server character set and conversion graph it supports and before receiving character set and conversion graph from the server. It took a minute in this step and finally gave up. Following is a section of the trace file where error happens. My question is: even we know when the error happens and at what step how can we use this info to further identify the root cause: Is this because we have too much network traffic which caused timeout or other reason(s)? By the way our db servers have “INBOUND_CONNECT_TIMEOUT=180" (3 minutes) in the sqlnet.ora file and whole trace file starts at [06-NOV-2009 14:50:23:352] and ends at [06-NOV-2009 14:51:24:758] which is a little over 1 minute. Greatly appreciate your insights and thoughts.
Shirley
[06-NOV-2009 14:50:23:836] nsdo: normal exit
[06-NOV-2009 14:50:23:836] nsdo: entry
[06-NOV-2009 14:50:23:836] nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0, cflgs=0x3
[06-NOV-2009 14:50:23:836] nsdo: rank=64, nsctxrnk=0
[06-NOV-2009 14:50:23:836] nsdo: nsctx: state=8, flg=0x400d, mvd=0
[06-NOV-2009 14:50:23:836] nsdo: gtn=127, gtc=127, ptn=10, ptc=32730
[06-NOV-2009 14:50:23:836] nsdo: switching to application buffer
[06-NOV-2009 14:50:23:836] nsrdr: entry
[06-NOV-2009 14:50:23:836] nsrdr: recving a packet
[06-NOV-2009 14:50:23:836] nsprecv: entry
[06-NOV-2009 14:50:23:836] nsprecv: reading from transport...
[06-NOV-2009 14:50:23:836] nttrd: entry
[06-NOV-2009 14:51:24:742] nttrd: exit
[06-NOV-2009 14:51:24:742] ntt2err: entry
[06-NOV-2009 14:51:24:742] ntt2err: Read unexpected EOF ERROR on 644
[06-NOV-2009 14:51:24:742] ntt2err: exit
[06-NOV-2009 14:51:24:742] nsprecv: error exit
[06-NOV-2009 14:51:24:742] nserror: entry
[06-NOV-2009 14:51:24:742] nserror: nsres: id=0, op=68, ns=12537, ns2=12560; nt[0]=507, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[06-NOV-2009 14:51:24:742] nsrdr: error exit
[06-NOV-2009 14:51:24:742] nsdo: nsctxrnk=0
[06-NOV-2009 14:51:24:742] nsdo: error exit
[06-NOV-2009 14:51:24:742] nioqer: entry
[06-NOV-2009 14:51:24:742] nioqer: incoming err = 12151
[06-NOV-2009 14:51:24:742] nioqce: entry
[06-NOV-2009 14:51:24:742] nioqce: exit
[06-NOV-2009 14:51:24:742] nioqer: returning err = 3113
[06-NOV-2009 14:51:24:742] nioqer: exit
[06-NOV-2009 14:51:24:742] nioqrc: exit
[06-NOV-2009 14:51:24:742] nioqrs: entry