TNS-12535: TNS:operation timed out以及TNS-00505: Operation timed out的处理

序言：查看alert日志，发现很多报错信息：
[oracle@localhost trace]$ more alert_powerdes.log


***********************************************************************


***********************************************************************


Fatal NI connect error 12170.


  VERSION INFORMATION:
TNS for Linux: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production


  VERSION INFORMATION:
TNS for Linux: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
  Time: 28-JAN-2015 15:14:16
  Time: 28-JAN-2015 15:14:16
  Tracing not turned on.
  Tracing not turned on.
  Tns error struct:
  Tns error struct:
    ns main err code: 12535
    ns main err code: 12535
    
TNS-12535: TNS:operation timed out
TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    ns secondary err code: 12560
    nt main err code: 505
    nt main err code: 505
    
TNS-00505: Operation timed out
TNS-00505: Operation timed out
    nt secondary err code: 110
    nt secondary err code: 110
    nt OS err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.170.220)(PORT=54418))
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.170.220)(PORT=54420))
Wed Jan 28 15:19:00 2015
LGWR: Standby redo logfile selected to archive thread 1 sequence 29226
LGWR: Standby redo logfile selected for thread 1 sequence 29226 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 29226 (LGWR switch)
  Current log# 3 seq# 29226 mem# 0: /home/oradata/powerdes/redo03.log
Wed Jan 28 15:19:01 2015
Archived Log entry 57344 added for thread 1 sequence 29225 ID 0xca2ab4eb dest 1:
Wed Jan 28 15:34:28 2015
Wed Jan 28 15:34:28 2015


......


一：分析，参考官方说明关于该警告的说明：
  Note:465043.1

   The "WARING:inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete it's authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

   You may also witness ORA-12170 without timeout error on the database sqlnet.log file.This entry would also have the client address which failed to get authenticated.Some applications or JDBC thin driver applications may not have these details.

 1、网络攻击，例如：半开连接攻击

    Server gets a connection request from a malcious client which is not supposed to connect to the database,in which case the error thrown is the correct behavior.You can get the client address for which the error was thrown via sqlnet log file.

这个oracle dba处于局域网，来自网络攻击的可能也被排除了。


2、Client在default 60秒内没有完成认证

    The server receives a valid client connection request but the client tabkes a long time to authenticate more than the default 60 seconds.


    去check是否默认的60秒：
[oracle@localhost ~]$ lsnrctl 


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:26:25

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully
LSNRCTL> 

看到inbound_connect_timeout是60秒，有可能是由于Client在默认60秒内没有完成认证这个原因引起的。


3、DB负载太高

    The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.

    WANGING:inbound connection timed out (ORA-3136)
[oracle@localhost admin]$ w
18:24:09 up 88 days, 17:36,  6 users,  load average: 0.60, 0.88, 1.21
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU WHAT
root     pts/3    xxx.1xx.120.238  Tue11    1:55m  0.29s  0.04s -bash
root     pts/4    xxx.1xx.120.238  Tue11    0.00s  0.18s  0.00s w
root     pts/7    xxx.1xx.120.238  Tue14    6:51m  0.28s  0.20s rlwrap sqlplus / as sysdba
root     pts/6    xxx.1xx.120.238  15:49    2:34m  0.00s  0.00s -bash
[oracle@localhost admin]$ 
线上db负载很低，w下来不到1，所以排除这种情况。


二：开始设置inbound_connect_timeout的值


1，查看inbound_connect_timeout的值

[oracle@localhost ~]$ lsnrctl 


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:26:25


Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Welcome to LSNRCTL, type "help" for information.


LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully
LSNRCTL> 


大概有3种办法来操作：
  1)、设置sqlnet.ora文件：SQLNET.INBOUND_CONNECT_TIMEOUT=0；
  2)、设置listener.ora文件：INBOUND_CONNECT_TIMEOUT_listenername=0；
  3)、然后reload或者重启监听。


2，在线临时重新设置值
LSNRCTL> show inbound_connect_timeout 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully
LSNRCTL> 
LSNRCTL> 
LSNRCTL> set inbound_connect_timeout 0
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully
LSNRCTL> 


3，永久性在listener.ora设置
[oracle@powerlong4 admin]$ vim listener.ora 
INBOUND_CONNECT_TIMEOUT_listener=0
[oracle@powerlong4 admin]$ 
[oracle@powerlong4 admin]$ 
[oracle@powerlong4 admin]$ lsnrctl stop


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:40:33


Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@powerlong4 admin]$ lsnrctl start


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:40:37


Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Starting /oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...


TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=powerlong4)(PORT=1521)))


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                28-JAN-2015 16:40:37
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=powerlong4)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@powerlong4 admin]$ lsnrctl 


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:40:41


Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Welcome to LSNRCTL, type "help" for information.


LSNRCTL> show inbound_connect_timeout
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully
LSNRCTL> exit
[oracle@powerlong4 admin]$ vim listener.ora 
[oracle@powerlong4 admin]$ vim listener.ora 
[oracle@powerlong4 admin]$ 
[oracle@powerlong4 admin]$ lsnrctl stop


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:41:38


Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
^[[AThe command completed successfully
[oracle@powerlong4 admin]$ lsnrctl start


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:41:46


Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Starting /oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...


TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=powerlong4)(PORT=1521)))


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                28-JAN-2015 16:41:46
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=powerlong4)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@powerlong4 admin]$ lsnrctl 


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:41:49


Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Welcome to LSNRCTL, type "help" for information.


LSNRCTL> show inbound_connect_timeout
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully
LSNRCTL> 
LSNRCTL> exit
[oracle@powerlong4 admin]$ 

PS：这里使用lsnrctl stop|start会断掉当前oracle里的所有客户端连接，也可以使用lsnrctl reload来加载

       后续：设置为0之后再没有出现过类似的报警信息，不过设置inbound_connect_timeout为0，是有隐患的。因为这个参数从9i开始引入，指定了客户端连接服务器并且提供认证信息的超时时间，如果超过这个时间客户端没有提供正确的认证信息，服务器会自动中止连接请求，同时会记录试图连接的IP地址和ORA-12170：TNS:Connect timeout occurred错误。

    这个参数的引入，主要是防止DoS攻击，恶意攻击者可以通过不停的开启大量连接请求，占用服务器的连接资源，使得服务器无法提供有效服务。在10.2.0.1起，该参数默认设置为60秒。但是，这个参数的引入也导致了一些相关的Bug。比如：

​
    Bug 5594769 - REMOTE SESSION DROPPED WHEN LOCAL SESSION SHARED AND INBOUND_CONNECT_TIMEOUT SET
    Bug 5249163 - CONNECTS REFUSED BY TNSLSNR EVERY 49 DAYS FOR INBOUND_CONNEC_TIMEOUT SECONDS
    所以设置为0也是存在被攻击的隐患，设置为60秒太长了，所以最后权衡了下，我将inbound_connect_timeout设置成了8秒。


    参考文章地址：http://www.cnblogs.com/future2012lg/p/3739752.html

 ----------------------------------------------------------------------------------------------------------------
<版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!>
原博客地址：      http://blog.itpub.net/26230597/viewspace-1418586/​
原作者：黄杉 (mchdba)
----------------------------------------------------------------------------------------------------------------