ORA-12170：TNS：连接超时

转载于http://www.cnblogs.com/kerrycode/archive/2012/12/14/2818421.html

在Red HatEnterprise Linux Server Releae 5.5 成功安装ORACLE 10g后，在客户端配置TNS后，测试是否可以连接到数据块服务器，结果报错： ORA-12170：TNS：连接超时

1：首先检查网络是否能ping通，如下所示，网络是畅通的。

 

2：检查TNS配置（TNS配置也没有问题）

GSP =

(DESCRIPTION =

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

(CONNECT_DATA=

(SERVER = DEDICATED)

(SERVICE_NAME = gsp)

)

)

3：查看服务器监听服务是否启动

[oracle@wgods ~]$ lsnrctl status

LSNRCTL for Linux: Version10.2.0.1.0 - Production on 14-DEC-2012 15:51:13

Copyright (c) 1991, 2005, Oracle.All rights reserved.

Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux:Version 10.2.0.1.0 - Production

Start Date 14-DEC-2012 13:15:28

Uptime 0 days 2 hr. 35 min. 45sec

Trace Level off

Security ON: Local OSAuthentication

SNMP OFF

Listener Parameter File/database/oracle/product/dbhome/network/admin/listener.ora

Listener Log File/database/oracle/product/dbhome/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wgods)(PORT=1521)))

Services Summary...

Service "PLSExtProc"has 1 instance(s).

Instance "PLSExtProc",status UNKNOWN, has 1 handler(s) for this service...

Service "gsp" has 2instance(s).

Instance "gsp", statusUNKNOWN, has 1 handler(s) for this service...

Instance "gsp", statusREADY, has 1 handler(s) for this service...

Service "gspXDB" has 1instance(s).

Instance "gsp", statusREADY, has 1 handler(s) for this service...

Service "gsp_XPT" has 1instance(s).

Instance "gsp", statusREADY, has 1 handler(s) for this service...

The command completedsuccessfully

 

4：使用tnsping命令检查，报TNS-12535:TNS: 操作超时，这时我们可以肯定是防火墙的问题了。

C:\Users\kerry>tnsping172.20.32.79

TNS Ping Utility for 32-bitWindows: Version 11.2.0.1.0 - Production on 14-12月-2012 15:47:15

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

已使用的参数文件:

E:\app\kerry\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

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

尝试连接(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.32.79)(PORT=1521)))

TNS-12535: TNS: 操作超时

对于防火墙问题，我们可以有两种解决方案：

1：关闭防火墙（这种方案不怎么好，关闭防火墙，会带来许多安全隐患）

[root@wgods ~]# service iptablesstop

Flushing firewall rules: [ OK ]

Setting chains to policy ACCEPT:filter [ OK ]

Unloading iptables modules: [ OK]

再次连接ping

 

2：修改iptables，开放1521端口，允许1521端口被连接

2.1编辑iptables文件，添加-ARH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT记录。

[root@wgods sysconfig]# viiptables

# Firewallconfiguration written by system-config-securitylevel

# Manualcustomization 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 -jRH-Firewall-1-INPUT

-A FORWARD -jRH-Firewall-1-INPUT

-ARH-Firewall-1-INPUT -i lo -j ACCEPT

-ARH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT

-ARH-Firewall-1-INPUT -p 50 -j ACCEPT

-ARH-Firewall-1-INPUT -p 51 -j ACCEPT

-ARH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT

-ARH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT

-ARH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT

-ARH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

-ARH-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 -ptcp --dport 1521 -j ACCEPT

-ARH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited

COMMIT

~                                   

"iptables" 24L, 1212Cwritten

2.2重启iptables服务

[root@wgods sysconfig]# serviceiptables restart

Flushing firewall rules: [ OK ]

Setting chains to policy ACCEPT:filter [ OK ]

Unloading iptables modules: [ OK]

Applying iptables firewall rules:[ OK ]

Loading additional iptablesmodules: ip_conntrack_netbios_ns ip_conntrack_ftp [ OK ]

2.3保存新增的规则，以免下次重启机器后，配置规则失效

[root@wgods sysconfig]# serviceiptables save

Saving firewall rules to/etc/sysconfig/iptables: [ OK ]

2.4查看1521端口是否开放，允许连接（见红色部分）

[root@localhost ~]#iptables -L -n

Chain INPUT (policyACCEPT)

target     prot opt source               destination        

RH-Firewall-1-INPUT  all --  0.0.0.0/0            0.0.0.0/0          

 

Chain FORWARD(policy ACCEPT)

target     prot opt source               destination        

RH-Firewall-1-INPUT  all --  0.0.0.0/0            0.0.0.0/0          

 

Chain OUTPUT (policyACCEPT)

target     prot opt source               destination        

 

ChainRH-Firewall-1-INPUT (2 references)

target     prot opt source               destination        

ACCEPT     all --  0.0.0.0/0            0.0.0.0/0          

ACCEPT     icmp -- 0.0.0.0/0           0.0.0.0/0           icmp type 255

ACCEPT     esp --  0.0.0.0/0            0.0.0.0/0          

ACCEPT     ah  --  0.0.0.0/0            0.0.0.0/0          

ACCEPT     udp --  0.0.0.0/0            224.0.0.251         udp dpt:5353

ACCEPT     udp --  0.0.0.0/0            0.0.0.0/0           udp dpt:631

ACCEPT     tcp --  0.0.0.0/0            0.0.0.0/0           tcp dpt:631

ACCEPT     all --  0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED

ACCEPT     tcp --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22

ACCEPT    tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:1521

REJECT     all --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

[root@localhost ~]#

从客户端用PL/SQL Developer连接数据库，问题解决。

出处：http://www.cnblogs.com/kerrycode/