ORA-3113 ORA-3114 or ORA-12151 when using DCD. (Doc ID 1300824.1)

Last updated on OCTOBER 02, 2024

Applies to:

Symptoms

ENV:

Release 11.1.0.7.0 - Production
Dead Connection Detection is turned on.

Application reports ORA-3113 or ORA-3114.
In the Sqlnet tracing the error takes place during the Break and Reset functionality and the trace reports the ORA-12151 Prior to a proper Close is requested by the application.

The sqlnet trace will yield a packet similar to
[08-OCT-2009 17:26:47:031] nioqrs: nioqrs: sucking for reset marker...
[08-OCT-2009 17:26:47:031] nioqar: entry
[08-OCT-2009 17:26:47:031] nioqar: nioqar: suck pipe til I get a reset...
[08-OCT-2009 17:26:47:031] nsbasic_brc: entry: oln/tot=10
[08-OCT-2009 17:26:47:031] nsbasic_brc: type=6, plen=10
[08-OCT-2009 17:26:47:031] nsbasic_brc: what=1, tot =10
[08-OCT-2009 17:26:47:031] nsbasic_brc: packet dump
[08-OCT-2009 17:26:47:031] nsbasic_brc: 00 0A 00 00 06 10 00 00 |........|
[08-OCT-2009 17:26:47:031] nsbasic_brc: 00 00 |.. |
[08-OCT-2009 17:26:47:032] nsbasic_brc: exit: oln=0, dln=0, tot=10, rc=0
[08-OCT-2009 17:26:47:032] nioqar: recieve failed, no reset marker.
[08-OCT-2009 17:26:47:032] nioqer: entry
[08-OCT-2009 17:26:47:032] nioqer: incoming err = 12151
[08-OCT-2009 17:26:47:032] nioqce: entry
[08-OCT-2009 17:26:47:032] nioqce: exit
[08-OCT-2009 17:26:47:032] nioqer: returning err = 12151
[08-OCT-2009 17:26:47:032] nioqer: exit
[08-OCT-2009 17:26:47:032] nioqar: exit
[08-OCT-2009 17:26:47:032] nioqrs: nioqrs: recieve failed, no reset marker.
[08-OCT-2009 17:26:47:032] nioqrs: exit
[08-OCT-2009 17:26:47:032] nioqds: entry
[08-OCT-2009 17:26:47:032] nioqds: disconnecting...

The connection will then look like a valid close in the trace file by sending the expected close connection request.

[08-OCT-2009 17:26:47:032] nttwr: exit
[08-OCT-2009 17:26:47:032] nspsend: packet dump
[08-OCT-2009 17:26:47:032] nspsend: 00 0A 00 00 06 00 00 00 |........|
[08-OCT-2009 17:26:47:032] nspsend: 00 40 |.@ |
[08-OCT-2009 17:26:47:032] nspsend: 10 bytes to transport
[08-OCT-2009 17:26:47:032] nspsend: normal exit
[08-OCT-2009 17:26:47:033] nsdofls: exit (0)
[08-OCT-2009 17:26:47:033] nsbfr: entry

There is normally some idle time between the last packet received and the error stack.
Both the 12151 and @ packets are very common to trace files and do not mean a problem.
The way to confirm the issue is taking place is looking closely at the order of the packets received between the clients last action and the close of the connection.

For example
This is the last Received packet on the client.
[08-OCT-2009 17:20:47:015] nsbasic_brc: 00 00 00 00 00 00 00 00 |........|
[08-OCT-2009 17:20:47:015] nsbasic_brc: 00 00 00 00 00 55 00 00 |.....U..|
[08-OCT-2009 17:20:47:015] nsbasic_brc: 00 09 01 00 00 00 04 00 |........|
[08-OCT-2009 17:20:47:015] nsbasic_brc: exit: oln=0, dln=166, tot=176, rc=0
[08-OCT-2009 17:20:47:015] nioqrc: exit

Now you see a small delay. This was the client idle time to allow the DCD packet to be sent.
The client wakes up and enters the Break Reset mode..

