无法从Oracle SQL Developer连接Oracle 21c数据库——监听问题求助

问题概述 #

配置Oracle SQL Developer连接21c数据库时失败，经排查数据库本身运行正常，但监听器无法启动，导致本地/远程工具无法连接。

已完成的排查细节 #

• 执行lsnrctl status返回超时及无监听器错误：

  C:\Windows\System32>lsnrctl status
  
  LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 18-NOV-2022 20:24:49
  
  Copyright (c) 1991, 2021, Oracle.  All rights reserved.
  
  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.207)(PORT=1521)))
  TNS-12535: TNS:operation timed out
   TNS-12560: TNS:protocol adapter error
    TNS-00505: Operation timed out
     64-bit Windows Error: 60: Unknown error
  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
  TNS-12541: TNS:no listener
   TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
     64-bit Windows Error: 2: No such file or directory
  

• 执行lsnrctl start时启动失败，核心错误为TNS-12545: Connect failed because target host or object does not exist：

  C:\Windows\System32>lsnrctl start
  
  LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 18-NOV-2022 20:26:01
  
  Copyright (c) 1991, 2021, Oracle.  All rights reserved.
  
  Starting tnslsnr: please wait...
  
  TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production
  System parameter file is C:\app\patil\product\21c\homes\OraDB21Home1\network\admin\listener.ora
  Log messages written to C:\app\patil\product\21c\diag\tnslsnr\Nisarg\listener\alert\log.xml
  Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.207)(PORT=1521)))
  TNS-12545: Connect failed because target host or object does not exist
   TNS-12560: TNS:protocol adapter error
    TNS-00515: Connect failed because target host or object does not exist
     64-bit Windows Error: 49: Unknown error
  
  Listener failed to start. See the error message(s) above...
  

• 本地通过sqlplus / as sysdba验证数据库正常运行，PDB处于读写状态：

  Microsoft Windows [Version 10.0.22621.819]
  (c) Microsoft Corporation. All rights reserved.
  
  C:\Users\patil>sqlplus / as sysdba
  
  SQL*Plus: Release 21.0.0.0.0 - Production on Fri Nov 18 20:37:34 2022
  Version 21.3.0.0.0
  
  Copyright (c) 1982, 2021, Oracle.  All rights reserved.
  
  
  Connected to:
  Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
  Version 21.3.0.0.0
  
  SQL> select name, open_mode from v$pdbs;
  
  NAME
  --------------------------------------------------------------------------------
  OPEN_MODE
  ----------
  PDB$SEED
  READ ONLY
  
  XEPDB1
  READ WRITE
  
  
  SQL>
  

• 数据库相关服务运行正常
• Oracle SQL Developer连接时提示无法连接到指定主机与端口

解决方案步骤 #

1. 修正listener.ora配置文件 #

监听器启动失败的核心原因是配置的IP192.168.1.207无法被本机绑定，按以下步骤修改：

• 打开路径C:\app\patil\product\21c\homes\OraDB21Home1\network\admin\listener.ora
• 将HOST字段修改为以下任一选项：
  • localhost：仅允许本地连接
  • 本机实际主机名：通过hostname命令获取
  • 0.0.0.0：允许绑定所有网卡，支持远程连接
• 修改后的示例配置：

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

2. 重启监听器服务 #

在管理员权限的命令提示符中执行：

lsnrctl stop
lsnrctl start

执行lsnrctl status确认监听器启动成功，正常输出应包含STATUS: READY。

3. 手动注册数据库服务 #

如果监听器启动后数据库未自动注册，通过sqlplus执行：

sqlplus / as sysdba
ALTER SYSTEM REGISTER;

再执行lsnrctl services，检查输出中是否存在XEPDB1或对应的数据库服务。

4. 检查防火墙与端口 #

• 确认Windows防火墙允许1521端口的入站和出站连接，可临时关闭防火墙测试是否为拦截问题
• 执行netstat -ano | findstr 1521，确认端口未被其他程序占用

5. 验证SQL Developer连接 #

监听器正常启动后，在SQL Developer中配置连接：

• 主机名填写localhost或修改后的IP/主机名
• 端口保持1521
• 服务名填写XEPDB1（根据之前的查询结果）
• 输入用户名和密码测试连接

内容的提问来源于stack exchange，提问作者Nisarg Patil