ORA-12170: TNS:Connect timeout occurred

今天数据库出现短暂的所有会话断开1至3分钟。

查看sqlnet.log （因为是生产环境所以我将IP都屏蔽下 ）

Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=1.0.0.0)(PORT=33534))
    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= 1.0.0.0)(PORT=54370))
    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= 1.0.0.0)(PORT=50517))
    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= 1.0.0.0)(PORT=47319))
    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= 1.0.0.0)(PORT=54752))
    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= 1.0.0.0)(PORT=47366))
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0



Fatal NI connect error 12170.


  VERSION INFORMATION:
        TNS for HPUX: Version 10.2.0.4.0 - Production
        Oracle Bequeath NT Protocol Adapter for HPUX: Version 10.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for HPUX: Version 10.2.0.4.0 - Production
  Time: 31-JUL-2014 12:26:36
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
    TNS-00505: Operation timed out
    nt secondary err code: 238
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=1.0.0.0)(PORT=60607))

在metalink 一直纠结12606错误，也没找到什么有用的线索。最后在群里问了下伤哥，他给了我一篇他的文章http://blog.itpub.net/29477587/viewspace-1078684/

文章介绍这个错误可能是由于网络或系统延迟,或者它可能表明,在服务器上存在恶意客户机试图攻击服务器引起的。
可以尝试修改 SQLNET.INBOUND_CONNECT_TIMEOUT参数

sqlnet.ora参数SQLNET.INBOUND_CONNECT_TIMEOUT
表示等待用户认证超时的时间，单位是秒，缺省值是60秒
如果用户认证超时了，服务器日志alert.log显示出错信息
WARNING: inbound connection timed out (ORA-3136)

查看bdump 中的 log

Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)
Thu Jul 31 11:06:13 2014
WARNING: inbound connection timed out (ORA-3136)

OK，果然是超时的原因。

解决方法：
修改 sqlnet.ora文件中的参数SQLNET.INBOUND_CONNECT_TIMEOUT

编辑 $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29532781/viewspace-1243041/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29532781/viewspace-1243041/