共计 3772 个字符，预计需要花费 10 分钟才能阅读完成。
今天尝试用 PL/SQL Developer 连接一个刚搭建好的 Oracle 数据库，收到下图所示报错 ORA-12154: TNS: 无法解析指定的连接标识符错误。
一般这个问题基本上就从以下两个方面考虑，
1）网络问题
2）本地 tnsname.ora 文件有问题。
首先检查网络：如下所示可以 ping 通。
然后找本地 tnsname.ora 文件错误，ip, 端口，服务名都没有问题，如下所示。。。。
看下监听的状态，注册到的服务就是 ora11g，端口就是 1521，ip 也没有错，
[oracle@JoinSpider ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 26-JUN-2017 18:30:29
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 – Production
Start Date 19-JUN-2017 17:34:50
Uptime 7 days 0 hr. 55 min. 39 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /files/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /files/oracle/diag/tnslsnr/JoinSpider/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=JoinSpider)(PORT=1521)))
Services Summary…
Service “ora11g” has 1 instance(s).
Instance “ora11g”, status READY, has 1 handler(s) for this service…
Service “ora11gXDB” has 1 instance(s).
Instance “ora11g”, status READY, has 1 handler(s) for this service…
The command completed successfully
本地 tnsname.ora 文件的 db239 配置
db239 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.239)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora11g)
)
)
找了半天也没找到原因，后来请教同学，给了我提示，说网络服务名 db239 前面可别有空格，赶紧去看，发现确实有空格，去掉就正常了。
下面是 oracle 给出的解决意见。。
[oracle@JoinSpider ~]$ oerr ora 12154
12154, 00000, “TNS:could not resolve the connect identifier specified”
// *Cause: A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a
// naming method repository, or the repository could not be
// located or reached.
// *Action:
// – If you are using local naming (TNSNAMES.ORA file):
// 1 – Make sure that “TNSNAMES” is listed as one of the values of the
// NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA)
// 2 – Verify that a TNSNAMES.ORA file exists and is in the proper
// directory and is accessible.
// 3 – Check that the net service name used as the connect identifier
// exists in the TNSNAMES.ORA file.
// 4 – Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
// file. Look for unmatched parentheses or stray characters. Errors
// in a TNSNAMES.ORA file may make it unusable.
// – If you are using directory naming:
// – Verify that “LDAP” is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// – Verify that the LDAP directory server is up and that it is
// accessible.
// – Verify that the net service name or database name used as the
// connect identifier is configured in the directory.
// – Verify that the default context being used is correct by
// specifying a fully qualified net service name or a full LDAP DN
// as the connect identifier
// – If you are using easy connect naming:
// – Verify that “EZCONNECT” is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// – Make sure the host, port and service name specified
// are correct.
// – Try enclosing the connect identifier in quote marks.
//
// See the Oracle Net Services Administrators Guide or the Oracle
// operating system specific guide for more information on naming.
我的是用的本地 TNSNAMES.ORA，也就是上面的红色部分解决办法，翻译如下：
1. 确保 SQLNET.ORA 文件的配置中有值：TNSNAMES，如下所示：
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
2，确保 TNSNAMES.ORA 文件存在，并且在 $ORACLE_HOME/network/admin 正确的路径下，并且是有权限读取。
3，检查网络服务名是不是监听注册到的服务名即可，也就是本地 tnsname.ora 文件中的 service_name= 监听状态 service 后面的名字（下面红色箭头的那两个都可以）
4，确保本地 tnsname.ora 文件配置中，没有语法错误，无效的括号或者多余的特殊字符。例如我的这次错误就是因为多个空格，
总结：在配置 oracle 的监听文件和 tnsname.ora 文件的时候，一定要格外注意，最好从官方文档拷贝，因为他们有严格的格式，稍不注意，就会出错，很难排查。
更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-06/145254.htm
正文完
星哥玩云-微信公众号
