TNS-12505:TNS:listener当前不知道连接描述符中给出的SID [英] TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

问题描述

我正在尝试使用以下连接字符串从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:\oraclexe\app\oracle\product\10.2.0\server\network\admin\listener.ora
Log messages written to D:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
Trace information written to D:\oraclexe\app\oracle\product\10.2.0\server\network\trace\listener.trc
Trace level is currently 0

Started with pid=3460
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_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:\oraclexe\app\oracle\product\10.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\oraclexe\app\oracle\product\10.2.0\server)
      (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服务

C:\Users\Brodyaga>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:\oraclexe\app\oracle\product\10.2.0\server)
     )
     (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = D:\oraclexe\app\oracle\product\10.2.0\server)
        (PROGRAM = extproc)
     )
     (SID_DESC =
       (SID_NAME = CLRExtProc)
       (ORACLE_HOME = D:\oraclexe\app\oracle\product\10.2.0\server)
       (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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！