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

Question

提问by Learner

I have installed Oracle 11g Express Edition Release 2 in my windows 7 64 bit OS and tried to execute JDBC program, then I got the following error:

我在 Windows 7 64 位操作系统中安装了 Oracle 11g Express Edition Release 2 并尝试执行 JDBC 程序，然后出现以下错误：

java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:412)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:531)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:221)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at com.jlcindia.jdbc.JDBCUtil.geOracleConnection(JDBCUtil.java:28)
    at Lab3O.main(Lab3O.java:15)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:385)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1042)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:301)
    ... 8 more

Answer 1

回答by Wade H

I fixed this issue by correcting my jdbc string.

我通过更正 jdbc 字符串解决了这个问题。

For example, the correct jdbc string should be...

例如，正确的 jdbc 字符串应该是...

jdbc:oracle:thin:@myserver:1521/XE

But the jdbs string I was using is ...

但是我使用的 jdbs 字符串是...

jdbc:oracle:thin:@myserver:1521:XE

(Note: between 1521and XEshould be a /)

（注意：1521和之间XE应该是/）

This bad jdbc string give me a ORA-12505 error too.

这个错误的 jdbc 字符串也给了我一个 ORA-12505 错误。

Answer 2

回答by Luke Woodward

There are a few things that can cause this problem, but before you get started with JDBC, you need to be sure that you can connect to the database using SQL*Plus. If you're not familiar with SQL*Plus, it's a command-line tool for connecting to Oracle databases that has been a standard part of Oracle for a long time and it is included with Oracle XE.

有几件事可能会导致此问题，但在开始使用 JDBC 之前，您需要确保可以使用 SQL*Plus 连接到数据库。如果您不熟悉 SQL*Plus，它是一个用于连接到 Oracle 数据库的命令行工具，长期以来一直是 Oracle 的标准部分，并且包含在 Oracle XE 中。

When connecting to an Oracle database using JDBC, you don't connect to the database directly. Instead, you connect to a TNS listener, which then connects you to the database. The error ORA-12505means that the listener was up and you could connect to it, but it couldn't connect you to the database because it doesn't know that that database is up. There are two reasons for this:

使用 JDBC 连接到 Oracle 数据库时，您不直接连接到数据库。相反，您连接到 TNS 侦听器，然后该侦听器将您连接到数据库。该错误ORA-12505意味着侦听器已启动并且您可以连接到它，但它无法将您连接到数据库，因为它不知道该数据库已启动。有两个原因：

