今天使用SQL Developer连接一台测试服务器数据库(ORACLE 11g)时,遇到了“ORA-12514, TNS:listener does not currently know of service requested in connect descriptor”错误,具体提示如下所示:
执行请求的操作时遇到错误:
Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
供应商代码 12514
几年前写过一篇关于”ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务“的案例文章,现在看来当时写的比较肤浅和粗糙,当然案例也不一样,下面在总结一下如何处理ORA-12514这个错误吧。
Step 1: 首先检查客户端的tnsnames.ora里面的host、port、service_name等是否正确
myvm =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.57.15)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = gsp.localdomain)
)
)
逐一仔细检查过后发现这些配置都是正确的。 其实前几天还是正常来的。测试服务器和tnsnames.ora都没有改动过。所以我们继续后面排查。
Step 2:检查确认数据库实例是否启动。
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL>
如上所示,数据库实例正常,并没有关闭。当数据库实例异常关闭,也会出现ORA-12514错误,如下所示(如果我关闭一数据库实例,然后使用SQL*Plus测试连接数据库就会遇到这个错误):
C:\Users>sqlplus system/123456@mydb
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 12 11:56
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
Step 3:检查网络是否正常,tnsping能否ping通
如下所示,ping 和tnsping 均都正常
监听服务也正常,没有问题
[oracle@DB-Server admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-APR-2017 10:36:40
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 12-APR-2017 10:31:20
Uptime 0 days 0 hr. 5 min. 20 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/DB-Server/listener/alert/log.xml
Listening Endpoints Summary...
(DESCR