11g告警日誌中大量ORA-12170錯誤

在11g資料庫的告警日誌中，經常可以看到ORA-12170錯誤資訊。

如果這個錯誤是偶然出現，問題可能是網路問題所致，但是如果這個錯誤短時間內頻繁出現，那麼問題就不一定是網路不暢那麼簡單了。

客戶的資料庫出現了應用無法連線的錯誤，而應用程式日誌出現下面的錯誤資訊：

ORA-12519, TNS:no appropriate service handler found

在告警日誌中比較頻繁的出現了下面的錯誤：

Fatal NI connect error 12170.
Sat Sep 03 12:43:43 2011

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: 03-SEP-2011 12:43:43
Time: 03-SEP-2011 12:43:43
Tns error struct:
Tns error struct:
Tracing not turned on.
Tracing not turned on.
ns main err code: 12535
ns main err code: 12535
ns main err code: 12535
Time: 03-SEP-2011 12:43:43
Tns error struct:
Tns error struct:

ns main err code: 12535
Tracing not turned on.
ns main err code: 12535

TNS-12535: TNS:operation timed out
TNS-12535: TNS:operation timed out

Tns error struct:
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
ns secondary err code: 12606
TNS-12535: TNS:operation timed out
TNS-12535: TNS:operation timed out
nt main err code: 0
nt main err code: 0
ns secondary err code: 12606
ns secondary err code: 12606
ns main err code: 12535
ns secondary err code: 12606
nt secondary err code: 0
nt secondary err code: 0
nt main err code: 0
nt main err code: 0
nt main err code: 0
nt OS err code: 0
nt OS err code: 0

nt secondary err code: 0
nt secondary err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.90.102)(PORT=33196))
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.90.102)(PORT=33191))
nt OS err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.90.102)(PORT=33201))
TNS-12535: TNS:operation timed out
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.90.102)(PORT=33195))
nt OS err code: 0
ns secondary err code: 12606
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.90.102)(PORT=33174))
nt main err code: 0

WARNING: inbound connection timed out (ORA-3136)
Sat Sep 03 12:48:28 2011
WARNING: inbound connection timed out (ORA-3136)
Sat Sep 03 12:48:28 2011
WARNING: inbound connection timed out (ORA-3136)
Sat Sep 03 12:48:28 2011
WARNING: inbound connection timed out (ORA-3136)
Sat Sep 03 12:48:30 2011
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 33554432 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
select total_size,awr_flush_emergency_count from v$ash_info;
Sat Sep 03 12:48:32 2011
Sweep [inc][48817]: completed
Sweep [inc2][48817]: completed

檢查了監聽日誌，在12:43的時刻出現了大量的ORA-12518錯誤，這個錯誤資訊是：TNS:listener could not hand off client connection，隨後不到1秒的時間，大量的連線錯誤導致了ORA-12519 TNS:no appropriate service handler found錯誤，這說明資料庫的對連線的響應能力已經跟不上了。

而導致這兩個問題出現的原因是大量的會話在短時間內連線到資料庫，根據監聽日誌，僅12:42分這一分鐘，連線資料庫的會話就建立了超過2000個連線。而正常情況下，這個資料庫一天的總連線數量也不過13000個左右。

Oracle在metalink文件ID 12535.1中，描述了11g告警日誌中出現ORA-12170以及ORA-12535錯誤的原因，由於大量的客戶端連線到伺服器，導致資料庫無法在短時間內處理連線風暴，從而引發了連線超時的錯誤。

最終發現，可能的中介軟體的連線重試策略配置存在一定的問題，導致當通訊或其他問題引發連線中斷後，會短時間內產生大量的連線重試，並不斷的增加連線數量，最終引發了籤的ORA-12519錯誤。