ORA-12520: TNS No Available Handler - Fix Shared Server

ORA-12520: TNS Listener Could Not Find Available Handler

Error Overview

Error Text: ORA-12520: TNS:listener could not find available handler for requested type of server

The ORA-12520 error is returned by the Oracle listener when a client requests a shared server connection (also called MTS — Multi-Threaded Server) and no dispatcher process is currently available or registered to handle that connection type. The listener cannot forward the client to a dispatcher, so it rejects the connection entirely.

This is distinct from ORA-12516 (no available handler for a dedicated connection) and ORA-12518 (handler handoff failure). ORA-12520 is specifically a shared server / dispatcher availability problem.

Common Causes

1. Shared Server Not Configured or Disabled

The database is running with SHARED_SERVERS=0 or DISPATCHERS parameter is not set. The listener received a request for a shared server connection but no dispatchers exist.

2. All Dispatchers at Maximum Load

DISPATCHERS is set but all running dispatcher processes have reached their connection limit (CONNECTIONS attribute). The listener has no dispatcher with available capacity.

3. Dispatcher Processes Crashed

One or more dispatcher processes died abnormally. PMON should restart them, but there is a window during which no dispatcher is available to accept connections.

4. Client Requesting Shared Server via SERVER=SHARED in TNS Descriptor

The client tnsnames.ora or connection string explicitly specifies (SERVER=SHARED), forcing a shared server connection even though the database may be configured for dedicated connections by default.

5. SERVICE_NAMES Mismatch

The service requested by the client is not registered with the listener for the shared server pathway. Dynamic service registration by LREG may not have propagated the shared server handler information yet.

6. SHARED_SERVERS Parameter Set Too Low

The SHARED_SERVERS initialization parameter defines the minimum number of shared server processes Oracle maintains. If this is set too low and peak load exceeds capacity, ORA-12520 occurs.

Diagnostic Queries

Check Current Dispatcher Configuration

-- View dispatcher parameters
SHOW PARAMETER dispatchers;
SHOW PARAMETER shared_servers;
SHOW PARAMETER max_shared_servers;

-- Active dispatchers
SELECT name, status, messages, bytes, breaks, protocol, owned, created
FROM v$dispatcher
ORDER BY name;

Check Dispatcher Load and Connections

-- Current load on each dispatcher
SELECT d.name, d.status, d.messages,
       c.circuit_count,
       d.owned AS connections
FROM v$dispatcher d
LEFT JOIN (
  SELECT dispatcher, COUNT(*) AS circuit_count
  FROM v$circuit
  GROUP BY dispatcher
) c ON d.paddr = c.dispatcher
ORDER BY d.name;

-- Maximum connections per dispatcher (from DISPATCHERS parameter)
-- Default is 1024 per dispatcher process
SELECT name, value
FROM v$parameter
WHERE name = 'dispatchers';

Check Shared Server Processes

-- Active shared server processes
SELECT name, status, messages, bytes, requests
FROM v$shared_server
ORDER BY name;

-- Shared server request queue — high values indicate bottleneck
SELECT name, queued, wait, totalq
FROM v$queue
WHERE type = 'COMMON';

Verify Service Registration with Listener

-- Services registered via LREG
SELECT service_id, name, network_name, pdb
FROM v$active_services
ORDER BY name;

-- Check handler type for services
SELECT s.name, h.handler_type, h.dispatcher
FROM v$service s, v$dispatcher_config d, v$dispatcher h
WHERE h.name IS NOT NULL;

Check Client Connection Mode

-- See current sessions and their server mode
SELECT sid, username, server, status, program, machine
FROM v$session
WHERE username IS NOT NULL
ORDER BY server, username;

-- Count by server type
SELECT server, COUNT(*) AS session_count
FROM v$session
WHERE username IS NOT NULL
GROUP BY server;

Listener Log Analysis

# Check listener log for ORA-12520 entries
# $ORACLE_BASE/diag/tnslsnr/<hostname>/listener/alert/log.xml
# Or the legacy listener.log:
grep -i "12520\|TNS-12520" $ORACLE_BASE/diag/tnslsnr/$(hostname)/listener/trace/listener.log | tail -50

Step-by-Step Resolution

1. Verify the Database Has Shared Server Enabled

-- Connect as SYSDBA
SHOW PARAMETER shared_servers;
-- If value = 0, shared server is disabled

SHOW PARAMETER dispatchers;
-- If empty or null, no dispatchers are configured

2. Enable or Increase Shared Server Dispatchers

If shared server should be active but is not configured:

-- Enable shared server with dispatchers for TCP protocol
ALTER SYSTEM SET DISPATCHERS = '(PROTOCOL=TCP)(SERVICE=mydbXDB)' SCOPE=BOTH;

-- Set minimum shared server processes
ALTER SYSTEM SET SHARED_SERVERS = 5 SCOPE=BOTH;

-- Set maximum shared server processes
ALTER SYSTEM SET MAX_SHARED_SERVERS = 20 SCOPE=BOTH;

If dispatchers are already configured but under-provisioned:

-- Add more dispatchers by updating the DISPATCHERS parameter
-- To have 3 dispatchers:
ALTER SYSTEM SET DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=3)' SCOPE=BOTH;

