IBM AIX 6.1 & Newer: TCP Connections Intermittently Failing ORA-12547 , TNS-12547, TNS-12560 and OS Error 32 (Doc ID 1084443.1)

Last updated on JULY 27, 2016

Applies to:

Oracle Net Services - Version 10.2.0.4 to 12.1.0.2 [Release 10.2 to 12.1]
IBM AIX on POWER Systems (64-bit)
***Checked for relevance on 5-DEC-2014***

Symptoms

Connections to the database via the listener (i.e. TCP)  are limited in number and testing reveals that this number is fairly consistent.  A shutdown of database and listener will resolve the issue until  <n> number of TCP connections is established again.  Local or bequeath connections are NOT exhibiting this problem so local connections to the database are not failing at the time that this issue is reproducible.


In our example case, when the number of TCP connections exceeded 500, remote connections started to fail.  SQL*Plus connections using TCP failed with ORA-12547 and Tnspings that are launched remotely or on the server are failing with the following error:

e.g.: tnsping MY_TNS_ALIAS
TNS-12547: TNS:lost contact

 

In our case, SQL*Plus connections using TCP are failing at around 500 connections also.  The following message and error stack can be seen in an Oracle Net client trace of a TCP connection:

[30-MAR-2010 11:38:33:434] nspsend: transport write error
[30-MAR-2010 11:38:33:467] nspsend: error exit
[30-MAR-2010 11:38:33:495] nscon: error sending NSPTCN packet

Followed shortly thereafter with:

[12-APR-2010 11:29:34:602] nserror: nsres: id=0, op=67, ns=12547, ns2=12560; nt[0]=517, nt[1]=32, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0

[12-APR-2010 11:29:34:605] nioqper: ns main err code: 12547
[12-APR-2010 11:29:34:614] nioqper: ns (2) err code: 12560
[12-APR-2010 11:29:34:621] nioqper: nt main err code: 517
[12-APR-2010 11:29:34:636] nioqper: nt (2) err code: 32
[12-APR-2010 11:29:34:650] nioqper: nt OS err code: 0
[12-APR-2010 11:29:34:661] niomapnserror: entry

You might also see this error stack in an Oracle Net client trace.  The exact messages would depend upon the version of client:

(1) [05-DEC-2014 09:50:46:999] nttcnp: creating a socket.
(1) [05-DEC-2014 09:50:46:999] nttcnp: exit
(1) [05-DEC-2014 09:50:46:999] nttcni: entry
(1) [05-DEC-2014 09:50:46:999] nttcni: Tcp conn timeout = 60000 (ms)
(1) [05-DEC-2014 09:50:46:999] nttcni: TCP Connect TO enabled. Switching to NB
(1) [05-DEC-2014 09:50:46:999] nttctl: entry
(1) [05-DEC-2014 09:50:46:999] nttctl: Setting connection into non-blocking mode
(1) [05-DEC-2014 09:50:46:999] nttcni: trying to connect to socket 8.
(1) [05-DEC-2014 09:50:47:000] ntt2err: entry
(1) [05-DEC-2014 09:50:47:000] ntt2err: soc 8 error - operation=1, ntresnt[0]=530, ntresnt[1]=73, ntresnt[2]=0
(1) [05-DEC-2014 09:50:47:000] ntt2err: exit
(1) [05-DEC-2014 09:50:47:000] nttcni: exit
(1) [05-DEC-2014 09:50:47:000] nttcon: exit
(1) [05-DEC-2014 09:50:47:000] nserror: entry
(1) [05-DEC-2014 09:50:47:000] nserror: nsres: id=0, op=65, ns=12560, ns2=0; nt[0]=530, nt[1]=73, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
(1) [05-DEC-2014 09:50:47:000] nsopen: unable to open transport

We have had reports of JDBC Thin clients failing with: 

"SQLException: oracle.net.ns.NetException: The Network Adapter could not establish the connection"


 

In addition to the client failures at <n> number of TCP connections, the LSNRCTL utility may start to fail with the following stack:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.com)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
IBM/AIX RISC System/6000 Error: 73: Connection reset by peer

It is important to note that this issue can affect all TCP connections so any feature or Oracle product that uses TCP might be exhibiting problems where different errors are being thrown. 

In one known case in a RAC installation, the Oracle Notification Service or ONS was crashing at around the same time as the other TCP failures started to occur.

This issue has also appeared at client only installations.  Client application servers running on AIX 6.1 or newer and with Traffic Regulation policies in place can suffer intermittent failures.

A client trace would show a failure to open a socket if the threshold for maximum sockets has been reached.  A client trace or log might show this stack: 

 The failure to connect would be intermittent because as sockets are freed on the client,  newly spawned connections would be successful. 

Changes

This is a new installation of IBM AIX 6.1 or the operating system has recently been upgraded to version 6.1.  Alternatively, the Traffic regulation policy may have been configured or modified recently.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms