Oracle连接出现ora-12154无法解析指定的连接标识符
配置好rac后,两个节点用tnsping都可以ping通,但用sqlplus连接都出现以下错误,不知道哪里出了问题
[oracle@rac2admin]$lsnrctlstatus
LSNRCTLforLinux:Version9.2.0.4.0-Productionon04-MAR-200808:32:26
Copyright(c)1991,2002,OracleCorporation. Allrightsreserved.
Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2)(PORT=1521)))
STATUSoftheLISTENER
------------------------
Alias LISTENER
Version TNSLSNRforLinux:Version9.2.0.4.0-Production
StartDate 04-MAR-200808:29:03
Uptime 0days0hr.3min.22sec
TraceLevel off
Security OFF
SNMP OFF
ListenerParameterFile /home/oracle/9.2.0.4/network/admin/listener.ora
ListenerLogFile /home/oracle/9.2.0.4/network/log/listener.log
ListeningEndpointsSummary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2)(PORT=1521)))
ServicesSummary...
Service"PLSExtProc"has1instance(s).
Instance"PLSExtProc",statusUNKNOWN,has1handler(s)forthisservice...
Service"esal"has2instance(s).
Instance"rac1",statusREADY,has1handler(s)forthisservice...
Instance"rac2",statusREADY,has1handler(s)forthisservice...
Thecommandcompletedsuccessfully
[oracle@rac2admin]$lsnrctlservice
LSNRCTLforLinux:Version9.2.0.4.0-Productionon04-MAR-200808:32:32
Copyright(c)1991,2002,OracleCorporation. Allrightsreserved.
Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2)(PORT=1521)))
ServicesSummary...
Service"PLSExtProc"has1instance(s).
Instance"PLSExtProc",statusUNKNOWN,has1handler(s)forthisservice...
Handler(s):
"DEDICATED"established:0refused:0
LOCALSERVER
Service"esal"has2instance(s).
Instance"rac1",statusREADY,has1handler(s)forthisservice...
Handler(s):
"DEDICATED"established:0refused:0state:ready
REMOTESERVER
(address=(protocol=tcp)(host=rac1)(port=1521))
Instance"rac2",statusREADY,has1handler(s)forthisservice...
Handler(s):
"DEDICATED"established:0refused:0state:ready
LOCALSERVER
Thecommandcompletedsuccessfully
[oracle@rac2admin]$sqlpluscy/zh@esal
SQL*Plus:Release9.2.0.4.0-ProductiononTueMar408:32:452008
Copyright(c)1982,2002,OracleCorporation. Allrightsreserved.
ERROR:
ORA-12154:TNS:couldnotresolveservicename
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
Enteruser-name:cy/zh@rac1
ERROR:
ORA-12154:TNS:couldnotresolveservicename
SP2-0157:unabletoCONNECTtoORACLEafter3attempts,exitingSQL*Plus
[oracle@rac2admin]$sqlpluscy/zh@rac2
SQL*Plus:Release9.2.0.4.0-ProductiononTueMar408:32:592008
Copyright(c)1982,2002,OracleCorporation. Allrightsreserved.
ERROR:
ORA-12154:TNS:couldnotresolveservicename
Enteruser-name:
按照如下过程,一般都能解决:
在虚拟机内开cmd,用lsnrctlstatus查看监听器监听的服务
如看到监听服务“orcl”,那在本机的tns配置中(SERVICE_NAME=orcl)必须写orcl;如果想要自定义,则在虚拟机内的listener配置中就要加入自定义服务名,记得要重启监听服务:lsnrctlreload
最后形成的配置文件如下
虚拟机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-12154tns无法解析指定的连接标示符
在保证你的代码没有错误的前提下:
(一)检查OracleOraDb10g_home1TNSListener服务有没有启动,即监听服务
(二)检查tnsnames.ora是否添加连接串(建议用SQLPLUS或者pl/sql测试),其路径一般是:%Oracle_home%product10.2.0db_1NETWORKADMINtnsnames.ora
其它网友的补充:
今天装oracle安装都完成了,但出现了“ORA-12154TNS”的错误,最开始以为是oracle安装处了问题,但后来发现并不是,因为用SQLPlus可以登录,只是用plsql登录时才出现这错,用360完全卸载了qlsql后,从新装了次,还是那错,郁闷。。。后来又把数据库删掉重新建立,还是不行,整了半天,最后自己都整晕了,算了,重头再来吧。。。
百度下很多安装图解,按着来就成
oracle服务端安装图解
https://www.nhooo.com/article/108555.htm
oracle客户端安装图解
https://www.nhooo.com/article/108556.htm
oracle完全卸载
https://www.nhooo.com/article/96395.htm
或者直接在注册表中按F3搜索oracle,删除注册文件,不过貌似文件有点多,操作有点慢,建议用上边链接的方法
总结了下要注意的几点
1.安装服务端是一般选“基本安装”就行
2.使用PLSQLDeveloper时,“ORA-12154TNS无法解析指定的连接标识符”的问题
安装时报后边的错“warningsomeoraclenetversionscannotconnectfromapathwithparentheses”,这错原来提示了,不能有括号,修改了安装路径就没有问题了
相应的上边的“ORA-12154TNS无法解析指定的连接标识符”错也没啦,有点晕,没整明白,但确实好啦
3.“ORA-12154TNS无法解析指定的连接标识符”按照网上的方法有两种,但我试了,在我这都不好使,下边贴出来,没准儿啥时候可以用
1.解决办法,修改PLSQLDeveloper的选项设置:
客户端、服务器端Oracle软件装好之后,用PLSQLDevloper登录时,点击取消,进入PLSQLDeveloper主界面,菜单Tools-Preferences,在设置窗口中,选择Oracle-Connection,对应设置窗口中,找到OracleHome,选择OraDb10g_home1,保存设置,重新登录PLSQLDeveloper即可
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) ) )
以上答案供参考