DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01035 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01035: ORACLE Only Available to Users with RESTRICTED SESSION Privilege

ORA-01035 occurs when a regular user attempts to connect to an Oracle database that is currently running in Restricted Session mode. Only users explicitly granted the RESTRICTED SESSION system privilege — typically DBAs — are allowed to log in during this state. This mode is commonly enabled during maintenance windows, patching, or data migration activities.

Top 3 Causes

1. Database Started with RESTRICT Option or Manually Enabled

A DBA may have started the database with STARTUP RESTRICT or executed ALTER SYSTEM ENABLE RESTRICTED SESSION, then forgotten to disable it after completing maintenance work.

-- Check current restricted mode status
SELECT INSTANCE_NAME, LOGINS, RESTRICTED
FROM V$INSTANCE;

-- Disable restricted session mode (requires SYSDBA)
ALTER SYSTEM DISABLE RESTRICTED SESSION;

-- Verify the change
SELECT LOGINS, RESTRICTED FROM V$INSTANCE;
-- Expected: LOGINS = ALLOWED, RESTRICTED = NO
Enter fullscreen mode Exit fullscreen mode

2. Automated Script or Patch Tool Left Database in Restricted Mode

Tools like OPatch or custom maintenance scripts may switch the database into Restricted mode during execution. If the script fails or terminates abnormally, the database remains locked out to regular users.

-- Connect as SYSDBA via OS authentication
-- sqlplus / as sysdba

-- Check instance state
SELECT STATUS, LOGINS, RESTRICTED, DATABASE_STATUS
FROM V$INSTANCE;

-- Force disable restricted mode
ALTER SYSTEM DISABLE RESTRICTED SESSION;
Enter fullscreen mode Exit fullscreen mode

3. User Lacks RESTRICTED SESSION Privilege During Maintenance

During a maintenance window, certain business users may need temporary access. Without the RESTRICTED SESSION privilege explicitly granted, their connection attempts will fail with ORA-01035.

-- Grant restricted session privilege to a specific user
GRANT RESTRICTED SESSION TO app_user;

-- Verify the grant
SELECT GRANTEE, PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE PRIVILEGE = 'RESTRICTED SESSION';

-- Revoke after maintenance is complete
REVOKE RESTRICTED SESSION FROM app_user;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1: Connect to the database as SYSDBA (OS-level authentication if needed):

-- From the server OS prompt
-- sqlplus / as sysdba
Enter fullscreen mode Exit fullscreen mode

Step 2: Check and disable Restricted Session mode:

SELECT LOGINS, RESTRICTED FROM V$INSTANCE;

ALTER SYSTEM DISABLE RESTRICTED SESSION;
Enter fullscreen mode Exit fullscreen mode

Step 3: If specific users must connect during maintenance, grant them temporary access:

GRANT RESTRICTED SESSION TO target_user;
-- Remember to revoke after maintenance
REVOKE RESTRICTED SESSION FROM target_user;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Add a mandatory checklist step to all maintenance runbooks

Always include ALTER SYSTEM DISABLE RESTRICTED SESSION as a required step in your post-maintenance checklist. Set up a monitoring script or Oracle Enterprise Manager alert that fires when RESTRICTED = 'YES' persists beyond an expected maintenance window.

-- Monitoring query — alert if restricted mode is unexpectedly active
SELECT INSTANCE_NAME, LOGINS, RESTRICTED
FROM V$INSTANCE
WHERE RESTRICTED = 'YES';
Enter fullscreen mode Exit fullscreen mode

2. Audit RESTRICTED SESSION privileges regularly

Apply the principle of least privilege — only grant RESTRICTED SESSION to accounts that absolutely need it, and audit the privilege list on a scheduled basis.

-- Audit current holders of RESTRICTED SESSION privilege
SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION
FROM DBA_SYS_PRIVS
WHERE PRIVILEGE = 'RESTRICTED SESSION'
ORDER BY GRANTEE;

-- Enable auditing for this privilege
AUDIT RESTRICTED SESSION BY ACCESS;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-01033 Oracle initialization or shutdown in progress
ORA-01034 Oracle not available (instance not started)
ORA-01089 Immediate shutdown in progress

These errors often appear alongside ORA-01035 in scenarios involving database maintenance, startup/shutdown sequences, or emergency recovery situations. Always check V$INSTANCE first when diagnosing any Oracle connectivity issue.

📖 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.

Top comments (0)