欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle连接出现ora-12154无法解析指定的连接标识符

程序员文章站 2022-11-23 13:51:52
配置好rac后,两个节点用tnsping都可以ping通,但用sqlplus连接都出现以下错误,不知道哪里出了问题 [oracle@rac2 admin]$ lsnrct...

配置好rac后,两个节点用tnsping都可以ping通,但用sqlplus连接都出现以下错误,不知道哪里出了问题
[oracle@rac2 admin]$ lsnrctl status
lsnrctl for linux: version 9.2.0.4.0 - production on 04-mar-2008 08:32:26
copyright (c) 1991, 2002, oracle corporation.  all rights reserved.
connecting to (description=(address=(protocol=tcp)(host=rac2)(port=1521)))
status of the listener
------------------------
alias                     listener
version                   tnslsnr for linux: version 9.2.0.4.0 - production
start date                04-mar-2008 08:29:03
uptime                    0 days 0 hr. 3 min. 22 sec
trace level               off
security                  off
snmp                      off
listener parameter file   /home/oracle/9.2.0.4/network/admin/listener.ora
listener log file         /home/oracle/9.2.0.4/network/log/listener.log
listening endpoints summary...
  (description=(address=(protocol=tcp)(host=rac2)(port=1521)))
services summary...
service "plsextproc" has 1 instance(s).
  instance "plsextproc", status unknown, has 1 handler(s) for this service...
service "esal" has 2 instance(s).
  instance "rac1", status ready, has 1 handler(s) for this service...
  instance "rac2", status ready, has 1 handler(s) for this service...
the command completed successfully
[oracle@rac2 admin]$ lsnrctl service
lsnrctl for linux: version 9.2.0.4.0 - production on 04-mar-2008 08:32:32
copyright (c) 1991, 2002, oracle corporation.  all rights reserved.
connecting to (description=(address=(protocol=tcp)(host=rac2)(port=1521)))
services summary...
service "plsextproc" has 1 instance(s).
  instance "plsextproc", status unknown, has 1 handler(s) for this service...
    handler(s):
      "dedicated" established:0 refused:0
         local server
service "esal" has 2 instance(s).
  instance "rac1", status ready, has 1 handler(s) for this service...
    handler(s):
      "dedicated" established:0 refused:0 state:ready
         remote server
         (address=(protocol=tcp)(host=rac1)(port=1521))
  instance "rac2", status ready, has 1 handler(s) for this service...
    handler(s):
      "dedicated" established:0 refused:0 state:ready
         local server
the command completed successfully
[oracle@rac2 admin]$ sqlplus cy/zh@esal
sql*plus: release 9.2.0.4.0 - production on tue mar 4 08:32:45 2008
copyright (c) 1982, 2002, oracle corporation.  all rights reserved.
error:
ora-12154: tns:could not resolve service name

enter user-name:
error:
ora-01017: invalid username/password; logon denied

enter user-name: cy/zh@rac1
error:
ora-12154: tns:could not resolve service name

sp2-0157: unable to connect to oracle after 3 attempts, exiting sql*plus
[oracle@rac2 admin]$ sqlplus cy/zh@rac2
sql*plus: release 9.2.0.4.0 - production on tue mar 4 08:32:59 2008
copyright (c) 1982, 2002, oracle corporation.  all rights reserved.
error:
ora-12154: tns:could not resolve service name

enter user-name:

按照如下过程,一般都能解决:
在虚拟机内开cmd,用lsnrctl status查看监听器监听的服务
如看到监听服务“orcl”,那在本机的tns配置中(service_name = orcl)必须写orcl;如果想要自定义,则在虚拟机内的listener配置中就要加入自定义服务名,记得要重启监听服务:lsnrctl reload
最后形成的配置文件如下
虚拟机listener:

sid_list_listener = 
 (sid_list = 
  (sid_desc = 
   (global_dbname = test)   自定义服务名 
   (oracle_home = e:oracleproduct10.2.0db_1) db_home 
   (sid_name = test)   
  ) 
  (sid_desc = 
   (program = extproc) 
   (sid_name = plsextproc) 
   (oracle_home = e:oracleproduct10.2.0db_1) ##这个服务保证你可以使用外部过程,如c程序 
  ) 
 )

本机tns:

orcl_xnj = 
 (description = 
  (address_list = 
   (address = (protocol = tcp)(host = 182.12.15.232)(port = 1521)) 
  ) 
  (connect_data = 
   (service_name = test) 与自定义服务名对应 
  ) 
 )

再次连接,问题解决!
记住,一定从最简单的原因找起,不要一上去就看到网上的什么改process数、注册表之类的,要想想本机能连为什么通过网络就不行,还是定位问题。
vs2008连接oracle 出现错误 ora-12154 tns 无法解析指定的连接标示符

在保证你的代码没有错误的前提下:
(一)检查oracleoradb10g_home1tnslistener 服务有没有启动,即监听服务
(二)检查tnsnames.ora是否添加连接串(建议用sqlplus或者pl/sql测试),其路径一般是:%oracle_home%product10.2.0db_1networkadmintnsnames.ora

其它网友的补充:

今天装oracle安装都完成了,但出现了“ora-12154 tns”的错误,最开始以为是oracle安装处了问题,但后来发现并不是,因为用sql plus可以登录,只是用plsql登录时才出现这错,用360完全卸载了qlsql后,从新装了次,还是那错,郁闷。。。后来又把数据库删掉重新建立,还是不行,整了半天,最后自己都整晕了,算了,重头再来吧。。。
百度下很多安装图解,按着来就成

oracle服务端安装图解

oracle客户端安装图解

oracle完全卸载

或者直接在注册表中按f3搜索oracle,删除注册文件,不过貌似文件有点多,操作有点慢,建议用上边链接的方法

总结了下要注意的几点

1.安装服务端是一般选“基本安装”就行
2.使用plsql developer时,“ora-12154 tns无法解析指定的连接标识符”的问题
安装时报后边的错“warning some oracle net versions cannot connect from a path with parentheses”,这错原来提示了,不能有括号,修改了安装路径就没有问题了
相应的上边的“ora-12154 tns无法解析指定的连接标识符”错也没啦,有点晕,没整明白,但确实好啦
3.“ora-12154 tns无法解析指定的连接标识符”按照网上的方法有两种,但我试了,在我这都不好使,下边贴出来,没准儿啥时候可以用
1.解决办法,修改plsql developer的选项设置:
 
客户端、服务器端oracle软件装好之后,用plsql devloper登录时,点击取消,进入plsql developer主界面,菜单tools-preferences,在设置窗口中,选择oracle-connection,对应设置窗口中,找到oracle home,选择oradb10g_home1,保存设置,重新登录plsql developer即可

2.修改d:\oracle\product\10.2.0\db_1\network\admin路径下的tnsnames.ora文件
###### 一般的配置 ##################################

oralocal =
 (description =
  (address_list =
   (address = (protocol = tcp)(host = 127.0.0.1)(port = 1521))
  )
  (connect_data =
   (server = dedicated)
   (service_name = oralocal)
  )
 )

以上答案供参考