ORA-12505，TNS:listener 当前不知道连接描述符中给出的 SID.Eclipse 和 Fedora 20 通过 JDBC

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor. Eclipse and Fedora 20 via JDBC(ORA-12505，TNS:listener 当前不知道连接描述符中给出的 SID.Eclipse 和 Fedora 20 通过 JDBC)

问题描述

我已经在 Fedora 20 虚拟机上安装了 Oracle 11g XE，对其进行了配置并设置了环境变量(运行 oracle_env.sh).尝试通过 jdbc 将 Eclipse 与数据库连接时出现此错误，使用此字符串 "jdbc.databaseurl=jdbc:oracle:thin:@192.168.88.134:1521:XE"

I've installed Oracle 11g XE on a Fedora 20 Virtual Machine, configured it and set the enviroment variables (running the oracle_env.sh). I've got this error when trying to connect Eclipse with the database via jdbc, using this string "jdbc.databaseurl=jdbc:oracle:thin:@192.168.88.134:1521:XE"

我可以通过 SQL*Plus (sqlplus/as sysdba) 连接到虚拟机中的数据库.

I can connect to the database in the Virtual Machine via SQL*Plus (sqlplus / as sysdba).

$ORACLE_SID 变量没问题 (XE).我已经尝试停止并启动侦听器，然后启动数据库，并使用 alter system register; 命令.

The $ORACLE_SID variable is ok (XE). I've already tried to stop and start the listener, then startup the database, and use the alter system register; command.

静态注册数据库也没有成功(我不能保证我没有弄错，所以如果有人认为这可以解决我的问题，我会再试一次).

Also statically registering the database without success (I cannot asure I did not mistake doing this, so if somebody thinks this could solve my problem I would try again).

这是 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 = 192.168.88.134)(PORT = 1521)) 
    ) 
  ) 

DEFAULT_SERVICE_LISTENER = (XE) 

还有 tnsnames.ora 文件:

# tnsnames.ora Network Configuration File: 

XE = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.134)(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 status:

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 31-MAR-2014 01:22:35 

Copyright (c) 1991, 2011, Oracle.  All rights reserved. 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) 
STATUS of the LISTENER 
------------------------ 
Alias                     LISTENER 
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production 
Start Date                30-MAR-2014 22:41:35 
Uptime                    0 days 2 hr. 41 min. 1 sec 
Trace Level               off 
Security                  ON: Local OS Authentication 
SNMP                      OFF 
Default Service           XE 
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora 
Listener Log File         /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/192/listener/alert/log.xml 
Listening Endpoints Summary... 
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE))) 
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.88.134)(PORT=1521))) 
Services Summary... 
Service "PLSExtProc" has 1 instance(s). 
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... 
The command completed successfully

我几乎可以肯定问题在于侦听器不知道数据库.前面的命令应该显示，除了它已经显示的内容，像

I'm almost sure that the problem is that the listener doesn't know about the database. The previous command should show, apart from what it already shows, something like

Service XE has 1 instance.
Instance "XE", status READY, has 1 handler for this service

...但它没有，我不知道如何解决这个问题.

... but it doesn't, and I don't know how to solve this.

我是这方面的新手，主要是在 Linux 方面，所以我会感谢您建议的解决方案中的每一个细节.

I'm new at this, mainly at Linux, so I will appreciate every detail in the solutions you suggest.

推荐答案

这听起来像是您的数据库正在尝试使用错误的 IP 地址注册以联系侦听器.您的侦听器配置为在 192.168.88.134 上侦听，但如果配置发生更改，数据库可能会假设 localhost (127.0.0.1) 或旧 IP 值.

This sounds like your database is trying to register using the wrong IP address to contact the listener. Your listener is configured to listen on 192.168.88.134, but perhaps the DB is assuming localhost (127.0.0.1), or an old IP value if the configuration has ever changed.

默认情况下，数据库将尝试针对服务器的外部主机名进行注册 (local_listener 为空时的默认值)，但您可能会从中获得意外的值 - 所以 /etc/hosts 中机器名称的内容很重要.无论是什么原因造成的，注册似乎都失败了.

By default the database will attempt to register against the server's external host name (the default when local_listener is blank), but you may be getting an unexpected value from that - so what is in /etc/hosts for the machine name matters. Whatever has caused that, registration seems to be failing.

您可以明确告诉数据库使用实际的侦听器地址进行注册:

You can explicitly tell the DB to register using the actual listener address:

alter system set local_listener = '192.168.88.134:1521' scope=memory;
alter system register;

如果可行并且 lsnrctl services 现在显示 XE，然后使用 scope=both 重复 set 命令让它在下次数据库重启时坚持下去.

If that works and lsnrctl services now shows XE, then repeat the set command with scope=both to make it stick on the next DB restart.

这篇关于ORA-12505，TNS:listener 当前不知道连接描述符中给出的 SID.Eclipse 和 Fedora 20 通过 JDBC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！