Oracle SQL Developer:失败 - 测试失败:网络适配器无法建立连接?

2021-12-30 00:00:00 oracle11g database-connection oracle oracle-sqldeveloper

请注意，为了安全起见，我更改了详细信息.然而，问题依然存在.

Please note that I changed details for security purposes. However, the problem remains intact.

我在服务器上安装了一个 Oracle 11g 数据库，例如 herp-devDV.derp.edu.现在我在某个位置的服务器上有另一个 Oracle 11g 数据库，例如，derp-db.derp.edu.

I installed an Oracle 11g database on a server at location, say, herp-devDV.derp.edu. Now I have another Oracle 11g database on a server at location, say, derp-db.derp.edu.

我在 Oracle SQL Developer 中为 herp-devDV.derp.edu 和 derp-db.derp.edu 输入了连接名称、用户名、密码、主机名和服务名称.我可以连接到 derp-db.derp.edu 中的模式，但不能连接到 herp-devDV.derp.edu.它给了我这个消息:

I entered the connection name, username, password, hostname, and service name in Oracle SQL developer for both herp-devDV.derp.edu and derp-db.derp.edu. I can connect to schema in derp-db.derp.edu, but not herp-devDV.derp.edu. It gives me this message: 

Failure - Test failed: The Network Adapter could not establish the connection

详情

我有以下信息:

  • 当我 telnet 时，任一服务器都没有打开端口 1521
  • 我的侦听器已为两者启动并运行.
  • 我可以在 Oracle SQL Developer 上访问 derp-db.derp.edu.
  • Oracle 客户端在我的本地机器上 + Oracle SQL Developer
  • 我可以将桌面远程连接到两台服务器
  • 谷歌搜索
  • Stackoverflow
  • 运行停止和启动lnrctl命令
  • 在 herp-devDB.derp.edu 上，我运行了 lsnrctl status

我收到以下输出

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                03-JUN-2014 13:37:22
Uptime                    6 days 0 hr. 53 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:oracleproduct11.2.0dbhome_1
etworkadminlistener.ora
Listener Log File         d:oraclediag	nslsnrHERP-DEVDBlisteneralertlog.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HERP-DEVDB.derp.edu)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "HERPDEVDBXDB" has 1 instance(s).
  Instance "herpdevdb", status READY, has 1 handler(s) for this service...
Service "herpdevdb" has 1 instance(s).
  Instance "herpdevdb", status READY, has 1 handler(s) for this service...
The command completed successfully

然后我查看我的 listener.ora 并找到

I then check out my listener.ora and find

  SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:oracleproduct11.2.0dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:oracleproduct11.2.0dbhome_1inoraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = HERP-DEVDB.derp.edu)(PORT = 1521))
    )
  )

在这一点上，我承认我正在挠头，因为我没有看到任何突出的东西并告诉我为什么这不应该起作用.

At this point, I confess I am scratching my head as I don't see anything sticking out and telling me why this should not be working.

唯一的线索是当我检查 derp-db.derp.edu 并运行命令 lsnrctl status 时.请见下面的摘录:

The only clue is when I check derp-db.derp.edu and run the command lsnrctl status. Please see excerpt below: 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DERP-DB.edu)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
Start Date                18-MAY-2014 02:19:01
Uptime                    22 days 12 hr. 23 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
---etc----

所以我还在挠头.为什么derp-db 连接到DERP-DB.edu 而herp-devDB 连接到EXTPROC1521?我该如何解决?这两个服务器之间的 listener.ora 和其他文件几乎相同，只是实例的名称不同.嗯.

So I am still scratching my head. Why would derp-db be connecting to the DERP-DB.edu but herp-devDB is connecting to EXTPROC1521? How do I fix this? The listener.ora and other files between these two servers are almost identical except for the name of the instances. Hmmm.

  • 我在 herp 中更改了 listener.ora.这并没有解决它.

  • I changed the listener.ora in herp. This didn't fix it.

我无法在端口 1521 上 telnet 到 derp-db.derp.edu.但我仍然可以使用 SQL Developer 连接到它?什么?

I cannot telnet into derp-db.derp.edu on port 1521. But I can still connect to it with SQL Developer? Wth?

无论如何，我们将不胜感激.谢谢

Anyway assistance would be greatly appreciated. Thanks

问候，极客

推荐答案

我回答这个是为了未来社区用户的利益.有多个问题.如果您遇到此问题，建议您查找以下内容:

I am answering this for the benefit of future community users. There were multiple issues. If you encounter this problem, I suggest you look for the following:

  • 确保您的 tnsnames.ora 是完整的并且具有您想要连接的数据库
  • 确保您可以对要连接的服务器进行 tnsping
  • 在服务器上，确保它将在您使用的特定应用程序所需的端口上打开.

一旦我做了这三件事，我的问题就解决了.

Once I did these three things, I solved my problem.

相关文章