oracle ORA-12514 TNS:listener 当前不知道连接描述符中请求的服务

ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

提问by Jacques

We have an application running locally where we're experiencing the following error:

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

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

ORA-12514: TNS:listener 当前不知道连接描述符中请求的服务

I've tested the connection using TNSPingwhich resolved correctly and I tried SQLPlusto try connecting, which failed with the same error as above. I used this syntax for SQLPlus:

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

sqlplus username/password@addressname[or host name]

We have verified that:

我们已经验证：

• the TNS Listener on the server is running.
• Oracle itself on the server is running.

• 服务器上的 TNS 侦听器正在运行。
• Oracle本身在服务器上运行。

We don't know of any changes that were made to this environment. Anything else we can test?

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

采纳答案by Brad Rippe

I had this issue and the fix was to make sure in tnsnames.orathe SERVICE_NAMEis a valid service name in your database. To find out valid service names, you can use the following query in oracle:

我有这个问题，并修复是确保在tnsnames.ora该SERVICE_NAME在数据库中，一个有效的服务名称。要找出有效的服务名称，您可以在 oracle 中使用以下查询：

select value from v$parameter where name='service_names'

Once I updated tnsnames.orato:

一旦我更新tnsnames.ora为：

TEST =
   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = *<validhost>*)(PORT = *<validport>*))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = *<servicenamefromDB>*)
    )
)

then I ran:

然后我跑了：

sqlplus user@TEST

Success! The listener is basically telling you that whatever service_name you are using isn't a valid service according to the DB.

成功！侦听器基本上是在告诉您，根据数据库，您使用的任何 service_name 都不是有效的服务。

(*I was running sqlplus from Win7 client workstation to remote DB and blame the DBAs ;) *)

（*我从 Win7 客户端工作站运行 sqlplus 到远程数据库并责怪 DBA ;）*）

回答by Joseph Argenio

I know this is an old question, but still unanswered. It took me a day of research, but I found the simplest solution, at least in my case (Oracle 11.2 on Windows 2008 R2) and wanted to share.

我知道这是一个老问题，但仍然没有答案。我花了一天的时间研究，但我找到了最简单的解决方案，至少在我的情况下（Windows 2008 R2 上的 Oracle 11.2）并想分享。

The error, if looked at directly, indicates that the listener does not recognize the service name. But where does it keep service names? In %ORACLE_HOME%\NETWORK\ADMIN\listener.ora

如果直接查看该错误，则表明侦听器无法识别服务名称。但是它在哪里保存服务名称？在%ORACLE_HOME%\NETWORK\ADMIN\listener.ora

The "SID_LIST" is just that, a list of SIDs and service names paired up in a format you can copy or lookup.

“SID_LIST”就是这样，一个以您可以复制或查找的格式配对的 SID 和服务名称列表。

I added the problem Service Name, then in Windows "Services" control panel, I did a "Restart" on the Oracle listener service. Now all is well.

我添加了问题服务名称，然后在 Windows 的“服务”控制面板中，我对 Oracle 侦听器服务进行了“重新启动”。现在一切都很好。

---

---

For example, your listener.ora file might initially look like:

例如，您的 listener.ora 文件最初可能如下所示：

# listener.ora Network Configuration File: C:\app\oracle_user\product.1.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\oracle_user\product.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle_user\product.1.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

... And to make it recognize a service name of orcl, you might change it to:

...并使其识别服务名称orcl，您可以将其更改为：

# listener.ora Network Configuration File: C:\app\oracle_user\product.1.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\oracle_user\product.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle_user\product.1.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC = 
        (GLOBAL_DBNAME = orcl)
        (ORACLE_HOME = C:\app\oracle_user\product.1.0\dbhome_1)
        (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

回答by Sepideh

I had this issue at Windows server 2008 R2and Oracle 11g

我在Windows Server 2008 R2和Oracle 11g 上遇到了这个问题

go to Net Manager > Listener > select database services form the combox > "Global Database Name" must be same as "SID" and "Oracle Home Directory" must be correct.

转到网络管理器 > 侦听器 > 从组合框中选择数据库服务 >“全局数据库名称”必须与“SID”相同，并且“Oracle 主目录”必须正确。

If you don't have any entry for database services, create one and set correct global database , sidand oracle home.

如果您没有任何数据库服务条目，请创建一个并设置正确的 global databasesid和 oracle home。

回答by manix

In my circumstances the error was due to the fact the listener did not have the db's service registered. I solved this by registering the services. Example:

在我的情况下，错误是由于侦听器没有注册数据库服务。我通过注册服务解决了这个问题。例子：

My descriptor in tnsnames.ora:

我的描述符在tnsnames.ora：

LOCALDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LOCALDB)
    )
  )

