DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01107 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01107: database must be mounted for media recovery

ORA-01107 is an Oracle error that occurs when you attempt to perform media recovery on a database that is not in the MOUNT state. Media recovery operations — such as restoring datafiles or applying archived redo logs — require exclusive control over the database files, which is only possible in MOUNT state. If the database is either OPEN or in NOMOUNT (STARTED) state when a recovery command is issued, Oracle throws this error immediately.

Top 3 Causes

1. Running Recovery Commands While Database is OPEN

The most common cause. A DBA attempts RECOVER DATABASE or RECOVER DATAFILE while the database is fully open and serving users. Oracle does not allow full media recovery on an open database.

-- Check current database status first
SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;

-- If STATUS = 'OPEN', shut down and remount
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

-- Now run media recovery safely
RECOVER DATABASE;

-- Re-open after successful recovery
ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

2. Issuing Recovery Commands from NOMOUNT State

After starting the instance with STARTUP NOMOUNT (typically for controlfile restoration), a DBA mistakenly tries to recover datafiles before mounting the database. In NOMOUNT state, Oracle has not yet read the controlfile, so it has no knowledge of datafile locations.

-- Transition from NOMOUNT to MOUNT state
ALTER DATABASE MOUNT;

-- Verify the state changed
SELECT STATUS FROM V$INSTANCE;
-- Expected output: MOUNTED

-- Proceed with recovery using backup controlfile if needed
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

-- Open with RESETLOGS after incomplete recovery
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

3. Automation Script Does Not Validate Database State

Scheduled or DR automation scripts sometimes skip state validation and blindly fire recovery commands. If a prior step fails silently, the database may not be in MOUNT state when the recovery command runs.

-- Always validate state before recovery in scripts
DECLARE
  v_status VARCHAR2(20);
BEGIN
  SELECT STATUS INTO v_status FROM V$INSTANCE;

  IF v_status != 'MOUNTED' THEN
    RAISE_APPLICATION_ERROR(
      -20001,
      'Database is not MOUNTED. Current state: ' || v_status
    );
  ELSE
    DBMS_OUTPUT.PUT_LINE('Database is MOUNTED. Proceeding with recovery.');
  END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step-by-step fix for the most common scenario:

-- Step 1: Check current state
SELECT STATUS FROM V$INSTANCE;

-- Step 2: Bring database to MOUNT state
SHUTDOWN ABORT;   -- Use ABORT only if IMMEDIATE hangs
STARTUP MOUNT;

-- Step 3: Restore and recover (RMAN recommended)
-- In RMAN:
-- RMAN> RESTORE DATABASE;
-- RMAN> RECOVER DATABASE;
-- RMAN> ALTER DATABASE OPEN;

-- Step 4: If using SQL*Plus for partial recovery
RECOVER DATAFILE 4;
-- or by file path
RECOVER DATAFILE '/u01/oradata/ORCL/users01.dbf';

-- Step 5: Confirm all datafiles are healthy after open
SELECT FILE#, NAME, STATUS FROM V$DATAFILE;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Always use RMAN for media recovery. RMAN handles state management more gracefully than manual SQL*Plus recovery and reduces the chance of human error. Include STARTUP MOUNT as an explicit command inside every RMAN recovery script. 
-- Recommended RMAN recovery template
-- $ rman target /
-- RMAN> STARTUP FORCE MOUNT;
-- RMAN> RESTORE DATABASE;
-- RMAN> RECOVER DATABASE;
-- RMAN> ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode
  1. Build state-check gates into every recovery runbook. Before executing any recovery command, query V$INSTANCE and assert the STATUS is MOUNTED. Document each step of your DR runbook with the expected database state, and conduct recovery drills at least once per quarter in a non-production environment to validate scripts end-to-end.

Related Oracle Errors

Error Code Description
ORA-01113 File needs media recovery — often appears alongside ORA-01107
ORA-01109 Database not open — occurs after recovery if OPEN step is missed
ORA-01194 File needs more recovery to be consistent — incomplete recovery issue
ORA-00264 No recovery required — harmless, means recovery is already complete

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