通过SQL Developer连接时出现ora-12505错误 [英] ora-12505 error while connecting via SQL Developer

问题描述

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

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的设置:

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

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

On the server side, this is listener.ora:

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\app\Owner\product\12.1.0\dbhome_1)
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Owner\product\12.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)
     )
   )

还有tnsnames.ora:

And 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)
    )
  )

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

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:

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

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

2014年9月9日更新:

UPDATE Sept. 9 2014:

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

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

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\12.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???

推荐答案

不要使用SID，请使用SERVICE-您的示例显示为编辑器".

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

在12c上，如果您要连接到可插拔电源，则始终需要使用服务. SID将解析为容器数据库(CDB).

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).

请确认是否正确，在服务器上运行"lsnrctl status"命令，并检查侦听器正在侦听哪些实际服务.

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.

这篇关于通过SQL Developer连接时出现ora-12505错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！