如何在oracle里查看路径,tnsnames.ora文件的查找路径

我们有时候会遇到,明明在$ORACLE_HOME/network/admin/tnsnames.ora文件中添加了数据库的连接配置,但是在执行tnsping的时候就是报无法解析的错误

/oravl01/oracle/9.2.0/network/admin > tnsping SP9DB1

TNS-03505: Failed to resolve name

对于这种问题，排除添加的配置格式错误后.可能原因是读取的tnsnames.ora路径根本就不是$ORACLE_HOME/network/admin

tnsnames.ora文件的查找路径顺序为

$HOME => $TNS_ADMIN => /etc => $ORACLE_HOME/network/admin/

我们从下面可以看出tnsnames.ora文件的查找路径顺序:

[oracle:/oracle#]

[oracle:/oracle#]

[oracle:/oracle#]echo $HOME ----oracle用户的HOME路径

/oracle

[oracle:/oracle#]echo $TNS_ADMIN

/home/oracle

[oracle:/oracle#]strace -o /tmp/a3 tnsping testdb ---设置的TNS_ADMIN路径

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 06-4?? -2011 16:04:50

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.4)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb)))

OK (10 msec)

[oracle:/oracle#]

[oracle:/oracle#]

[oracle:/oracle#]

[oracle:/oracle#]

[oracle:/oracle#]grep tns /tmp/a3

execve("/opt/ora10g/product/10.2.0/db_1/bin/tnsping", ["tnsping", "testdb"], [/* 32 vars */]) = 0

access("/oracle/.tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory) ---首先查找$HOME/.tnsnames.ora文件未找到

access("/home/oracle/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory) ---再查找$TNS_ADMIN/tnsnames.ora文件未找到

access("/etc/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory) ---查找/etc/tnsnames.ora文件未找到

access("/opt/ora10g/product/10.2.0/db_1/network/admin/tnsnames.ora", F_OK) = 0 ---最后查找$ORACLE_HOME/network/admin/tnsnames.ora文件

stat64("/opt/ora10g/product/10.2.0/db_1/network/admin/tnsnames.ora", {st_mode=S_IFREG|0644, st_size=181, ...}) = 0

open("/opt/ora10g/product/10.2.0/db_1/network/admin/tnsnames.ora", O_RDONLY|O_LARGEFILE) = 3

open("/opt/ora10g/product/10.2.0/db_1/network/mesg/tnszhs.msb", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/opt/ora10g/product/10.2.0/db_1/network/mesg/tnsus.msb", O_RDONLY) = 3

[oracle:/oracle#]