oracle客户端plsql developer连接不上报错ora-12154
今天尝试用plsql developer连接一个刚搭建好的oracle数据库，收到下图所示报错ora-12154
一般这个问题基本上就从以下两个方面考虑，
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前面可别有空格，赶紧去看，发现确实有空格，去掉就正常了， fuck!!!
下面是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文件的时候，一定要格外注意，最好从官方文档拷贝，因为他们有严格的格式，稍不注意，就会出错，很难排查。
正文到此结束
热门推荐
相关文章
Loading...
![[HBLOG]公众号](http://www.liuhaihua.cn/img/qrcode_gzh.jpg)
