ORA-12514: TNS:监听程序当前无法识别连接描述符中请求的服务
这个提示在一定程度上导致产生误解，让人认为数据库服务名SERVICE NAME本身有问题，其实它指的是tnsnames.ora中配置的别名ALIAS所对应的SERVICE_NAME与在监听程序listener的注册的服务名中找不到匹配的服务名，当然如果只是配置了动态监听，则可能一个也没有（也可能一个也没有，比如只配置了动态监听）。
tnsnames.ora中的服务名SERVICE_NAME的可选值为静态和动态注册的服务名（SERVICE_NAME）,也即listener.ora中的GLOBAL_DBNAME和PARAMETER中的SERVICE_NAME，可以是任意支持的字符，但两者必须与保持一致。而sqlplus中的链接字符串，并不是SERVICE_NAME，而是一个SERVICE_NAME的ALIAS(在tnsname.ora中配置）。
lsnrctl status的结果：
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "yangdb" has 1 instance(s).
Instance "yangdb", status UNKNOWN, has 1 handler(s) for this service...
listener.ora的内容：
(SID_DESC =
(GLOBAL_DBNAME = yangdb) --listener.ora文件中注册静态监听时的服务名，如上面的Service "yangdb"
(ORACLE_HOME = /opt/oracle/11.2.0/alifpre)
(SID_NAME = yangdb) --数据库的实例名
)
tnsnames.ora的内容
yangdb = --别名Alias，可以任意填写！sqlplus USER/PWD@yangdb 用的就是这个别名
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.241 )(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = yangdb) --可以任意填写，但是必须和listener.ora中的GLOBAL_DBNAME(静态监听)
) --或者动态监听时和lsnrctl status 中的"Service"的值一致
)
当然对于tnsnames.ora中的SERVICE_NAME 替换为SID的话，必须和lsnrctl status中的 Instance "yangdb" 一致！
使用service_name连接oracle 数据库的方式见《service_name和service_names的关系》一文，下面讨论一下我遇到的使用sid连接数据库的情况：
服务端：
oracle@dba-host1:/opt/oracle/products/9.2.0/network/admin>cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/products/9.2.0)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba-host1.dev.sd.aliyun.com)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
oracle@dba-host1:/opt/oracle/products/9.2.0/network/admin>cat tnsnames.ora
yang_TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.171)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = yang)
)
)
oracle@dba-host1:/home/oracle>lsnrctl status
LSNRCTL for Linux: Version 9.2.0.8.0 - Production on 11-NOV-2011 02:06:41
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dba-host1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dba-host1)(PORT=8080))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dba-host1)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "ASOFT" has 1 instance(s).
Instance "yang", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "yangXDB" has 1 instance(s).
Instance "yang", status READY, has 1 handler(s) for this service...
The command completed successfully
在本地的tnsnames.ora文件中service_name 指定 ASOFT
yang_TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.171 )(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = ASOFT)
)
)
或者知道SID为yang
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.171 )(PORT = 1521))
(CONNECT_DATA =
(SID=yang)
)
)
都可以连接到数据库，如果没有按照上面的例子来做，就会遇到 TNS-12154
使用windows的客户端连接
C:\Users\aaaa>sqlplus yang/yang@test
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 11 02:08:12 201
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
yang@rac1>EXIT
从 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production 断开
C:\Users\aaaa>