转载

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文件的时候，一定要格外注意，最好从官方文档拷贝，因为他们有严格的格式，稍不注意，就会出错，很难排查。

正文到此结束

所属分类：编程技术

本文标签： UI value Security 翻译 Oracle CTO Connection IO db 配置 key ACE ldap description TCP Developer ORM db2 sql XML http linux tar IDE 总结 Service 端口 cat App src 数据 list Action tab 数据库 operating system example id ip
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。