the database has not been started up,
the database has not registered with the listener, e.g. because the database was started before the listener. (When the database starts, it registers itself with a listener if it is already running. If the listener isn't running, the database doesn't register itself, and if the listener starts, it doesn't go looking for databases that might register with it.)

数据库尚未启动，
数据库尚未向侦听器注册，例如因为数据库在侦听器之前启动。（当数据库启动时，如果它已经在运行，它会向一个监听器注册自己。如果监听器没有运行，数据库不会注册自己，如果监听器启动，它不会去寻找可能注册。）

ORA-12505 means that the listener knows about that database, but the listener hasn't received a notification from the database that the database is up. (If you were trying to connect to the wrong database, using the wrong SID, you would get an ORA-12154 error "TNS: could not resolve the connect identifier specified".)

ORA-12505 意味着侦听器知道该数据库，但侦听器尚未从数据库收到数据库已启动的通知。（如果您尝试连接到错误的数据库，使用错误的 SID，您将收到 ORA-12154 错误“TNS：无法解析指定的连接标识符”。）

What Oracle services are running in the Services snap-in? (Open this from Control Panel > Administrative Tools > Services, or just Start > Run > services.msc.) You need the services OracleServiceXE and OracleXETNSListener to be running.

服务管理单元中正在运行哪些 Oracle 服务？（从“控制面板”>“管理工具”>“服务”打开它，或者直接“开始”>“运行”>“ services.msc。）您需要运行 OracleServiceXE 和 OracleXETNSListener 服务。

If the services have both been started, can you connect to the database in SQL*Plus using any of the following at a command prompt? (I'm assuming you're running these on the machine you've installed Oracle XE on.)

如果这两个服务都已启动，您能否在命令提示符下使用以下任一命令连接到 SQL*Plus 中的数据库？（我假设您在安装了 Oracle XE 的机器上运行这些程序。）

sqlplus system/system-password@XE

sqlplus system/system-password

sqlplus / as sysdba

(Replace system-passwordwith the password you set for the SYS and SYSTEM users during the Oracle XE installation.)

（替换system-password为您在 Oracle XE 安装期间为 SYS 和 SYSTEM 用户设置的密码。）

The first of these three connect via the TNS listener, but the second two connect directly to the database without going via the listener, and only work if you're on the same machine as the database. If the first one fails but the other two succeed, then JDBC connections will also fail. If so, connect to the database using either of the other two and run ALTER SYSTEM REGISTER. Then exit from SQL*Plus and try the first form again.

这三个中的第一个通过 TNS 侦听器连接，但后两个直接连接到数据库而不通过侦听器，并且仅当您与数据库在同一台机器上时才有效。如果第一个失败而其他两个成功，那么 JDBC 连接也将失败。如果是这样，请使用其他两个连接到数据库并运行ALTER SYSTEM REGISTER. 然后退出 SQL*Plus 并再次尝试第一种形式。

If the third one fails but the second one works, add your user account to the ora_dba group. Do this in Control Panel > Computer Management > Local Users and Groups.

如果第三个失败但第二个有效，请将您的用户帐户添加到 ora_dba 组。在控制面板 > 计算机管理 > 本地用户和组中执行此操作。

Once you can get connections of the form

一旦你可以获得表格的连接

sqlplus system/system-password@XE

to work, you ought to be able to connect to Oracle XE via JDBC. (Incidentally, you haven't shown us the JDBC code you're using to connect to the database, but I would suspect that it is quite probably correct; there would be various other errors if parts of the connection string were wrong.)

要工作，您应该能够通过 JDBC 连接到 Oracle XE。（顺便说一下，您没有向我们展示您用来连接到数据库的 JDBC 代码，但我怀疑它很可能是正确的；如果连接字符串的某些部分错误，则会出现各种其他错误。）

Answer 3

回答by Raj

I too got the same error but when tried all the three of them failed. If the above three fails.Try LSNRCTL status if you find the service (XE in my case)missing try this

我也遇到了同样的错误，但是当尝试时，他们三个都失败了。如果以上三个失败。如果您发现服务（在我的情况下为 XE）丢失，请尝试 LSNRCTL 状态，请尝试此

sqlplus /nolog
conn  system
alter system register;  
exit  
lsnrctl status

Now you can see the service
Even if don't see try this one out

现在你可以看到服务
即使没有看到试试这个

sqlplus /nolog  
conn system  
alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope = both;  
alter system register;  
exit  
lsnrctl status

This should probably work ...

这应该可以工作...

Answer 4

回答by C Nageswara Reddy

When your getting this error "ORA-12505, TNS:listener does not currently know of SID given in connect descriptor"

当您收到此错误“ORA-12505，TNS:listener 目前不知道连接描述符中给出的 SID”时

Solution: Open Services, and start OracleServiceXE, after that try to connect...

解决方案：打开服务，然后启动OracleServiceXE，然后尝试连接...

Answer 5

回答by rajeesh

I found some reasons for this exception.they are

我发现了这个异常的一些原因。他们是

1)The name of the database XE by default.so the url will be"jdbc:oracle:thin:@localhost:1521:XE".

1)默认数据库XE的名称。所以url将为“ jdbc:oracle:thin:@localhost:1521:XE”。

2)Make sure that OracleServiceXE,OracleXETNSListener is running.it will be in Control Panel\All Control Panel Items\Administrative Tools\Services

2) 确保 OracleServiceXE,OracleXETNSListener 正在运行。它将位于Control Panel\All Control Panel Items\Administrative Tools\Services

Answer 6

回答by javabeginner

I solved this issue by correcting my JDBC code.

我通过更正我的 JDBC 代码解决了这个问题。

