ORA-12514错误求助：TNS监听器无法识别请求的服务

前端登录时触发错误：ORA-12514 TNS:listener does not currently know of service requested in connect descriptor。

已做排查：

• 多数方案指向修改tnsnames.ora的service_name，但通过命令行确认该名称正确
• 昨日系统仍正常运行，此前曾遇相同问题，上次通过修改主机名解决，但本次主机名已确认无误

本人是Oracle新手，不清楚还需提供哪些信息，若有需要请告知。

我的tnsnames.ora配置 #

# tnsnames.ora Network Configuration File: C:\oracle\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


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

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

我的listener.ora配置 #

# listener.ora Network Configuration File: C:\oracle\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oracle)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\bin\oraclr19.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PROLTPE15G4PS.Home)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

可尝试的排查步骤 #

• 检查监听器服务注册状态
  打开命令行执行 lsnrctl status，查看输出里的Services Summary部分是否包含orcl服务。如果没有，说明监听器未自动注册该服务。

• 手动注册服务
  执行 sqlplus / as sysdba 登录数据库，然后运行 alter system register;，之后再用lsnrctl status确认服务是否已注册。

• 统一主机配置
  listener.ora中监听器绑定的主机是PROLTPE15G4PS.Home，但tnsnames.ora用的是localhost。可以二选一调整：

  1. 将tnsnames.ora里的HOST = localhost改为HOST = PROLTPE15G4PS.Home
  2. 在listener.ora的LISTENER节点中添加localhost的监听地址：

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

  修改后重启监听器：先执行lsnrctl stop，再执行lsnrctl start。

• 确认Oracle核心服务状态
  在Windows服务列表中，检查OracleServiceORCL和OracleOraDB19Home1TNSListener是否处于启动状态，未启动则手动启动。

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