So, I proceed to register the service in the listener.oramanually:

所以，我继续listener.ora手动注册服务：

SID_LIST_LISTENER =
    (SID_DESC =
      (GLOBAL_DBNAME = LOCALDB)
      (ORACLE_HOME = C:\Oracle\product.2.0\dbhome_1)
      (SID_NAME = LOCALDB)
    )

Finally, restart the listener by command:

最后，通过命令重启监听器：

> lsnrctl stop
> lsnrctl start

Done!

完毕！

回答by Ishildur Baggins

Starting the OracleServiceXXX from the services.msc worked for me in Windows.

从 services.msc 启动 OracleServiceXXX 在 Windows 中对我来说有效。

回答by Capricorn1

This really should be a comment to Brad Rippe's answer, but alas, not enough rep. That answer got me 90% of the way there. In my case, the installation and configuration of the databases put entries in the tnsnames.ora file for the databases I was running. First, I was able to connect to the database by setting the environment variables (Windows):

这真的应该是对Brad Rippe回答的评论，但可惜，代表不够。这个答案让我完成了 90% 的工作。就我而言，数据库的安装和配置将条目放入我正在运行的数据库的 tnsnames.ora 文件中。首先，我能够通过设置环境变量（Windows）连接到数据库：

set ORACLE_SID=mydatabase
set ORACLE_HOME=C:\Oracle\product.2.0\dbhome_1

and then connecting using

然后使用连接

sqlplus / as sysdba

Next, running the command from Brad Rippe's answer:

接下来，运行 Brad Rippe 的回答中的命令：

select value from v$parameter where name='service_names';

showed that the names didn't match exactly. The entries as created using Oracle's Database Configuration Assistant where originally:

显示名称不完全匹配。使用 Oracle 的数据库配置助手创建的条目最初是：

MYDATABASE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mylaptop.mydomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydatabase.mydomain.com)
    )
  ) 

The service name from the query was just mydatabaserather than mydatabase.mydomain.com. I edited the tnsnames.ora file to just the base name without the domain portion so they looked like this:

查询中的服务名称只是mydatabase而不是mydatabase.mydomain.com. 我将 tnsnames.ora 文件编辑为没有域部分的基本名称，因此它们看起来像这样：

MYDATABASE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mylaptop.mydomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydatabase)
    )
  ) 

I restarted the TNS Listener service (I often use lsnrctl stopand lsnrctl startfrom an administrator command window [or Windows Powershell] instead of the Services control panel, but both work.) After that, I was able to connect.

我重新启动TNS Listener服务（我经常使用lsnrctl stop并lsnrctl start从管理员命令窗口[或Windows PowerShell]代替服务控制面板，但两者的工作。）在那之后，我能够连接。

回答by Rika

I had the same problem. For me, just writing

我有同样的问题。对我来说，只是写作

sqlplus myusername/mypassword@localhost

did the trick, doing so makes it connect to the default service name, I guess.

做到了，我猜这样做会使其连接到默认服务名称。

回答by isabelle martz

what worked for me was really simple, I just needed to initiate the service manually in the "Windows Services" (services.msc in cmd trompt). my service name is: OracleServiceXXXXX.

对我有用的非常简单，我只需要在“Windows 服务”（cmd trompt 中的 services.msc）中手动启动服务。我的服务名称是：OracleServiceXXXXX。

回答by th3uiguy

For those that may be running Oracle in a VM (like me) I saw this issue because my VM was running out of memory, which seems to have prevented OracleDB from starting up/running correctly. Increasing my VM memory and restarting fixed the issue.

对于那些可能在 VM 中运行 Oracle 的人（比如我），我看到了这个问题，因为我的 VM 内存不足，这似乎阻止了 OracleDB 正确启动/运行。增加我的 VM 内存并重新启动解决了这个问题。

回答by Elmue

Lots of answers here, but here comes a working example with code that you can copy and paste and test immediately:

这里有很多答案，但这里有一个工作示例，其中包含您可以立即复制、粘贴和测试的代码：

For me the error 12514 was solved after specifying the correct SERVICE_NAME. You find that on the server in the file tnsnames.orawhich comes with 3 predefined service names (one of them is "XE").

