这几天客户反映用网站每天都有几分钟连不上，报错信息如下:
刚开始判断是由于IIS或者网络原因设置导致的，调整了一下IIS的参数试了一下网络，网络正常。第二天还是一样的报错。最后将原因归结于oracle数据库的问题。随即查看alert.log。
在alert.log日志中出现大量的报错信息:
atal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
Time: 5-6月 -2020 16:50:52
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=10.170.1.100)(PORT=1521))
WARNING: inbound connection timed out (ORA-3136)
结合Oracle的MOS文档发现是由于sqlnet的inbound_connect_timieout_listener_name限制导致的报错
sqlnet的inbound_connect_timieout_listener_name限制
单位是秒，默认值是60
Use the INBOUND_CONNECT_TIMEOUT_listener_name parameter to specify the time, in seconds, for the client to complete its connect request to the listener after the network connection had been established.
If the listener does not receive the client request in the time specified, then it terminates the connection. In addition, the listener logs the IP address of the client and an ORA-12525:TNS: listener has not received client's request in time allowed error message to the listener.log file.
To protect both the listener and the database server, Oracle Corporation recommends setting this parameter in combination with theSQLNET.INBOUND_CONNECT_TIMEOUT parameter in the sqlnet.ora file. When specifying values for these parameters, consider the following recommendations:
-
Set both parameters to an initial low value.
-
Set the value of the INBOUND_CONNECT_TIMEOUT_listener_name parameter to a lower value than the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.
For example, you can set INBOUND_CONNECT_TIMEOUT_listener_name to 2 seconds and INBOUND_CONNECT_TIMEOUT parameter to 3 seconds. If clients are unable to complete connections within the specified time due to system or network delays that are normal for the particular environment, then increment the time as needed.
如果客户端在指定的时间内没有连接上数据库，会在listener.log日志中出现 ORA-12525,同时在alert日志中会报错ora-03136
解决办法:
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
最终解决办法:
cd /oracle/app/product/11.2.0/db_1/network/admin/
vim sqlnet.ora
把INBOUND_CONNECT_TIMEOUT_LISTENER=0添加至最后一行，wq!保存
lsnrctl
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-6▒▒ -2020 11:20:01
Copyright (c) 1991, 2013, Oracle. All rights reserved.
▒▒ӭ▒▒▒LSNRCTL, ▒▒▒▒▒"help"▒Ի▒▒▒▒Ϣ▒▒
LSNRCTL> show INBOUND_CONNECT_TIMEOUT
▒▒▒▒▒▒ӵ▒ (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oadb)(PORT=1521)))
LISTENER ▒▒▒▒ "inbound_connect_timeout" ▒▒Ϊ 60
▒▒▒▒ִ▒гɹ▒
LSNRCTL> reload
▒▒▒▒▒▒ӵ▒ (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oadb)(PORT=1521)))
▒▒▒▒ִ▒гɹ▒
LSNRCTL> show INBOUND_CONNECT_TIMEOUT
▒▒▒▒▒▒ӵ▒ (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oadb)(PORT=1521)))
LISTENER ▒▒▒▒ "inbound_connect_timeout" ▒▒Ϊ 0
▒▒▒▒ִ▒гɹ▒
LSNRCTL>
LSNRCTL>
LSNRCTL> exit
问题解决