DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-12170 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12170: TNS Connect Timeout Occurred — Causes, Fixes & Prevention

ORA-12170 is an Oracle Net (TNS) error that occurs when a client fails to establish a connection to the database within the configured timeout period. The error indicates that the listener or server process did not respond in time, causing the TNS layer to abort the connection attempt. In practice, this error typically points to network issues, firewall interference, listener overload, or misconfigured timeout parameters.

Top 3 Causes

1. Firewall Blocking or Dropping TNS Packets

Firewalls and network appliances between the client and the database server often silently drop Oracle TNS packets on port 1521, or terminate idle TCP sessions before Oracle can complete the handshake. This is the most common root cause and the hardest to diagnose without network-level tracing.

-- Check long-running idle sessions that may be killed by the firewall
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.machine,
       s.last_call_et AS idle_seconds
FROM   v$session s
WHERE  s.type    = 'USER'
  AND  s.status  = 'INACTIVE'
  AND  s.last_call_et > 300
ORDER  BY s.last_call_et DESC;
Enter fullscreen mode Exit fullscreen mode

Fix: Add SQLNET.EXPIRE_TIME=10 to sqlnet.ora on the server side. This sends a keep-alive probe every 10 minutes, preventing the firewall from silently dropping the session.

2. Oracle Listener Overload or Abnormal State

When the listener is overwhelmed by a surge of connection requests (e.g., after an application server restart), its queue fills up and new requests time out before being handed off to a server process. A misconfigured or crashed listener will produce the same symptom.

-- Check listener-registered services from the database side
SELECT name,
       network_name,
       creation_date
FROM   v$services
ORDER  BY name;

-- Review alert log for listener-related errors
SELECT originating_timestamp,
       message_text
FROM   v$diag_alert_ext
WHERE  message_text LIKE '%ORA-12170%'
   OR  message_text LIKE '%TNS-12535%'
ORDER  BY originating_timestamp DESC
FETCH FIRST 30 ROWS ONLY;
Enter fullscreen mode Exit fullscreen mode

Fix: Restart the listener (lsnrctl stop / lsnrctl start) and review listener.log for repeat connection failures. Implement connection pooling on the application side to smooth out connection storms.

3. Misconfigured Timeout Parameters in sqlnet.ora / tnsnames.ora

If SQLNET.INBOUND_CONNECT_TIMEOUT is set too low in sqlnet.ora, even a minor network hiccup will cause the connection to be dropped before it completes. Similarly, a missing or incorrect CONNECT_TIMEOUT in tnsnames.ora can cause unnecessary failures on slower or geographically distant networks.

-- Check current network-related parameters from the DB
SELECT name, value
FROM   v$parameter
WHERE  name LIKE '%timeout%'
ORDER  BY name;
Enter fullscreen mode Exit fullscreen mode

Recommended sqlnet.ora settings:

-- sqlnet.ora (server-side) — edit file directly at $ORACLE_HOME/network/admin/
-- SQLNET.INBOUND_CONNECT_TIMEOUT  = 60
-- SQLNET.RECV_TIMEOUT             = 30
-- SQLNET.SEND_TIMEOUT             = 30
-- SQLNET.EXPIRE_TIME              = 10
Enter fullscreen mode Exit fullscreen mode

Recommended tnsnames.ora entry:

-- tnsnames.ora (client-side)
-- ORCL =
--   (DESCRIPTION =
--     (CONNECT_TIMEOUT=30)(RETRY_COUNT=3)(RETRY_DELAY=3)
--     (ADDRESS = (PROTOCOL=TCP)(HOST=mydb.example.com)(PORT=1521))
--     (CONNECT_DATA =
--       (SERVER = DEDICATED)
--       (SERVICE_NAME = orcl.example.com)
--     )
--   )
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist 

-- 1. Verify basic network reachability (run from client OS)
-- telnet <DB_HOST> 1521
-- tnsping <TNS_ALIAS> 5

-- 2. Confirm listener is running and services are registered
-- lsnrctl status
-- lsnrctl services

-- 3. Check for TNS errors in the listener log
-- grep "TNS-12170\|TNS-12535" $ORACLE_BASE/diag/tnslsnr/<host>/listener/trace/listener.log

-- 4. Review active connection count
SELECT status, COUNT(*) AS session_count
FROM   v$session
WHERE  type = 'USER'
GROUP  BY status;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Standardize sqlnet.ora across all environments. Always include SQLNET.EXPIRE_TIME=10 and set SQLNET.INBOUND_CONNECT_TIMEOUT to at least 60 seconds. Add these settings to your infrastructure build runbooks so every new Oracle server is configured correctly from day one.

  • Monitor listener health proactively. Use Oracle Enterprise Manager, Zabbix, or a simple cron-based script to alert when lsnrctl status shows a degraded state or when active connection counts approach the maximum threshold. Catching listener stress early prevents ORA-12170 storms during peak load.

Related Errors

Error Code Description
ORA-12535 TNS operation timed out — occurs during data transfer after connection
ORA-12541 TNS no listener — listener is not running at all
ORA-12543 TNS destination host unreachable — routing/network failure
ORA-12606 TNS application timeout — application-level timeout exceeded

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)