关于Oracle连接超时的问题
测试环境ORACLE 11.2.0.
如果连接池设置单个连接闲置时间⼤于数据库连接超时时间，则连接池中的连接发出数据请求时会出现Connect timeout occurred错误，
这是由于连接超时所产⽣的问题，在10.2.0.1.0版本中sqlnet.inbound_connect_timeout参数默认为60秒:
/*
*时间：2009-03-010
*环境：AIX5.3 10g
*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: TNSperation timed out
ns condary err code: 12606
nt main err code: 0
nt condary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.171.4.201)(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 databa rver 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 the details.
可能的原因：
1.⽹络攻击，例如半开连接攻击
Server gets a connection request from a malicious client which is not suppod to connect to the databa ,
in which ca 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 rver receives a valid client connection request but the client takes a long time to authenticate more than the default 60 conds.
3.DB负载太⾼
The DB rver is heavily loaded due to which it cannot finish the client logon within the timeout specified.
WARNING: inbound connection timed out (ORA-3136)
这个错误跟监听的⼀个参数有关：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来禁⽤，在服务媏
设置a⽂件：SQLNET.INBOUND_CONNECT_TIMEOUT＝0
设置a⽂件: INBOUND_CONNECT_TIMEOUT_listenername＝0
然后reload或者重启监听
这是由于连接超时所产⽣的问题，在10.2.0.1.0版本中sqlnet.inbound_connect_timeout参数默认为60秒，即如果连接时间超过60秒则提⽰超时，⽽在其他10G版本中这两个参数默认为0，即⽆限制。
如何操作：
⼀、查看数据库中a中的inbound_connect_timeout参数值
1、进⼊lsnrctl，
LHXXDBS01raoms> 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" t to 0
The command completed successfully
如果inbound_connect_timeout参数值不为0，则可以修改为0
修改：
LSNRCTL> t inbound_connect_time 0
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LHXXDBS)(PORT=1568)))
LISTENER parameter "inbound_connect_timeout" t to 0
The command completed successfully
⼆、修改/oracle/oms/102_64/network/a
a
SQLNET.INBOUND_CONNECT_TIMEOUT = 0
保持退出 wq!
三、重新载⼊listener
LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LHXXDBS)(PORT=1568)))
The command completed successfully
第⼆天观察没有出现WARNING: inbound connection timed out (ORA-3136)连接超时的现象了。
调度时程的忙百分⽐（当这个⽐例超过50时，需要增加DISPATCHERS 的值）：
Select Name "Dispatcher",
Network,
(Round(Sum(Busy) / (Sum(Busy) + Sum(Idle)), 4)) * 100 "Busy_Rate"
From V$dispatcher
Group By Name, Network;
