本机测试tnsping正常了。
7. 接着，使用netca新增tnsnames.ora文件：
# tnsnames.ora Network Configuration File: /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/tnsnames.ora # Generated by Oracle configuration tools. DCSOPEN = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = dcsopen) ) )
本机测试sqlplus ...@dcsopen正常了。
8. 从另外一台机器访问这个数据库dcsopen，修改tnsnames.ora文件，执行tnsping dcsopen报错：
ora10g@localhost.localdomain$tnsping dcsopen TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 08-JAN-2015 00:51:37 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521))) (CONNECT_DATA = (service_name = dcsopen))) TNS-12560: TNS:protocol adapter error
执行sqlplus ...@dcsopen报错：
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 8 00:58:14 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
于是先看看端口是否开通，执行telnet报错：
ora10g@localhost.localdomain$telnet 172.101.19.57 1521
Trying 172.101.19.57...
telnet: connect to address 172.101.19.57: No route to host
如果端口未开，实际报错：
ora10g@localhost.localdomain$telnet 172.27.19.56 1521
Trying 172.27.19.56...
telnet: connect to address 172.27.19.56: Connection refused
是不是防火墙的问题？？？
从数据库服务器关闭防火墙：
[root@dcsopen2Node ~]# service iptables stop iptables: Flushing firewall rules: [ OK ] iptables: Setting chains to policy ACCEPT: nat mangle filter [ OK ] iptables: Unloading modules: [ OK ] 再从远程机器执行： ora10g@localhost.localdomain$telnet 172.101.19.571521 Trying 172.101.19.57... Connected to 172.101.19.57. Escape character is '^]'.
说明端口已开，更重要的是，明确了，就是防火墙问题。于是参考，将/etc/sysconfig/iptables文件新增一行，表示允许1521端口访问：
[root@dcsopen2Node sysconfig]# vi iptables # Firewall configuration written by system-config-firewall # Manual customization of this file is not recommended. *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [0:0] -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT
启动防火墙：
[root@dcsopen2Node sysconfig]# service iptables start iptables: Applying firewall rules: [ OK ] 或service iptables restart 从远程机访问： ora10g@localhost.localdomain$sqlplus dcsopen/dcsopen1@dcsopen SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 8 01:11:12 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
正常了。
总结：
1. listener.ora和tnsnames.ora配置文件最好使用netca工具创建，否则手工修改很可能出现各式问题导致无法解析读取。
2. 我的排查思路是，先确保本机可以tnsping和sqlplus ...@xxx，本机可访问了，再看远程机器。
远程访问tnsping和sqlplus报错TNS-12560: TNS:protocol adapter error，说明可能两台机器之间的连接有问题而不是监听自身的问题。
3. telnet报错是No route to host，不是Connection refused，不可轻易认为是网络端口未开