tnsping通oracle连不上,写正确了tnsnames文件却tnsping无法ping通

今天遇到个问题比较奇怪，tnsnames.ora明明配置正确，在其中写的IP是10.1.2.40，但是在tnsping中却解析到10.1.2.45：

[oracle@si_adm02 admin]$ cat tnsnames.ora

# TNSNAMES.ORA.SI_WWW01 Network Configuration File: /ora9i/app/oracle/product/9.2.0/network/admin/tnsnames.ora.si_www01

# Generated by Oracle configuration tools.

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

dev2 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.2.40)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME =misc1)

)

)

[oracle@si_adm02 admin]$

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

[oracle@si_adm02admin]$cattnsnames.ora

# TNSNAMES.ORA.SI_WWW01 Network Configuration File: /ora9i/app/oracle/product/9.2.0/network/admin/tnsnames.ora.si_www01

# Generated by Oracle configuration tools.

EXTPROC_CONNECTION_DATA=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))

)

(CONNECT_DATA=

(SID=PLSExtProc)

(PRESENTATION=RO)

)

)

dev2=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.2.40)(PORT=1521))

)

(CONNECT_DATA=

(SERVICE_NAME=misc1)

)

)

[oracle@si_adm02admin]$

[oracle@si_adm02 admin]$ tnsping dev2

TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 06-1Ղ -2010 15:03:15

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:

/oracle/product/9.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.54)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora9i)))

OK (0 msec)

[oracle@si_adm02 admin]$

1

2

3

4

5

6

7

8

9

10

11

12

13

14

[oracle@si_adm02admin]$tnspingdev2

TNSPingUtilityforLinux:Version9.2.0.4.0-Productionon06-1Ղ-201015:03:15

Copyright(c)1997OracleCorporation.Allrightsreserved.

Usedparameterfiles:

/oracle/product/9.2.0/network/admin/sqlnet.ora

UsedTNSNAMESadaptertoresolvethealias

Attemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.3.54)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ora9i)))

OK(0msec)

[oracle@si_adm02admin]$

而且我把tnsnames.ora改名成别的名称，这个service name依然可以被解析到。

既然都改名了，难道还有别的tnsnames.ora文件在被使用？用find名称查了一下：

[oracle@si_adm02 admin]$ find / -name tnsnames.ora >hjm.txt

[oracle@si_adm02 admin]$ cat hjm.txt

/opt/aspire/product/imallcpp/product/9.2.0/network/admin/samples/tnsnames.ora

/opt/aspire/product/imallcpp/product/9.2.0/network/admin/tnsnames.ora

/opt/instantclient_10_2/tnsnames.ora

/opt/instantclient_10_2_4/tnsnames.ora

/oracle/product/9.2.0/network/admin/samples/tnsnames.ora

1

2

3

4

5

6

7

[oracle@si_adm02admin]$find/-nametnsnames.ora>hjm.txt

[oracle@si_adm02admin]$cathjm.txt

/opt/aspire/product/imallcpp/product/9.2.0/network/admin/samples/tnsnames.ora

/opt/aspire/product/imallcpp/product/9.2.0/network/admin/tnsnames.ora

/opt/instantclient_10_2/tnsnames.ora

/opt/instantclient_10_2_4/tnsnames.ora

/oracle/product/9.2.0/network/admin/samples/tnsnames.ora

发现在其他路径下还是有tnsnames.ora文件的，而且用于解析的，就是其他路径的这个文件。

在此，基本的问题就搞明白了，由于用了其他路径的tnsnames.ora，所以无法正确解析。为解决这个问题，我们指定$ORACLE_HOME下的network/admin路径，我们export一下TNS的路径，或者在环境变量中加上TNS_ADMIN，就能解决这个问题了：

[oracle@si_adm02 admin]$ export TNS_ADMIN=/oracle/product/9.2.0/network/admin

[oracle@si_adm02 admin]$

[oracle@si_adm02 admin]$

[oracle@si_adm02 admin]$ tnsping dev2

TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 06-1Ղ -2010 15:12:03

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:

/oracle/product/9.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.2.40)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =misc1)))

OK (10 msec)

[oracle@si_adm02 admin]$

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

[oracle@si_adm02admin]$exportTNS_ADMIN=/oracle/product/9.2.0/network/admin

[oracle@si_adm02admin]$

[oracle@si_adm02admin]$

[oracle@si_adm02admin]$tnspingdev2

TNSPingUtilityforLinux:Version9.2.0.4.0-Productionon06-1Ղ-201015:12:03

Copyright(c)1997OracleCorporation.Allrightsreserved.

Usedparameterfiles:

/oracle/product/9.2.0/network/admin/sqlnet.ora

UsedTNSNAMESadaptertoresolvethealias

Attemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.2.40)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=misc1)))

OK(10msec)

[oracle@si_adm02admin]$

最后，查到原来在root用户下，已经指定了这个TNS_ADMIN：

[root@si_adm02 root]# id

uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)

[root@si_adm02 root]# env |grep -i tns

TNS_ADMIN=/opt/instantclient_10_2

[root@si_adm02 root]#

1

2

3

4

5

[root@si_adm02root]# id

uid=0(root)gid=0(root)groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)

[root@si_adm02root]# env |grep -i tns

TNS_ADMIN=/opt/instantclient_10_2

[root@si_adm02root]#

应该是oracle用户没有配TNS_ADMIN，且oracle用户又继承了root的环境变量，所以在tnsping的时候，没有去正确的路径找tnsnames.ora文件，所以，解析不到了。