oracle 无法连接 ORA-12514: TNS:listener 当前不知道连接描述符中请求的服务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46914943/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can not connect ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
提问by snowman0805
I want to connect my PLSQL developer tools into database but fail to connect due to error below. ORA-12514: TNS: listener does not currently know of service requested in connect descriptor
我想将我的 PLSQL 开发人员工具连接到数据库，但由于以下错误而无法连接。ORA-12514: TNS: 侦听器当前不知道连接描述符中请求的服务
Check configuration below. How to configure to make it works?
检查下面的配置。如何配置以使其工作？
SQL> select value from v$parameter where name='service_names';
VALUE
orcl
SQL> select instance from v$thread;
INSTANCE
orcl
SQL> select host_name,instance_name,version from v$instance;
HOST_NAME
INSTANCE_NAME VERSION
ex-cs-b
orcl 11.2.0.1.0
SQL> select global_name from global_name;
GLOBAL_NAME
ORCL
SQL>
lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-OCT-2017 16:49:51
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 23-OCT-2017 16:49:40
Uptime 0 days 0 hr. 0 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /app/oracle/diag/tnslsnr/ex-cs-b/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ex-cs-b)(PORT=1521)))
The listener supports no services
The command completed successfully
lsnrctl services
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-OCT-2017 16:58:18
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
more tnsnames.ora
# tnsnames.ora Network Configuration File: /app/oracle/product/11.2.0/dbhome_1/network
/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ex-cs-b)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
more listener.ora
# listener.ora Network Configuration File: /app/oracle/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 = ex-cs-b)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /app/oracle
回答by Steven Ensslen
Your database hasn't been registered with the listener. lsnrctl statusreturned "The listener supports no services" and your listener.orafile doesn't have a SID_LIST.
您的数据库尚未在侦听器中注册。lsnrctl status返回“侦听器不支持任何服务”并且您的listener.ora文件没有 SID_LIST。
Connect to your database instance as a SYSDBA and run:
以 SYSDBA 身份连接到您的数据库实例并运行：
ALTER SYSTEM SET LOCAL_LISTENER='';
If that doesn't resolve your problem then you mostly likely have several listeners and you'll need to follow the registration processfor the listener that you want.
如果这不能解决您的问题，那么您很可能有多个侦听器，并且您需要按照所需侦听器的注册过程进行操作。
All of this complexity is to allow an operating system to run multiple listeners, which can be useful.
所有这些复杂性都是为了允许操作系统运行多个侦听器，这很有用。
Do note that depending on how you started your instance, you may need to specify SCOPEon the ALTER SYSTEMcommand or your configuration will disappear on restart of the instance.
请注意，根据您启动实例的方式，您可能需要SCOPE在ALTER SYSTEM命令中指定，否则您的配置将在实例重新启动时消失。
