Oracle ORA-12535 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12535: TNS Operation Timed Out — Causes, Fixes & Prevention

ORA-12535 is a TNS (Transparent Network Substrate) layer error that occurs when an Oracle client fails to receive a response from the server within the allotted connection timeout period. This error can stem from network issues, firewall interference, or misconfigured Oracle network parameters. Left unresolved, it can bring down entire application tiers that depend on database connectivity.

Top 3 Causes

1. Firewall or Network Device Killing Idle TCP Sessions

Firewalls and load balancers often silently drop idle TCP connections after a timeout threshold. Connection pool sessions sitting idle are terminated by the network device, but the Oracle client is unaware — resulting in ORA-12535 on the next operation.

-- Identify long-running inactive sessions
SELECT sid, serial#, username, status,
       last_call_et AS idle_seconds,
       machine, program
FROM v$session
WHERE status = 'INACTIVE'
  AND last_call_et > 300   -- idle more than 5 minutes
ORDER BY last_call_et DESC;

-- Kill a zombie session cleaned up by firewall
ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

Fix: Enable Dead Connection Detection in sqlnet.ora:

-- Add to $ORACLE_HOME/network/admin/sqlnet.ora
-- SQLNET.EXPIRE_TIME = 10   (probe every 10 minutes)
-- SQLNET.RECV_TIMEOUT = 60
-- SQLNET.SEND_TIMEOUT = 60

2. Oracle Listener Not Responding

If the Oracle Listener process is down, overloaded, or misconfigured, client connection requests will hang until the timeout triggers ORA-12535. This is a critical failure point since the Listener handles all incoming network requests.

-- Check registered services and listener health
SELECT name, network_name, creation_date
FROM v$services
ORDER BY name;

-- Check instance availability
SELECT inst_id, instance_name, status, host_name
FROM gv$instance;

-- Review recent alert log entries for listener errors
SELECT originating_timestamp, message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-12535%'
   OR message_text LIKE '%listener%'
ORDER BY originating_timestamp DESC
FETCH FIRST 10 ROWS ONLY;

Fix: Restart the Listener and verify registration:

-- Run from OS (can use HOST command in SQL*Plus)
-- host lsnrctl stop
-- host lsnrctl start
-- host lsnrctl status

-- Confirm database is open after listener restart
SELECT name, open_mode FROM v$database;

3. Insufficient Timeout Values in sqlnet.ora / tnsnames.ora

If CONNECT_TIMEOUT, SQLNET.INBOUND_CONNECT_TIMEOUT, or related parameters are set too low for your network latency (common in WAN or cloud environments), even valid connections will be dropped before they complete.

-- Check current network-related parameters
SELECT name, value
FROM v$parameter
WHERE name LIKE '%timeout%'
   OR name LIKE '%listener%'
ORDER BY name;

-- Monitor network wait events to gauge real latency
SELECT event,
       total_waits,
       total_timeouts,
       average_wait
FROM v$system_event
WHERE event LIKE 'SQL*Net%'
ORDER BY total_timeouts DESC;

Fix: Update tnsnames.ora with appropriate timeout values:

/*
ORCL =
  (DESCRIPTION =
    (CONNECT_TIMEOUT=30)
    (RETRY_COUNT=3)
    (RETRY_DELAY=3)
    (ADDRESS =
      (PROTOCOL = TCP)(HOST = db-host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)))
*/

Quick Fix Checklist

Ping and traceroute to the DB host to rule out basic network issues.
Check Listener with lsnrctl status and restart if necessary.
Enable DCD by setting SQLNET.EXPIRE_TIME=10 in sqlnet.ora.
Increase timeout values in both sqlnet.ora and tnsnames.ora to match your actual network RTT.
Review firewall rules to ensure TCP port 1521 is open and idle session timeouts are adequate.

Prevention Tips

Always enable SQLNET.EXPIRE_TIME in production environments to detect and clean up stale connections before they cause application errors.
Monitor network wait events regularly using v$system_event and ASH (v$active_session_history) to catch timeout trends early.

-- Proactive timeout monitoring via ASH
SELECT event, COUNT(*) AS occurrences
FROM v$active_session_history
WHERE event LIKE '%timeout%'
  AND sample_time > SYSDATE - 1
GROUP BY event
ORDER BY occurrences DESC;

Related Errors

Error Code	Description
ORA-12170	TNS: Connect timeout occurred (server-side)
ORA-12541	TNS: No listener
ORA-12543	TNS: Destination host unreachable
ORA-03113	End-of-file on communication channel
ORA-03114	Not connected to ORACLE

📖 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.

DEV Community