服务端监听配置
[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_1和dbhome_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.ora和tnsnames.ora到network下面新建的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
2013/10/10 00:20
2013/02/28 19:24
2013/02/28 19:21
2013/02/28 19:21
2013/02/28 19:29
2013/02/28 19:27
2013/02/28 19:27
2013/02/28 19:27
2013/02/28 19:29
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.ora和tnsnames.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来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29239083/viewspace-774023/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29239083/viewspace-774023/
213
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
