TNS-12535: TNS: 操作超时 / ORA-12170: TNS: 连接超时

环境：
oracle服务端:
oracle 10.2.0.5
rhel 5.4 32bit
ip 192.168.0.221

数据库相关信息
SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      orcl
db_unique_name                       string      orcl
global_names                         boolean     FALSE
instance_name                        string      orcl
lock_name_space                      string
log_file_name_convert                string
service_names                        string      orcl

windows客户端
tns：

ORCL_192.168.0.221 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.221)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

==================================================================================================

故障现象
C:\Users\thinkpad>ping 192.168.0.221
正在 Ping 192.168.0.221 具有 32 字节的数据:
来自 192.168.0.221 的回复: 字节=32 时间<1ms TTL=64
来自 192.168.0.221 的回复: 字节=32 时间<1ms TTL=64
来自 192.168.0.221 的回复: 字节=32 时间<1ms TTL=64
来自 192.168.0.221 的回复: 字节=32 时间<1ms TTL=64

192.168.0.221 的 Ping 统计信息:
    数据包: 已发送 = 4，已接收 = 4，丢失 = 0 (0% 丢失)，
往返行程的估计时间(以毫秒为单位):
    最短 = 0ms，最长 = 0ms，平均 = 0ms

C:\Users\thinkpad>
C:\Users\thinkpad>tnsping orcl_192.168.0.221

TNS Ping Utility for 32-bit Windows: Version 11.2.0.4.0 - Production on 23-1月 -2016 12:13:52

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

已使用的参数文件:
E:\ora11g\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.221)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
TNS-12535: TNS: 操作超时

C:\Users\thinkpad>
C:\Users\thinkpad>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on 星期六 1月 23 12:14:22 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL>
SQL> conn system/oracle@orcl_192.168.0.221 as sysdba
ERROR:
ORA-12170: TNS: 连接超时

SQL>

经过诊断，发现是服务端的防火墙开启，拒绝1521端口连接。
[root@rhel ~]# service iptables status
Table: filter
Chain INPUT (policy ACCEPT)
num  target     prot opt source               destination         
1    RH-Firewall-1-INPUT  all  --  0.0.0.0/0            0.0.0.0/0           

Chain FORWARD (policy ACCEPT)
num  target     prot opt source               destination         
1    RH-Firewall-1-INPUT  all  --  0.0.0.0/0            0.0.0.0/0           

Chain OUTPUT (policy ACCEPT)
num  target     prot opt source               destination         

Chain RH-Firewall-1-INPUT (2 references)
num  target     prot opt source               destination         
1    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           
2    ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0           icmp type 255 
3    ACCEPT     esp  --  0.0.0.0/0            0.0.0.0/0           
4    ACCEPT     ah   --  0.0.0.0/0            0.0.0.0/0           
5    ACCEPT     udp  --  0.0.0.0/0            224.0.0.251         udp dpt:5353 
6    ACCEPT     udp  --  0.0.0.0/0            0.0.0.0/0           udp dpt:631 
7    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:631 
8    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED 
9    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:21 
10   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22 
11   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:23 
12   REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited 

[root@rhel ~]# 
[root@rhel ~]# cat /etc/sysconfig/iptables
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:RH-Firewall-1-INPUT - [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
-A RH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -p 50 -j ACCEPT
-A RH-Firewall-1-INPUT -p 51 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 21 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 23 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT
[root@rhel ~]# 

于是，修改防火墙策略，编辑/etc/sysconfig/iptables添加1521端口，允许访问1521端口。然后重启防火墙。
[root@rhel ~]# vi /etc/sysconfig/iptables

# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:RH-Firewall-1-INPUT - [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
-A RH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -p 50 -j ACCEPT
-A RH-Firewall-1-INPUT -p 51 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 21 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 23 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport 1521 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT
~
~
~
~
~
~
"/etc/sysconfig/iptables" 23L, 1109C written
[root@rhel ~]# 
[root@rhel ~]# 
[root@rhel ~]# 
[root@rhel ~]# cat /etc/sysconfig/iptables
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:RH-Firewall-1-INPUT - [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
-A RH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -p 50 -j ACCEPT
-A RH-Firewall-1-INPUT -p 51 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 21 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 23 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport 1521 -j ACCEPT   --该行vi编辑时，新增端口1521
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT
[root@rhel ~]# 
[root@rhel ~]# service iptables stop
Flushing firewall rules: [  OK  ]
Setting chains to policy ACCEPT: filter [  OK  ]
Unloading iptables modules: [  OK  ]
[root@rhel ~]# 
[root@rhel ~]# 
[root@rhel ~]# service iptables start
Flushing firewall rules: [  OK  ]
Setting chains to policy ACCEPT: filter [  OK  ]
Unloading iptables modules: [  OK  ]
Applying iptables firewall rules: [  OK  ]
Loading additional iptables modules: ip_conntrack_netbios_ns ip_conntrack_ftp [  OK  ]
[root@rhel ~]# 
[root@rhel ~]# 
[root@rhel ~]# service iptables status
Table: filter
Chain INPUT (policy ACCEPT)
num  target     prot opt source               destination         
1    RH-Firewall-1-INPUT  all  --  0.0.0.0/0            0.0.0.0/0           

Chain FORWARD (policy ACCEPT)
num  target     prot opt source               destination         
1    RH-Firewall-1-INPUT  all  --  0.0.0.0/0            0.0.0.0/0           

Chain OUTPUT (policy ACCEPT)
num  target     prot opt source               destination         

Chain RH-Firewall-1-INPUT (2 references)
num  target     prot opt source               destination         
1    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           
2    ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0           icmp type 255 
3    ACCEPT     esp  --  0.0.0.0/0            0.0.0.0/0           
4    ACCEPT     ah   --  0.0.0.0/0            0.0.0.0/0           
5    ACCEPT     udp  --  0.0.0.0/0            224.0.0.251         udp dpt:5353 
6    ACCEPT     udp  --  0.0.0.0/0            0.0.0.0/0           udp dpt:631 
7    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:631 
8    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED 
9    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:21 
10   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22 
11   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:23 
12   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:1521 
13   REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited 
[root@rhel ~]# 
把端口加入后，防火墙开着也可以连接了。

C:\Users\thinkpad>tnsping orcl_192.168.0.221
TNS Ping Utility for 32-bit Windows: Version 11.2.0.4.0 - Production on 23-1月 -2016 12:34:26

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

已使用的参数文件:
E:\ora11g\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.221)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 毫秒)
C:\Users\thinkpad>
C:\Users\thinkpad>
C:\Users\thinkpad>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on 星期六 1月 23 12:34:29 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL>
SQL> conn sys/oracle@orcl_192.168.0.221 as sysdba
已连接。
SQL>

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

转载于:http://blog.itpub.net/24626891/viewspace-2122472/