对我来说，在指定正确的 SERVICE_NAME 后，错误 12514 已解决。您会在tnsnames.ora带有 3 个预定义服务名称（其中之一是“XE”）的文件中的服务器上找到该文件。

1. I installed the Oracle Express database OracleXE112 which already comes with some preinstalled demo tables.
2. When you start the installer you are asked for a password. I entered "xxx" as password. (not used in production)
3. My server runs on the machine 192.168.1.158
4. On the server you must explicitely allow access for the process TNSLSNR.exe in the Windows Firewall. This process listens on port 1521.
5. OPTION A:For C# (.NET2 or .NET4) you can download ODAC11, from which you have to add Oracle.DataAccess.dll to your project. Additionally this DLL depends on: OraOps11w.dll, oci.dll, oraociei11.dll (130MB!), msvcr80.dll. These DLLs must be in the same directory as the EXE or you must specify the DLL path in: HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.112.4.0\DllPath. On 64 bit machines write additionally to HKLM\SOFTWARE\Wow6432Node\Oracle\...
6. OPTION B:If you have downloaded ODAC12you need Oracle.DataAccess.dll, OraOps12w.dll, oci.dll, oraociei12.dll (160MB!), oraons.dll, msvcr100.dll. The Registry path is HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.121.2.0\DllPath
7. OPTION C:If you don't want huge DLL's of more than 100 MB you should download ODP.NET_Managed12.x.x.x.xxxxx.zip in which you find Oracle.ManagedDataAccess.dllwhich is only 4 MB and is a pure managed DLL which works in 32 bit and 64 bit processes as well and depends on no other DLL and does not require any registry entries.
8. The following C# code works for me without any configuration on the server side (just the default installation):

1. 我安装了 Oracle Express 数据库 OracleXE112，它已经带有一些预安装的演示表。
2. 当您启动安装程序时，系统会要求您输入密码。我输入了“xxx”作为密码。（未用于生产）
3. 我的服务器运行在机器 192.168.1.158 上
4. 在服务器上，您必须明确允许访问 Windows 防火墙中的进程 TNSLSNR.exe。此进程侦听端口 1521。
5. 选项 A：对于 C#（.NET2 或 .NET4），您可以下载ODAC11，您必须从中将 Oracle.DataAccess.dll 添加到您的项目中。此外，此 DLL 依赖于：OraOps11w.dll、oci.dll、oraociei11.dll（130MB！）、msvcr80.dll。这些 DLL 必须与 EXE 位于同一目录中，或者您必须在以下位置指定 DLL 路径：HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.112.4.0\DllPath. 在 64 位机器上额外写入HKLM\SOFTWARE\Wow6432Node\Oracle\...
6. 选项 B：如果您已下载ODAC12，则需要 Oracle.DataAccess.dll、OraOps12w.dll、oci.dll、oraociei12.dll (160MB!)、oraons.dll、msvcr100.dll。注册表路径是HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.121.2.0\DllPath
7. 选项 C：如果您不想要超过 100 MB 的巨大 DLL，您应该下载 ODP.NET_Managed12.xxxxxxxx.zip，您会在其中找到Oracle.ManagedDataAccess.dll只有 4 MB 的纯托管 DLL，可在 32 位和 64 位进程中工作并且不依赖于其他 DLL 并且不需要任何注册表项。
8. 以下 C# 代码适用于我，无需在服务器端进行任何配置（只是默认安装）：

using Oracle.DataAccess.Client;
or
using Oracle.ManagedDataAccess.Client;

....

string oradb = "Data Source=(DESCRIPTION="
    + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.158)(PORT=1521)))"
    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));"
    + "User Id=SYSTEM;Password=xxx;";

using (OracleConnection conn = new OracleConnection(oradb)) 
{
    conn.Open();
    using (OracleCommand cmd = new OracleCommand())
    {
        cmd.Connection  = conn;
        cmd.CommandText = "select TABLESPACE_NAME from DBA_DATA_FILES";

        using (OracleDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                listBox.Items.Add(dr["TABLESPACE_NAME"]);
            }
        }
    }
}

If the SERVICE_NAME=XEis wrong you get error 12514. The SERVICE_NAMEis optional. You can also leave it away.

如果SERVICE_NAME=XE错误，您会收到错误 12514。这SERVICE_NAME是可选的。你也可以把它放在一边。