SQL Plus可正常连接，但SQL Developer报ORA-12505 TNS监听错误

问题背景 #

Windows Server 2019上安装Oracle 19c Enterprise后，SQL Plus用SYS as SYSDBA可正常连接并执行查询，但SQL Developer连接时报错：

Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

现有配置文件 #

tnsnames.ora（手动创建） #

TEST_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )

listener.ora（默认配置+手动添加一行） #

# listener.ora Network Configuration File: C:\Users\testUser\Downloads\WINDOWS.X64_193000_db_home\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

DEFAULT_SERVICE_LISTENER = XE  # 手动添加的行

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Users\testUser\Downloads\WINDOWS.X64_193000_db_home)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Users\testUser\Downloads\WINDOWS.X64_193000_db_home\bin\oraclr19.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

服务与监听器状态 #

服务器重启后所有Oracle服务运行，执行lsnrctl status和lsnrctl start的结果：

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                12-JUL-2024 16:04:43
Uptime                    0 days 0 hr. 5 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\Users\testUser\Downloads\WINDOWS.X64_193000_db_home\network\admin\listener.ora
Listener Log File         C:\Oracle19c\diag\tnslsnr\V-LABAPPDB2\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

C:\Windows\system32>lsnrctl start

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 12-JUL-2024 16:11:12

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

TNS-01106: Listener using listener name LISTENER has already been started

已尝试的排查操作 #

• SQL Developer连接组合：用户名用sys as sysdba、sys（设置Role为SYSDBA）；主机名用localhost和专用IP；端口1522；SID试TEST和TEST_DB，均失败。
• 执行tnsping test_db结果：

Used parameter files:
C:\Users\testUser\Downloads\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = LCM)))
OK (0 msec)

• 尝试tnsping localhost 1522无效，tnsping localhost默认走1521端口。

解决方案步骤 #

1. 确认数据库实例的实际服务名/SID #

用SQL Plus登录后执行以下命令，获取正确的服务名和SID：

-- 获取服务名
SELECT value FROM v$parameter WHERE name = 'service_names';
-- 获取SID
SELECT instance_name FROM v$instance;

注：从tnsping结果看，实际尝试的是SERVICE_NAME = LCM，但tnsnames.ora里配置的是TEST，这里存在不一致，需以数据库实际返回值为准

2. 更新listener.ora，添加数据库实例到监听器 #

修改SID_LIST_LISTENER部分，添加你的数据库实例描述（替换<实际SID>为步骤1获取的SID）：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Users\testUser\Downloads\WINDOWS.X64_193000_db_home)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Users\testUser\Downloads\WINDOWS.X64_193000_db_home\bin\oraclr19.dll")
    )
    (SID_DESC =
      (SID_NAME = <实际SID>)
      (ORACLE_HOME = C:\Users\testUser\Downloads\WINDOWS.X64_193000_db_home)
    )
  )

3. 重启监听器 #

执行命令：

lsnrctl stop
lsnrctl start

再次执行lsnrctl status，检查Services Summary中是否出现你的数据库服务。

4. 修正tnsnames.ora配置 #

确保tnsnames.ora中的SERVICE_NAME或SID与数据库实际值一致：

• 用服务名连接（推荐）：

TEST_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <实际服务名>)
    )
  )

• 用SID连接：

TEST_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SID = <实际SID>)
    )
  )

5. 配置SQL Developer连接 #

• 选择TCP/IP连接类型
• 主机名：localhost（或服务器IP）
• 端口：1522
• 连接类型：若用服务名则选Service Name并填写实际服务名；若用SID则选SID并填写实际SID
• 用户名：SYS，角色选择SYSDBA

额外检查项 #

• 确保Oracle数据库服务（OracleService<实际SID>）处于运行状态
• 若用远程IP连接，检查防火墙是否开放1522端口
• 确认ORACLE_HOME环境变量指向路径：C:\Users\testUser\Downloads\WINDOWS.X64_193000_db_home

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