当前位置：theitroad>oracle TNS-12505：TNS:listener 当前不知道连接描述符中给出的 SID

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

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/5661610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-10 03:12:50 　来源:igfitidea点击:

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

oraclejdbclistenertnsnames

提问by Anton

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

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

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

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:

最奇怪的部分是一切正常，直到重启之一。我开始一直收到这个 TNS-12505 错误；查看 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.2.0\server\network\admin\listener.ora
Log messages written to D:\oraclexe\app\oracle\product.2.0\server\network\log\listener.log
Trace information written to D:\oraclexe\app\oracle\product.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

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

前三个条目是我从 NetBeans 连接的尝试。第四个，通过 SQL*Plus 的连接，工作得很好。

Here are the contents of my listener.ora:

以下是我的内容listener.ora：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oraclexe\app\oracle\product.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\oraclexe\app\oracle\product.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)

And tnsnames.ora:

并且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) 
    ) 
  )

And output from > lsnrctl services

以及来自 > lsnrctl services 的输出

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

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

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

Is there some workaround for this error?

是否有针对此错误的解决方法？

回答by Bjarte Brandt

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

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

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

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

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.

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

So please edit your listener.ora like this:

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

SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
       (SID_NAME = XE)
       (ORACLE_HOME = C:\oraclexe\app\oracle\product.2.0\server)
     )
     (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = D:\oraclexe\app\oracle\product.2.0\server)
        (PROGRAM = extproc)
     )
     (SID_DESC =
       (SID_NAME = CLRExtProc)
       (ORACLE_HOME = D:\oraclexe\app\oracle\product.2.0\server)
       (PROGRAM = extproc)
     )
  )

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.

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

回答by The Camster

this worked for me - I did all of the above then changed:

这对我有用 - 我做了以上所有的事情然后改变了：

jdbc.databaseurl=jdbc:oracle:thin:@localhost:1521:xe

to:

到：

jdbc.databaseurl=jdbc:oracle:thin:@localhost:1521/xe

回答by Jose Zorrilla

After a change of ip in our oracle virtual machine, the listener never worked again. Finally these command solved the problem (where 192.168.10.200 is the new ip)

在我们的 oracle 虚拟机中更改 ip 后，监听器再也没有工作过。最后这些命令解决了问题（其中 192.168.10.200 是新的 ip）

[oracle@oracle admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 10 12:57:37 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.200)(PORT=1521))' scope=both;

System altered.

SQL> alter system register;

System altered.

SQL> exit

[oracle@oracle admin]$ sqlplus / as sysdba

SQL*Plus：2016 年 5 月 10 日星期二 12:57:37 发布 11.2.0.1.0 生产版

版权所有 (c) 1982, 2009，甲骨文。版权所有。

连接到：Oracle 数据库 11g 企业版 11.2.0.1.0 版 - 具有分区、OLAP、数据挖掘和实际应用程序测试选项的 64 位生产

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.200)(PORT=1521))' scope=both;

系统变了。

SQL> 更改系统寄存器；

系统变了。

SQL> 退出

回答by robm

I ran into this problem after a firewall change to restrict access between our internal network and the database server reporting the error located in the DMZ. Communication was working fine until the change, and system and database restarts were of no help. In my case both Oracle XE11gR2 installations are on Windows.

在更改防火墙以限制我们的内部网络和报告位于 DMZ 中的错误的数据库服务器之间的访问后，我遇到了这个问题。在更改之前，通信工作正常，并且系统和数据库重新启动也无济于事。就我而言，两个 Oracle XE11gR2 安装都在 Windows 上。

After day of struggle I found http://edstevensdba.wordpress.com/2011/07/30/exploring-the-local_listener-parameter/and solved the problem with:

经过一天的斗争，我找到了http://edstevensdba.wordpress.com/2011/07/30/exploring-the-local_listener-parameter/并解决了这个问题：

alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=[my server's ip])(PORT=1521))' scope=both;
alter system register;

it may be that this worked simply because of the 'alter system register' as suggested by ik_zelf. I did have the IP already set in relevant places in listener.ora and tnsnames.ora.

这可能仅仅是因为 ik_zelf 建议的“更改系统寄存器”。我确实已经在 listener.ora 和 tnsnames.ora 中的相关位置设置了 IP。

回答by Hassaan Hassaan

I'm running oracle xpress edition 11.2 on windows 8 and I had the same error when trying to connect to DB using sqldeveloper.

我在 Windows 8 上运行 oracle xpress 11.2 版，并且在尝试使用 sqldeveloper 连接到数据库时遇到了同样的错误。

I've edited listener.ora as per Brandt answer above and even restarted my machine the issue wasn't fixed.

我已经按照上面 Brandt 的回答编辑了 listener.ora，甚至重新启动了我的机器，问题没有解决。

I've done the following: go to control panel -> administrative tools -> services you will find a service called "OracleServiceXE" not running.

我已完成以下操作：转到控制面板 -> 管理工具 -> 服务，您会发现名为“OracleServiceXE”的服务未运行。

I started it and tried to connect again, issue resolved.

我启动它并尝试再次连接，问题解决了。

回答by RedstoneCS

Step 1 – Check the DB listener status

步骤 1 – 检查数据库侦听器状态

   lsnrctl status

Notice that the listener you want (in our case “orcl”) is not showing.

请注意，您想要的侦听器（在我们的示例中为“orcl”）没有显示。

Step 2 – Login via sqlplus

第 2 步 – 通过 sqlplus 登录

   sqlplus sys/oracle as sysdba

Sqlplus gave us this error message:

Sqlplus 给了我们这个错误信息：

   Writing audit records to Windows Event Log failed

Step 3 – Go into the Windows Event Viewer (eventvwr.exe)

步骤 3 – 进入 Windows 事件查看器 (eventvwr.exe)

Under “Windows Logs”, right click on Application and select “Clear Log”. Do the same for System.

在“Windows 日志”下，右键单击应用程序并选择“清除日志”。对系统做同样的事情。

It may also be wise to right click on Application and select Properties. Then, under “Log Size” select the following option under “When maximum log size is reached”: “Overwrite events as needed”. This should prevent the log from maxing out and causing the DB not to start.

右键单击“应用程序”并选择“属性”也可能是明智之举。然后，在“日志大小”下选择“达到最大日志大小时”下的以下选项：“根据需要覆盖事件”。这应该可以防止日志最大化并导致数据库无法启动。

In Windows Vista and higher, you can execute the following command to clear the Application log:

在 Windows Vista 及更高版本中，您可以执行以下命令来清除应用程序日志：

   wevtutil cl Application

Step 4 – Login via sqlplus

第 4 步 – 通过 sqlplus 登录

   sqlplus sys/oracle as sysdba

You should now be able to login with no error messages.

您现在应该能够在没有错误消息的情况下登录。

Step 5 - Check the DB listener status

第 5 步 - 检查数据库侦听器状态

   lsnrctl status

You should now see your listener running.

您现在应该看到您的侦听器正在运行。

Step 6 – Start UCM

第 6 步 - 启动 UCM

UCM should now start up.

UCM 现在应该启动了。

For a more in-depth answer to this question you can read my full blog post.

要更深入地回答这个问题，您可以阅读我的完整博客文章。

回答by kravemir

I had the same issue on Windows 7. The cause was, that I had been connected to VPN using Cisco AnyConnect Secure Mobility Client.

我在 Windows 7 上遇到了同样的问题。原因是，我使用 Cisco AnyConnect 安全移动客户端连接到 VPN。

回答by Mohit Tilva

As mentioned by removing the colon :and replacing with slash /before the sid worked for me.

正如在 sid 对我来说有效之前删除冒号:并替换为斜杠所提到的/。

I have had this issue before, too.

我以前也遇到过这个问题。

回答by Kshresthasan

In my case i just went through following steps in windows 10.

就我而言，我只是在 Windows 10 中完成了以下步骤。

  1. goto control panel
  2. click administrative
  3. click services
  4. find OracelServeceXE, OracleXEClrAgeng, OracleXETNSListener
  5. Right click and press Start/Restart
  6. After Completing Process. Check it will work or it will work ;)
  7. Done
  8. All the Best.
  1. 转到控制面板
  2. 单击管理
  3. 点击服务
  4. 找到OracelServeceXE, OracleXEClrAgeng,OracleXETNSListener
  5. 右键单击并按Start/Restart
  6. 完成流程后。检查它会起作用还是会起作用;）
  7. 完毕
  8. 祝一切顺利。

回答by ik_zelf

Your database, that apparently has the ORACLE_SID XE, is not defined in the listener.ora. That is no problem, since when the database normally opens, it will register itself to the default listener, being the one on port 1521 so that is ok.

您的数据库显然具有 ORACLE_SID XE，但未在 listener.ora 中定义。这没问题，因为当数据库正常打开时，它会将自己注册到默认侦听器，即端口 1521 上的侦听器，这样就可以了。

  1. is the database open?
  2. what is the start order of listener/database?
  3. is the error persistent?
  1. 数据库打开了吗？
  2. 侦听器/数据库的启动顺序是什么？
  3. 错误是否持续存在？

If the database starts before the listener, the database has no listener to register to. It will do so every few minutes so after a while, I expect the error will go away because of the registration has taken place. You can issue alter system register;to speed this. If the database is in restricted mode, the connections using a service will fail. You are using ORACLE_SID so that is not your problem.

如果数据库在侦听器之前启动，则数据库没有要注册的侦听器。它会每隔几分钟执行一次，所以过了一会儿，我希望错误会因为注册已经发生而消失。你可以发出alter system register;来加速这个。如果数据库处于受限模式，则使用服务的连接将失败。您正在使用 ORACLE_SID，所以这不是您的问题。

Also check the names in use. Is localhost resolving to the same address as Brodyaga-PC? In the jdbc string you use localhost and the listener listens om Brodyaga-PC. Is localhost 127.0.0.1 ?

还要检查正在使用的名称。localhost 是否解析为与 Brodyaga-PC 相同的地址？在 jdbc 字符串中，您使用 localhost 并且侦听器侦听 om Brodyaga-PC。本地主机是 127.0.0.1 吗？

相关推荐

oracle 如何在 SELECT FROM 语句中使用表类型？

oracle 如何在 SELECT FROM 语句中使用表类型？

oracle PL/SQL：从表中选择到关联数组

oracle PL/SQL：从表中选择到关联数组

oracle 从查询设置表单字段的值

oracle 从查询设置表单字段的值

oracle 甲骨文数转十进制

oracle 甲骨文数转十进制

相关推荐
最近更新
标签