[08-OCT-2009 17:26:47:028] nioqhp: entry
[08-OCT-2009 17:26:47:028] nioqhp: handling break in state posted (4)
[08-OCT-2009 17:26:47:028] nioqsm: entry
[08-OCT-2009 17:26:47:028] nioqsm: Sending break packet (1)...
[08-OCT-2009 17:26:47:028] nscontrol: entry
[08-OCT-2009 17:26:47:028] nscontrol: cmd=45, lcl=0x0
[08-OCT-2009 17:26:47:028] nscontrol: normal exit
[08-OCT-2009 17:26:47:028] nsdo: entry
[08-OCT-2009 17:26:47:028] nsdo: cid=0, opcode=67, *bl=1, *what=18, uflgs=0x100, cflgs=0x3
[08-OCT-2009 17:26:47:028] nsdo: rank=64, nsctxrnk=0
[08-OCT-2009 17:26:47:028] nsdo: nsctx: state=8, flg=0x400d, mvd=0
[08-OCT-2009 17:26:47:028] nsdo: gtn=127, gtc=127, ptn=10, ptc=8155
[08-OCT-2009 17:26:47:029] nsdo: sending ATTN
[08-OCT-2009 17:26:47:029] nsdo: 1 urgent byte to transport
Note the sending Break packet.

The packet is then sent
[08-OCT-2009 17:26:47:029] nsdo: sending NSPTMK packet
[08-OCT-2009 17:26:47:029] nspsend: entry
[08-OCT-2009 17:26:47:029] nspsend: plen=11, type=12
[08-OCT-2009 17:26:47:029] nttwr: entry
[08-OCT-2009 17:26:47:029] nttwr: socket 6 had bytes written=11
[08-OCT-2009 17:26:47:029] nttwr: exit
[08-OCT-2009 17:26:47:029] nspsend: packet dump
[08-OCT-2009 17:26:47:029] nspsend: 00 0B 00 00 0C 00 00 00 |........|
[08-OCT-2009 17:26:47:029] nspsend: 01 00 02 |... |
[08-OCT-2009 17:26:47:029] nspsend: 11 bytes to transport
[08-OCT-2009 17:26:47:029] nspsend: normal exit
[08-OCT-2009 17:26:47:029] nsdoacts: entry
[08-OCT-2009 17:26:47:030] nsdofls: entry

The client gets the 10 byte DCD packet instead of the expected reset packet.
[08-OCT-2009 17:26:47:030] nioqrs: nioqrs: sucking for reset marker...
[08-OCT-2009 17:26:47:030] nioqar: entry
[08-OCT-2009 17:26:47:030] nioqar: nioqar: suck pipe til I get a reset...
[08-OCT-2009 17:26:47:030] nsbasic_brc: entry: oln/tot=0
[08-OCT-2009 17:26:47:030] nsbasic_brc: type=6, plen=10
[08-OCT-2009 17:26:47:030] nsbasic_brc: what=1, tot =20
[08-OCT-2009 17:26:47:030] nsbasic_brc: packet dump
[08-OCT-2009 17:26:47:030] nsbasic_brc: 00 0A 00 00 06 10 00 00 |........|
[08-OCT-2009 17:26:47:030] nsbasic_brc: 00 00 |.. |
[08-OCT-2009 17:26:47:030] nsbasic_brc: exit: oln=10, dln=0, tot=20, rc=0
[08-OCT-2009 17:26:47:030] nioqar: recieve failed, no reset marker.
[08-OCT-2009 17:26:47:030] nioqer: entry
[08-OCT-2009 17:26:47:030] nioqer: incoming err = 12151
[08-OCT-2009 17:26:47:030] nioqce: entry
[08-OCT-2009 17:26:47:030] nioqce: exit
[08-OCT-2009 17:26:47:030] nioqer: returning err = 12151
[08-OCT-2009 17:26:47:030] nioqer: exit
[08-OCT-2009 17:26:47:030] nioqar: exit
[08-OCT-2009 17:26:47:030] nioqrs: nioqrs: recieve failed, no reset marker.
and yields the error that break reset failed.
That is because its not the expected Marker packet from the server instead its the 10 byte DCD packet.

This may take place a couple more times as the Break reset tries again to complete.
But eventually fails and the connection properly closes out as mentioned above by sending the @ connection closed packet.
This proves you have the error.

Changes

Dead Connection detection is used by setting SQLNET.EXPIRE_TIME=# in the SQLNET.ORA file on the server.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.