DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01033 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01033: ORACLE Initialization or Shutdown in Progress

ORA-01033 is thrown when a client attempts to connect to an Oracle database that is either in the middle of starting up or shutting down. The database is not yet in a fully OPEN state — it may be in NOMOUNT or MOUNT phase — or it is currently undergoing instance recovery after a crash. Understanding the root cause quickly is essential to minimize downtime in production environments.

Top 3 Causes and Fixes

Cause 1: Database Stuck in MOUNT State

The most common cause is the database reaching the MOUNT stage but failing to transition to OPEN due to a missing or corrupt datafile/redo log.

Diagnosis:

-- Connect as SYSDBA
CONNECT / AS SYSDBA

-- Check current instance status
SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS
FROM V$INSTANCE;

-- Check datafile status
SELECT FILE#, STATUS, NAME
FROM V$DATAFILE
WHERE STATUS NOT IN ('SYSTEM', 'ONLINE');
Enter fullscreen mode Exit fullscreen mode

Fix:

-- If status is MOUNTED, open the database
ALTER DATABASE OPEN;

-- If a datafile is offline, bring it online first
ALTER DATABASE DATAFILE '/oradata/orcl/users01.dbf' ONLINE;

-- Then retry
ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

Cause 2: Instance Recovery in Progress After Crash

After an abnormal shutdown (power loss, OS crash, SHUTDOWN ABORT), Oracle's SMON process automatically performs instance recovery before allowing connections. This can take minutes to hours depending on transaction volume.

Monitor recovery progress:

-- Check estimated recovery completion
SELECT RECOVERY_ESTIMATED_IOS,
       ACTUAL_IOS_DONE,
       ESTIMATED_COMPLETION_TIME
FROM V$INSTANCE_RECOVERY;

-- Check active rollback transactions
SELECT XIDUSN, STATUS, START_TIME
FROM V$TRANSACTION
WHERE STATUS = 'ACTIVE';
Enter fullscreen mode Exit fullscreen mode

Fix: In most cases, simply wait for recovery to complete. If it hangs indefinitely, check the alert log for ORA-00600 or ORA-00604 errors that may require Oracle Support.

-- After recovery, verify database is fully open
SELECT STATUS, DATABASE_STATUS
FROM V$INSTANCE;
-- Expected: STATUS = 'OPEN', DATABASE_STATUS = 'ACTIVE'
Enter fullscreen mode Exit fullscreen mode

Cause 3: Shutdown Command in Progress

If a DBA is running SHUTDOWN IMMEDIATE or SHUTDOWN TRANSACTIONAL, new connections will receive ORA-01033 until the database is back online.

Fix — Clean restart procedure:

-- Connect as SYSDBA
CONNECT / AS SYSDBA

-- Graceful shutdown and restart
SHUTDOWN IMMEDIATE;
STARTUP;

-- Verify after startup
SELECT INSTANCE_NAME,
       STATUS,
       DATABASE_STATUS,
       TO_CHAR(STARTUP_TIME, 'YYYY-MM-DD HH24:MI:SS') AS STARTUP_TIME
FROM V$INSTANCE;
Enter fullscreen mode Exit fullscreen mode

Quick Diagnostic Script

Run this immediately when ORA-01033 is reported:

-- Full health check
SELECT i.INSTANCE_NAME,
       i.STATUS,
       i.DATABASE_STATUS,
       d.LOG_MODE,
       d.OPEN_MODE
FROM V$INSTANCE i, V$DATABASE d;

-- Check for problem datafiles
SELECT FILE#, STATUS, NAME
FROM V$DATAFILE
WHERE STATUS NOT IN ('SYSTEM', 'ONLINE', 'RECOVER');

-- Check redo log status
SELECT GROUP#, STATUS, ARCHIVED, MEMBERS
FROM V$LOG
ORDER BY GROUP#;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Set FAST_START_MTTR_TARGET to reduce recovery time

-- Aim for recovery within 60 seconds
ALTER SYSTEM SET FAST_START_MTTR_TARGET = 60 SCOPE=BOTH;

-- Verify the setting
SELECT TARGET_MTTR, ESTIMATED_MTTR
FROM V$INSTANCE_RECOVERY;
Enter fullscreen mode Exit fullscreen mode

2. Automate database status monitoring

Schedule a cron job or use Oracle Enterprise Manager to alert DBAs immediately when V$INSTANCE.STATUS != 'OPEN'. Early detection prevents end-users from ever seeing ORA-01033 in production.

Related Errors

Error Code Description
ORA-01034 ORACLE not available — instance is completely down
ORA-01113 File needs media recovery — blocks database OPEN
ORA-00600 Internal error during recovery — contact Oracle Support
ORA-01122 Database file version mismatch — prevents OPEN

Pro Tip: ORA-01033 and ORA-01034 often appear together. ORA-01034 means the instance isn't running at all, while ORA-01033 means it's running but not yet accessible. Always check V$INSTANCE first to distinguish between the two.

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