ORA-12514 TNS：侦听器当前不知道连接描述符中请求的服务

Question

我们有一个本地运行的应用程序，遇到以下错误：

ORA-12514：TNS：侦听器当前不知道连接描述符中请求的服务

我已经测试了使用TNSPing该连接可以正确解决的连接，并且尝试SQLPlus尝试连接，但连接失败，并出现与上述相同的错误。我将此语法用于SQLPlus：

sqlplus username/password@addressname[or host name]

我们已验证：

服务器上的TNS侦听器正在运行。

服务器上的Oracle本身正在运行。

我们不知道对此环境所做的任何更改。我们还有什么可以测试的吗？

Answer 1

我遇到了这个问题，解决方法是确保中tnsnames.ora的SERVICE_NAME数据库中的服务名称有效。要查找有效的服务名称，可以在oracle中使用以下查询：select value from v$parameter where name='service_names'一旦我更新tnsnames.ora为：TEST =&nbsp; &nbsp;(DESCRIPTION =&nbsp; &nbsp; (ADDRESS_LIST =&nbsp; &nbsp; &nbsp; (ADDRESS = (PROTOCOL = TCP)(HOST = *<validhost>*)(PORT = *<validport>*))&nbsp; &nbsp; )&nbsp; &nbsp; (CONNECT_DATA =&nbsp; &nbsp; &nbsp; (SERVER = DEDICATED)&nbsp; &nbsp; &nbsp; (SERVICE_NAME = *<servicenamefromDB>*)&nbsp; &nbsp; ))然后我跑了：sqlplus user@TEST成功！监听程序基本上是在告诉您，根据数据库，您正在使用的任何service_name都不是有效的服务。

Answer 2

我知道这是一个老问题，但仍然没有答案。我花了一天的时间进行研究，但我找到了最简单的解决方案，至少就我而言（在Windows 2008 R2上为Oracle 11.2），并且希望共享。如果直接查看该错误，则表明侦听器无法识别服务名称。但是它在哪里保留服务名称？在%ORACLE_HOME%\NETWORK\ADMIN\listener.ora“ SID_LIST”就是这样，以可复制或查找的格式配对的SID和服务名称列表。我添加了问题“服务名称”，然后在Windows“服务”控制面板中对Oracle侦听器服务进行了“重新启动”。现在一切都很好。例如，您的listener.ora文件最初可能看起来像：# listener.ora Network Configuration File: C:\app\oracle_user\product\12.1.0\dbhome_1\network\admin\listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER =&nbsp; (SID_LIST =&nbsp; &nbsp; (SID_DESC =&nbsp; &nbsp; &nbsp; (SID_NAME = CLRExtProc)&nbsp; &nbsp; &nbsp; (ORACLE_HOME = C:\app\oracle_user\product\12.1.0\dbhome_1)&nbsp; &nbsp; &nbsp; (PROGRAM = extproc)&nbsp; &nbsp; &nbsp; (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle_user\product\12.1.0\dbhome_1\bin\oraclr12.dll")&nbsp; &nbsp; )&nbsp; )LISTENER =&nbsp; (DESCRIPTION_LIST =&nbsp; &nbsp; (DESCRIPTION =&nbsp; &nbsp; &nbsp; (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))&nbsp; &nbsp; &nbsp; (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))&nbsp; &nbsp; )&nbsp; )...并使其能够识别服务名称orcl，您可以将其更改为：# listener.ora Network Configuration File: C:\app\oracle_user\product\12.1.0\dbhome_1\network\admin\listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER =&nbsp; (SID_LIST =&nbsp; &nbsp; (SID_DESC =&nbsp; &nbsp; &nbsp; (SID_NAME = CLRExtProc)&nbsp; &nbsp; &nbsp; (ORACLE_HOME = C:\app\oracle_user\product\12.1.0\dbhome_1)&nbsp; &nbsp; &nbsp; (PROGRAM = extproc)&nbsp; &nbsp; &nbsp; (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle_user\product\12.1.0\dbhome_1\bin\oraclr12.dll")&nbsp; &nbsp; )&nbsp; &nbsp; (SID_DESC =&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; (GLOBAL_DBNAME = orcl)&nbsp; &nbsp; &nbsp; &nbsp; (ORACLE_HOME = C:\app\oracle_user\product\12.1.0\dbhome_1)&nbsp; &nbsp; &nbsp; &nbsp; (SID_NAME = orcl)&nbsp; &nbsp; )&nbsp; )LISTENER =&nbsp; (DESCRIPTION_LIST =&nbsp; &nbsp; (DESCRIPTION =&nbsp; &nbsp; &nbsp; (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))&nbsp; &nbsp; &nbsp; (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))&nbsp; &nbsp; )&nbsp; )

Answer 3

在我的情况下，错误是由于侦听器未注册数据库服务而造成的。我通过注册服务解决了这个问题。例：我的描述符在tnsnames.ora：LOCALDB =&nbsp; (DESCRIPTION =&nbsp; &nbsp; (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))&nbsp; &nbsp; (CONNECT_DATA =&nbsp; &nbsp; &nbsp; (SERVER = DEDICATED)&nbsp; &nbsp; &nbsp; (SERVICE_NAME = LOCALDB)&nbsp; &nbsp; )&nbsp; )因此，我继续listener.ora手动注册服务：SID_LIST_LISTENER =&nbsp; &nbsp; (SID_DESC =&nbsp; &nbsp; &nbsp; (GLOBAL_DBNAME = LOCALDB)&nbsp; &nbsp; &nbsp; (ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)&nbsp; &nbsp; &nbsp; (SID_NAME = LOCALDB)&nbsp; &nbsp; )最后，通过命令重新启动侦听器：> lsnrctl stop> lsnrctl start做完了！