Fatal NI connect error 12170 TNS-12535 TNS-00505

                  Fatal NI connect error 12170 TNS-12535 TNS-00505

今天一位朋友遇到這個錯誤，每2個小時長時間執行的儲存過程就斷開，一開始懷疑PROFILE或者RESOURCE PLAN限制。
但是大家都明白，一般很少用這些，特別是資源計劃，拿到報錯後如下：
Fatal NI connect error 12170.


  VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.1.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.1.0 - Production
  Time: 27-1月 -2015 15:43:45
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS: 操作超時
    ns secondary err code: 12560
    nt main err code: 505
    TNS-00505: 操作超時
    nt secondary err code: 60
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.47.98.172)(PORT=60611))
  這裡的
  nt secondary err code: 60 可能隨著系統的不同而不同
Linux x86 or Linux x86-64: "nt secondary err code: 110"
HP-UX : "nt secondary err code: 238"
AIX: "nt secondary err code: 78"
Solaris: "nt secondary err code: 145"


對於這樣的錯誤在11G以前是不計入alert日誌的，但是11G後 Automatic Diagnostic Repository (ADR)記錄，其實可以透過如下方法進行遮蔽掉：
To revert to Oracle Net Server tracing/logging, set following parameter in the server's sqlnet.ora :
DIAG_ADR_ENABLED = OFF
Also, to back out the ADR diag for the Listener component, set following parameter in the server's listener.ora:
DIAG_ADR_ENABLED_ = OFF
   - Where the would be replaced with the actual name of the configured listener(s) in the listener.ora configuration file.  For example, if the listener name is 'LISTENER', the parameter would read:
DIAG_ADR_ENABLED_LISTENER = OFF


上述報錯原因如下：
This issue can arise during a long running query or when using JDBC Thin connection pooling. 
If there is no data 'on the wire' for lengthy，This would indicate an issue with a firewall 
here a maximum idle time setting is in place.
The alert.log message indicates that a connection was terminated AFTER it was established to the instance.  
In this case, it was terminated 2 hours and 3 minutes after the listener handed the connection to the database. 
 也就是他出現在長期的操作或者使用連線池的時候，並且設定了防火牆，並且2小時會進行中斷一次。
解決辦法如下：
The non-Oracle solution would be to remove or increase the firewall setting for maximum idle time.  
In cases where this is not feasible, Oracle offers the following suggestion:
The following parameter, set at the **RDBMS_HOME/network/admin/sqlnet.ora, can resolve this kind of problem.  
DCD or SQLNET.EXPIRE_TIME can mimic data transmission between the server and the client during long periods of idle time. 
SQLNET.EXPIRE_TIME=n  Where is a non-zero value set in minutes.  
在ORACLE級別可以設定SQLNET.EXPIRE_TIME引數，如果設定為2，可以2分鐘發起一次探測，傳送最小的探測包來保證連線之間有流量，
而不至於被防火牆中斷。當然也可以設定防火牆的最大IDLE TIME。

參考：
Fatal NI Connect Error 12170, 'TNS-12535: TNS:operation timed out' Reported in 11g Alert Log (文件 ID 1286376.1)
Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out (文件 ID 1628949.1)