oracle tns测试,在客户端配置TNS测试报错ORA-12170:TNS:连接超时

灰太狼 2022-10-21 01:17 235阅读 0赞

在Red Hat Enterprise 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: Version 10.2.0.1.0 - Production on 14-DEC-2012 15:51:13

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. 45 sec

Trace Level off

Security ON: Local OS Authentication

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 2 instance(s).

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

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

Service "gspXDB" has 1 instance(s).

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

Service "gsp\_XPT" has 1 instance(s).

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

The command completed successfully

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

C:\\Users\\kerry>tnsping 172.20.32.79

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

已使用的参数文件:

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 iptables stop

Flushing firewall rules: \[ OK \]

Setting chains to policy ACCEPT: filter \[ OK \]

Unloading iptables modules: \[ OK \]

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

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

\[root@wgods sysconfig\]\# vi iptables

\# Generated by iptables-save v1.3.5 on Fri Dec 14 17:03:58 2012

\*filter

:INPUT ACCEPT \[0:0\]

:FORWARD ACCEPT \[0:0\]

:OUTPUT ACCEPT \[1749:243629\]

: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 -m icmp --icmp-type any -j ACCEPT

\-A RH-Firewall-1-INPUT -p esp -j ACCEPT

\-A RH-Firewall-1-INPUT -p ah -j ACCEPT

\-A RH-Firewall-1-INPUT -d 224.0.0.251 -p udp -m udp --dport 5353 -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 RELATED,ESTABLISHED -j ACCEPT

\-A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport 21 -j ACCEPT

\-A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport 25 -j ACCEPT

\-A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT

\-A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m 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

\# Completed on Fri Dec 14 17:03:58 2012

"iptables" 24L, 1212C written

2.2 重启iptables服务

\[root@wgods sysconfig\]\# service iptables restart

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 \]

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

\[root@wgods sysconfig\]\# service iptables save

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

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

\[root@wgods sysconfig\]\# iptables -L -n

Chain INPUT (policy ACCEPT)

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 (policy ACCEPT)

target prot opt source destination

Chain RH-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:21

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

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:23

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@wgods sysconfig\]\#

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

oracle tns测试,在客户端配置TNS测试报错ORA-12170:TNS:连接超时

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关使用Navicat连接Oracle数据库及ORA-12541: TNS: 无监听程序、ORA-28547:connection to server failed、ORA-12514：TNS报错解决方案

相关 oracle无实例plsql怎么登陆,oracle用cmd货plsql登录oracle实例提示“ORA :12170”TNS：连接超时...

相关 Oracle的TNS配置与数据库连接

相关 oracle tns测试,在客户端配置TNS测试报错ORA-12170:TNS:连接超时

相关 sqlplus登录报错TNS-12547: TNS:lost contact

相关 Oracle TNS协议

相关 ORA-12170 TNS 连接超时

相关 Oracle连接报错ORA-03134和required Basic and TNS connection

相关 ORA-12547:TNS:丢失连接

相关关于ORA-12170 tns 连接超时等一系列Oracle无法正常登入问题