ORA-12154 TNS could not resolve the connect identifier specified

ORA-12154 & TNS-03505 案例分享

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):
// - Make sure that "TNSNAMES" is listed as one of the values of the
// NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA)
// - Verify that a TNSNAMES.ORA file exists and is in the proper
// directory and is accessible.
// - Check that the net service name used as the connect identifier
// exists in the TNSNAMES.ORA file.
// - 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.


应用服务器连接数据库出现”ORA-12154: TNS: 无法解析指定的连接标识符“。

应用服务器安装的ORACLE 11g客户端，tnsname里面的有如下配置，我们要通过它连接到目标服务器

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xxx.xxxx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xxxx)
)
)


此时连接数据库时，就会报ORA-12154: TNS: 无法解析指定的连接标识符。仔细检查过tnsname里面的配置，没有任何问题。

C:\Users>sqlplus test/test@TEST
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 5月 16 14:00:04 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS: 无法解析指定的连接标识符
请输入用户名:

接下来尝试一下tnsping是否正常，发现其报TNS-03505: Failed to resolve name。
两个服务器直接的网络是正常的。telnet 检测1521端口也是正常的。

C:\Users>tnsping TEST
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 16-5月 -
2016 14:20:44
Copyright (c) 1997, 2010, Oracle. All rights reserved.

已使用的参数文件:
C:\OracleClient\Oracle32\product\11.2.0\client_1\network\admin\sqlnet.ora
TNS-03505: 无法解析名称


是否很纳闷，那么环境变量是否正常呢？ 在David的这篇博客ORA-12154 和 TNS-03505 监听错误的解决方法里面有介绍。
echo %path% 查看环境变量都OK，而且也能使用sqlplus连接到其它数据库（后面述说），说明应用服务器的客户端环境是正常的。
https://blog.csdn.net/tianlesoftware/article/details/5716028


那么为什么会出现这样的诡异的问题呢？
其实是因为在客户端的sqlnet.ora配置文件里面，添加了一行"NAMES.DEFAULT_DOMAIN = db.test.com", 如下所示，

解决方法有两种：
1：修改tnsname.ora里面的配置信息,改为下面即可解决这个问题。此时使用TEST.db.test.com 或TEST都可以连接到数据库。
2：修改sqlnet.ora配置文件，删除这一行"NAMES.DEFAULT_DOMAIN = db.test.com"。

TEST.db.test.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xxx.xxxx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xxxx)
)
)


tnsping TEST.db.test.com
tnsping TEST


关于NAMES.DEFAULT_DOMAIN 请参考下面官方文档介绍。

NAMES.DEFAULT_DOMAIN

Purpose
To set the domain from which the client most often looks up names resolution requests.

Usage Notes
When this parameter is set, the default domain name is automatically appended to any unqualified net service name or service name.

For example, if the default domain is set to us.example.com, then the connect string CONNECT scott@sales gets searched as sales.us.example.com. If the connect string includes the domain extension, such as CONNECT scott@sales.us.example.com, then the domain is not appended to the string.

Default
None

Example
NAMES.DEFAULT_DOMAIN=example.com