ORA-01089: Immediate Shutdown in Progress - Wait or Abort

ORA-01089: Immediate Shutdown in Progress

Error Overview

Error Text: ORA-01089: immediate shutdown or close in progress - no operations are permitted

The ORA-01089 error is raised when a user or application attempts to perform a database operation after SHUTDOWN IMMEDIATE (or SHUTDOWN TRANSACTIONAL) has been issued. Oracle has started the shutdown sequence: it has stopped accepting new logins, has begun rolling back uncommitted transactions, and is waiting for that rollback to complete before closing and dismounting the database. Any operation attempted against the instance during this window — including queries — will receive ORA-01089.

Even privileged SYSDBA connections that are already established will receive this error for DML/DDL operations once the shutdown sequence has been initiated.

Common Causes

1. SHUTDOWN IMMEDIATE Waiting on Active Transactions

The most common scenario. One or more sessions had large uncommitted transactions when SHUTDOWN IMMEDIATE was issued. Oracle is rolling them back (which can take as long as the original transaction took to generate), and all operations are blocked until rollback completes.

2. SHUTDOWN TRANSACTIONAL Waiting for Sessions to Commit or Disconnect

With SHUTDOWN TRANSACTIONAL, Oracle waits for all active transactions to commit or roll back on their own before proceeding. Sessions that remain connected with open transactions block the shutdown indefinitely until they complete or are killed.

3. Long-Running Jobs or Background Processes

Scheduler jobs, Data Pump operations, RMAN backup sessions, or Streams/replication processes that were active when shutdown was initiated prolong the SHUTDOWN IMMEDIATE wait.

4. Hung or Zombie Sessions

Sessions that are in a wait state (network I/O wait, lock wait) may not respond to the shutdown signal promptly, leaving the instance in the shutdown-in-progress state for an extended period.

5. Application Receiving ORA-01089 After Reconnect Attempt

An application detects a dropped connection and immediately attempts to reconnect, but the instance has not yet fully shut down. The reconnect fails with ORA-01089.

Diagnostic Queries

Check Shutdown Progress

-- Connect as SYSDBA before issuing shutdown, or from another SYSDBA session
SELECT instance_name, status, active_state, shutdown_pending
FROM v$instance;

-- active_state values:
-- NORMAL     - normal operation
-- QUIESCING  - ALTER SYSTEM QUIESCE RESTRICTED in progress
-- QUIESCED   - fully quiesced
-- shutdown_pending = YES means a shutdown has been requested

Identify Active Transactions Blocking Shutdown

-- Active uncommitted transactions
SELECT t.addr, t.xidusn, t.xidslot, t.xidsqn,
       t.used_ublk, t.used_urec,
       s.sid, s.serial#, s.username, s.status,
       s.last_call_et, s.program
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
ORDER BY t.used_ublk DESC;

Find Long-Running Active Sessions

-- Sessions with the longest elapsed time since last call
SELECT sid, serial#, username, status, last_call_et,
       ROUND(last_call_et/60, 1) AS minutes_active,
       sql_id, program, machine, osuser
FROM v$session
WHERE username IS NOT NULL
  AND status = 'ACTIVE'
ORDER BY last_call_et DESC;

Check Rollback Progress During Shutdown

-- Monitor undo usage — decreasing used_ublk indicates rollback is progressing
SELECT t.xidusn, t.used_ublk, t.used_urec,
       s.sid, s.username, s.program
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
ORDER BY t.used_ublk DESC;

-- Also check SMON activity — it handles distributed transaction recovery
SELECT name, description, state
FROM v$bgprocess
WHERE name = 'SMON';

Estimate Remaining Rollback Time

-- Compare undo blocks used over time to estimate rollback rate
-- Run twice, 60 seconds apart, to get blocks/second
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') AS sample_time,
       SUM(used_ublk) AS total_undo_blocks
FROM v$transaction;

Check for Active RMAN or Data Pump Sessions

-- RMAN sessions during shutdown
SELECT sid, serial#, username, program, status, last_call_et
FROM v$session
WHERE program LIKE '%rman%'
   OR program LIKE '%RMAN%'
   OR program LIKE '%dmp%';

-- Data Pump jobs
SELECT job_name, state, degree, attached_sessions
FROM dba_datapump_jobs
WHERE state != 'NOT RUNNING';

Step-by-Step Resolution

1. Wait for Shutdown to Complete (Preferred)

If the shutdown was intentional and transactions are rolling back, the correct action is to wait. Monitor progress with another SYSDBA session:

-- In a second SYSDBA session (already established before shutdown was issued):
-- Check every 60 seconds whether transactions are shrinking
SELECT SUM(used_ublk) AS blocks_remaining
FROM v$transaction;

2. Kill Blocking Sessions to Speed Up SHUTDOWN IMMEDIATE

