ORA-12528: TNS All Instances Blocked - Fix Listener Routing

ORA-12528: TNS All Instances Blocked

Error Overview

Error Text: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

The ORA-12528 error is returned by the Oracle listener when it has located one or more instances that match the requested service or SID, but every matching instance is currently in a state that blocks new connections. The listener knows the instance exists — it has a registration entry — but the instance itself has signalled that it is not ready to accept client connections.

This differs from ORA-12514 (service unknown to listener) and ORA-12541 (no listener running). With ORA-12528, the listener is running, the service is known, but the instance is explicitly refusing connections.

Common Causes

1. Instance in Restricted Session Mode

A DBA issued ALTER SYSTEM ENABLE RESTRICTED SESSION or started the database with STARTUP RESTRICT. Only users with the RESTRICTED SESSION system privilege can connect. The listener blocks all other connection attempts with ORA-12528.

2. Database in MOUNT State During Startup

The instance is starting up and has reached MOUNT state but not yet OPEN. LREG has registered the service with the listener, but the database is not open for normal connections.

3. Instance Not Yet Registered After Startup

After ALTER DATABASE OPEN, LREG sends updated service registration to the listener. There is a brief window (up to 60 seconds) before the listener updates its records. During this window, stale registration data may still indicate the instance is blocked.

4. RAC Instance Starting or Undergoing Instance Recovery

In a RAC environment, when one node is in the process of starting up and performing instance recovery for a previously crashed node, it may temporarily block connections at the listener level for that service.

5. PDB (Pluggable Database) in MOUNT State

In Oracle Multitenant, if a specific PDB service is requested but that PDB is not open (still in MOUNT state), the listener blocks connections to that service with ORA-12528.

6. Instance Quiesced

ALTER SYSTEM QUIESCE RESTRICTED places the instance in QUIESCED state, where only active sessions may continue; new connections from non-DBA users are blocked.

Diagnostic Queries

Check Instance Open Mode and Restricted Status

-- Requires SYSDBA connection
SELECT instance_name, status, database_status, active_state,
       logins, shutdown_pending
FROM v$instance;

-- Database open mode
SELECT name, open_mode, restricted, database_role
FROM v$database;

Check Restricted Session Status

-- Check if restricted mode is active
SELECT value
FROM v$parameter
WHERE name = 'restricted';

-- Alternative — check instance directly
SELECT logins FROM v$instance;
-- ALLOWED = normal, RESTRICTED = restricted session mode active

Check Who Has RESTRICTED SESSION Privilege

-- Find users who can connect during restricted mode
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE privilege = 'RESTRICTED SESSION'
ORDER BY grantee;

Check PDB Status (Multitenant)

-- Check all PDB open modes from CDB$ROOT
SELECT con_id, name, open_mode, restricted, recovery_status
FROM v$pdbs
ORDER BY con_id;

-- Services registered per PDB
SELECT s.name, s.pdb, s.network_name, s.blocked
FROM v$active_services s
ORDER BY s.pdb, s.name;

Check LREG Service Registration

-- Services currently registered and their blocked status
SELECT name, blocked, pdb, goal, dtp
FROM v$active_services
ORDER BY blocked DESC, name;

-- Instance registration info sent to listener
SELECT instance_name, service_name, status
FROM v$services;

Listener Registration Details

# View what the listener currently knows about instances
lsnrctl services

# Check for BLOCKED status in listener services output:
# Handler(s):
#   "DEDICATED" established:0 refused:0 state:blocked

Check for Active Quiesce

-- Check quiesce state
SELECT active_state FROM v$instance;
-- NORMAL = no quiesce
-- QUIESCING = in progress
-- QUIESCED = fully quiesced, new connections blocked

Step-by-Step Resolution

1. Diagnose the Blocked State

-- Connect as SYSDBA (SYSDBA connections bypass restricted/blocked states)
sqlplus / as sysdba

-- Or remotely:
sqlplus sys@//hostname:1521/sid as sysdba

-- Determine the exact cause
SELECT instance_name, status, logins, active_state,
       database_status
FROM v$instance;

SELECT name, open_mode, restricted FROM v$database;

2. Disable Restricted Session Mode

If the database is in restricted mode and it should be open to all users:

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

-- Verify
SELECT logins FROM v$instance;
-- Should return ALLOWED

3. Open the Database

If the instance is in MOUNT state and needs to be opened:

-- Open the database
ALTER DATABASE OPEN;

-- For a PDB in MOUNT state:
ALTER PLUGGABLE DATABASE pdb_name OPEN;

-- Open all PDBs
ALTER PLUGGABLE DATABASE ALL OPEN;

4. Force LREG Re-Registration

After resolving the blocking condition, force an immediate listener registration update:

