监听服务器异常，应用程序无法连接，ORA-3136

原创 ByteHouse 2024-09-24

197

1.问题描述

在alert文件存在错误：

</txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt>    
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.177.7.62)(PORT=39664))
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt>
Fatal NI connect error 12170.
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt>TNS-12535: TNS:operation timed out
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='yadb' host_addr='10.177.7.62' module='OMS'
 pid='6601'>
 <txt>WARNING: inbound connection timed out (ORA-3136)
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt>
  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
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> ns secondary err code: 12606
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> Time: 13-JUN-2018 10:09:01
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> nt main err code: 0
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> Tracing not turned on.
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> Tns error struct:
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> nt secondary err code: 0
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> ns main err code: 12535
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt>    
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> nt OS err code: 0
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt>TNS-12535: TNS:operation timed out
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.177.7.62)(PORT=19387))
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='yadb' host_addr='10.177.7.62' module='emagent@yadb (TNS V1-V3)'
 pid='6733'>
 <txt>WARNING: inbound connection timed out (ORA-3136)
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> ns secondary err code: 12606
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> nt main err code: 0
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> nt secondary err code: 0
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.427+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> nt OS err code: 0
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.428+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt> Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.177.1.233)(PORT=13144))
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.428+08:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='yadb' host_addr='10.177.7.62' module='oracle@yadb2 (TNS V1-V3)'
 pid='6738'>
 <txt>WARNING: inbound connection timed out (ORA-3136)
 </txt>
</msg>
<msg time='2018-06-13T10:09:01.474+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='yadb'
 host_addr='10.177.7.62'>
 <txt>

2.问题分析：

网络攻击，例如半开连接攻击
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.
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.
DB负载太高
The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.

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

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.

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 and INBOUND_CONNECT_TIMEOUT_listenername were given default values of 60 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.解决方法

set INBOUND_CONNECT_TIMEOUT_listenername=0 in listener.ora
set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.
stop and start both listener and database.
Now try to connect to DB and observe the behaviour

SQLNET.INBOUND_CONNECT_TIMEOUT：这个参数是指客户端连接数据库服务认证的时间长，即用户连接DB的时间的，单位是秒。
当 client 在INBOUND_CONNECT_TIMEOUT指定的时间内没有成功连接上服务器（db 负载大，网络延时等原因）。那么在服务器的sqlnet.log里就会记录下客户端的IP和ORA-12170:TNS:Connect timeout occurred的错误信息。同时客户端接收到ORA-12547:TNS:lost contact 或者ORA-12637:Packet receive failed的错误。

在oracle10.2.0.1之前，这个参数默认是0s。即不受连接时间的限制。在这个版本之后，为了防止Dos的攻击，将默认值该成60s.

4.具体操作

4.1 修改INBOUND_CONNECT_TIMEOUT_listenername

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

LSNRCTL> set inbound_connect_time 0

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully

LSNRCTL>
LSNRCTL> save_config

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Saved LISTENER configuration parameters.
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Old Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.bak
The command completed successfully
LSNRCTL>

$ vi listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = yadb)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

INBOUND_CONNECT_TIMEOUT_LISTENER = 0
LOGGING_LISTENER = ON

执行上面的命令，会自动修改listener.ora文件，加入: INBOUND_CONNECT_TIMEOUT_listenername=0

4.2 修改SQLNET.INBOUND_CONNECT__TIMEOUT

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

[oracle@yadb ~]$ cd $ORACLE_HOME
[oracle@yadb db_1]$ pwd
/u01/app/oracle/product/11.2.0/db_1
[oracle@yadb db_1]$ cd network/admin/
[oracle@yadb admin]$ ls
listener.bak listener.ora samples shrept.lst sqlnet.ora tnsnames.ora
[oracle@yadb admin]$ vi sqlnet.ora 

# sqlnet.ora Network Configuration File: /oracle/product/10.2.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
#SQLNET.EXPIRE_TIME=25
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.INBOUND_CONNECT_TIMEOUT = 0

重新加载监听文件

LSNRCTL> 
LSNRCTL> reload  
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
LSNRCTL>

5.官方文档

APPLIES TO:

Oracle Net Services - Version 10.1.0.3 to 11.2.0.4 [Release 10.1 to 11.2]
Information in this document applies to any platform.

This problem can occur on any platform.

SYMPTOMS

Error TNS-12535 when connecting to Oracle database via dispatchers (shared servers).

The dispatcher trace shows following error message -

NS Primary Error: TNS-12535: TNS:operation timed out 
NS Secondary Error: TNS-12606: TNS: Application timeout occurred 
kmduicxd: 0EEE01DC, kmduiflg: 1, circuit: 4D08C930 
(circuit) dispatcher process id = (85917028, 1) 
parent process id = (64, 1) 
serial # = 44 
connection context = 0EEE01DC 
user session = (00000000), flag = (100c0), queue = (9) 
current buffer = (0), status = (4, 0) 
Client Address = (ADDRESS=(PROTOCOL=tcp)(HOST=<IP address>)(PORT=<port>))

CAUSE

The SHARED_SERVER parameter is set to an inadequate value and should be raised. A connection via shared server will fail if there are no free shared servers available to honor the request.

The 12535 error in the dispatcher trace indicates that the connection was timed out in the dispatchers queue.

SOLUTION

Increase value for following database parameters -

max_shared_servers
shared_servers

These parameters are modifiable using ALTER SYSTEM. For example -

SQL> alter system set shared_server=30;   
SQL> alter system set max_shared_server=300;

其中有这么一段描述，在共享服务器连接模式当中，如果SHARED_SERVER参数设置过小或不足，如果没有空闲的shared server进程可用，那么通过共享服务器模式连接到数据库的连接请求就会失败， TNS-12535错误就会出现dispatcher trace文件中，表明dispatcher队列中的请求超时。