【ORA-3136】WARNING: inbound connection timed out (ORA-3136)

WARNING: inbound connection timed out (ORA-3136)连接超时问题

1、alter_SID.log日志：aaa

Mon Mar 9 02:18:40 2009

ksvcreate: Process(q002) creation failed

Mon Mar 9 02:32:29 2009

WARNING: inbound connection timed out (ORA-3136)

Mon Mar 9 02:33:02 2009

WARNING: inbound connection timed out (ORA-3136)

Mon Mar 9 02:33:19 2009

WARNING: inbound connection timed out (ORA-3136)

 

 

2、sqlnet.log日志

 

Fatal NI connect error 12170.

 

VERSION INFORMATION:

TNS for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production

TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production

Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production

Time: 09-MAR-2009 02:32:29

Tracing not turned on.

Tns error struct:

    ns main err code: 12535

    TNS-12535: TNS:operation timed out

    ns secondary err code: 12606

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.4.36)(PORT=3663))

 

 

 

3、参考官方说明关于该警告的说明：

Note:465043.1

 

 

The "WARNING: 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 server sqlnet.log file.

This entry would also have the clinet 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 malicious 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.

 

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

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

 

3.DB负载太高

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

 

 

WARNING: inbound connection timed out (ORA-3136)

这个错误跟 oracle 监听的一个参数有关：SQLNET.INBOUND_CONNECT_TIMEOUT

这个参数从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_CONNECT_TIMEOUT SECONDS

该参数可以通过设置为0来禁用，在服务媏

设置sqlnet.ora文件：SQLNET.INBOUND_CONNECT_TIMEOUT＝0

设置listener.ora文件: INBOUND_CONNECT_TIMEOUT_listenername＝0

然后reload或者重启监听

 

 

这是由于连接超时所产生的问题，在10.2.0.1.0版本中sqlnet.inbound_connect_timeout参数默认为60秒，即如果连接时间超过60秒则提示超时，而在其他10G版本中这两个参数默认为0，即无限制。

 

 

如何操作：

一、查看数据库中listener.ora中的inbound_connect_timeout参数值

1、进入lsnrctl，

LHXXDBS01:oraoms> lsnrctl

2、查看inbound_connect_timeout参数：

LSNRCTL> show inbound_connect_time

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LHXXDBS)(PORT=1568)))

LISTENER parameter "inbound_connect_timeout" set to 0

The command completed successfully

 

如果inbound_connect_timeout参数值不为0，则可以修改为0

修改：

LSNRCTL> set inbound_connect_time 0

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LHXXDBS)(PORT=1568)))

LISTENER parameter "inbound_connect_timeout" set to 0

The command completed successfully

 

 

二、修改/oracle/oms/102_64/network/admin/sqlnet.ora

SQLNET.INBOUND_CONNECT_TIMEOUT = 0

 

 

三、重新载入listener

LSNRCTL> reload

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LHXXDBS)(PORT=1568)))

The command completed successfully

--The End---

-----------------------------------------------------------------------------------------------------------------------------------------------

WARNING: inbound connection timed out (ORA-3136)

1、现象
alert文件中
Mon Jun 27 11:12:34 2011
WARNING: inbound connection timed out (ORA-3136)

sqlnet.log文件中
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 10.2.0.4.0 – Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.4.0 – Production
TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.4.0 – Production
Time: 27-JUN-2011 11:12:34
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.227.20)(PORT=2104))

2、原因
Whenever default timeouts are assigned to a parameter, there may be cases where this default does not work well with a particular application. However, some type of timeout on the connection establishment is necessary to combat Denial of Service attacks on the database. In this case, SQLNET.INBOUND_CONNECT__TIMEOUT andINBOUND_CONNECT_TIMEOUT_listenername were given default values of60 seconds in Oracle 10.2. It is these timeout values that can cause the errors described in this note.
Also note that it is possilbe the reason the database is slow to authenticate, may be due to an overloaded Oracle database or node.

3、解决
1). set INBOUND_CONNECT_TIMEOUT_listenername=0 in listener.ora
2). set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.
3). stop and start both listener and database.
4). Now try to connect to DB and observe the behaviour

4、具体操作
4.1）修改INBOUND_CONNECT_TIMEOUT_listenername
4.1.1)lsnrctl命令修改
LSNRCTL> set inbound_connect_timeout 0
LSNRCTL>save_config

4.1.2)vi修改
修改listener.ora文件，加入: INBOUND_CONNECT_TIMEOUT_listenername=0

4.2）修改SQLNET.INBOUND_CONNECT__TIMEOUT
修改sqlnet.ora文件，加入: SQLNET.INBOUND_CONNECT__TIMEOUT=0

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

问题处理(ORA-3136、TNS-12535等问题）

oracle 11g 日志如下：

WARNING: inbound connection timed out (ORA-3136)
TNS-12535: TNS:operation timed out
    ns secondaryerr code: 12606
    nt main errcode: 0
Fatal NI connect error12170.    VERSIONINFORMATION:    TNS for Linux: Version 11.1.0.7.0 -Production  
  Unix Domain Socket IPC NT Protocol Adaptor forLinux: Version 11.1.0.7.0 -Production   
   Oracle Bequeath NT ProtocolAdapter for Linux: Version 11.1.0.7.0 -Production    
   TCP/IP NT Protocol Adapter forLinux: Version 11.1.0.7.0 -Production  
   Time: 08-NOV-201113:57:10  
   Tracing not turnedon.  
   Tns errorstruct:    
   ns main err code:12535 
    TNS-12535:TNS:operation timed out  
     ns secondary err code:12560    
     nt main err code: 505 
      TNS-00505: Operation timedout  
        nt secondary err code: 110 
           nt OS err code: 0
             Client address:(ADDRESS=(PROTOCOL=tcp)(HOST=10.100.107.62)(PORT=52357))
+++++++++++++++++++++++

解决方法：

  listener.ora
   INBOUND_CONNECT_TIMEOUT_LISTENER = 0
   DIAG_ADR_ENABLED_LISTENER = OFF

   sqlnet.ora
   DIAG_ADR_ENABLED = OFF
   SQLNET.INBOUND_CONNECT_TIMEOUT =0