sqlnet.log error - Operation timed out

 

    最近在$ORACLE_HOME/network/admin/log 下的sqlnet.log檔案裡，不斷發現異常error：

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

  VERSION INFORMATION:
        TNS for Solaris: Version 10.2.0.1.0 – Production
        Oracle Bequeath NT Protocol Adapter for Solaris: Version 10.2.0.1.0 – Production
        TCP/IP NT Protocol Adapter for Solaris: Version 10.2.0.1.0 – Production
  Time: 1-MAR-2009 18:42:58
  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: 145
    (Look up error number 145 in /usr/include/sys/errno.h )
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.221.70.83)(PORT=4032))
***********************************************************************

透過client address可以查詢到時應用伺服器的地址，從listener.log裡可以查到此session的連結時間，大部分都是4-6個小時左右，斷開。客戶端沒有人報告異常，也沒有客戶反應連結不到資料庫，那為什麼會出現這個timed out的異常告警呢？如果沒有問題的話，不應該會平白無故的告警的？

在網上查詢到相關資料，懷疑是防火牆設定問題：

This is indicative of a client connection being dropped. This can be the result of firewall dropping connections per the firewall policy of idle sessions.  The workaround for this can be done as follows:

“For Oracle databases, SQLNET.EXPIRE_TIME can be used.  Although SQLNET.EXPIRE_TIME was intended as a dead connection detection (DCD) method, a side effect of sending probe packets between the database server and client/application server is that the connection remains active.  Set the SQLNET.EXPIRE_TIME to less than the firewall timeout and the problem is solved.”

參考連結： 1.

                      2.