ORA-12170：TNS：Connect timeout occurred 的问题分析与解决
目录
通过navicat连接Oracle数据库报错：
ORA-12170: TNS:Connect timeout occurred
原因是连接地址为转发的 Oracle RAC VIP 地址，VIP 返回后端节点 IP，但本机无法访问该内网 IP，导致连接超时。
1.问题现象
内网oracle连接信息
ip: 172.xxx.xxx.165
port: 1521
service: orcl
username: usernamexxx
password: passwordxxx内网访问地址（172.xxx.xxx.165:1521）转发到公网地址 1xx.xxx.xxx.xxx 1xxxx
本地通过navicat连接oracle，使用公网地址 1xx.xxx.xxx.xxx 1xxxx 访问报错
ORA-12170: TNS:Connect timeout occurred2.原因分析
2.1 wireshark抓包分析
1.抓访问连接地址的包
ip.addr == 1xx.xxx.xxx.xxx
返回的连接地址是内网IP，有两种情况：
- 返回连接地址是
(HOST=172.xxx.xxx.164)(PORT=1521),INSTANCE_NAME=orcl2
.........9.,...............:....AA........................(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1xx.xxx.xxx.xxx)(PORT=1xxxx))(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(PROGRAM=D:\Program?Files\PremiumSoft\Navicat?Premium?16\navicat.exe)(HOST=DESKTOP-XXXXXXX)(USER=userxxx))))
........6.@.......@(ADDRESS=(PROTOCOL=TCP)(HOST=172.xxx.xxx.164)(PORT=1521)).(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1xx.xxx.xxx.xxx)(PORT=1xxxx))(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(PROGRAM=D:\Program?Files\PremiumSoft\Navicat?Premium?16\navicat.exe)(HOST=DESKTOP-XXXXXXX)(USER=userxxx))(SERVER=dedicated)(INSTANCE_NAME=orcl2)))- 返回连接地址是
(HOST=172.xxx.xxx.163)(PORT=1521)，INSTANCE_NAME=orcl1
.........9.,...............:....AA........................(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1xx.xxx.xxx.xxx)(PORT=1xxxx))(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(PROGRAM=D:\Program?Files\PremiumSoft\Navicat?Premium?16\navicat.exe)(HOST=DESKTOP-XXXXXXX)(USER=userxxx))))
........6.@.......@(ADDRESS=(PROTOCOL=TCP)(HOST=172.xxx.xxx.163)(PORT=1521)).(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1xx.xxx.xxx.xxx)(PORT=1xxxx))(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(PROGRAM=D:\Program?Files\PremiumSoft\Navicat?Premium?16\navicat.exe)(HOST=DESKTOP-XXXXXXX)(USER=userxxx))(SERVER=dedicated)(INSTANCE_NAME=orcl1)))通过navicat请求连接 1xx.xxx.xxx.xxx 1xxxx 也就是 172.xxx.xxx.165 1521，返回的地址是orcl1（172.xxx.xxx.163 1521）或者orcl2（172.xxx.xxx.164 1521）
2.抓关于 172.xxx.xxx.163 或者 172.xxx.xxx.164 的包
ip.addr == 172.xxx.xxx.163
无法访问该地址
9120 2024-07-18 15:16:25.851801 192.168.1.11 172.xxx.xxx.163 TCP 66 53953 → 1521 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM
9139 2024-07-18 15:16:26.853695 192.168.1.11 172.xxx.xxx.163 TCP 66 [TCP Retransmission] 53953 → 1521 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM
9165 2024-07-18 15:16:28.853896 192.168.1.11 172.xxx.xxx.163 TCP 66 [TCP Retransmission] 53953 → 1521 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM
9263 2024-07-18 15:16:32.864864 192.168.1.11 172.xxx.xxx.163 TCP 66 [TCP Retransmission] 53953 → 1521 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM
9665 2024-07-18 15:16:40.868136 192.168.1.11 172.xxx.xxx.163 TCP 66 [TCP Retransmission] 53953 → 1521 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERMip.addr == 172.xxx.xxx.164
无法访问该地址
136 2024-07-18 15:15:06.832502 192.168.1.11 172.xxx.xxx.164 TCP 66 53922 → 1521 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM
176 2024-07-18 15:15:07.837003 192.168.1.11 172.xxx.xxx.164 TCP 66 [TCP Retransmission] 53922 → 1521 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM
243 2024-07-18 15:15:09.841958 192.168.1.11 172.xxx.xxx.164 TCP 66 [TCP Retransmission] 53922 → 1521 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM
449 2024-07-18 15:15:13.847593 192.168.1.11 172.xxx.xxx.164 TCP 66 [TCP Retransmission] 53922 → 1521 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM
3752 2024-07-18 15:15:21.849066 192.168.1.11 172.xxx.xxx.164 TCP 66 [TCP Retransmission] 53922 → 1521 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM也就是说，通过navicat请求连接 1xx.xxx.xxx.xxx 1xxxx 也就是 172.xxx.xxx.165 1521，返回的地址是orcl1（172.xxx.xxx.163 1521）或者orcl2（172.xxx.xxx.164 1521），navicat再根据返回的oracle地址进行连接。
由于无法访问返回的内网 oracle 地址，导致超时报错（ORA-12170: TNS:Connect timeout occurred）。
2.2 Oracle VIP 返回机制
在 RAC（Real Application Clusters）环境里，客户端连接 VIP（Virtual IP）后，会被服务端“重定向/返回”到实际可用节点。
VIP 本身不处理数据库连接，它只是“入口”，真正的连接会被重定向到具体的 RAC 节点实例。
这样做的目的是为了高可用和负载分流。
3.解决方案
直接转发orcl1（172.xxx.xxx.163 1521）或者orcl2（172.xxx.xxx.164 1521）的地址，进行访问。
测试成功
这里转发orcl1（172.xxx.xxx.163 1521）到 1xx.xxx.xxx.xxx 2xxxx
通过1xx.xxx.xxx.xxx 2xxxx访问，成功连接
查看网络信息，抓包分析
ASCII 编码
tcp三次握手
成功进行交互
# -> 1xx.xxx.xxx.xxx
.........9.,...............:....AA........................(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1xx.xxx.xxx.xxx)(PORT=2xxxx))(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(PROGRAM=D:\Program?Files\PremiumSoft\Navicat?Premium?16\navicat.exe)(HOST=DESKTOP-XXXXXXX)(USER=userxxx))))
# <- 1xx.xxx.xxx.xxx
........
# -> 1xx.xxx.xxx.xxx
.........9.,...............:....AA........................(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1xx.xxx.xxx.xxx)(PORT=2xxxx))(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(PROGRAM=D:\Program?Files\PremiumSoft\Navicat?Premium?16\navicat.exe)(HOST=DESKTOP-XXXXXXX)(USER=userxxx))))
# <- 1xx.xxx.xxx.xxx
. .......9........... AA........
# -> 1xx.xxx.xxx.xxx
...................................
........q...V...................................
..........................
............
.................
.........
# <- 1xx.xxx.xxx.xxx
...............u.................... ....................................... ..................... .................... .......
# -> 1xx.xxx.xxx.xxx
.'................IBMPC/WIN_NT64-9.1.0.
# <- 1xx.xxx.xxx.xxx
.............x86_64/Linux 2.4.xx.T......R..R...
f.R..R.f..S.R..R.S...d...`.$..........
. .............
...........#G#G..#..A.G...T........................................'..................
......... ..................
... 中间具体在干什么，不清楚
# -> 1xx.xxx.xxx.xxx
...........v...H.............p...................L........usernamexxx....
AUTH_TERMINAL.....DESKTOP-XXXXXXX.........AUTH_PROGRAM_NM.....navicat.exe.........AUTH_MACHINE2....WORKGROUP\DESKTOP-XXXXXXX.........AUTH_PID.....29076:11084.........AUTH_SID.....userxxx....
# <- 1xx.xxx.xxx.xxx
..................AUTH_SESSKEY@...@F59C6B2B2C2CAD3003DC2AE81BC5318D8A4DCA38AA24044993C0EFA6B5EE8832....
...
AUTH_VFR_DATA....9 ....................................................................
# -> 1xx.xxx.xxx.xxx
...........s...H.............0.......
...........t........usernamexxx.....AUTH_SESSKEY....@3408C9C260EC70F0F3E40FC13F1967128E3D34EFA1106E5D8FA5E7E5471A9F14........
AUTH_PASSWORD....@DB8799DDDD370F24C5150279B7BE5D0D90EFC12E5E748C0053A9212E62D4261A.........AUTH_RTT.....196584........
AUTH_CLNT_MEM.....4096........
AUTH_TERMINAL.....DESKTOP-XXXXXXX.........AUTH_PROGRAM_NM.....navicat.exe.........AUTH_MACHINE2....WORKGROUP\DESKTOP-XXXXXXX.........AUTH_PID.....29076:11084.........AUTH_SID.....userxxx.........AUTH_ACL.....8000....$....AUTH_ALTER_SESSIONJ...%ALTER SESSION SET TIME_ZONE='+08:00'..........AUTH_LOGICAL_SESSION_ID@... FF06A59547B54676A3D9A051CA2D2F8E.... ....AUTH_FAILOVER_ID........
# <- 1xx.xxx.xxx.xxx
.K.........$......AUTH_VERSION_STRING.....- 64bit Production.........AUTH_VERSION_SQL.....22.........AUTH_XACTION_TRAITS.....3.........AUTH_VERSION_NO ... 186647552.........AUTH_VERSION_STATUS.....0.........AUTH_CAPABILITY_TABLE.............AUTH_DBNAME.....ORCL.........AUTH_SESSION_ID.....1581.........AUTH_SERIAL_NUM.....5285.........AUTH_INSTANCE_NO.....1.........AUTH_FAILOVER_ID.....1.........AUTH_SERVER_PID.....54641.........AUTH_SC_SERVER_HOST.....rac1.........AUTH_SC_DBUNIQUE_NAME.....orcl.........AUTH_SC_INSTANCE_NAME.....orcl1.........AUTH_SC_SERVICE_NAME.....orcl.........AUTH_SC_INSTANCE_ID.....1.........AUTH_SC_INSTANCE_START_TIME$...$2024-06-21 23:11:29.000000000 +08:00.........AUTH_SC_DB_DOMAIN.............AUTH_SC_SVC_FLAGS.....8.........AUTH_INSTANCENAME.....orcl1.........AUTH_NLS_LXLAN......SIMPLIFIED CHINESE.........AUTH_NLS_LXCTERRITORY......CHINA.........AUTH_NLS_LXCCURRENCY......?.........AUTH_NLS_LXCISOCURR......CHINA.........AUTH_NLS_LXCNUMERICS.......,.........AUTH_NLS_LXCDATEFM. ... DD-MON-RR.........AUTH_NLS_LXCDATELANG......SIMPLIFIED CHINESE.........AUTH_NLS_LXCSORT......BINARY.........AUTH_NLS_LXCCALENDAR. ... GREGORIAN.........AUTH_NLS_LXCUNIONCUR......?.........AUTH_NLS_LXCTIMEFM......HH.MI.SSXFF AM.........AUTH_NLS_LXCSTMPFM......DD-MON-RR HH.MI.SSXFF AM.........AUTH_NLS_LXCTTZNFM......HH.MI.SSXFF AM TZR.........AUTH_NLS_LXCSTZNFM......DD-MON-RR HH.MI.SSXFF AM TZR.........AUTH_SVR_RESPONSE`...`82BCE20BC7E4B30821EA4CDC8FAE401637BE19FA4482E6105BDBFF8F6282EBBD4E58B200A1E7D8625E4B44C343D8B7DD......................................................................
# -> 1xx.xxx.xxx.xxx
.8.........k.-............;.............L.......H.......
# <- 1xx.xxx.xxx.xxx
..............Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options.. . ......
# -> 1xx.xxx.xxx.xxx
...........^.a........7K.....J.....J.....
...........P.J.............................................................R.J..... 8K.....................h.J.....%SELECT USERENV('SESSIONID') FROM DUAL....................................................
# <- 1xx.xxx.xxx.xxx
............!.%..IG.:.../G..x|.............Q....................................USERENV('SESSIONID')...............x|..........................W......................................D<<............................................................................
# -> 1xx.xxx.xxx.xxx
.............................
# <- 1xx.xxx.xxx.xxx
.L..........................................................................
# -> 1xx.xxx.xxx.xxx
.................d...
# <- 1xx.xxx.xxx.xxx
..........."......d.................,.>S...........{.......................................................ORA-01403: ..............
# -> 1xx.xxx.xxx.xxx
...........i p'K..............^
!........>K.....V.....J.....
...........P.J.............................................................R.J......'K.....................h.J.....+ALTER SESSION SET CURRENT_SCHEMA = usernamexxx....................................................
# <- 1xx.xxx.xxx.xxx
...............W..............................usernamexxx..............................................*.......................
....................
# -> 1xx.xxx.xxx.xxx
. .........i...K.............. .
# <- 1xx.xxx.xxx.xxx
.......... ......
# -> 1xx.xxx.xxx.xxx
.
.......@
四次挥手
这里进行了三次挥手
第一次 -> 1xx.xxx.xxx.xxx FIN,ACK
第二次 <- 1xx.xxx.xxx.xxx FIN,ACK
第三次 -> 1xx.xxx.xxx.xxx ACK
连接关闭4.总结
该问题的根本原因在于: 外部环境通过转发后的 Oracle RAC VIP 地址进行访问时，VIP在建立连接后会返回后端节点的真实内网IP。由于外部网络无法访问该内网IP，导致连接失败并触发 ORA-12180 超时错误。
在Oracle 内网部署环境中，由于客户端与 RAC 节点处于同一网络，可正常访问 VIP 返回的节点IP，因此不会出现该问题。
若必须在外部环境访问内网 Oracle，可通过直接转发 RAC 后端节点 IP 的方式进行连接。但该方式绕过了 VIP 与 SCAN 的高可用机制，不符合 Oracle RAC 的标准设计理念。
5.扩展知识
5.1 Oracle如何实现访问指定 IP 后返回后端节点 IP 连接
Oracle如何实现请求连接172.xxx.xxx.165 1521，返回的地址是orcl1（172.xxx.xxx.163 1521）或者orcl2（172.xxx.xxx.164 1521），然后客户端根据返回的地址连接oracle？
是通过Oracle的RAC(Real Application Clusters)实现的，可以参考 如何配置和使用 Oracle Real Application Clusters (RAC) 和 Oracle WebLogic Server 10.3
5.2 类似问题
ORA-12516, TNS:listener could not find available handler with matching protocol stack.
和navicat连接异常ORA-12170: TNS:Connect timeout occurred类似
抓包获取信息
.........9.,...............:....AA........................(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1xx.xxx.xxx.xxx)(PORT=5xxxx))(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(PROGRAM=D:\Program?Files\PremiumSoft\Navicat?Premium?16\navicat.exe)(HOST=DESKTOP-XXXXXXX)(USER=userxxx))))
0.043824s
.
.......4.>.......@(ADDRESS=(PROTOCOL=TCP)(HOST=172.xxx.xxx.68)(PORT=1521)).(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1xx.xxx.xxx.xxx)(PORT=5xxxx))(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(PROGRAM=D:\Program?Files\PremiumSoft\Navicat?Premium?16\navicat.exe)(HOST=DESKTOP-XXXXXXX)(USER=userxxx))(SERVER=dedicated)(INSTANCE_NAME=orcl2)))原因：
oracle高可用配置HA导致
连接地址为VIP，实际转到后端其它地址，导致失败
解决方法：
调整映射，直接转发Oracle后端真实地址
测试，可正常连接