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

说明：

9i开始引入，指定了客户端连接服务器并且提供认证信息的超时时间，如果超过这个时间客户端没有提供正确的认证信息，服务器会自动中止该连接请求，防止网络攻击,同时会记录试图连接的IP地址和ORA-12170: TNS:Connect timeout occurred错误在10.2.0.1起，该参数默认设置为60秒但是，这个参数的引入也导致了一些相关的bug。比如：Bug 5594769 - REMOTE SESSION DROPPED WHEN LOCAL SESSION SHARED AND INBOUND_CONNECT_TIMEOUT SETBug 5249163 - CONNECTS REFUSED BY TNSLSNR EVERY 49 DAYS FOR INBOUND_CONNECT_TIMEOUT SECONDS该参数可以通过设置为0来禁用，然后reload或者重启监听

alert.ora报错信息如下：

vim /opt/oracle/admin/cim/bdump/alert_cim.log

Fri Dec 27 10:48:47 CST 2019
WARNING: inbound connection timed out (ORA-3136)
Fri Dec 27 10:48:50 CST 2019
WARNING: inbound connection timed out (ORA-3136)
Fri Dec 27 10:48:53 CST 2019
WARNING: inbound connection timed out (ORA-3136)

sqlnet.log报错信息如下：

vim /opt/oracle/product/10.2/db1/network/log/sqlnet.log
***********************************************************************
Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 10.2.0.5.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.5.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.5.0 - Production
  Time: 27-DEC-2019 10:48:47
  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=172.17.1.120)(PORT=61895))


***********************************************************************
Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 10.2.0.5.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.5.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.5.0 - Production
  Time: 27-DEC-2019 10:48:50
  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=172.17.1.168)(PORT=17654))


***********************************************************************
Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 10.2.0.5.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.5.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.5.0 - Production
  Time: 27-DEC-2019 10:48:53
  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=172.17.1.168)(PORT=17652))

解决办法：

查看数据库中listener.ora中的inbound_connect_timeout参数值，并禁用。

[oracle@msiccimdb admin]$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 27-DEC-2019 11:09:12

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> show inbound_connect_time
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=msiccimdb)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start               stop                status              
services            version             reload              
save_config         trace               spawn               
change_password     quit                exit                
set*                show*               

LSNRCTL> set inbound_connect_time 0
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=msiccimdb)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=msiccimdb)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File   /opt/oracle/product/10.2/db1/network/admin/listener.ora
Old Parameter File   /opt/oracle/product/10.2/db1/network/admin/listener.bak
The command completed successfully

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

vim /opt/oracle/product/10.2/db1/network/admin/sqlnet.ora

SQLNET.INBOUND_CONNECT_TIMEOUT = 0

重启监听

[oracle@msiccimdb admin]$ lsnrctl reload

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 27-DEC-2019 11:09:02

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=msiccimdb)(PORT=1521)))
The command completed successfully