1，系統環境：
作業系統：Solaris 64位
資料庫版本：11.2.0.4 64位

2，在檢查資料庫的alert日誌的時候，發現大量的12170和TNS-12535的錯誤
***********************************************************************
Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.4.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.4.0 - Production
Time: 05-AUG-2015 23:09:19
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
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=37982))
Wed Aug 05 23:09:20 2015
***********************************************************************

3，在不同的作業系統對應不同的ns secondary err code
The 'nt secondary err code' translates to underlying network transport timeouts for the following Operating Systems:

For the Solaris system: nt secondary err code: 145:
ETIMEDOUT 145 /* Connection timed out */

For the Linux operating system: nt secondary err code: 110
ETIMEDOUT 110 Connection timed out

For the HP-UX system: nt secondary err code: 238:
ETIMEDOUT 238 /* Connection timed out */

For AIX: nt secondary err code: 78:
ETIMEDOUT 78 /* Connection timed out */

4,觸發原因

一個客戶端連線整個步驟：
1、客戶端發起一個connection連線監聽
2、監聽啟動一個專屬程式(伺服器程式，也就是我們通常說的LOCA=NO程式)用於接收這個connection
3、在專屬程式啟動之後，監聽會將這個connection傳遞給這個專屬程式
4、專屬程式透過這個connection來跟客戶端握手
5、專屬程式跟客戶端資訊交換需要建立一個session
6、session開啟

當在以上的第3步到第4步時客戶端關閉，所以當專屬程式嘗試跟客戶端聯絡時發現連線已關閉時，就會報出我們看到的錯誤！！

5,解決方案

1）在伺服器端的sqlnet.ora中設定
DIAG_ADR_ENABLED = OFF
2）在伺服器端的listener.ora中設定
DIAG_ADR_ENABLED_<listenername> = OFF

舉例：如果監聽個名字是LISTENER，引數設定如下：
DIAG_ADR_ENABLED_LISTENER = OFF

如果要生效，需重啟監聽或reload監聽。


另外的解決方法參考：
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 <n> is a non-zero value set in minutes

進入ORACLE_HOME/network/admin目錄下，新增sqlnet.ora檔案，增加一行SQLNET.EXPIRE_TIME=XX

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

Fatal NI connect error 12170 TNS-12535/TNS-00505 TNS:operation timed out

相關文章