TNS-12505:TNS:listener 当前不知道连接描述符中给出的 SID

问题描述

我正在尝试使用以下连接字符串从 NetBeans 连接到 Oracle 10.2.0:

I'm trying to connect to Oracle 10.2.0 from NetBeans, using the following connection string:

jdbc:oracle:thin:@localhost:1521:XE

最奇怪的部分是一切正常，直到重启之一.我开始一直收到这个 TNS-12505 错误；查看我的 listener.log 中的最终条目:

The weirdest part is that everything worked fine, until the one of the reboots. I started getting this TNS-12505 errors all the time; look at the final entries in my listener.log:

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 14-APR-2011 13:46:48

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

System parameter file is D:oraclexeapporacleproduct10.2.0server
etworkadminlistener.ora
Log messages written to D:oraclexeapporacleproduct10.2.0server
etworkloglistener.log
Trace information written to D:oraclexeapporacleproduct10.2.0server
etwork	racelistener.trc
Trace level is currently 0

Started with pid=3460
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC_FOR_XEipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Brodyaga-PC)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
14-APR-2011 13:48:54 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=Brodyaga))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58458)) * establish * XE * 12505
TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
14-APR-2011 13:49:00 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=Brodyaga))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58481)) * establish * XE * 12505
TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
14-APR-2011 13:49:02 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=Brodyaga))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58487)) * establish * XE * 12505
TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
14-APR-2011 13:50:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Brodyaga))(COMMAND=services)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * services * 0

前三个条目是我从 NetBeans 连接的尝试.第四个，通过 SQL*Plus 连接，工作正常.

The first three entries are my attempts to connect from NetBeans. The fourth, the connection via SQL*Plus, worked just fine.

这里是我的listener.ora的内容:

Here are the contents of my listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:oraclexeapporacleproduct10.2.0server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:oraclexeapporacleproduct10.2.0server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Brodyaga-PC)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

和tnsnames.ora:

And tnsnames.ora:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Brodyaga-PC)(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)
    )
  )

ORACLR_CONNECTION_DATA = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) 
    ) 
    (CONNECT_DATA = 
      (SID = CLRExtProc) 
      (PRESENTATION = RO) 
    ) 
  ) 

以及来自 > lsnrctl services

C:UsersBrodyaga>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 14-APR-2011 13:59
:45

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
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

两个 Oracle 服务都已启动并且 SQL*Plus 连接正常.

Both Oracle services are started and SQL*Plus connects fine.

是否有解决此错误的方法?

Is there some workaround for this error?

推荐答案

您需要为 XE 添加 SID 条目，以便向侦听器注册实例.

You need to add the SID entry for XE in order to register the instance with the listener.

安装 Oracle XE 后，一切看起来都不错，但是当您发出

After installation of Oracle XE, everything looks good, but when you issue

C:>sqlplus / as sysdba
SQL>shutdown immediate
SQL>startup

TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

该实例不会向侦听器注册.

the instance will not register with the listener.

所以请像这样编辑你的 listener.ora:

So please edit your listener.ora like this:

SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
       (SID_NAME = XE)
       (ORACLE_HOME = C:oraclexeapporacleproduct10.2.0server)
     )
     (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = D:oraclexeapporacleproduct10.2.0server)
        (PROGRAM = extproc)
     )
     (SID_DESC =
       (SID_NAME = CLRExtProc)
       (ORACLE_HOME = D:oraclexeapporacleproduct10.2.0server)
       (PROGRAM = extproc)
     )
  )

这个问题是我在 Windows 7 上安装 Oracle XE 时出现的.我在 Windows XP 上没有遇到这个问题.通常，此条目不是必需的，因为实例应自动向侦听器注册.在 Linux (Fedora) 上运行 Oracle XE，无需将 XE 添加到 sid-list.

This issue came up when I installed Oracle XE on Windows 7. I did not face this problem on Windows XP. In general, this entry should not be necessary, because the instance should register with the listener automatically. Running Oracle XE on Linux (Fedora), there is no need to add XE to the sid-list.

这篇关于TNS-12505:TNS:listener 当前不知道连接描述符中给出的 SID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！