ORA-12170：TNS：Connect timeout occurred 的问题分析与解决
描述
通过navicat连接Oracle数据库报错：
ORA-12170: TNS:Connect timeout occurred
这里是通过转发oracle的地址进行访问，由于转发的oracle的地址，是oracle通过RAC配置高可用后的VIP，连接时，会返回后端节点的IP，本机无法访问该IP，导致出现连接超时的问题。
下面是分析过程：
背景
内网oracle连接信息
ip: 172.xxx.xxx.165
port: 1521
service: orcl
username: usernamexxx
password: passwordxxx内网端口转发到公网地址 1xx.xxx.xxx.xxx 1xxxx
本地通过navicat连接oracle，公网地址1xx.xxx.xxx.xxx 1xxxx报错
ORA-12170: TNS:Connect timeout occurred分析
wireshark抓包:
ip.addr == 1xx.xxx.xxx.xxx
返回连接地址是(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）
wireshark 抓关于 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地址进行连接
原因
通过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）
解决方法
直接转发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
连接关闭说明
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
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，实际转到后端其它地址，导致失败
解决方法：
调整映射，直接转发真实地址
测试，可正常连接