TNS-12535 / ORA-12535 on Connection to Database

APPLIES TO

Oracle Net Services - Version 8.1.7.0.0 and later
Information in this document applies to any platform.
***Checked for relevance on 07-Apr-2014***

SYMPTOMS

Oracle Net connections to database fail with error code TNS-12535 / ORA-12535: TNS:operation timed out.

Oracle Net client trace can show large delay upon resolving TNS listener host

[03-AUG-2005 09:42:29:385] nttgetport: port resolved to 1521 
[03-AUG-2005 09:42:29:385] nttgetport: exit 
[03-AUG-2005 09:42:29:385] nttbnd2addr: looking up IP addr for host:  
sample.com 
[03-AUG-2005 09:46:12:658] nserror: nsres: id=0, op=65, ns=12535, ns2=12560; 
 nt[0]=505, nt[1]=145, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0 
[03-AUG-2005 09:46:12:658] nsopen: unable to open transport

Sample trace from Solaris and shows lowest error code in the stack is nt[1]=145

nserror: nsres: id=0, op=65, ns=12535, ns2=12560;  
 nt[0]=505, nt[1]=145, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0 

Errno.h

#define ETIMEDOUT 145 /* Connection timed out */

Sqlnet.log will also show the error stack

CAUSE

Operating system error connection timed out. The TNS listener is unable to service the requests quickly enough. The TNS listener has a queue (backlog) of connection requests that it needs to service. If it is 
unable to service these quick enough, they will start to timeout.

SOLUTION

The size of the TNS listener queue can be influenced by setting parameter QUEUESIZE in the listener.ora file.  

To specify a queue size for a listener, enter a value to the QUEUESIZE parameter at the end of any listening address:

LISTENER = 
  (ADDRESS =(PROTOCOL = TCP)(HOST=SAMPLE.COM)(PORT = 1521)(QUEUESIZE=99))

However,the parameter will be resized to the max allowable value by the operating system TCP/IP parameters.

Sun Solaris:    tcp_conn_req_max_q
HP-UX:          tcp_conn_req_max  
HP Tru64 / AIX  somaxconn 

This can be checked by trussing the TNS listener startup :

truss -feao /tmp/truss.out lsnrctl start 

Further information on truss can be found in  Document 110888.1 How to Trace Unix System Calls
Review the truss.out file and look for the string 'listen'

6234: listen(9, 5) = 0 

listen is defined as int listen(int s, int backlog); 

Example above shows the QUEUESIZE is set to 5
First value 6234 is the process id (PID) of the lsitener truss'd
9 is the file Descriptor 

Taking the above commands the following information was confirmed for default LISTENER.ORA queue sizes

Linux

strace -fo /tmp/queue.log lsnrctl start listener
vi /tmp/queue.log and search on listen

Oracle Version   Default QUEUESIZE Value
                 
9.2              10317 listen(8, 128) 
10.1             15520 listen(8, 128) 
10.2             30842 listen(8, 128) 
11.1             31140 listen(8, 128) 
11.2             2099  listen(8, 128) 

Default QUEUESIZE values show PID, FD and QUEUESIZE

    10317 listen(8, 128)

That is PID 10317
FD (File Descriptor) 8
QUEUESIZE 128

128 is the default value taken from SOMAXCONN value. See /usr/include/linux/socket.h
This is the same for other platforms tested HP and AIX. That is SOMAXCONN value is used for default value of QUEUESIZE for the TNS listener.

HP default QUEUESIZE is therefore 4096
AIX default QUEUESIZE is therefore 1024

Solaris testing shows the following.

Solaris 
Oracle Version   Default QUEUESIZE Value
  
9.2              20423: listen(10, 5, 1)
10.1             26948: listen(10, 32, 1) 
10.2             4621: listen(10, 32, 1) 
11.1             12585: listen(10, 32, 1
11.2             552: listen(10, 32, SOV_DEFAULT) 

From 10.1 onwards Solaris Oracle Net code is set to use value of 32. 
This is fixed from release 11.2.0.4 and 12c onwards, that is Solaris QUEUESIZE will default use SOMAXCONN.
See Bug:13113888 Default Queuesize From Solaris not using OS Default

以上内容为MOS  文档 ID 214122.1中的内容。