Oracle ORA-12526 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12526: TNS – Listener: All Appropriate Instances Are in Restricted Mode

ORA-12526 occurs when a client attempts to connect to an Oracle database, but every available instance is running in Restricted Mode, which only allows users with the RESTRICTED SESSION privilege (typically DBAs) to log in. Regular application users are completely blocked from connecting, making this error a potential service-affecting issue in production environments. Understanding the root cause quickly is essential to minimizing downtime.

Top 3 Causes

1. Database Started with STARTUP RESTRICT

The most common cause is a DBA starting the database using the STARTUP RESTRICT command for maintenance, and forgetting to disable restricted mode afterward.

-- Check if the instance is in restricted mode
SELECT INSTANCE_NAME, STATUS, LOGINS
FROM V$INSTANCE;
-- LOGINS = 'RESTRICTED' confirms the problem

-- Fix: Disable restricted mode without restarting
ALTER SYSTEM DISABLE RESTRICTED SESSION;

-- Verify the fix
SELECT INSTANCE_NAME, LOGINS
FROM V$INSTANCE;
-- LOGINS should now show 'ALLOWED'

2. Restricted Mode Manually Enabled During Maintenance

A DBA may have run ALTER SYSTEM ENABLE RESTRICTED SESSION for a maintenance task and failed to disable it after completion, especially when a script error caused the cleanup step to be skipped.

-- Enable restricted mode (maintenance use only)
ALTER SYSTEM ENABLE RESTRICTED SESSION;

-- ... perform maintenance work ...

-- Always disable after work is done
ALTER SYSTEM DISABLE RESTRICTED SESSION;

-- Confirm current login status
SELECT NAME, OPEN_MODE, RESTRICTED
FROM V$DATABASE;
-- RESTRICTED column should return 'NO'

3. RAC Environment – All Instances in Restricted Mode

In a RAC setup, if every node is simultaneously placed in restricted mode (e.g., during rolling patches), no general connections can be established, triggering ORA-12526.

-- Check all RAC instances at once
SELECT INST_ID, INSTANCE_NAME, STATUS, LOGINS
FROM GV$INSTANCE
ORDER BY INST_ID;

-- Disable restricted mode on each node individually
-- (connect to each instance and run the following)
ALTER SYSTEM DISABLE RESTRICTED SESSION;

-- Confirm all nodes are back to normal
SELECT INST_ID, INSTANCE_NAME, LOGINS
FROM GV$INSTANCE
WHERE LOGINS = 'RESTRICTED';
-- Should return no rows when all instances are normal

Quick Fix Summary

-- Step 1: Connect as SYSDBA
-- sqlplus / as sysdba

-- Step 2: Confirm restricted mode
SELECT LOGINS FROM V$INSTANCE;

-- Step 3: Disable restricted mode
ALTER SYSTEM DISABLE RESTRICTED SESSION;

-- Step 4: Confirm fix
SELECT LOGINS FROM V$INSTANCE;
-- Expected result: ALLOWED

Prevention Tips

1. Always wrap maintenance scripts with error handling to ensure restricted mode is disabled even if an error occurs mid-script:

BEGIN
    EXECUTE IMMEDIATE 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
    -- maintenance work here
    EXECUTE IMMEDIATE 'ALTER SYSTEM DISABLE RESTRICTED SESSION';
EXCEPTION
    WHEN OTHERS THEN
        EXECUTE IMMEDIATE 'ALTER SYSTEM DISABLE RESTRICTED SESSION';
        RAISE;
END;
/

2. Set up proactive monitoring by scheduling the query below via a cron job or OEM alert rule to detect restricted mode automatically:

-- Run this as a scheduled monitoring check
SELECT INSTANCE_NAME, LOGINS,
    CASE WHEN LOGINS = 'RESTRICTED'
         THEN 'ALERT: Restricted mode is ACTIVE'
         ELSE 'OK'
    END AS STATUS
FROM V$INSTANCE;
-- Trigger an alert if result contains 'RESTRICTED'

Related Errors

Error Code	Description
ORA-12527	Listener: all instances are in restricted mode or blocking connections
ORA-12528	Listener: all instances are blocking new connections (QUIESCE state)
ORA-01035	Oracle only available to users with RESTRICTED SESSION privilege

📖 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