ORA-12514: TNS:listener does not currently know of service requested in connect
转载 https://www.cnblogs.com/jianglaoda/p/9184371.html
1，登录报错如下：
[oracle@oracle_yueworld admin]$ rlwrap sqlplus system/system51@st1
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 3 16:33:26 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor2，查看下lsnrctl状态，看是否是lsnrctl监听的问题
[oracle@oracle_yueworld admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-DEC-2015 17:03:07
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.51)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 03-DEC-2015 17:02:55
Uptime 0 days 0 hr. 0 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/oracle_yueworld/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.121.51)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@oracle_yueworld admin]$
[oracle@oracle_yueworld admin]$ 从中可以看出监听正常，但是The listener supports no services这个貌似是不识别service_name啊
原blog地址：http://blog.csdn.net/mchdba/article/details/50166153
3，去查看下参数
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /oracle/app/oracle/oradata/pow
erdes, /oracle/app/oracle/orad
ata/orcl
db_name string orcl
db_unique_name string stunq
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
service_names string stunq
SQL> 看到db_name和db_unique_name以及service_names不一样
4，解决方案
在listener.ora文件里面添加SID_LIST_LISTENER，如下所示：
作用：以下红色的模块是静态注册监听服务，方便客户端远程连接该数据库使用
[oracle@oracle_yueworld admin]$ more listener.ora
# listener.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stunq)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = stunq)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.51)(PORT = 1521))
)
ADR_BASE_LISTENER = /oracle/app/oracle
[oracle@oracle_yueworld admin]$ 再重启lsnrctl服务:
[oracle@oracle_yueworld admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-DEC-2015 18:00:09
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/oracle_yueworld/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.121.51)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.51)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 03-DEC-2015 18:00:09
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/oracle_yueworld/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.121.51)(PORT=1521)))
Services Summary...
Service "stunq" has 1 instance(s).
Instance "stunq", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracle_yueworld admin]$ 看到The listener supports no services错误已经没有了，可以去试试登录
5，再次登录报错
[oracle@oracle_yueworld admin]$ rlwrap sqlplus orclk/st141118@st1
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 3 18:03:55 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0
Enter user-name: 6，去修改服务端和客户端的配置文件
google了很多资料，大部分都说是listener.ora配置错误导致listener找不到你要请求的sid。这有两种可能，一种是client端的tnsnames.ora中配置了错误的SID，一种是server端的listener.ora中配置错了SID。仔细检查，然后开始修改服务器listener.ora listener.ora，将SID_NAME修改成正确的instance_name为orcl
[oracle@oracle_yueworld admin]$ more listener.ora
# listener.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stunq)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
#PS: 这里面的SID_NAME是oracle实例名，GLOBAL_DBNAME是全局的dbname，是db_unique_name
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.51)(PORT = 1521))
)
ADR_BASE_LISTENER = /oracle/app/oracle
[oracle@oracle_yueworld admin]$ 修改客户端tnsnames.ora
[oracle@oracle_yueworld admin]$ vim tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
st1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.51)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stunq)
)
)
# PS：这里的service_name就是可以从执行 show parameter name;中显示的service_names参数值。7，然后验证登录，成功了，如下所示：
[oracle@oracle_yueworld admin]$
[oracle@oracle_yueworld admin]$ rlwrap sqlplus orclk/st141118@st1
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 3 19:43:10 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL> 
