1、TNS连接错误
同事跟我说连接oracle数据库报错ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务，如下所示：
2、查看本地TNSPING
查看本地的tns配置：
|
WXX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.58)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
查看本地的tnsping是ok的
C:UsersAdministrator>tnsping WXX
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 26-10月-2016 11:31:04
Copyright (c) 1997,2010,Oracle. All rights reserved.
已使用的参数文件:
D:appAdministratorproduct11.2.0client_2networkadminsqlnet.ora
已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.58)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (30 毫秒)
C:UsersAdministrator>
本地的tnsping暂时看来是正常的，是ok的。
3、查看网络连接
telnet 192.168.121.58 1521，也是能进去的，证明网络ok
oracle服务器的iptables也是关闭的
文章来源地址：http://www.52php.cn/article/p-rnjuqweu-bau.html，博主mchdba（黄杉）谢绝转载。
4、查看服务器的lsnrctl服务
去服务器看lsnrctl状态：
[oracle@iZ23vluqpmmZ admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-OCT-2016 11:33:32
Copyright (c) 1991,2009,Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 26-OCT-2016 11:09:33
Uptime 0 days 0 hr. 23 min. 59 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /usr/oracle/app/diag/tnslsnr/iZ23vluqpmmZ/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.121.58)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@iZ23vluqpmmZ admin]$
看到no services，也就是说lsnrctl监听服务启动起来，没有任何oracle实例的，所以问题就来了，没有oracle实例，客户端通过lsnrctl监听的话，就连接不到实例信息。所以这里我们需要添加实例信息，添加实例信息，就在listener.ora配置文件里面。
5、在listener.ora里面添加实例信息
完善listener.ora配置：
[oracle@iZ23vluqpmmZ admin]$ cp listener.ora z_listener.ora.bak
[oracle@iZ23vluqpmmZ admin]$ vim listener.ora
[oracle@iZ23vluqpmmZ admin]$
[oracle@iZ23vluqpmmZ admin]$ more listener.ora
# listener.ora Network Configuration File: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.58)(PORT = 1521))
)
) # 下面就是添加的新实例信息
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
)
)
ADR_BASE_LISTENER = /usr/oracle/app
[oracle@iZ23vluqpmmZ admin]$
设置完后，重启监听lsnrctl服务，可以看到会有一个instance服务启动起来了：
[oracle@iZ23vluqpmmZ admin]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-OCT-2016 11:38:29
Copyright (c) 1991,Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@iZ23vluqpmmZ admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-OCT-2016 11:38:33
Copyright (c) 1991,Oracle. All rights reserved.
Starting /usr/oracle/app/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /usr/oracle/app/diag/tnslsnr/iZ23vluqpmmZ/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.75.58)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 26-OCT-2016 11:38:33
Uptime 0 days 0 hr. 1 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /usr/oracle/app/diag/tnslsnr/iZ23vluqpmmZ/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.75.58)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl",status UNKNOWN,has 1 handler(s) for this service...
The command completed successfully
[oracle@iZ23vluqpmmZ admin]$
OK，在问下同事，同事说能连接上了，问题解决。
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