3. Switch Client to Dedicated Mode (Bypass the Problem)

If dedicated server connections are acceptable and shared server is not a requirement, modify the client connection descriptor to force dedicated:

# tnsnames.ora
MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = mydb)
      (SERVER = DEDICATED)    -- Force dedicated, bypasses ORA-12520
    )
  )

Or in the connect string:

-- JDBC thin URL: add /dedicated
jdbc:oracle:thin:@//host:1521/service?oracle.net.server.type=dedicated

4. Kill and Restart Stuck Dispatcher Processes

-- Identify the dispatcher process addresses
SELECT name, paddr, status FROM v$dispatcher;

-- Find OS PID from paddr
SELECT p.spid, d.name, d.status
FROM v$process p
JOIN v$dispatcher d ON p.addr = d.paddr;

# At OS level, if a dispatcher is hung:
kill -9 <spid>
# PMON will restart the dispatcher automatically within seconds

5. Force LREG to Re-Register Services

-- Force immediate service re-registration with the listener
ALTER SYSTEM REGISTER;

-- Then verify with lsnrctl:
-- lsnrctl services <listener_name>
-- Confirm (HANDLER=D000) entries appear for the service

6. Adjust Dispatcher Connection Limit

The default connections-per-dispatcher is high (typically 1024), but it can be explicitly capped in the DISPATCHERS parameter:

-- Set dispatcher with explicit connection ceiling
ALTER SYSTEM SET DISPATCHERS =
  '(PROTOCOL=TCP)(SERVICE=mydb)(CONNECTIONS=500)(DISPATCHERS=4)' SCOPE=BOTH;

Prevention Strategies

1. Monitor Dispatcher Saturation

-- Proactive dispatcher load monitoring
CREATE OR REPLACE PROCEDURE monitor_dispatchers AS
  v_max_load  NUMBER := 0.8;  -- 80% threshold
  v_load      NUMBER;
BEGIN
  SELECT MAX(owned / NULLIF(1024, 0)) INTO v_load
  FROM v$dispatcher;

  IF v_load > v_max_load THEN
    INSERT INTO dba_alerts(alert_time, alert_msg)
    VALUES (SYSDATE, 'Dispatcher load at ' || ROUND(v_load*100,1) || '% — ORA-12520 risk');
    COMMIT;
  END IF;
END;
/

2. Set Up Automatic Dispatcher Scaling

-- Oracle automatically scales shared servers between SHARED_SERVERS and MAX_SHARED_SERVERS
-- Set a generous ceiling to allow scaling
ALTER SYSTEM SET SHARED_SERVERS     = 5  SCOPE=BOTH;
ALTER SYSTEM SET MAX_SHARED_SERVERS = 50 SCOPE=BOTH;

-- Allow Oracle to auto-tune dispatchers
ALTER SYSTEM SET DISPATCHERS =
  '(PROTOCOL=TCP)(DISPATCHERS=2)(SERVICE=mydb)' SCOPE=BOTH;

3. Prefer Dedicated Connections for OLTP

Shared server is primarily useful for reducing OS process overhead in high-connection-count environments. For modern OLTP systems using connection pools, dedicated server connections combined with proper pool sizing are preferred:

• Use (SERVER=DEDICATED) in all TNS descriptors for connection-pooled applications
• Reserve shared server for legacy client/server applications with many idle connections
• Use Oracle Connection Manager (CMAN) as an alternative for connection multiplexing

4. Regular Listener and Dispatcher Health Checks

-- Weekly health check query
SELECT 'Dispatchers'  AS component, COUNT(*) AS active_count FROM v$dispatcher
UNION ALL
SELECT 'Shared Svrs', COUNT(*) FROM v$shared_server
UNION ALL
SELECT 'Circuits',    COUNT(*) FROM v$circuit;

Related Errors

• ORA-12514 - TNS Listener Does Not Currently Know of Service Requested
• ORA-12516 - TNS Listener No Available Handler for Dedicated
• ORA-12518 - TNS Listener Could Not Hand Off Client Connection
• ORA-12519 - TNS No Appropriate Service Handler Found
• ORA-12528 - TNS All Instances Blocked

Emergency Response

Quick Fixes

1. Force dedicated mode from client (fastest fix)

   # Add SERVER=DEDICATED to tnsnames.ora connect descriptor
   (SERVER = DEDICATED)

2. Increase dispatchers immediately

   ALTER SYSTEM SET DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=5)' SCOPE=BOTH;
   ALTER SYSTEM REGISTER;

3. Check for crashed dispatchers and force PMON restart

   SELECT name, status FROM v$dispatcher;
   -- If any show DEAD status, identify OS PID and kill -9 at OS level

Post-Resolution Cleanup

-- Verify dispatchers are healthy
SELECT name, status, owned AS connections FROM v$dispatcher;

-- Confirm service registration
-- lsnrctl services | grep -A5 DISPATCHER

-- Review if shared server is actually needed
SELECT server, COUNT(*) FROM v$session
WHERE username IS NOT NULL GROUP BY server;
-- If SHARED count is low vs DEDICATED, consider switching fully to dedicated mode