-- Force LREG to re-register all services with the listener
ALTER SYSTEM REGISTER;

# Verify the listener now shows the instance as available (not blocked)
lsnrctl services
# Look for: state:ready (not state:blocked)

5. Exit Quiesced State

If the database was quiesced:

-- Un-quiesce the database
ALTER SYSTEM UNQUIESCE;

-- Verify
SELECT active_state FROM v$instance;
-- Should return NORMAL

6. Resolve Stuck RAC Instance Registration

In a RAC environment, if one node is blocking and the listener is routing all connections to it:

# Check which instances are registered with the SCAN listener
srvctl status database -db mydb

# Check individual instance status
srvctl status instance -db mydb -instance mydb1

# If an instance is down or in restricted mode, restart it
srvctl stop instance  -db mydb -instance mydb1
srvctl start instance -db mydb -instance mydb1

7. Verify After Resolution

# Full listener service check
lsnrctl services

# Expected output should show state:ready, not state:blocked
# Handler(s):
#   "DEDICATED" established:N refused:N state:ready

-- Confirm normal connections are now possible (as a non-SYSDBA user)
CONNECT app_user/password@service_name
SELECT 'Connection successful' FROM DUAL;

Prevention Strategies

1. Use STARTUP RESTRICT Intentionally and Briefly

-- Controlled pattern: start restricted, do maintenance, then open to all
STARTUP RESTRICT;

-- Perform DBA-only maintenance tasks here...
-- e.g., dictionary recompilation, patch application

-- Then open to all users when ready
ALTER SYSTEM DISABLE RESTRICTED SESSION;
ALTER SYSTEM REGISTER;  -- Force immediate listener update

2. Automate PDB Open After CDB Startup

-- Prevent PDBs from being left in MOUNT state after CDB restart
ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL SAVE STATE;

-- Verify saved state
SELECT con_name, state FROM dba_pdb_saved_states;

3. Create a Database Startup Trigger to Open PDBs

-- Automatically open all PDBs when the CDB starts
CREATE OR REPLACE TRIGGER open_all_pdbs
  AFTER STARTUP ON DATABASE
BEGIN
  EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
EXCEPTION
  WHEN OTHERS THEN
    -- Log but do not fail the trigger
    NULL;
END;
/

4. Monitor Blocked Services

-- Schedule a periodic check for blocked services
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'MONITOR_BLOCKED_SERVICES',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      DECLARE
        v_blocked NUMBER;
      BEGIN
        SELECT COUNT(*) INTO v_blocked
        FROM v$active_services
        WHERE blocked = 'YES';
        IF v_blocked > 0 THEN
          INSERT INTO dba_alerts(alert_time, alert_msg)
          VALUES (SYSDATE, v_blocked || ' service(s) blocked — ORA-12528 risk');
          COMMIT;
        END IF;
      END;
    ]',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
    enabled         => TRUE
  );
END;
/

5. Best Practices for Restricted Mode

• Always set a reminder or scheduled job to disable restricted mode after maintenance
• Grant RESTRICTED SESSION only to DBA accounts, not application users
• Document every use of STARTUP RESTRICT in the change management log
• In RAC, coordinate restricted mode across all instances to avoid split-brain routing by the listener

Related Errors

• ORA-12514 - TNS Listener Does Not Currently Know of Service
• ORA-12516 - TNS Listener No Available Handler
• ORA-12518 - TNS Listener Could Not Hand Off Client Connection
• ORA-12520 - TNS No Available Handler for Shared Server
• ORA-01033 - Oracle Initialization or Shutdown in Progress
• ORA-01034 - Oracle Not Available

Emergency Response

Quick Fixes

1. Check and clear restricted mode

   SELECT logins FROM v$instance;
   ALTER SYSTEM DISABLE RESTRICTED SESSION;

2. Open a mounted database or PDB

   SELECT open_mode FROM v$database;
   ALTER DATABASE OPEN;
   -- or
   ALTER PLUGGABLE DATABASE ALL OPEN;

3. Force listener re-registration

   ALTER SYSTEM REGISTER;
   -- Wait 30 seconds, then:
   -- lsnrctl services  (verify state:ready)

Post-Resolution Cleanup

-- Confirm normal operation
SELECT instance_name, status, logins, active_state FROM v$instance;
SELECT name, open_mode, restricted FROM v$database;
SELECT name, blocked FROM v$active_services ORDER BY blocked DESC;

-- Save PDB states to prevent recurrence
ALTER PLUGGABLE DATABASE ALL SAVE STATE;

-- Confirm listener shows ready status
-- lsnrctl services | grep state

-- Document the incident: why was restricted mode active?
-- Update runbook to include DISABLE RESTRICTED SESSION in post-maintenance checklist