-- Kill the largest transactions first to accelerate rollback completion
-- (Must be executed from the SYSDBA session that initiated the shutdown,
--  or from another SYSDBA session already logged on)
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- Kill all non-system active sessions in a loop
BEGIN
  FOR s IN (
    SELECT sid, serial#
    FROM v$session
    WHERE username IS NOT NULL
      AND username NOT IN ('SYS','SYSTEM')
      AND status = 'ACTIVE'
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE';
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;
  END LOOP;
END;
/

3. Escalate to SHUTDOWN ABORT

When SHUTDOWN IMMEDIATE is hung and cannot be resolved by killing sessions, escalate to SHUTDOWN ABORT. This is safe in modern Oracle because crash recovery runs automatically on the next STARTUP.

-- From the same SYSDBA session or a new one (if the original hangs):
SHUTDOWN ABORT;

-- Immediately follow with a clean startup to perform instance recovery:
STARTUP;

-- Oracle SMON will automatically perform crash recovery (roll forward + roll back)
-- before opening the database. This is equivalent to a controlled crash recovery.

4. Prevent Applications from Reconnecting During Shutdown

Before issuing a shutdown, put the listener into a state that stops new connections:

# Stop listener from accepting new connections (existing ones are unaffected)
lsnrctl stop

# Or, using lsnrctl to suspend:
lsnrctl services   # confirm current state

-- Prevent new logins at the database level before shutdown
ALTER SYSTEM ENABLE RESTRICTED SESSION;

-- Then kill existing sessions before issuing SHUTDOWN IMMEDIATE

5. Handle ORA-01089 in Applications

Applications should treat ORA-01089 as a signal that the database is deliberately going offline:

import cx_Oracle
import time

try:
    cursor.execute(sql)
except cx_Oracle.DatabaseError as e:
    error_obj, = e.args
    if error_obj.code == 1089:
        # Database is shutting down — back off and retry later
        print("Database shutdown in progress. Will retry in 60 seconds.")
        time.sleep(60)
        # Re-establish connection and retry
    else:
        raise

Prevention Strategies

1. Pre-Shutdown Checklist

-- Before issuing SHUTDOWN IMMEDIATE, always check:

-- 1. Active transactions
SELECT COUNT(*) AS active_transactions FROM v$transaction;

-- 2. Long-running sessions
SELECT sid, serial#, username, ROUND(last_call_et/60,1) AS minutes,
       status, program
FROM v$session
WHERE username IS NOT NULL
  AND last_call_et > 300  -- running > 5 minutes
ORDER BY last_call_et DESC;

-- 3. Active Scheduler jobs
SELECT job_name, state, last_start_date
FROM dba_scheduler_running_jobs;

-- 4. Active Data Pump jobs
SELECT job_name, state FROM dba_datapump_jobs WHERE state != 'NOT RUNNING';

2. Graceful Pre-Shutdown Procedure

-- Step 1: Block new logins
ALTER SYSTEM ENABLE RESTRICTED SESSION;

-- Step 2: Wait for or gracefully terminate active jobs
BEGIN
  DBMS_SCHEDULER.STOP_JOB(job_name => 'SCHEMA.LONG_RUNNING_JOB', force => TRUE);
END;
/

-- Step 3: Kill long-running user sessions
BEGIN
  FOR s IN (SELECT sid, serial# FROM v$session
            WHERE username IS NOT NULL
              AND username NOT IN ('SYS','SYSTEM')
              AND last_call_et > 60) LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
  END LOOP;
END;
/

-- Step 4: Now issue SHUTDOWN IMMEDIATE — should complete quickly
SHUTDOWN IMMEDIATE;

3. Monitoring During Planned Maintenance

-- Create a monitoring job that fires during planned shutdown windows
-- to alert if shutdown is taking longer than expected
CREATE OR REPLACE PROCEDURE check_shutdown_progress AS
  v_status VARCHAR2(20);
BEGIN
  SELECT status INTO v_status FROM v$instance;
  IF v_status = 'STARTED' THEN
    DBMS_OUTPUT.PUT_LINE('Instance in NOMOUNT — startup/shutdown sequence active');
  END IF;
END;
/

Related Errors

• ORA-01033 - Oracle Initialization or Shutdown in Progress
• ORA-01034 - Oracle Not Available (instance is fully down)
• ORA-01109 - Database Not Open
• ORA-01012 - Not Logged On (sessions dropped during shutdown)
• ORA-01013 - User Requested Cancel of Current Operation

Emergency Response

Quick Fixes

1. Check current shutdown state

   SELECT status, shutdown_pending FROM v$instance;
   SELECT SUM(used_ublk) FROM v$transaction;

2. Kill all blocking user sessions

   BEGIN
     FOR s IN (SELECT sid, serial# FROM v$session
               WHERE username NOT IN ('SYS','SYSTEM') AND username IS NOT NULL) LOOP
       BEGIN
         EXECUTE IMMEDIATE
           'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE';
       EXCEPTION WHEN OTHERS THEN NULL;
       END;
     END LOOP;
   END;
   /

3. Abort and restart

   SHUTDOWN ABORT;
   STARTUP;

Post-Resolution Cleanup

-- Confirm database is open after STARTUP following SHUTDOWN ABORT
SELECT name, open_mode FROM v$database;
SELECT instance_name, status FROM v$instance;

-- Verify crash recovery completed (no messages about recovery in progress)
-- Check alert log: adrci> show alert -tail 50

-- Open PDBs if applicable
ALTER PLUGGABLE DATABASE ALL OPEN;

-- Re-enable normal sessions if restricted mode was set
ALTER SYSTEM DISABLE RESTRICTED SESSION;