windows ORA-12560: TNS: 协议适配器错误
1.first it report ORA-12560: TNS: 协议适配器错误
手工设定环境变量如下:
set ORACLE_HOME=d:\app\OAadmin\product\11.2.0\dbhome_1
set ORACLE_SID=boe3prod
set TNS_ADMIN=d:\app\OAadmin\product\11.2.0\dbhome_1\network\admin
set PATH=d:\app\OAadmin\product\11.2.0\dbhome_1\bin;%PATH%
still report ORA-12560
2. use below method , it report ORA-12518
windows下 sqlplus / as sysdba 报ora-12560错误
-
确认windows注册表中的ORACLE_HOME 键及其 键值 是正确的，是有效的，用运行regedit，打开注册表
-
确定注册值：HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1下，找到ORACLE_HOME 键，对应的键值为H:\oracle\product\10.2.0\db_1 ,其实正确的键值为F:\oracle\product\10.2.0\db_1
注意：windows下，请不要设置ORACLE_HOME环境变量
-
确保windows 服务里变的OracleServiceXXX服务是已启动状态。
-
cmd下,cd到 F:\oracle\product\10.2.0\db_1\bin下，再敲sqlplus / as sysdba
-
cmd下，set ORACLE_SID=你想进入的数据库的那个sid
-
cmd下,cd到 F:\oracle\product\10.2.0\db_1\bin下，再敲sqlplus /@网络服务名 as sysdba -》still report ORA-12518.
(it is ok now )
END
3.
ORA-12518 TNS:监听程序无法分发客户机连接的解决办法
SQL> select * from dba_high_water_mark_statistics where name='SESSIONS';
SQL> select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION from v$resource_limit where resource_name='sessions';
the fixed method is as belows:
1.打开服务窗口，点 dbservice右键 ，查看属性，确认可执行文件的路径
为d:\app\oracle\product\11.2.0\dbhome_1\bin\ORACLE.EXE db
2.cd到d:\app\oracle\product\11.2.0\dbhome_1\bin下
.\sqlplus as sysdba
it is ok ,fixed
###2.restart service dbservice. tnsservice. and clean listener.or all is ok .
###########sample 1
服务器上的程序连接数据库报错：ORA-12560: TNS: 协议适配器错误
Master_arc 2021-01-20 11:17:06 293 收藏
文章标签： oracle
版权
应用服务器：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不是很了解，简单粗暴的重启应用服务器后解决了此问题。
————————————————
版权声明：本文为CSDN博主「Master_arc」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_33642204/article/details/112860534
