原來是它(Oracle.ManagedDataAccess.Client)——再說ORA-12516故障解決

Hoegh發表於2015-06-18
Oracleclient
        最近一直和ORA-12516報錯作鬥爭。接著我之前分享的一篇博文說起http://blog.itpub.net/30162081/viewspace-1623524/，當時透過kill程式的方法臨時解決了問題，但是根本問題沒有找到——為什麼裝置會產生大量連線？說到底，這個問題當時並沒有解決，並且繼續“折磨”了我將近一個月。這段時間，不斷地有人找我——資料庫連不上了！！！問題到底出在哪兒了呢。
       下面和大家分享一下解決過程，希望對大家有所借鑑。

開發環境

作業系統：Windows Server 2008 R2  Standard
資料庫：Oracle 10g(10.2.0.4) 64bit

頻繁報錯ORA-12516，初步分析

最近ORA-12516錯誤頻出，我也是不勝其煩。但是每次都找不到根本原因，只能停監聽、殺程式或者直接關閉客戶端程式。
複述一下故障現象：連線資料庫報錯，提示“ORA-12516”錯誤——“ORA-12516: TNS: 監聽程式無法找到匹配協議棧的可用控制程式碼”。

一直很穩定的系統架構，為什麼突然不行了呢？
資料庫processes引數使用的預設值150，相應的sessions為170,；
我們採用C/S架構，其中Client數量並不多，也就20臺左右，所以正常情況下sessions數量為40左右，其中包括16個資料庫內部連線。
可現在，sessions數量動輒就到了130~140，直接導致ORA-12516報錯。

下面貼一個當時的查詢結果：

點選(此處)摺疊或開啟

  1. SQL> set pagesize 150
  2. SQL> set linesize 200
  3. SQL> col username for a10
  4. SQL> col terminal for a20
  5. SQL> col program for a20
  6. SQL> select username,program,terminal,count(*) from v$session group by rollup(username,program,terminal);
  7.  
  8. USERNAME PROGRAM TERMINAL COUNT(*)
  9. ---------- -------------------- -------------------- ----------
  10. ......此處省略內容
  11.            ORACLE.EXE (q000) 1
  12.            ORACLE.EXE (q001) DBSERVER 1
  13.            ORACLE.EXE (q001) 1
  14.                                                              16
  15. SYS sqlplus.exe DBSERVER 1
  16. SYS sqlplus.exe 1
  17. SYS 1
  18. HOEGH HOEGH.exe Client7 1
  19. HOEGH HOEGH.exe CLIENT2 95
  20. HOEGH HOEGH.exe Client3 1
  21. HOEGH HOEGH.exe Client4 2
  22. HOEGH HOEGH.exe CLIENT5 28
  23. HOEGH HOEGH.exe Client6 1
  24. HOEGH HOEGH.exe 128
  25. HOEGH 128
  26.                                                             145
可以看出，CLIENT2 和CLIENT5 兩個終端的連線數分別為95和28，肯定有問題。

查詢session，嘗試定位故障裝置

透過select username,program,terminal from v$sessions;查詢資料庫連線，看到某一臺或者某兩臺的session數達到30，有的甚至超過70。把相應裝置的應用程式（應用程式連線資料庫）重啟後，問題有時能迅速緩解，有時卻沒有效果。
起初是懷疑Client裝置的硬體或者作業系統，因為這些裝置中的作業系統比較雜（實驗室為了測試方便），包括32位的Windows XP、32位及64位的Win7，還有64位的Win8。

後來經過長時間的觀察，發現故障裝置並不固定，也就是說，每一臺裝置都有可能成為“故障裝置”。

有點迷茫了。

C#清空連線池

經過長時間觀察及初步分析，得出的結論是這個問題可能與具體裝置沒有直接關係，而是和目前的資料庫斷連判斷程式碼有關。

我們的應用程式為了顯示資料庫連線狀態，定時去連線資料庫，起到“心跳”的作用。我們懷疑是不是每臺裝置都這麼去頻繁連線資料庫，而這些連線卻沒有釋放，時間長了session數量就上去了，於是在程式碼中新增了清空連線池的語句。

但是，沒有效果。

其實，現在是懷疑一切了，因為以往專案我們採取的是同樣的資料庫“心跳”機制，一直沒有出過問題。

新增靜態監聽

後來一直在網上查這個ORA-12516，不少朋友提到了透過監聽去發現、解決問題。我看了一下資料庫伺服器的監聽日誌Listener.log，好傢伙，都80多M了，記事本都打不開了。最後用UE開啟監聽日誌，發現裡面大片的TNS-12514報錯。

