DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-12537 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12537: TNS Connection Closed – Causes, Fixes & Prevention

What Is ORA-12537?

ORA-12537 ("TNS: connection closed") occurs when the Oracle Net layer detects that a network connection between the client and the database server has been unexpectedly terminated. This error is typically raised by an intermediate network device (such as a firewall or load balancer) silently dropping idle TCP sessions, or by the Oracle Listener/database instance shutting down abruptly. It is one of the most common connectivity errors in production Oracle environments.

Top 3 Causes

1. Firewall or Network Device Session Timeout

The most frequent culprit. Firewalls often terminate TCP sessions that have been idle beyond a configured threshold. The Oracle client believes the session is still alive, but the next SQL execution fails with ORA-12537 because the underlying TCP connection no longer exists.

Fix – Enable Dead Connection Detection (DCD) in sqlnet.ora:

-- sqlnet.ora (set lower than the firewall idle timeout, in minutes)
SQLNET.EXPIRE_TIME = 10
Enter fullscreen mode Exit fullscreen mode

Check idle sessions in the database:

SELECT
    sid,
    serial#,
    username,
    status,
    last_call_et AS idle_seconds,
    machine
FROM v$session
WHERE username IS NOT NULL
  AND status = 'INACTIVE'
ORDER BY last_call_et DESC;
Enter fullscreen mode Exit fullscreen mode

Limit idle time via Oracle Profile:

-- Create a profile that kills sessions idle for more than 30 minutes
CREATE PROFILE app_profile LIMIT
    IDLE_TIME    30
    CONNECT_TIME UNLIMITED;

ALTER USER app_user PROFILE app_profile;
Enter fullscreen mode Exit fullscreen mode

2. Oracle Listener or Instance Crash/Restart

When the Listener is restarted or the database instance is shut down (especially with SHUTDOWN ABORT), all existing client connections are dropped immediately, producing ORA-12537 on the client side.

Verify listener and instance status:

-- Check instance health
SELECT instance_name, status, database_status
FROM v$instance;

-- Identify orphaned or killed sessions
SELECT sid, serial#, username, status, last_call_et
FROM v$session
WHERE username IS NOT NULL
ORDER BY last_call_et DESC;

-- Force kill a stale session if needed
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
Enter fullscreen mode Exit fullscreen mode

3. Misconfigured Oracle Net Parameters

Incorrect values in sqlnet.ora or listener.ora — such as an overly short SQLNET.INBOUND_CONNECT_TIMEOUT — cause the server to drop the connection before the handshake completes. Undersized buffer settings can also break connections during large data transfers.

Recommended sqlnet.ora settings:

-- Increase handshake timeout (seconds)
SQLNET.INBOUND_CONNECT_TIMEOUT = 60

-- Tune network buffers for large data loads
RECV_BUF_SIZE = 87380
SEND_BUF_SIZE = 87380

-- Reduce TCP latency
TCP.NODELAY = YES
Enter fullscreen mode Exit fullscreen mode

Verify current Oracle Net parameters from within the DB:

SELECT name, value
FROM v$parameter
WHERE name LIKE '%sqlnet%'
   OR name LIKE '%recv_buf%'
   OR name LIKE '%send_buf%';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  1. Set SQLNET.EXPIRE_TIME in sqlnet.ora to send keep-alive probes before the firewall timeout.
  2. Check the Listener log ($ORACLE_BASE/diag/tnslsnr/.../listener.log) for recent errors.
  3. Review the database alert log for instance crashes or unexpected shutdowns.
  4. Kill orphaned sessions using ALTER SYSTEM KILL SESSION.
  5. Increase SQLNET.INBOUND_CONNECT_TIMEOUT if clients are slow to authenticate.

Prevention Tips

  • Enable Connection Validation in Your Pool: Always set testOnBorrow=true (JDBC/DBCP) or setValidateConnectionOnBorrow(true) (Oracle UCP) so stale connections are detected before use, not during a production query.
  • Monitor Idle Sessions Proactively: Schedule a recurring job using DBMS_SCHEDULER to alert DBAs when the count of sessions idle for more than 30 minutes exceeds a threshold, giving you early warning before ORA-12537 impacts end users.

Related Errors

Error Code Description
ORA-12541 TNS: no listener
ORA-12535 TNS: operation timed out
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.

Top comments (0)