DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-12525 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12525: TNS Listener Has Not Received Client Request in Time Allowed

ORA-12525 is a Oracle Net (TNS) error that occurs when the listener establishes a TCP connection with a client but does not receive the complete TNS connect data packet within the configured timeout period (INBOUND_CONNECT_TIMEOUT). This error is commonly seen in high-latency WAN environments, behind firewalls, or during network-level denial-of-service attacks targeting the Oracle listener port.

Top 3 Causes and Fixes

Cause 1: INBOUND_CONNECT_TIMEOUT Set Too Low

The listener's timeout value may be too aggressive for your network latency, causing legitimate clients to be dropped before they complete the TNS handshake.

Check current timeout settings:

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

Fix — Update listener.ora and sqlnet.ora:

-- In listener.ora: increase the inbound connect timeout (seconds)
-- INBOUND_CONNECT_TIMEOUT_LISTENER = 120

-- In sqlnet.ora: align client-side timeout
-- SQLNET.INBOUND_CONNECT_TIMEOUT = 120

-- Apply dynamically without listener restart (via lsnrctl):
-- LSNRCTL> SET INBOUND_CONNECT_TIMEOUT 120
-- LSNRCTL> SAVE_CONFIG
Enter fullscreen mode Exit fullscreen mode

Cause 2: Firewall or Network Device Interference

Firewalls and load balancers sitting between the client and the Oracle server can delay or drop TNS handshake packets, causing the listener to time out even for legitimate users.

Check for SQL*Net wait events indicating network delay:

-- Identify SQL*Net related wait events with high wait times
SELECT event,
       total_waits,
       time_waited,
       average_wait
FROM v$system_event
WHERE event LIKE '%SQL*Net%'
ORDER BY time_waited DESC;

-- Review recent connection failures from AWR
SELECT to_char(sample_time, 'YYYY-MM-DD HH24:MI') AS snap_time,
       event,
       COUNT(*) AS hit_count
FROM dba_hist_active_sess_history
WHERE event LIKE '%SQL*Net%'
  AND sample_time >= SYSDATE - 1
GROUP BY to_char(sample_time, 'YYYY-MM-DD HH24:MI'), event
ORDER BY snap_time DESC;
Enter fullscreen mode Exit fullscreen mode

Quick Fix: Coordinate with your network team to ensure the firewall's idle connection timeout is greater than Oracle's INBOUND_CONNECT_TIMEOUT. Also enable TCP keepalive at the OS and Oracle level.

-- Enable Dead Connection Detection in sqlnet.ora
-- SQLNET.EXPIRE_TIME = 10  (minutes - sends probe packets to detect dead connections)
Enter fullscreen mode Exit fullscreen mode

Cause 3: DoS Attack / Port Scanning on Listener Port

Attackers or scanners may open TCP connections to port 1521 without sending TNS data, exhausting listener resources and flooding the listener log with ORA-12525 errors.

Identify suspicious inactive sessions and connection sources:

-- Find long-standing inactive sessions by host
SELECT machine,
       username,
       status,
       logon_time,
       last_call_et AS seconds_inactive
FROM v$session
WHERE status = 'INACTIVE'
  AND last_call_et > 3600   -- inactive for more than 1 hour
ORDER BY last_call_et DESC;

-- Review audit trail for failed connection attempts
SELECT os_username,
       userhost,
       timestamp,
       action_name,
       returncode
FROM dba_audit_trail
WHERE returncode != 0
  AND timestamp >= SYSDATE - 1
ORDER BY timestamp DESC
FETCH FIRST 50 ROWS ONLY;
Enter fullscreen mode Exit fullscreen mode

Fix — Restrict listener access to known IP ranges via sqlnet.ora:

-- Add to sqlnet.ora to whitelist trusted IP addresses
-- TCP.VALIDNODE_CHECKING = YES
-- TCP.INVITED_NODES = (10.0.0.0/8, 192.168.1.0/24)
-- TCP.EXCLUDED_NODES = (203.0.113.55)

-- Reload listener after updating sqlnet.ora
-- LSNRCTL> RELOAD
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary

Symptom Action
Occasional timeouts on slow WAN Increase INBOUND_CONNECT_TIMEOUT to 120+ seconds
Errors after firewall changes Align firewall idle timeout with Oracle timeout values
Mass ORA-12525 in listener.log Suspect DoS; enable TCP.VALIDNODE_CHECKING and block offending IPs

Prevention Tips

  • Tune timeouts proactively: Always configure INBOUND_CONNECT_TIMEOUT based on your actual network round-trip time (RTT). For WAN or VPN environments, values between 90–180 seconds are generally recommended. Review listener logs quarterly.
  • Harden listener security from day one: Enable TCP.VALIDNODE_CHECKING in sqlnet.ora, restrict listener access to known application server IPs, and avoid exposing port 1521 directly to the internet. Pair this with Oracle Auditing to catch unauthorized access attempts early.

Related Oracle Errors

  • ORA-12516 — Listener cannot find available handler
  • ORA-12541 — No listener (listener not running)
  • ORA-03135 — Connection lost contact (often appears alongside ORA-12525)
  • TNS-12535 — TNS operation timed out (commonly found together in listener.log)

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