oracle客户端plsql developer连接不上报错ora-12154

今天尝试用plsql developer连接一个刚搭建好的oracle数据库,收到下图所示报错ora-12154
oracle客户端plsql developer连接不上报错ora-12154
oracle客户端plsql developer连接不上报错ora-12154
一般这个问题基本上就从以下两个方面考虑,
1)网络问题
2)本地tnsname.ora文件有问题。
首先检查网络:如下所示可以ping通。
oracle客户端plsql developer连接不上报错ora-12154
oracle客户端plsql developer连接不上报错ora-12154
然后找本地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客户端plsql developer连接不上报错ora-12154
oracle客户端plsql developer连接不上报错ora-12154
下面是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后面的名字(下面红色箭头的那两个都可以)
oracle客户端plsql developer连接不上报错ora-12154
oracle客户端plsql developer连接不上报错ora-12154
4,确保本地tnsname.ora文件配置中,没有语法错误,无效的括号或者多余的特殊字符。例如我的这次错误就是因为多个空格,
总结:在配置oracle的监听文件和tnsname.ora文件的时候,一定要格外注意,最好从官方文档拷贝,因为他们有严格的格式,稍不注意,就会出错,很难排查。
本站部分文章源于互联网,本着传播知识、有益学习和研究的目的进行的转载,为网友免费提供。如有著作权人或出版方提出异议,本站将立即删除。如果您对文章转载有任何疑问请告之我们,以便我们及时纠正。

PS:推荐一个微信公众号: askHarries 或者qq群:474807195,里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分析,高并发、高性能、分布式、微服务架构的原理,JVM性能优化这些成为架构师必备的知识体系。还能领取免费的学习资源,目前受益良多

转载请注明原文出处:Harries Blog™ » oracle客户端plsql developer连接不上报错ora-12154

赞 (0)
分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址