ORA-12514：TNS：监听器当前不知道连接描述符中请求的服务 [英] 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 参数未显式设置，因此您的数据库正在尝试注册<默认地址上的href =http://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams116.htm =nofollow>;并且因为它没有注册，所以它使用的任何地址似乎与听众不同。

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服务现在显示 XE 并且您对此感到满意，您可以将内存更改为两者并重新执行因此它会持续存在下一次重新启动数据库。

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 / hosts 中执行此操作;或者您的 / 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：监听器当前不知道连接描述符中请求的服务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！