Oracle连接出现ora-12154-Mysql Tutorial-php.cn

Oracle连接出现ora-12154

WBOY

Release： 2016-06-07 17:46:02

Original

1476 people have browsed it

Oracle连接出现ora-12154

配置好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

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

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

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Mar 4 08:32:45 2008

ERROR:
ORA-12154: TNS:could not resolve service name

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
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

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Mar 4 08:32:59 2008

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

以上答案供参考