oracle 通过 SQL Developer 连接时出现 ora-12505 错误

ora-12505 error while connecting via SQL Developer

提问by Alan

I'm trying to connect remotely to Oracle 12c database with SQL Developer. In order to connect remotely from another computer, on the computer running Oracle I opened a port in the Windows 7 Firewall. That part worked, but now the listener isn't letting me in due to this error ORA-12505. It is saying it doesn't recognize the SID I provided when I try to connect with SQL Developer in the remote computer. I even tried setting service name to "editor", but still nothing.

我正在尝试使用 SQL Developer 远程连接到 Oracle 12c 数据库。为了从另一台计算机远程连接，在运行 Oracle 的计算机上，我在 Windows 7 防火墙中打开了一个端口。那部分工作了，但现在由于这个错误 ORA-12505，听众不允许我进入。据说当我尝试在远程计算机上连接 SQL Developer 时，它无法识别我提供的 SID。我什至尝试将服务名称设置为“编辑器”，但仍然没有。

Following are the setting from SQL Developer on the remote computer:

以下是远程计算机上 SQL Developer 的设置：

On the server side, this is listener.ora:

在服务器端，这是 listener.ora：

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\app\Owner\product.1.0\dbhome_1)
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Owner\product.1.0\dbhome_1\bin\oraclr12.dll")
  )
)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (SERVICE_NAME = editor)
    )
 )

 REMOTE_LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.19)(PORT = 1531))
       (SERVICE_NAME = editor)
     )
   )

And tnsnames.ora:

和 tnsnames.ora：

EDITOR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.19)(PORT = 1531))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = editor)
    )
  )

  LISTENER_EDITOR =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = editor)
    )
  )


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

You'll notice that the default listener is set to localhost on port 1521. As long as that stays like that, I can connect on the server with SQL Developer. So in order to connect remotely, I setup a second listener set for port 1531 and entered the IP address of the server. The firewall has also been setup to allow a connection through port 1531. As you can see, I did edit the tnsnames.ora file a bit to allow for a connection to the Editor database, but my edit didn't seem to fix anything. I still can't connect with SQL Developer on the client side. On the server, I tried using the Oracle Net Configuration Assistant to test the Editor entry and wound up with error message:

您会注意到默认侦听器在端口 1521 上设置为 localhost。只要保持这样，我就可以使用 SQL Developer 连接到服务器。因此，为了远程连接，我为端口 1531 设置了第二个侦听器并输入了服务器的 IP 地址。防火墙也已设置为允许通过端口 1531 进行连接。如您所见，我确实编辑了 tnsnames.ora 文件以允许连接到编辑器数据库，但我的编辑似乎没有解决任何问题。我仍然无法在客户端连接 SQL Developer。在服务器上，我尝试使用 Oracle Net Configuration Assistant 来测试 Editor 条目并最终得到错误消息：

ORA-12514 Listener does not currently know of service requested in connect descriptor.

ORA-12514 侦听器当前不知道连接描述符中请求的服务。

UPDATE Sept. 9 2014:

2014 年 9 月 9 日更新：

I was asked to run lsnrctl status from the command prompt. Following is the output from that command:

我被要求从命令提示符运行 lsnrctl status。以下是该命令的输出：

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))(SERVICE_NAM
E=editor))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.1.0 - Produ
ction
Start Date                09-SEP-2014 14:33:06
Uptime                    0 days 4 hr. 14 min. 38 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\Owner\product.1.0\dbhome_1\network\admin\lis
tener.ora
Listener Log File         C:\app\Owner\diag\tnslsnr\Shiers-PC\listener\alert\log
.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\EXTPROC1521ipc))(SERVIC
E_NAME=editor))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))(SERVICE_NAME=
editor))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=Shiers-PC)(PORT=5500))(Security=(my
_wallet_directory=C:\APP\OWNER\admin\editor\xdb_wallet))(Presentation=HTTP)(Sess
ion=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "editor" has 1 instance(s).
  Instance "editor", status READY, has 1 handler(s) for this service...
Service "editorXDB" has 1 instance(s).
  Instance "editor", status READY, has 1 handler(s) for this service...
Service "pdborcl" has 1 instance(s).
  Instance "editor", status READY, has 1 handler(s) for this service...
The command completed successfully

OK...so what am I supposed to do with this???

好吧……那我该怎么办？？？

回答by thatjeffsmith

Don't use the SID, use the SERVICE - from what your example shows, 'editor'.

不要使用 SID，使用 SERVICE - 从你的例子中显示，'editor'。

On 12c if you are connecting to a pluggable then you will ALWAYS need to use service. The SID will resolve to the Container Database (CDB).

在 12c 上，如果您连接到可插拔设备，那么您将始终需要使用服务。SID 将解析为容器数据库 (CDB)。

Ton confirm that is right, run the 'lsnrctl status' command on your server, and check out what the actual services are being listened to by the listener.

Ton 确认这是正确的，在您的服务器上运行 'lsnrctl status' 命令，并检查侦听器正在侦听的实际服务。

回答by DallasB

Forgive me if I don't understand the question properly. Are there 3 machines involved. Client, listener and database ? If you are just trying to advertise the database on port 1531 then modify the LISTENER section in the listener.ora file to include an additional port

如果我没有正确理解问题，请原谅我。是否涉及3台机器。客户端、监听器和数据库 ? 如果您只是尝试在端口 1531 上通告数据库，请修改 listener.ora 文件中的 LISTENER 部分以包含其他端口

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1531))
(SERVICE_NAME = editor)
)
)

回答by Scotty Boy

I am using SQL Developer on the remote computer. The image above is the dialog box I'm using to try to connect. I went looking for a file on the remote computer named tnsnames.ora and didn't find any. – Alan Sep 7 at 13:58

我在远程计算机上使用 SQL Developer。上图是我用来尝试连接的对话框。我在名为 tnsnames.ora 的远程计算机上寻找一个文件，但没有找到。– 艾伦 9 月 7 日 13:58

You need an Oracle Client on the remote client computer with the same tnsnames.ora file as you have on the server.

您需要远程客户端计算机上的 Oracle 客户端具有与服务器上相同的 tnsnames.ora 文件。