DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-12152 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12152: TNS: Unable to Send Break Message — Causes, Fixes, and Prevention

What Is ORA-12152?

ORA-12152 is a network-layer error that occurs when the Oracle TNS (Transparent Network Substrate) client cannot deliver a break message to the database server. This typically happens when a user cancels a running query (e.g., pressing Ctrl+C) or when an application tries to interrupt an active database session, but the underlying network connection cannot relay that interrupt signal. Left unresolved, it can cause orphaned server sessions and resource leaks on the database side.

Top 3 Causes

1. Firewall or Load Balancer Blocking OOB Packets

Oracle Net transmits break messages using TCP Out-Of-Band (OOB/Urgent) data by default. Many firewalls, load balancers (AWS ELB, F5), and NAT devices do not support or actively block OOB packets, causing the break message to fail silently.

-- Check current network service banner for the session
SELECT network_service_banner
FROM   v$session_connect_info
WHERE  sid = SYS_CONTEXT('USERENV', 'SID');
Enter fullscreen mode Exit fullscreen mode

2. Misconfigured or Missing sqlnet.ora Parameters

A mismatch between client-side and server-side sqlnet.ora configurations — especially the DISABLE_OOB parameter — is a leading cause. If the server expects OOB but the client's network path cannot deliver it, break messages will fail consistently.

-- Identify inactive/orphaned sessions lingering after the error
SELECT sid,
       serial#,
       username,
       status,
       machine,
       last_call_et AS idle_seconds
FROM   v$session
WHERE  status   = 'INACTIVE'
AND    last_call_et > 1800   -- idle more than 30 minutes
ORDER  BY last_call_et DESC;
Enter fullscreen mode Exit fullscreen mode

3. Unresponsive Listener or Server Shadow Process

If the Oracle Listener or the dedicated server process is in a degraded state, it may be unable to receive and process the break signal even when it arrives on the network. Listener logs often show accompanying TNS-12535 or TNS-00505 timeout errors around the same timestamp.

-- Check sessions stuck on SQL*Net related wait events
SELECT s.sid,
       s.serial#,
       s.username,
       w.event,
       w.seconds_in_wait
FROM   v$session      s
JOIN   v$session_wait w ON s.sid = w.sid
WHERE  w.event LIKE '%SQL*Net%'
   OR  w.event LIKE '%message%'
ORDER  BY w.seconds_in_wait DESC;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 — Set DISABLE_OOB on both client and server sqlnet.ora:

-- Add to $ORACLE_HOME/network/admin/sqlnet.ora (both sides)
DISABLE_OOB = ON
Enter fullscreen mode Exit fullscreen mode

Restart the listener after the change:

-- OS commands
-- lsnrctl stop
-- lsnrctl start
Enter fullscreen mode Exit fullscreen mode

Step 2 — Kill orphaned sessions left after the error:

-- Force-kill a specific orphaned session
ALTER SYSTEM KILL SESSION '&sid,&serial#' IMMEDIATE;
Enter fullscreen mode Exit fullscreen mode

Step 3 — Recommended full sqlnet.ora settings:

-- Recommended sqlnet.ora template
DISABLE_OOB        = ON
SQLNET.EXPIRE_TIME = 10    -- Dead Connection Detection (minutes)
SQLNET.RECV_TIMEOUT = 60   -- Receive timeout (seconds)
SQLNET.SEND_TIMEOUT = 60   -- Send timeout (seconds)
TCP.CONNECT_TIMEOUT = 15   -- TCP connect timeout (seconds)
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Enable Dead Connection Detection (DCD): Set SQLNET.EXPIRE_TIME = 10 in sqlnet.ora so the server automatically cleans up sessions from clients that disconnected abnormally — reducing orphan session buildup after ORA-12152 events.
  • Standardize DISABLE_OOB = ON across all environments: Include it in your enterprise-standard sqlnet.ora template and ensure the firewall's TCP idle timeout is always greater than Oracle's SQLNET.EXPIRE_TIME to prevent the firewall from silently dropping connections before Oracle can detect them.

Related Oracle Errors

Error Code Description
ORA-12150 TNS: unable to send data
ORA-12535 TNS: operation timed out
ORA-12537 TNS: connection closed
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)