ORA-12154，ORA-12560解決過程

ORA-12154，ORA-12560解決過程

應用伺服器：Windows Server 2008 R2 Enterprise
故障現象：項目側同事反映應用伺服器上的程式串連資料庫報錯：ORA-12560: TNS: 協議適配器錯誤

• 1.故障重現
• 2.定位問題
• 3.解決問題

1.故障重現

在應用伺服器上使用sqlplus和PL/SQL工具登入
串連資料庫伺服器均報錯：

ORA-12154: TNS: 無法解析指定的串連標識符

2.定位問題2.1 ping測試網路

ping 資料庫IP地址 網路通暢

C:\Users\Administrator>ping 192.168.1.100正在 Ping 192.168.1.100 具有 32 位元組的資料:來自 192.168.1.100 的回複: 位元組=32 時間<1ms TTL=64來自 192.168.1.100 的回複: 位元組=32 時間<1ms TTL=64來自 192.168.1.100 的回複: 位元組=32 時間<1ms TTL=64來自 192.168.1.100 的回複: 位元組=32 時間<1ms TTL=64192.168.1.100 的 Ping 統計資訊:    資料包: 已發送 = 4，已接收 = 4，丟失 = 0 (0% 丟失)，往返行程的估計時間(以毫秒為單位):    最短 = 0ms，最長 = 0ms，平均 = 0ms

2.2 tnsping測試連接埠

tnsping 資料庫IP地址，報錯：TNS-12560：TNS：協議適配器錯誤

C:\Users\Administrator>tnsping 192.168.1.100TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 27-1月 -2016 09:55:56Copyright (c) 1997, 2010, Oracle.  All rights reserved.已使用的參數檔案:D:\app\administrator\product\11.2.0\client_1\network\admin\sqlnet.ora已使用 EZCONNECT 適配器來解析別名嘗試串連 (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))TNS-12560: TNS: 協議適配器錯誤

2.3 登入資料庫主機

SecureCRT工具登入資料庫主機伺服器報錯：

An operation on a socket could not be performed because the system lackedsufficient buffer space or because a queue was full.

2.4 嘗試其他機器串連

發現其他機器是可以登入到資料庫伺服器的，進一步查看資料庫相關資訊，也發現一切正常：

• 資料庫服務正常
• 資料庫監聽正常
• 防火牆/SELINUX均未啟用
• 其他與應用伺服器同一網段的主機測試到資料庫的串連也正常

看起來非常詭異，不過到現在也確定了不是資料庫伺服器那邊的原因，不是網路的原因。

3.解決問題

 

3.1 兩個ORA錯誤的官方解釋

ORA-12560

$ oerr ora 1256012560, 00000, "TNS:protocol adapter error"// *Cause: A generic protocol adapter error occurred.// *Action: Check addresses used for proper protocol specification. Before// reporting this error, look at the error stack and check for lower level// transport errors. For further details, turn on tracing and reexecute the// operation. Turn off tracing when the operation is complete.

ORA-12154

$ oerr ora 1215412154, 00000, "TNS:could not resolve the connect identifier specified"// *Cause:  A connection to a database or other service was requested using// a connect identifier, and the connect identifier specified could not// be resolved into a connect descriptor using one of the naming methods// configured. For example, if the type of connect identifier used was a// net service name then the net service name could not be found in a // naming method repository, or the repository could not be// located or reached.// *Action://   - If you are using local naming (TNSNAMES.ORA file)://      - Make sure that "TNSNAMES" is listed as one of the values of the//        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile//        (SQLNET.ORA)//      - Verify that a TNSNAMES.ORA file exists and is in the proper//        directory and is accessible.//      - Check that the net service name used as the connect identifier//        exists in the TNSNAMES.ORA file.//      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA//        file.  Look for unmatched parentheses or stray characters. Errors//        in a TNSNAMES.ORA file may make it unusable.//   - If you are using directory naming://      - Verify that "LDAP" is listed as one of the values of the//        NAMES.DIRETORY_PATH parameter in the Oracle Net profile//        (SQLNET.ORA).//      - Verify that the LDAP directory server is up and that it is//        accessible.//      - Verify that the net service name or database name used as the//        connect identifier is configured in the directory.//      - Verify that the default context being used is correct by//        specifying a fully qualified net service name or a full LDAP DN//        as the connect identifier//   - If you are using easy connect naming://      - Verify that "EZCONNECT" is listed as one of the values of the//        NAMES.DIRETORY_PATH parameter in the Oracle Net profile//        (SQLNET.ORA).//      - Make sure the host, port and service name specified//        are correct.//      - Try enclosing the connect identifier in quote marks.// //   See the Oracle Net Services Administrators Guide or the Oracle//   operating system specific guide for more information on naming.

3.2 windows應用伺服器資訊

使用systeminfo命令查看這台windows應用伺服器資訊的部分內容：

C:\Users\Administrator>systeminfo主機名稱:           XXXXWEB1OS 名稱:          Microsoft Windows Server 2008 R2 EnterpriseOS 版本:          6.1.7600 暫缺 Build 7600OS 製造商:        Microsoft CorporationOS 配置:          主網域控制站OS 構件類型:      Multiprocessor Free註冊的所有人:註冊的組織:產品識別碼:          xxxxx-OEM-xxxxxxx-xxxxx初始安裝日期:     2014/1/26, 21:31:46系統啟動時間:     2014/9/5, 13:42:21系統製造商:       HP系統型號:         ProLiant BL460c Gen8系統類別型:         x64-based PC處理器:           安裝了 2 個處理器。                  [01]: Intel64 Family 6 Model 45 Stepping 7 GenuineIntel ~2000Mhz                  [02]: Intel64 Family 6 Model 45 Stepping 7 GenuineIntel ~2000MhzBIOS 版本:        HP I31, 2013/12/20

可以看到，伺服器據上一次啟動，已經運行了一年多。
而從上面CRT串連報出的錯誤資訊初步確定是此台Windows伺服器的socket資源耗盡。
對Windows不是很瞭解，簡單粗暴的重啟應用伺服器後解決了此問題。