點選(此處)摺疊或開啟

  1. 10-6月 -2015 16:26:53 * service_update * hoegh * 0
  2. 10-6月 -2015 16:26:56 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=hoegh)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.191)(PORT=50641)) * establish * hoegh * 12514
  3. TNS-12514: TNS: 監聽程式當前無法識別連線描述符中請求的服務
  4. 10-6月 -2015 16:26:58 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=hoegh)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.191)(PORT=50644)) * establish * hoegh * 12514
  5. TNS-12514: TNS: 監聽程式當前無法識別連線描述符中請求的服務
  6. 10-6月 -2015 16:26:58 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=hoegh)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.191)(PORT=50645)) * establish * hoegh * 12514
  7. TNS-12514: TNS: 監聽程式當前無法識別連線描述符中請求的服務
  8. 10-6月 -2015 16:26:58 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=hoegh)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.191)(PORT=50647)) * establish * hoegh * 12514
  9. TNS-12514: TNS: 監聽程式當前無法識別連線描述符中請求的服務
  10. 10-6月 -2015 16:26:58 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=hoegh)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.191)(PORT=50648)) * establish * hoegh * 12514
  11. TNS-12514: TNS: 監聽程式當前無法識別連線描述符中請求的服務
  12. 10-6月 -2015 16:27:08 * service_update * hoegh * 0
  13. 10-6月 -2015 16:27:09 * (CONNECT_DATA=(SERVICE_NAME=hoegh)(CID=(PROGRAM=D:\\HOEGHATS\\HOEGH.exe)(HOST=)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=3.16.11.50)(PORT=4528)) * establish * hoegh * 0
根據大家的建議，我嘗試在監聽配置檔案Listener.ora中新增了靜態監聽,其中黃色部分為新新增內容。
但是，效果不大。

點選(此處)摺疊或開啟

  1. SID_LIST_LISTENER =
  2.   (SID_LIST =
  3.     (SID_DESC =
  4.       (SID_NAME = PLSExtProc)
  5.       (ORACLE_HOME = c:\\oracle\\product\\10.2.0\\db_1)
  6.       (PROGRAM = extproc)
  7.     )
  8.     (SID_DESC =
  9.       (SID_NAME = hoegh)
  10.       (ORACLE_HOME = c:\\oracle\\product\\10.2.0\\db_1)
  11.     )
  12.   )

  14. LISTENER =
  15.   (DESCRIPTION_LIST =
  16.     (DESCRIPTION =
  17.       (ADDRESS = (PROTOCOL = TCP)(HOST = DBSERVER)(PORT = 1521))
  18.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
  19.     )
  20.   )

增大processes初始化引數

黔驢技窮，我又使了這招，可是絲毫不起作用，不同的是以往連線總數是一百三四十，現在增加到了二百七八十，問題依舊。
結合上面的靜態監聽，我再次檢視listener.log，發現大量TNS-12518和TNS-12560報錯，以及少量的TNS-12514報錯：

點選(此處)摺疊或開啟

  1. 15-6月 -2015 15:39:13 * 12502
  2. TNS-12502: TNS: 監聽程式沒有從客戶機收到 CONNECT_DATA
  3. 15-6月 -2015 15:39:13 * (CONNECT_DATA=(SERVICE_NAME=hoegh)(CID=(PROGRAM=D:\\HOEGHATS\\HOEGH.exe)(HOST=CLIENT5)(USER=work))) * (ADDRESS=(PROTOCOL=tcp)(HOST=3.16.210.94)(PORT=55359)) * establish * hoegh * 12518
  4. TNS-12518: TNS: 監聽程式無法分發客戶機連線
  5.  TNS-12560: TNS: 協議介面卡錯誤
  6. 15-6月 -2015 15:39:13 * (CONNECT_DATA=(SERVICE_NAME=hoegh)(CID=(PROGRAM=D:\\HOEGHATS\\HOEGH.exe)(HOST=)(USER=JDWL))) * (ADDRESS=(PROTOCOL=tcp)(HOST=3.20.16.50)(PORT=2200)) * establish * hoegh * 12518
  7. TNS-12518: TNS: 監聽程式無法分發客戶機連線
  8.  TNS-12560: TNS: 協議介面卡錯誤
  9. 15-6月 -2015 15:39:13 * (CONNECT_DATA=(SERVICE_NAME=hoegh)(CID=(PROGRAM=D:\\HOEGHATS\\HOEGH.exe)(HOST=DISPATCHER1)(USER=work))) * (ADDRESS=(PROTOCOL=tcp)(HOST=3.16.210.92)(PORT=50761)) * establish * hoegh * 12518
  10. TNS-12518: TNS: 監聽程式無法分發客戶機連線
  11.  TNS-12560: TNS: 協議介面卡錯誤
  12. 15-6月 -2015 15:39:13 * 12502
  13. TNS-12502: TNS: 監聽程式沒有從客戶機收到 CONNECT_DATA
  14. 15-6月 -2015 15:39:13 * (CONNECT_DATA=(SERVICE_NAME=hoegh)(CID=(PROGRAM=D:\\HOEGHATS\\HOEGH.exe)(HOST=WINOW-BWMCG6U3V)(USER=JDWL))) * (ADDRESS=(PROTOCOL=tcp)(HOST=3.20.1.52)(PORT=3487)) * establish * hoegh * 12518
  15. TNS-12518: TNS: 監聽程式無法分發客戶機連線
  16.  TNS-12560: TNS: 協議介面卡錯誤
  17. 15-6月 -2015 15:39:13 * (CONNECT_DATA=(SERVICE_NAME=hoegh)(CID=(PROGRAM=D:\\HOEGHATS\\HOEGH.exe)(HOST=ATSSERVER1)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=3.16.210.74)(PORT=3404)) * establish * hoegh * 12518
  18. TNS-12518: TNS: 監聽程式無法分發客戶機連線
  19.  TNS-12560: TNS: 協議介面卡錯誤
  20. 15-6月 -2015 15:39:13 * (CONNECT_DATA=(SERVICE_NAME=hoegh)(CID=(PROGRAM=D:\\HOEGHATS\\HOEGH.exe)(HOST=CLIENT2)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=3.16.210.66)(PORT=2229)) * establish * hoegh * 12518
  21. TNS-12518: TNS: 監聽程式無法分發客戶機連線
  22.  TNS-12560: TNS: 協議介面卡錯誤

