Oracle ORA-12547 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12547: TNS Lost Contact — Causes, Fixes, and Prevention

ORA-12547 occurs when the Oracle Net (TNS) layer loses communication between the client and the server during the connection handoff process. It most commonly surfaces when the Oracle listener successfully receives a connection request but fails to pass it to a dedicated server process. This error is deceptively simple in its message but can stem from several distinct root causes.

Top 3 Causes

1. Incorrect Permissions on the Oracle Binary

The single most common cause is a missing or incorrect setuid bit on $ORACLE_HOME/bin/oracle. The listener spawns a new server process by executing this binary, and if the permissions are wrong, the process creation fails immediately.

-- Verify active server processes (run after successful connection)
SELECT spid, program, username, background
FROM v$process
WHERE program LIKE '%oracle%'
ORDER BY spid;

The correct OS permission for the Oracle binary should be -rwsr-s--x (octal 6751). After OS patching or security hardening, this is frequently reset. Fix it with:
chmod 6751 $ORACLE_HOME/bin/oracle

2. Listener and Instance Misalignment

When the listener references a different ORACLE_HOME than the running instance, the handoff fails mid-connection. This often happens in multi-Oracle-home environments or after a migration.

-- Force dynamic service registration
ALTER SYSTEM REGISTER;

-- Confirm registered services
SELECT name, network_name, pdb, creation_date
FROM v$active_services
ORDER BY name;

-- Check listener-related parameters
SHOW PARAMETER local_listener;
SHOW PARAMETER remote_listener;

Always ensure that listener.ora points to the same ORACLE_HOME as your target database instance.

3. OS Resource Exhaustion

When the operating system runs out of process slots, file descriptors, or shared memory, the fork() call to create a new Oracle server process fails silently — dropping the client connection with ORA-12547.

-- Check Oracle resource limits and current utilization
SELECT resource_name,
       current_utilization,
       max_utilization,
       limit_value
FROM v$resource_limit
WHERE resource_name IN ('processes', 'sessions', 'enqueue_locks')
ORDER BY resource_name;

-- Identify stale inactive sessions consuming process slots
SELECT sid, serial#, username, status,
       last_call_et AS idle_seconds, machine
FROM v$session
WHERE status = 'INACTIVE'
  AND last_call_et > 1800
  AND username IS NOT NULL
ORDER BY idle_seconds DESC;

-- Kill a stale session if needed
-- ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Quick Fix Checklist

Check binary permissions: ls -la $ORACLE_HOME/bin/oracle — restore with chmod 6751 if setuid is missing.
Restart the listener: lsnrctl stop → lsnrctl start → verify with lsnrctl status.
Force service re-registration: Run ALTER SYSTEM REGISTER; from SQL*Plus.
Review alert log: Check $ORACLE_BASE/diag/rdbms/<db>/<sid>/trace/alert_<sid>.log for accompanying ORA-27300/27301 errors that pinpoint OS-level failures.
Increase processes parameter if utilization is near the limit:

-- Increase max processes (requires restart)
ALTER SYSTEM SET processes = 500 SCOPE = SPFILE;

-- Then bounce the database
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

Prevention Tips

Monitor resource utilization proactively:

-- Run this regularly; alert if usage exceeds 80%
SELECT resource_name,
       current_utilization,
       limit_value,
       ROUND(current_utilization /
             DECODE(limit_value,'UNLIMITED',99999, TO_NUMBER(limit_value))
             * 100, 2) AS pct_used
FROM v$resource_limit
WHERE limit_value NOT IN ('UNLIMITED','0')
ORDER BY pct_used DESC;

Automate permission checks after every OS patch cycle. Add a post-patch validation step to your change management process that explicitly verifies $ORACLE_HOME/bin/oracle permissions. Security scanning tools (CIS benchmarks, Qualys, etc.) are known to strip the setuid bit, which is a leading cause of ORA-12547 in production environments.

Related Errors

ORA-12541 — TNS: no listener (listener not running)
ORA-12514 — TNS: service not registered with listener
ORA-12560 — TNS: protocol adapter error (local IPC issues)
ORA-27300/27301/27302 — OS process spawn failure, often logged alongside ORA-12547 in the alert 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.

DEV Community