问题: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

image.png

大概意思就是TNS监听器程序当前无法识别连接描述符中请求的服务。

问题分析:

1.首先默认的oracle配置文件中tnsnames.ora和listener.ora中默认监听的地址是localhost,如果删除了/etc/hosts中localhost的映射也会出现这个问题。
2.手动的修改listener.org和tnsnames.org文件
日志：/opt/oracle/product/11.2.0/dbhome_1/listener.log

解决方案

首先确定Oracle的安装位置： /opt/oracle/product/11.2.0/dbhome_1/

1修改listener.ora
vim /opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
修改前

# listener.ora Network Configuration File: /opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

ADR_BASE_LISTENER = /opt/oracle

修改后:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
        (GLOBAL_DBNAME = orcl)
        (ORACLE_HOME = /opt/oracle/product/11.2.0/dbhome_1)
        (SID_NAME = orcl)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.23.1.192)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /opt/oracle
image.png

说明: 也可以使用IP为HOST = 0.0.0.0

  1. 修改tnsnames.org
    vim /opt/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
    修改前
# tnsnames.ora Network Configuration File: /opt/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

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

修改后

# tnsnames.ora Network Configuration File: /opt/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.23.1.192)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = orcl)
    )
  )
image.png

3.重启监听器服务

$ lsnrctl stop
$ lsnrctl start
image.png

image.png

4.重启数据库
以sqlplus / as sysdba登录

$ sqlplus / as sysdba
SQL>shutdown immediate  #立即关闭数据库
SQL>startup   #开启数据库
SQL>alter system register;  #注册

到此，问题解决。

©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容