重建監聽器

重啟裝置是一個“大招”，以前碰到“疑難雜症”重啟往往能夠收到奇效。最後，我甚至都想著重建資料庫了，不過考慮著動靜有點大，可能會影響其他人工作，只好作罷；那我重建一下監聽試一下吧。

於是，我透過netca刪除原有監聽，重建監聽程式listener，都採用預設配置。

可是，問題依舊。

更換微軟碟機動System.Data.OracleClient

真是沒招了，最後和部門的架構師一起討論這個問題，會不會和資料庫驅動有關係呢。
我們的應用程式是用C#開發的，連線資料庫需要資料驅動支援，以前我們採用的是微軟的資料庫驅動System.Data.OracleClient，最近這個專案換成了Oracle官方驅動Oracle.ManagedDataAccess.Client。至於為什麼要換，最主要是考慮Oracle官方驅動部署方便，無需安裝Oracle資料庫客戶端。

說幹就幹，把實驗室的所有客戶端裝置的驅動都更換為System.Data.OracleClient，我再去檢視session連線狀況，看到輸出結果哭了都快，session數真的好少啊！！！

點選(此處)摺疊或開啟

  1. SQL> set pagesize 150
  2. SQL> set linesize 200
  3. SQL> col username for a10
  4. SQL> col terminal for a20
  5. SQL> col program for a20
  6. SQL> select username,program,terminal,count(*) from v$session group by rollup(username,program,terminal);
  7.  
  8. USERNAME PROGRAM TERMINAL COUNT(*)
  9. ---------- -------------------- -------------------- ----------
  10. ......此處省略內容
  11.            ORACLE.EXE (q000) 1
  12.            ORACLE.EXE (q001) DBSERVER 1
  13.            ORACLE.EXE (q001) 1
  14.                                                              16
  15. SYS sqlplus.exe DBSERVER 1
  16. SYS sqlplus.exe 1
  17. SYS 1
  18. HOEGH HOEGH.exe Client7 1
  19. HOEGH HOEGH.exe CLIENT2 1
  20. HOEGH HOEGH.exe Client3 1
  21. HOEGH HOEGH.exe Client4 2
  22. HOEGH HOEGH.exe CLIENT5 1
  23. HOEGH HOEGH.exe Client6 1
  24. HOEGH HOEGH.exe 7
  25. HOEGH 7
  26.                                                             24


說到最後的話

這個問題折騰一個多月了，現在看似得到徹底解決，但是心裡還是不踏實，畢竟Oracle官方驅動Oracle.ManagedDataAccess.Client還是有不少優點的。
  • Oracle驅動部署方便，資料型別相容性好，如Timestamp型別支援儲存ms級資料（大家可以參考以前的一篇博文http://blog.itpub.net/30162081/viewspace-1561171/）；
  • 微軟碟機動更加穩定，但是不再提供更新，無法支援一些新特性。

那麼，問題來了，會不會是我們的使用方法有問題呢，還請用過Oracle官方驅動Oracle.ManagedDataAccess.Client的朋友不吝賜教。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30162081/viewspace-1703627/，如需轉載，請註明出處，否則將追究法律責任。

相關文章