Oracle ORA-12564 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12564: TNS Connection Refused — Causes, Fixes, and Prevention

Oracle error ORA-12564 occurs when a client attempts to establish a TNS connection to an Oracle database, but the server explicitly refuses the request. Unlike ORA-12541 (no listener), this error means the listener is running but is actively rejecting the connection due to configuration or resource constraints. Understanding the root cause quickly is critical to minimizing downtime in production environments.

Top 3 Causes and Fixes

1. Valid Node Checking (VNC) Blocking the Client IP

Oracle's sqlnet.ora supports IP-level access control via TCP.VALIDNODE_CHECKING. If your client IP is not in the TCP.INVITED_NODES list or is explicitly listed in TCP.EXCLUDED_NODES, the listener will refuse the connection immediately.

Fix: Edit $ORACLE_HOME/network/admin/sqlnet.ora and update the node lists, then reload the listener.

-- Example sqlnet.ora configuration
-- TCP.VALIDNODE_CHECKING = YES
-- TCP.INVITED_NODES = (192.168.1.10, 10.0.0.5)
-- TCP.EXCLUDED_NODES = (192.168.1.99)

-- After editing sqlnet.ora, reload listener from OS prompt:
-- lsnrctl reload LISTENER

-- Verify listener is active and services are registered
-- lsnrctl status LISTENER

2. Database in RESTRICTED Mode or Not Fully Open

If the database instance is in MOUNT, NOMOUNT, or RESTRICTED SESSION mode, only users with SYSDBA or RESTRICTED SESSION privilege can connect. All other connection attempts are refused, often surfacing as ORA-12564 or ORA-01035.

Fix: Disable restricted session mode or open the database fully.

-- Check current instance status and login mode
SELECT STATUS, LOGINS, DATABASE_STATUS
FROM V$INSTANCE;

-- Disable RESTRICTED SESSION mode
ALTER SYSTEM DISABLE RESTRICTED SESSION;

-- If the database is in MOUNT state, open it
ALTER DATABASE OPEN;

-- Confirm the change
SELECT STATUS, LOGINS
FROM V$INSTANCE;

3. Maximum Sessions or Processes Limit Exceeded

When the number of active sessions or processes reaches the limit defined by PROCESSES and SESSIONS initialization parameters, Oracle cannot accept new connections and refuses them. This is common during peak traffic periods.

Fix: Check resource limits and either kill idle sessions or increase parameter values.

-- Check current resource utilization
SELECT RESOURCE_NAME,
       CURRENT_UTILIZATION,
       MAX_UTILIZATION,
       LIMIT_VALUE
FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME IN ('sessions', 'processes');

-- Find and kill long-running inactive sessions
SELECT SID, SERIAL#, USERNAME, STATUS, LAST_CALL_ET
FROM V$SESSION
WHERE STATUS = 'INACTIVE'
  AND LAST_CALL_ET > 3600
ORDER BY LAST_CALL_ET DESC;

-- Kill a specific inactive session
ALTER SYSTEM KILL SESSION '101,202' IMMEDIATE;

-- Increase PROCESSES and SESSIONS limits (requires restart)
ALTER SYSTEM SET PROCESSES = 500 SCOPE = SPFILE;
ALTER SYSTEM SET SESSIONS = 550 SCOPE = SPFILE;

Quick Prevention Tips

Monitor resource limits proactively. Set up alerts when session or process utilization exceeds 80% of the defined limit. Use the query below in a scheduled monitoring job:

SELECT RESOURCE_NAME,
       CURRENT_UTILIZATION,
       LIMIT_VALUE,
       ROUND(CURRENT_UTILIZATION /
             DECODE(LIMIT_VALUE,'UNLIMITED',9999,
             TO_NUMBER(LIMIT_VALUE)) * 100, 2) AS USAGE_PCT
FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME IN ('sessions','processes');

Version-control your network configuration files. Store sqlnet.ora, listener.ora, and tnsnames.ora in a Git repository. Any time IPs change or security policies are updated, review TCP.INVITED_NODES and TCP.EXCLUDED_NODES as part of your change management checklist.

Related Errors

Error Code	Description
ORA-12541	TNS: no listener — listener is not running at all
ORA-12537	TNS: connection closed — connection dropped after handshake
ORA-01035	Oracle only available to RESTRICTED SESSION users
ORA-00020	Maximum number of processes exceeded
ORA-12520	Listener could not find available handler

📖 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