DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01014 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01014: ORACLE Shutdown in Progress – What Every DBA Should Know

ORA-01014 is an Oracle error that occurs when a user or application attempts to connect to or execute operations against a database instance that is currently in the process of shutting down. During a shutdown sequence, Oracle rejects all new connections and pending operations, returning this error to protect data integrity. Understanding this error is critical for DBAs who manage high-availability environments where unexpected downtime can have significant business impact.

Top 3 Causes

1. Intentional Shutdown Command Execution

The most common cause is a DBA intentionally running a shutdown command while active sessions or applications are still trying to connect.

-- Common shutdown commands that trigger ORA-01014 for active clients
SHUTDOWN NORMAL;       -- Waits for all sessions to disconnect
SHUTDOWN IMMEDIATE;    -- Rolls back transactions, kills sessions
SHUTDOWN TRANSACTIONAL; -- Waits for transactions to complete
SHUTDOWN ABORT;        -- Immediate crash-style shutdown (use with caution)

-- Always check active sessions BEFORE shutting down
SELECT SID, SERIAL#, USERNAME, STATUS, MACHINE, PROGRAM
FROM V$SESSION
WHERE STATUS = 'ACTIVE'
  AND USERNAME IS NOT NULL;

-- Kill a blocking session before shutdown if needed
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
-- Example:
ALTER SYSTEM KILL SESSION '101,4892' IMMEDIATE;
Enter fullscreen mode Exit fullscreen mode

2. Scheduled Maintenance or Auto-Restart Jobs

Automated scripts, Oracle Enterprise Manager jobs, or OS-level cron tasks may trigger a database restart during peak hours, causing applications to receive ORA-01014 unexpectedly.

-- Check scheduled DBMS_SCHEDULER jobs that may restart the DB
SELECT JOB_NAME, STATE, LAST_RUN_DATE, NEXT_RUN_DATE, ENABLED
FROM DBA_SCHEDULER_JOBS
WHERE ENABLED = 'TRUE'
ORDER BY NEXT_RUN_DATE;

-- In RAC environments, check all instance statuses
SELECT INST_ID, INSTANCE_NAME, STATUS, DATABASE_STATUS
FROM GV$INSTANCE
ORDER BY INST_ID;
Enter fullscreen mode Exit fullscreen mode

3. Abnormal Instance Crash or OS-Level Process Kill

An unexpected OS-level kill (kill -9 on Oracle processes), power failure, or crash of a critical background process (PMON, SMON) can cause an unclean shutdown, triggering ORA-01014 for connected clients.

-- After an abnormal shutdown, check the alert log location
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'background_dump_dest';

-- Oracle 11g+: Use ADR for diagnostics
SELECT NAME, VALUE FROM V$DIAG_INFO WHERE NAME = 'ADR Home';

-- After restart, verify instance recovery completed successfully
SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS, ACTIVE_STATE
FROM V$INSTANCE;

-- Confirm database is fully open
SELECT NAME, OPEN_MODE, LOG_MODE FROM V$DATABASE;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Once the shutdown completes or the instance crashes, restart the database using the following steps:

-- Connect as SYSDBA
CONNECT / AS SYSDBA

-- Start the instance and open the database
STARTUP;

-- Or step-by-step for more control
STARTUP NOMOUNT;
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

-- Verify the database is open and healthy
SELECT STATUS, DATABASE_STATUS FROM V$INSTANCE;
SELECT NAME, OPEN_MODE FROM V$DATABASE;
Enter fullscreen mode Exit fullscreen mode

If you encounter ORA-01014 in the middle of a shutdown you want to cancel (only possible before shutdown completes), there is no way to abort a shutdown in progress — you must wait for it to finish and then restart.

Prevention Tips

  1. Implement Connection Retry Logic in Applications
    Applications should handle ORA-01014 gracefully by implementing retry logic with exponential backoff. Use connection pool settings such as testOnBorrow=true and validationQuery (e.g., SELECT 1 FROM DUAL) to detect stale connections automatically. This minimizes user-facing errors during planned maintenance windows.

  2. Standardize a Pre-Shutdown Checklist
    Before executing any shutdown command, always run a pre-shutdown verification script to identify active sessions, long-running transactions, and critical batch jobs. Schedule maintenance during off-peak hours and communicate downtime windows to all stakeholders in advance. Using SHUTDOWN IMMEDIATE over SHUTDOWN ABORT ensures a clean shutdown with proper transaction rollback, reducing recovery time after restart.

-- Pre-shutdown checklist script
-- Step 1: Check active sessions
SELECT COUNT(*) AS ACTIVE_SESSIONS FROM V$SESSION
WHERE STATUS = 'ACTIVE' AND USERNAME IS NOT NULL;

-- Step 2: Check long-running transactions
SELECT s.SID, s.SERIAL#, s.USERNAME, t.USED_UBLK, t.START_TIME
FROM V$SESSION s JOIN V$TRANSACTION t ON s.TADDR = t.ADDR
ORDER BY t.START_TIME;

-- Step 3: Check running jobs
SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_RUNNING_JOBS;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01034: ORACLE not available — occurs after shutdown is complete (ORA-01014 is during shutdown; ORA-01034 is after).
  • ORA-03113: End-of-file on communication channel — seen on the client side when the server disconnects unexpectedly.
  • ORA-01012: Not logged on — may follow ORA-01014 when a disconnected session attempts further operations.

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