ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 因多个ORACLE_HOME导致tns不能被正常识别

因多个ORACLE_HOME导致tns不能被正常识别

原创 Linux操作系统 作者:Hongdba 时间:2013-10-10 00:17:18 0 删除 编辑

服务端监听配置

[oracle@localhost ~]$ lsnrctl status

 

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 27-JUL-2013 09:00:58

 

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

 

Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 9.2.0.4.0 - Production

Start Date                27-JUL-2013 04:29:08

Uptime                    0 days 4 hr. 31 min. 50 sec

Trace Level               off

Security                  OFF

SNMP                      OFF

Listener Parameter File   /home/oracle/db_Home1/network/admin/listener.ora

Listener Log File         /home/oracle/db_Home1/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))

Services Summary...

Service "hong" has 1 instance(s).

  Instance "hong", status READY, has 1 handler(s) for this service...

Service "hongXDB" has 1 instance(s).

  Instance "hong", status READY, has 1 handler(s) for this service...

The command completed successfully

 

Tnsnames.ora文件配置

Tns文件路径

D:\app\Hong\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

 

Tns配置内容

ORACLE9IVM=

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = hong)

    )

  )

 

 

测试tnsping

默认测试tnsping

C:\Users\Hong>tnsping oracle9ivm

 

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 10-10-

2013 00:24:06

 

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

 

已使用的参数文件:

D:\app\Hong\product\11.2.0\dbhome_3\network\admin\sqlnet.ora

 

TNS-03505: 无法解析名称

 

通过红色部分可以看到,改tnsping访问的是dbhome_3下面的tnsnames.ora文件,而不是我们配置的dbhome_1下面的tnsnames.ora文件,因此出现该错误

 

 

检查ORACLE_HOME配置

注册表路径

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

 

这里我们看到有两个oracle_home,分别执行dbhome_1dbhome_3

 

通过分析path中的oracle安装路径,当我们在默认情况下执行tnsping的时候,使用的是dbhome_3下面的tnsping,因此会对应的调用dbhome_3中默认的tnsnames.ora文件,从而出现了上述错误.

 

 

故障解决方法

修改注册表

 

C:\Users\Hong>tnsping oracle9ivm

 

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 10-10-

2013 00:37:55

 

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

 

已使用的参数文件:

D:\app\Hong\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

 

 

已使用 TNSNAMES 适配器来解析别名

尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.16

8.2.99)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = hong)))

OK (40 毫秒)

 

设置tns_admin

C:\Users\Hong>tnsping oracle9ivm

 

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 10-10-

2013 00:39:54

 

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

 

已使用的参数文件:

D:\app\Hong\product\11.2.0\dbhome_3\network\admin\sqlnet.ora

 

TNS-03505: 无法解析名称

 

C:\Users\Hong>set tns_admin=D:\app\Hong\product\11.2.0\dbhome_1\NETWORK\ADMIN

 

C:\Users\Hong>tnsping oracle9ivm

 

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 10-10-

2013 00:40:20

 

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

 

已使用的参数文件:

D:\app\Hong\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora

 

 

已使用 TNSNAMES 适配器来解析别名

尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.16

8.2.99)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = hong)))

OK (50 毫秒)

 

修改path

C:\Windows\System32>D:\app\Hong\product\11.2.0\dbhome_1\BIN\tnsping oracle9ivm

 

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 10-10-

2013 00:55:37

 

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

 

已使用的参数文件:

D:\app\Hong\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora

 

 

已使用 TNSNAMES 适配器来解析别名

尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.16

8.2.99)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = hong)))

OK (50 毫秒)

 

 

 

关于tns_admin意义测试

重命名dbhome_3下面的admin文件夹,拷贝sqlnet.oratnsnames.oranetwork下面新建的aa文件夹中

C:\Users\Hong>cd D:\app\Hong\product\11.2.0\dbhome_3\NETWORK

 

C:\Users\Hong>d:

 

D:\app\Hong\product\11.2.0\dbhome_3\NETWORK>dir

 驱动器 D 中的卷没有标签。

 卷的序列号是 C657-FE16

 

 D:\app\Hong\product\11.2.0\dbhome_3\NETWORK 的目录

 

2013/10/10  00:41   

          .

2013/10/10  00:41   

          ..

2013/10/10  00:41   

          aa

2013/10/10  00:20   

          ADMIN-bak

2013/02/28  19:24   

          doc

2013/02/28  19:21   

          install

2013/02/28  19:21   

          jlib

2013/02/28  19:29   

          log

2013/02/28  19:27   

          mesg

2013/02/28  19:27   

          Names

2013/02/28  19:27   

          tools

2013/02/28  19:29   

          trace

               0 个文件              0 字节

              12 个目录 140,054,380,544 可用字节

 

D:\app\Hong\product\11.2.0\dbhome_3\NETWORK>cd aa

 

D:\app\Hong\product\11.2.0\dbhome_3\NETWORK\aa>dir

 驱动器 D 中的卷没有标签。

 卷的序列号是 C657-FE16

 

 D:\app\Hong\product\11.2.0\dbhome_3\NETWORK\aa 的目录

 

2013/10/10  00:41   

          .

2013/10/10  00:41   

          ..

2013/10/10  00:20               460 sqlnet.ora

2013/10/10  00:20               776 tnsnames.ora

               2 个文件          1,236 字节

               2 个目录 140,054,380,544 可用字节

 

C:\Users\Hong>set tns_admin=D:\app\Hong\product\11.2.0\dbhome_3\NETWORK\ADMIN

 

C:\Users\Hong>tnsping oracle9ivm

 

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 10-10-

2013 00:42:13

 

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

 

已使用的参数文件:

D:\app\Hong\product\11.2.0\dbhome_3\network\admin\sqlnet.ora

 

TNS-03505: 无法解析名称

 

因为dbhome_3中已经没有了sqlnet.oratnsnames.ora文件,因此不能解析

 

修改tns_admin目录为aa

C:\Users\Hong>set tns_admin=D:\app\Hong\product\11.2.0\dbhome_3\NETWORk\aa

 

C:\Users\Hong>tnsping oracle9ivm

 

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 10-10-

2013 00:42:38

 

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

 

已使用的参数文件:

D:\app\Hong\product\11.2.0\dbhome_1\NETWORk\aa\sqlnet.ora

 

 

已使用 TNSNAMES 适配器来解析别名

尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.16

8.2.99)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = hong)))

OK (0 毫秒)

Tnsping测试成功

 

 

在未设置tns_admin的时候,数据库默认访问当前执行文件对应的oracle_home下面的network/admin里面的相关文件(sqlnet.ora,tnsnames.ora)

1.jpg

2.jpg

3.jpg

4.jpg

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-10-10

  • 博文量
    2
  • 访问量
    16619