ORA-12514: TNS:listener 当前不知道连接描述符中请求的服务 [英] ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

问题描述

我在尝试通过侦听器连接到 Oracle 数据库 XE 时遇到问题:

I have problem, when trying to connect to Oracle Database XE via listener:

 sqlplus system/my_password@XE

我收到以下错误:

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

我使用 Ubuntu 12.10 和 Oracle Express Edition 11g.

I use Ubuntu 12.10 and Oracle Express Edition 11g.

我的主要目标是将 java + oracle db 与 jdbc 连接起来，但我遇到了同样的错误.

My main goal to connect java + oracle db with jdbc, but there i get the same error.

这是我的 listener.ora

Here are my listener.ora

# listener.ora Network Configuration File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = watson)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

和 tnsnames.ora

and tnsnames.ora

# tnsnames.ora Network Configuration File:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = watson)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

和

lsnrctl service

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 04-FEB-2014 17:01:44
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

推荐答案

假设您的数据库即使在侦听器运行了一段时间后也无法注册，听起来您的 local_listener 参数不是't 显式设置，因此您的数据库正在尝试在默认地址上注册 ;并且由于它无法注册，因此它使用的任何地址似乎都与侦听器所在的地址不同.

Assuming your database is failing to register even when the listener has been running for a while, it sounds like your local_listener parameter isn't explicitly set, so your database is trying to register on a default address; and since it's failing to register, whatever address it's using doesn't seem to be the same one the listener is on.

假设 watson 正在解析一个有用的外部 IP 地址，如果您能够从外部获取该 TNS 错误，可能就是这种情况，您可以告诉数据库使用相同的注册地址:

Assuming watson is resolving to a useful external IP address, which is probably the case if you're able to get that TNS error from outside your box, you can tell the database to use the same address to register:

alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=watson)(PORT=1521))' scope=memory;
alter system register;

如果可行并且 lsnrctl services 现在显示 XE 并且您对此感到满意，您可以将 memory 更改为 并重新执行 使其持续存在下次数据库重启.

If that works and lsnrctl services now shows XE and you're happy with it, you can change the memory to both and re-execute so it persists across the next DB restart.

或者，如果您只尝试在同一个框中连接，watson 可能会解析为无用的东西，例如 127.0.0.1，如果它在您的 /etc/中设置为这样做主机;或者您的 /etc/hosts 地址可能与您的 DNS 条目不一致.您可以将 listener.ora 和 tnsnames.ora 改为正确的"FQDN 或 IP 地址，只要它与您的数据库认为的网络名称相匹配.

Alternatively, if you've only tried connecting within the same box, watson might be resolving to something unhelpful, like 127.0.0.1 if it's set to do that in your /etc/hosts; or your /etc/hosts address might not agree with your DNS entry. You can change the listener.ora and tnsnames.ora to the 'right' FQDN or IP address instead, as long as that matches whatever your database thinks is the network name.

这篇关于ORA-12514: TNS:listener 当前不知道连接描述符中请求的服务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！