Intermittent ORA-12170 at pool recycle time on a 3-node RAC cluster
ORDER runs on a 3-node RAC cluster: SCAN with three VIPs, service ORDER_svc with two preferred instances, and 180 Java application servers each holding a 40-connection pool. Pools recycle connections between 07:55 and 08:10, and roughly 6% of new connections fail after about 60 seconds with "ORA-12170: TNS:Connect timeout occurred". The application retries twice and then raises an incident. The database-server listeners show normal connection volume and no corresponding failures, and the failing attempts cannot be found in any listener log. The network team reports no packet loss and no firewall denies, but the firewall drops idle TCP flows after 5 minutes, while the pools keep connections idle for up to 40 minutes. Server sqlnet.ora contains only SQLNET.INBOUND_CONNECT_TIMEOUT=60; the app servers' sqlnet.ora is empty. DNS (TTL 1 hour) and the cluster configuration are unchanged in the last week.
1. Why is the absence of listener log entries significant? Where in the connection path does ORA-12170 arise, and why does that point at a firewall idle timeout?
Read the worked answer
A listener logs a connection when it accepts and handles it, and also when it must reject it (TNS-12525 no handler, TNS-12516/12520 resource errors, and so on). If clients time out with ORA-12170 and nothing appears in the log of any SCAN listener, the request never reached a listener process. ORA-12170 is raised in the connect phase - the client sent its SYN or TNS connect packet and received no usable response within the timeout.
The firewall fits the evidence precisely because of the timing mismatch: pools hold connections idle for up to 40 minutes, the firewall discards the state entry after 5 minutes, and the 07:55-08:10 recycle is exactly when those half-dead connections are reused and new ones are attempted. Packets arrive for a flow the firewall has forgotten and are dropped silently - no ICMP, no RST - which explains "no denies, no packet loss". The 08:00 spike reflects reuse of stale pooled state, not a network outage.
Corroborating checks: tnsping from an affected app server during the window, netstat -an showing SYNs with no SYN-ACK on specific SCAN IPs, and firewall session-table exports around 07:55.
2. Propose the fix, naming which side (client or server) each parameter belongs on and why.
Read the worked answer
SQLNET.EXPIRE_TIME=2insqlnet.oraon the database servers. The server then probes each idle connection every 2 minutes. The probes refresh the firewall's state entry and also detect genuinely dead clients so the server frees the process. Keep it well below the 5-minute firewall timeout; a value of 2-3 minutes is the practical range.- Pool configuration on the application servers: reduce
maxIdleTimebelow the firewall timeout (for example 120 s) and validate connections on borrow (isValid()/SELECT 1 FROM DUAL), so a stale connection is dropped before it is used for real work. - Belt and braces: enable OS-level TCP keepalive on the app servers and consider raising the firewall's idle timeout for the database port.
SQLNET.INBOUND_CONNECT_TIMEOUT on the server governs how long the listener waits for a client to complete the handshake after accepting - a different phase that never starts here, so it is not the fix.
One caution: do not simply raise the firewall timeout to 60 minutes without probes. Half-open flows then linger and you trade connect timeouts for hung sessions and leaked server processes.
3. The application team asks whether raising SQLNET.INBOUND_CONNECT_TIMEOUT would help. Answer them, and describe a second failure mode that produces 08:00 timeouts but does leave listener entries.
Read the worked answer
No. The listener never accepted these connections, so the timer that parameter controls never started. Raising it would only affect connections stuck in the handshake after acceptance - and those would be visible in the listener log.
A second mode that does leave listener entries is handler saturation: the listener accepts the connection but no server process is available, producing TNS-12516/TNS-12518/TNS-12520 in the listener log and ORA-12516/ORA-12520 at the client - a different error from ORA-12170. Confirm with lsnrctl services (refused/blocked counts), V$RESOURCE_LIMIT and the processes/sessions settings; a recycle storm at 08:00 can momentarily exceed them.
A useful discriminator for what you have now: if failures spread evenly across all three SCAN VIPs, suspect the network/firewall path; if they cluster on one VIP or node, suspect an unreachable SCAN VIP or node listener - check srvctl status scan, srvctl status scan_listener and crsctl stat res -t, and correlate the failing client's resolved IP with the SCAN VIP list.