DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01074 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01074: cannot shut down ORACLE; please log off first

ORA-01074 occurs when a DBA attempts to issue a SHUTDOWN command while the current session is still actively connected to the Oracle database. Oracle requires the active session to be properly disconnected before it can proceed with the shutdown process. This error is commonly seen in SQL*Plus or scripted environments where session management steps are skipped.

Top 3 Causes

1. Issuing SHUTDOWN Without Logging Off First

The most common cause. A DBA connects as SYSDBA and immediately runs SHUTDOWN without first disconnecting the current session.

-- Wrong approach (causes ORA-01074)
CONNECT / AS SYSDBA
SHUTDOWN IMMEDIATE;  -- Error occurs here

-- Correct approach
DISCONNECT;
CONNECT / AS SYSDBA
SHUTDOWN IMMEDIATE;
Enter fullscreen mode Exit fullscreen mode

2. Multiple Active Sessions Still Connected

Background sessions from scripts, batch jobs, or monitoring tools remain connected when a shutdown is attempted.

-- Check all active user sessions
SELECT sid, serial#, username, status, program
FROM v$session
WHERE type = 'USER'
ORDER BY status;

-- Kill a specific blocking session
ALTER SYSTEM KILL SESSION '45,1123' IMMEDIATE;

-- Kill all non-SYS user sessions before shutdown
BEGIN
  FOR r IN (
    SELECT sid, serial#
    FROM v$session
    WHERE username IS NOT NULL
      AND username != 'SYS'
      AND type = 'USER'
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER SYSTEM KILL SESSION ''' || r.sid || ',' || r.serial# || ''' IMMEDIATE';
  END LOOP;
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Oracle Management Agent or Third-Party Tools Holding Sessions

OEM Agent, Cloud Control, or tools like Toad and SQL Developer maintain persistent background connections, preventing a clean shutdown.

# Stop OEM Agent before shutdown (OS level)
$AGENT_HOME/bin/emctl stop agent

# Stop DB Console
$ORACLE_HOME/bin/emctl stop dbconsole
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Fix 1: Disconnect and reconnect before SHUTDOWN

-- Step 1: Logoff current session
DISCONNECT;

-- Step 2: Reconnect as SYSDBA
CONNECT / AS SYSDBA

-- Step 3: Shutdown cleanly
SHUTDOWN IMMEDIATE;
Enter fullscreen mode Exit fullscreen mode

Fix 2: Use SHUTDOWN ABORT as a last resort

-- Use only when other methods fail
SHUTDOWN ABORT;

-- Restart (instance recovery runs automatically)
STARTUP;

-- Verify database health after restart
SELECT status FROM v$instance;
SELECT open_mode FROM v$database;
Enter fullscreen mode Exit fullscreen mode

⚠️ SHUTDOWN ABORT bypasses normal checkpoint operations. Always prefer SHUTDOWN IMMEDIATE when possible.

Prevention Tips

1. Standardize your shutdown procedure with a script

Always use a standardized shell script that handles session cleanup before shutdown:

#!/bin/bash
export ORACLE_SID=ORCL
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1

sqlplus -S / as sysdba <<EOF
SELECT COUNT(*) AS active_sessions
FROM v$session
WHERE type = 'USER' AND status = 'ACTIVE';
SHUTDOWN IMMEDIATE;
EXIT;
EOF
Enter fullscreen mode Exit fullscreen mode

2. Limit sessions per user via Oracle Profiles

Prevent unexpected session buildup by enforcing session limits through Oracle Profiles:

-- Create a profile with session limits
CREATE PROFILE app_profile LIMIT
  SESSIONS_PER_USER   5
  IDLE_TIME           30
  CONNECT_TIME        480;

-- Apply to a user
ALTER USER app_user PROFILE app_profile;

-- Verify profile assignment
SELECT username, profile FROM dba_users WHERE username = 'APP_USER';
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-01075 You are currently logged on — session already active
ORA-01092 Oracle instance terminated, disconnection forced
ORA-01034 Oracle not available — DB fully shut down
ORA-00600 Internal error — may occur after incomplete recovery post ABORT

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