One of the most frustrating oracle errors we hate as DBA’s is the famous ora-3113 (often accompanied by ora-3114 and ora-12170).

Let’s take a scenario where we start hearing complaints about network errors and sporadic connection break downs from the end users.

The following example is taken from a windows 64bit and client windows 32 bit.

I’m hoping that the next procedure might help you better understand where the error stems from and debug it:

• First step in debugging such a behavior is looking from which side the errors come from. It could come from the server’s side, the client’s side or both. If such error comes from the server side we would probably see some evidence in the alert log and observe some traces in the user dump destination. We should also take a look at the listener.log as it might also give us a hint.
• In case the alert log is empty of errors and no traces were gathered in the udump directory we should check the client for errors. Now, we might see some network substrate (ns) related errors in the sqlnet.log such as  ” ns main err code: 12547″ but it does not point out the real issue as it indicates only a lost connection of the client. At this point we would want to trace the sql*net further.

In order to get some detailed information let’s add the para  meter

TRACE_LEVEL_CLIENT = 16 to the sqlnet.ora.

• Our trace file is going to grow very fast and so enough disk space is necessary. If we do get an error when the trace is running it should look like the following:

(636) [13-NOV-2009 07:21:52:294] ntt2err: entry
(636) [13-NOV-2009 07:21:52:294] ntt2err: soc 7480 error - operation=5,
ntresnt[0]=517, ntresnt[1]=54, ntresnt[2]=0
(636) [13-NOV-2009 07:21:52:294] ntt2err: exit
(636) [13-NOV-2009 07:21:52:294] nttrd: exit
(636) [13-NOV-2009 07:21:52:294] nsprecv: error exit
(636) [13-NOV-2009 07:21:52:294] nserror: entry(636) [13-NOV-2009 07:21:52:294] nserror:
nsres:, op=68, ns=12547, ns2=12560; nt[0]=517, nt[1]=54, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
(636) [13-NOV-2009 07:21:52:294] nsrdr: error exit
(636) [13-NOV-2009 07:21:52:294] snsbitts_ts: entry
(636) [13-NOV-2009 07:21:52:294] snsbitts_ts: acquired the bit
(636) [13-NOV-2009 07:21:52:294] snsbitts_ts: normal exit
(636) [13-NOV-2009 07:21:52:294] snsbitcl_ts: entry
(636) [13-NOV-2009 07:21:52:294] snsbitcl_ts: normal exit
(636) [13-NOV-2009 07:21:52:294] snsbitts_ts: entry
(636) [13-NOV-2009 07:21:52:294] snsbitts_ts: acquired the bit
(636) [13-NOV-2009 07:21:52:294] snsbitts_ts: normal exit
(636) [13-NOV-2009 07:21:52:294] nsdo: nsctxrnk=0
(636) [13-NOV-2009 07:21:52:294] snsbitcl_ts: entry
(636) [13-NOV-2009 07:21:52:294] snsbitcl_ts: normal exit
(636) [13-NOV-2009 07:21:52:294] nsdo: error exit
(636) [13-NOV-2009 07:21:52:294] nioqrc: wanted 1 got 0, type 0
(636) [13-NOV-2009 07:21:52:294] nioqper: error from nioqrc
(636) [13-NOV-2009 07:21:52:294] nioqper: ns main err code: 12547
(636) [13-NOV-2009 07:21:52:294] nioqper: ns (2) err code: 12560
(636) [13-NOV-2009 07:21:52:294] nioqper: nt main err code: 517
(636) [13-NOV-2009 07:21:52:294] nioqper: nt (2) err code: 54
(636) [13-NOV-2009 07:21:52:294] nioqper: nt OS err code: 0
(636) [13-NOV-2009 07:21:52:294] nioqer: entry
(636) [13-NOV-2009 07:21:52:294] nioqer: incoming err = 12151
(636) [13-NOV-2009 07:21:52:294] nioqce: entry
(636) [13-NOV-2009 07:21:52:294] nioqce: exit
(636) [13-NOV-2009 07:21:52:294] nioqer: returning err = 3135
(636) [13-NOV-2009 07:21:52:294] nioqer: exit
(636) [13-NOV-2009 07:21:52:294] nioqrc: exit
(636) [13-NOV-2009 07:21:52:294] nioqds: entry
(636) [13-NOV-2009 07:21:52:294] nioqds: disconnecting...
(636) [13-NOV-2009 07:21:52:294] nsclose: entry
(636) [13-NOV-2009 07:21:52:294] nstimarmed: entry
(636) [13-NOV-2009 07:21:52:294] nstimarmed: no timer allocated
(636) [13-NOV-2009 07:21:52:294] nstimarmed: normal exit

• The highlighted line is what we need to focus on. The actual error is the ntt2error and the following arguments  – operation=5, ntresnt[0]=517, ntresnt[1]=54, ntresnt[2]=0 mean that a the network is stressed.

The error means that a packet being sent from the client does not receive ACK for 93 seconds, which is the default of the parameter TcpMaxDataRetransmissions

(look at Microsoft TechNet for further details –  http://technet.microsoft.com/en-us/library/cc938210.aspx ). The retransmission mechanism works in the way that when a packet is being send it waits for 3 seconds for ack before resending it. If it does not receive ack it will wait 3 seconds times 2, then times 4, times 8, and times 16 (5 times total).

The workaround for a busy network is to increase the default of the TcpMaxDataRetransmissions parameter. This parameter is located in the registry under

HKEY_LOCAL_MACHINE > system > CurrentControlSet > Services > Tcpip > Parameters

If the parameter does not exsist, just add it but bear in mind that adjusting the parameter can lead to a longer timeout wait rather than actually solve the problem.

So what you really need to do is to get the guy with the sniffer over and have him search for the problem.

Good luck!