ORA-12537 TNS Connection Closed - Resolution Guide

ORA-12537: TNS:connection Closed

Error Overview

Error Text: ORA-12537: TNS:connection closed

This error occurs when a network connection to an Oracle database is unexpectedly terminated. The connection was established but then dropped before the operation completed. It’s a critical production error that often indicates network infrastructure problems, listener issues, or resource exhaustion.

Common Causes

1. Firewall Timeout

• Firewall dropping idle connections
• Connection idle longer than firewall timeout
• Stateful firewall losing connection state

2. Network Infrastructure Issues

• Network switches or routers dropping connections
• MTU size mismatches causing packet fragmentation
• Load balancer timeout settings
• VPN tunnel disconnections

3. Listener Problems

• Listener process crashing or restarting
• Listener overloaded with connection requests
• Incorrect listener configuration

4. Oracle Server Issues

• Database instance crash or shutdown
• PMON process cleanup killing sessions
• Memory pressure causing process termination
• OS killing Oracle processes (OOM killer)

5. Client-Side Issues

• Client network timeout settings too aggressive
• TCP keepalive not configured
• Client process abnormal termination

Diagnostic Queries

Check Listener Status and Log

# Check listener status
lsnrctl status

# View listener log for errors
tail -100 $ORACLE_BASE/diag/tnslsnr/$(hostname)/listener/trace/listener.log

# Check for connection refused or reset entries
grep -i "12537\|connection closed\|connection reset" $ORACLE_BASE/diag/tnslsnr/*/listener/trace/listener.log

Check Database Instance Status

-- Verify instance is running
SELECT instance_name, status, startup_time, host_name
FROM v$instance;

-- Check for recent instance restarts
SELECT startup_time, instance_name
FROM v$instance;

-- Check alert log for errors around the time of disconnection
SELECT originating_timestamp, message_text
FROM x$dbgalertext
WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '1' HOUR
  AND (message_text LIKE '%ORA-%' OR message_text LIKE '%shutdown%' OR message_text LIKE '%terminated%')
ORDER BY originating_timestamp DESC;

Check Session and Process Limits

-- Are we hitting resource limits?
SELECT resource_name, current_utilization, max_utilization, limit_value
FROM v$resource_limit
WHERE resource_name IN ('processes', 'sessions', 'transactions');

-- Check for dead/disconnected sessions
SELECT status, COUNT(*) as session_count
FROM v$session
GROUP BY status;

-- Sessions killed recently (KILLED status)
SELECT sid, serial#, username, machine, program, status, last_call_et
FROM v$session
WHERE status = 'KILLED';

Check Network Configuration

-- Current SQL*Net parameters
SELECT name, value
FROM v$parameter
WHERE name IN (
  'sqlnet.expire_time',
  'tcp.connect_timeout',
  'tcp.validnode_checking'
);

-- Check for SQLNET.EXPIRE_TIME (dead connection detection)
-- This should be set in sqlnet.ora

Check for OOM Killer Activity (Linux)

# Check if Linux OOM killer terminated Oracle processes
dmesg | grep -i "out of memory\|killed process" | tail -20

# Check specific Oracle process kills
grep -i "oracle\|ora_" /var/log/messages | grep -i "killed"

Resolution Steps

Solution 1: Configure TCP Keepalive

# In sqlnet.ora on BOTH client and server:
SQLNET.EXPIRE_TIME = 10
# Sends a probe every 10 minutes to keep connection alive

# For more aggressive keepalive (Linux server):
# /etc/sysctl.conf
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_intvl = 60
net.ipv4.tcp_keepalive_probes = 5

# Apply without reboot
sysctl -p

Solution 2: Adjust Firewall Timeouts

# Identify firewall timeout
# Most firewalls default to 30-60 minutes for idle TCP connections

# Set SQLNET.EXPIRE_TIME lower than firewall timeout
# If firewall drops at 30 min, set probe at 10 min:
SQLNET.EXPIRE_TIME = 10

# Or request firewall team to increase timeout
# Recommended: at least 2 hours for Oracle connections

Solution 3: Configure Connection Timeout Parameters

# sqlnet.ora - Server side
SQLNET.EXPIRE_TIME = 10                    # Dead connection detection (minutes)
SQLNET.RECV_TIMEOUT = 300                  # Receive timeout (seconds)
SQLNET.SEND_TIMEOUT = 300                  # Send timeout (seconds)

# sqlnet.ora - Client side
SQLNET.OUTBOUND_CONNECT_TIMEOUT = 30       # Connection establishment timeout
SQLNET.RECV_TIMEOUT = 600                  # Allow longer for query results
TCP.CONNECT_TIMEOUT = 10                   # TCP level connection timeout

Solution 4: Fix Listener Configuration

# listener.ora - Ensure proper configuration
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = your_hostname)(PORT = 1521))
    )
  )

# Increase listener queue size for busy servers
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = your_hostname)(PORT = 1521))
      (QUEUESIZE = 50)
    )
  )

# Reload listener after changes
lsnrctl reload

Solution 5: Application Connection Pool Validation

-- Configure connection pool to validate connections before use
-- JDBC example:
-- Set validationQuery: SELECT 1 FROM dual
-- Set testOnBorrow: true
-- Set testWhileIdle: true
-- Set timeBetweenEvictionRuns: 30000

-- For Oracle UCP (Universal Connection Pool):
-- setValidateConnectionOnBorrow(true)
-- setConnectionWaitTimeout(30)
-- setSQLForValidateConnection("SELECT 1 FROM dual")

Solution 6: Check and Increase Resource Limits

-- If hitting process/session limits
ALTER SYSTEM SET processes = 500 SCOPE=SPFILE;
ALTER SYSTEM SET sessions = 600 SCOPE=SPFILE;
-- Requires database restart

-- Check current limits
SHOW PARAMETER processes;
SHOW PARAMETER sessions;

Monitoring and Prevention

Proactive Connection Monitoring

-- Monitor connection patterns
SELECT
  TO_CHAR(sample_time, 'YYYY-MM-DD HH24') as hour,
  COUNT(DISTINCT session_id) as active_sessions,
  COUNT(CASE WHEN event LIKE '%SQL*Net%' THEN 1 END) as network_waits
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1
GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

-- Track disconnection frequency
SELECT
  TO_CHAR(logon_time, 'YYYY-MM-DD') as day,
  COUNT(*) as total_sessions,
  SUM(CASE WHEN status = 'KILLED' THEN 1 ELSE 0 END) as killed_sessions
FROM v$session
GROUP BY TO_CHAR(logon_time, 'YYYY-MM-DD')
ORDER BY day DESC;

Network Health Check Script

#!/bin/bash
# Quick network connectivity test to Oracle server
DB_HOST="your_db_host"
DB_PORT="1521"

# Test TCP connectivity
nc -z -w5 $DB_HOST $DB_PORT
if [ $? -ne 0 ]; then
  echo "ERROR: Cannot reach $DB_HOST:$DB_PORT"
  exit 1
fi

# Test tnsping
tnsping YOUR_TNS_ALIAS
if [ $? -ne 0 ]; then
  echo "ERROR: tnsping failed"
  exit 1
fi

echo "Network connectivity OK"

Related Errors

• ORA-03113: End-of-file on Communication - Similar disconnection error
• ORA-12170: TNS Connect Timeout - Connection establishment timeout
• ORA-12547: TNS Lost Contact - Connection lost during operation
• ORA-12541: TNS No Listener - Listener not running
• ORA-12560: TNS Protocol Adapter Error - Protocol-level failure