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

闲来无事，重温当年OCM的考题时，在配置完tnsnames.ora文件后，登录系统时遇到 ora-12514问题

tnsnames.ora中的配置为：

PROD1_S =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p1.example.com)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = shared)
      (SERVICE_NAME = PROD1)
    )
  )

使用此prod1_s来登录时报错：

[oracle@edbjr2p1 ~]$ sqlplus sys/oracle@PROD1_S as sysdba       

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 17 18:36:43 2020

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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


Enter user-name: 

监听怎么无法识别这个请求呢？

检查监听文件listener.ora

LSNR2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p1.example.com)(PORT = 1526))
    )
  )

SID_LIST_LSNER2=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=PROD1)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=PROD1))
)

重点是协议protocol,主机host,端口port都没有问题，listener与tns都是一一对应的；问题不在这里

顺便用tnsping测试一下，结果正常，说明我的监听和tnsname配置没有问题；

[oracle@edbjr2p1 ~]$ tnsping prod1_s

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 17-APR-2020 18:40:11

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p1.example.com)(PORT = 1526)) (CONNECT_DATA = (SERVER = shared) (SERVICE_NAME = PROD1)))
OK (0 msec)

这时想到，应该是这个监听没有在oracle数据库中注册；检查这个local_listerner中的配置果然没有1526的配置；

SYS@PROD1> show parameter local; --local_listener中只有1521没有1526的配置

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS = (PROTOCOL = TCP)(HO
                                                 ST = edbjr2p1.example.com)(POR
                                                 T = 1521))
log_archive_local_first              boolean     TRUE
parallel_force_local                 boolean     FALSE

看来问题就是出在这里的，重新修改local_listener的配置添加1526的配置，然后再重新注册

--在local_listener中添加上1526的配置
SYS@PROD1> alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=edbjr2p1.example.com)(PORT=1521))','(ADDRESS=(PROTOCOL=tcp)(HOST=edbjr2p1.example.com)(PORT=1526))';

System altered.

SYS@PROD1> alter system register; --配置完后要重新注册

System altered.

SYS@PROD1> exit

重新使用prod1_s来登录，这次顺利登录了

[oracle@edbjr2p1 ~]$ sqlplus sys/oracle@PROD1_S as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 17 18:46:31 2020

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@PROD1_S> 

因为这个tnsname配置的是共享登录，所以在session动态视图中可以看到它的登录记录

SYS@PROD1> select server from v$session;

SERVER
---------
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
SHARED    --这个就是prod1_s的登录session

25 rows selected.

小结：

关于oracle数据库的ORA-12514问题常见的原因有：

1.所要连接的数据库是否打开; 在linux系统中可使用ps -ef|grep pmon来判断是否有关于这个数据库的oracle进程；

2.listener文件配置是否正确；主要是协议，主机名，端口，服务名等，另外这个文件中的格式也要正确；

3.tnsname文件配置是否与listener文件中对应一致；

4.监听是否在数据库中进行了注册；