DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01089 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01089: Immediate Shutdown in Progress - No Operations Are Permitted

ORA-01089 is an Oracle database error that occurs when a client or application attempts to perform any operation while the database instance is in the process of an immediate shutdown (SHUTDOWN IMMEDIATE or SHUTDOWN ABORT). During this phase, Oracle rejects all new connections and SQL operations to ensure a clean and controlled shutdown. This error is particularly common in environments where connection pools or automated scripts continuously attempt to reconnect without proper error handling.

Top 3 Causes

1. SHUTDOWN IMMEDIATE Executed While Sessions Are Active

The most common cause is a DBA running SHUTDOWN IMMEDIATE while active sessions or applications are still trying to communicate with the database. Any in-flight query or new connection attempt after this command will immediately receive ORA-01089.

-- DBA executes this command
SHUTDOWN IMMEDIATE;

-- Any session attempting this after shutdown begins will receive ORA-01089
SELECT SYSDATE FROM DUAL;
-- ERROR: ORA-01089: immediate shutdown in progress
Enter fullscreen mode Exit fullscreen mode

2. Connection Pool Auto-Reconnect During Shutdown

Application server connection pools (e.g., HikariCP, WebLogic, DBCP) automatically attempt to restore broken connections. When a database shutdown is in progress, these reconnect attempts consistently fail with ORA-01089 until the instance is fully stopped or restarted.

-- Check active sessions before shutdown to identify connection pool connections
SELECT SID, SERIAL#, USERNAME, MACHINE, PROGRAM, STATUS
FROM V$SESSION
WHERE USERNAME IS NOT NULL
  AND PROGRAM LIKE '%JDBC%'   -- or your connection pool identifier
ORDER BY LOGON_TIME;

-- Optionally kill pool connections gracefully before shutdown
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
Enter fullscreen mode Exit fullscreen mode

3. RAC Instance Shutdown Without Service Relocation

In Oracle RAC environments, shutting down one instance without properly relocating services causes sessions still routed to that instance to encounter ORA-01089.

-- Check instance and service status in RAC
SELECT INST_ID, INSTANCE_NAME, STATUS
FROM GV$INSTANCE
ORDER BY INST_ID;

-- Verify service placement before shutdown
SELECT NAME, INST_ID
FROM GV$ACTIVE_SERVICES
ORDER BY INST_ID;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1: Verify database status

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

SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS
FROM V$INSTANCE;
Enter fullscreen mode Exit fullscreen mode

Step 2: Restart the database

-- If the database is shut down, start it up
STARTUP;

-- Verify it's open and writable
SELECT OPEN_MODE FROM V$DATABASE;
-- Expected result: READ WRITE
Enter fullscreen mode Exit fullscreen mode

Step 3: Handle the error in PL/SQL

DECLARE
  v_result NUMBER;
  e_db_shutdown EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_db_shutdown, -1089);
BEGIN
  SELECT COUNT(*) INTO v_result FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('Success: ' || v_result);
EXCEPTION
  WHEN e_db_shutdown THEN
    DBMS_OUTPUT.PUT_LINE('DB shutdown in progress. Retry later.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Restrict sessions before shutdown

Always restrict new connections and notify application teams before initiating a shutdown. This prevents unexpected ORA-01089 errors from flooding logs.

-- Block new connections first
ALTER SYSTEM ENABLE RESTRICTED SESSION;

-- Wait for active sessions to complete, then shutdown gracefully
SHUTDOWN NORMAL;
Enter fullscreen mode Exit fullscreen mode

2. Pre-check database state in scripts

Add a database availability check at the beginning of all automated scripts and batch jobs to prevent unnecessary ORA-01089 errors.

DECLARE
  v_open_mode VARCHAR2(20);
BEGIN
  SELECT OPEN_MODE INTO v_open_mode FROM V$DATABASE;
  IF v_open_mode != 'READ WRITE' THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Database not available. Mode: ' || v_open_mode);
  END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01090: Shutdown in progress (normal/transactional shutdown variant)
  • ORA-03113: End-of-file on communication channel (connection lost after abort)
  • ORA-01033: Oracle initialization or shutdown in progress
  • ORA-03114: Not connected to Oracle (post-shutdown disconnection)

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