转载

记录一则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=64  192.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.100  TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 27-1月 - 2016 09:55:56  Copyright (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 lacked sufficient buffer space or because a queue was full.

2.4 尝试其他机器连接

发现其他机器是可以登录到数据库服务器的，进一步查看数据库相关信息，也发现一切正常：

数据库服务正常
数据库监听正常
防火墙/SELINUX均未启用
其他与应用服务器同一网段的主机测试到数据库的连接也正常

看起来非常诡异，不过到现在也确定了不是数据库服务器那边的原因，不是网络的原因。

3.解决问题

3.1 两个ORA错误的官方解释

ORA-12560

$ oerr ora 12560 12560, 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 12154 12154, 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 主机名:           XXXXWEB1 OS 名称:          Microsoft Windows Server 2008 R2 Enterprise OS 版本:          6.1.7600 暂缺 Build 7600 OS 制造商:        Microsoft Corporation OS 配置:          主域控制器 OS 构件类型:      Multiprocessor Free 注册的所有人: 注册的组织: 产品 ID:          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 ~2000 Mhz                   [02]: Intel64 Family 6 Model 45 Stepping 7 GenuineIntel ~2000 Mhz BIOS 版本:        HP I31, 2013/12/20

可以看到，服务器据上一次启动，已经运行了一年多。

而从上面CRT连接报出的错误信息初步确定是此台windows服务器的socket资源耗尽。

对windows不是很了解，简单粗暴的重启应用服务器后解决了此问题。

原文 http://www.cnblogs.com/jyzhao/p/5162787.html

正文到此结束