TNSNAMES已正确配置RACDB，sqlplus user/pass@RACDB一直报错ORA-12154

最新推荐文章于 2021-04-12 22:32:54 发布

转载最新推荐文章于 2021-04-12 22:32:54 发布 · 412 阅读

文章标签：

本文介绍了解决ORA-12154错误的过程，该错误出现在尝试使用新创建的操作系统用户连接到Oracle数据库时。通过检查监听状态、tnsnames.ora文件权限及OS用户组设置，最终确定问题是由于新用户缺少必要的文件读取权限。

一、问题现象
模拟真实生产环境，新建一个操作系统用户test,并设置ORACLE_HOME,ORACLE_SID后，运行以下命令，报错ORA-12154
[test@node2 ~]$ sqlplus system/oracle@RACDB
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 23 16:59:22 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified。

在同一台机器，使用OS用户oracle进行连接，可以正常联接。
[oracle@node2 ~]$ sqlplus system/oracle@RACDB
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 23 17:05:10 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select status from v$instance;
STATUS
------------
OPEN

二、基础环境配置
[oracle@node2 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACDB.us.oracle.com)
)
)

三、分析过程
1.查看监听是否正常
[grid@node2 ~]$ lsnrctl status LISTENER_SCAN1
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 23-JAN-2018 17:07:25
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias LISTENER_SCAN1
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 23-JAN-2018 16:56:39
Uptime 0 days 0 hr. 10 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/11.2.0/grid/log/diag/tnslsnr/node2/listener_scan1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.118.38)(PORT=1521)))
Services Summary...
Service "RACDB.us.oracle.com" has 1 instance(s).
Instance "RACDB2", status READY, has 1 handler(s) for this service...
Service "RACDBXDB.us.oracle.com" has 1 instance(s).
Instance "RACDB2", status READY, has 1 handler(s) for this service...
The command completed successfully

基于以上内容来看，未发现问题，说明监听没有问题。

2、查看$ORACLE_HOME/network/admin/tnsnames.ora文件权限
[oracle@node2 ~]$ ll $ORACLE_HOME/network/admin/tnsnames.ora
-rw-r----- 1 oracle oinstall 357 Jan 23 10:50 /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

3、查看OS系统用户oracle和test的组是否正确
[oracle@node2 ~]$ id oracle
uid=500(oracle) gid=513(oinstall) groups=513(oinstall),511(asmdba),514(dba)
[oracle@node2 ~]$ id test
uid=1102(test) gid=1102(test) groups=1102(test)

oracle有权读取tnsnames.ora,但test不在oinstall组，属于other组，从tnsnames.ora的权限来看，test用户应该没有读取权限，难道是权限问题？

4.确认是否是tnsnames.ora文件权限问题
运行以下命令修改tnsnames.ora文件的权限
[oracle@node2 ~]$ chmod o+r $ORACLE_HOME/network/admin/tnsnames.ora

再次用test用户运行sqlplus，一切正常。
[test@node2 ~]$ sqlplus system/oracle@RACDB
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 23 17:16:44 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select status from v$instance;
STATUS
------------
OPEN

四、结论
运行SQLPLUS命令前，需要确保OS用户对$ORACLE_HOME/tnsnames.ora文件有读取权限，否则，无法通过TNSNAMES进行连接。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31448585/viewspace-2150438/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31448585/viewspace-2150438/