the correct JDBC string should be...

正确的 JDBC 字符串应该是...

conection = DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:xe","system","ishantyagi");

But the JDBC string I was using was ...

但是我使用的 JDBC 字符串是......

conection = DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:orcl","system","ishantyagi");

So, the mistake of specifying orcl instead of xe showed this error as the SID name was wrong.

因此，指定 orcl 而不是 xe 的错误显示此错误，因为 SID 名称错误。

Answer 7

回答by Rasheed Puttur

My issue is resolved when I use the below code:

当我使用以下代码时，我的问题得到解决：

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@IPAddress:1521/servicename","userName","Password");

Answer 8

回答by Nirmal Mangal

Faced similar error, any of the above solutions didn't help. There was a problem in the listner.ora file. By mistake I had added SIDout of the SID_LISTsee below(section between the stars *).

面对类似的错误，上述任何解决方案都没有帮助。listner.ora 文件中存在问题。我错误地添加SID了SID_LIST下面的内容（星星之间的部分*）。

 SID_LIST_LISTENER =
      (SID_LIST =

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

 *(SID_DESC =
           (SID_NAME = XE)
           (ORACLE_HOME = C:\oraclexe\app\oracle\product.2.0\server)
         )*
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
        )
      )


    DEFAULT_SERVICE_LISTENER = (XE)

Corrected this error as below:

更正此错误如下：

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 = C:\oraclexe\app\oracle\product.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product.2.0\server)
      (PROGRAM = extproc)
    )
    )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  )


DEFAULT_SERVICE_LISTENER = (XE)

Stopped and the database

停止和数据库

Stopped the listeners OracleServiceXE and OracleXETNSListener manually as it did not stop automatically by going to Control Panel\All Control Panel Items\Administrative Tools\Services. Restarted the database and it worked like a charm.

手动停止侦听器 OracleServiceXE 和 OracleXETNSListener，因为它不会通过转到控制面板\所有控制面板项目\管理工具\服务自动停止。重新启动数据库，它就像一个魅力。

Answer 9

回答by jeton

Oracle: Thin-style Service Name Syntax

Oracle：精简式服务名称语法

Thin-style service names are supported only by the JDBC Thin driver. The syntax is:

只有 JDBC 瘦驱动程序支持瘦风格的服务名称。语法是：

@//host_name:port_number/service_name

http://docs.oracle.com/cd/B28359_01/java.111/b31224/urls.htm#BEIDHCBA

Answer 10

回答by Jose Rego

i initially came here with the same problem. I had jus installed Oracle 12c on Windows 8 (64-bit),but i have since resolved it by 'TNSPING xe' on the command line... If the connection isn't established or name not found,try the database name,in my case it was 'orcl'... 'TNSPING orcl' again and if it pings successfully then u need to change the SID to 'orcl' in this case (or whatever database name u used)...

我最初带着同样的问题来到这里。我刚刚在 Windows 8（64 位）上安装了 Oracle 12c，但我已经通过命令行上的“TNSPING xe”解决了它......如果没有建立连接或找不到名称，请尝试数据库名称，在我的情况下它是'orcl'...再次'TNSPING orcl'，如果它成功ping然后你需要在这种情况下将SID更改为'orcl'（或你使用的任何数据库名称）......

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

提问by Learner

回答by Wade H

回答by Luke Woodward

回答by Raj

回答by C Nageswara Reddy

回答by rajeesh

回答by javabeginner

回答by Rasheed Puttur

回答by Nirmal Mangal

回答by jeton

回答by Jose Rego

相关推荐

最近更新

标签

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

提问by Learner

回答by Wade H

回答by Luke Woodward

回答by Raj

回答by C Nageswara Reddy

回答by rajeesh

回答by javabeginner

回答by Rasheed Puttur

回答by Nirmal Mangal

回答by jeton

回答by Jose Rego

相关推荐

为什么我在格式化之后的 NUMBER 列中得到 ####... Oracle？

连接到 Oracle 数据库

Oracle 案例

Oracle 限制每个用户的同时连接数

相关推荐

最近更新

标签