DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01124 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01124: cannot recover data file - file is in use or recovery

ORA-01124 occurs when Oracle cannot perform media recovery on a data file because the file is either currently in use by the database or another recovery operation is already in progress. This error is commonly encountered during manual recovery attempts via SQL*Plus or RMAN when the data file has not been properly taken offline beforehand. It is a critical error that must be resolved promptly to restore database availability.

Top 3 Causes and Fixes

Cause 1: Data File is Still ONLINE During Recovery Attempt

Trying to recover a data file while it remains in ONLINE status is the most frequent cause. Oracle requires the file to be OFFLINE before manual recovery can proceed.

Diagnosis:

-- Check data file status
SELECT file#, name, status, recover
FROM v$datafile
WHERE recover = 'YES' OR status NOT IN ('ONLINE','SYSTEM');

-- Check files pending recovery
SELECT * FROM v$recover_file;
Enter fullscreen mode Exit fullscreen mode

Fix:

-- Take the data file offline
ALTER DATABASE DATAFILE 5 OFFLINE;

-- Perform recovery
RECOVER DATAFILE 5;

-- Bring it back online
ALTER DATABASE DATAFILE 5 ONLINE;
Enter fullscreen mode Exit fullscreen mode

Cause 2: Previous Recovery Session Was Interrupted

If a prior RMAN or SQL*Plus recovery job was killed or crashed abnormally, the data file may remain flagged as "under recovery" internally. Starting a new recovery on the same file triggers ORA-01124.

Diagnosis:

-- Check for lingering recovery-related sessions
SELECT sid, serial#, username, program, status
FROM v$session
WHERE program LIKE '%RMAN%'
   OR status = 'KILLED';
Enter fullscreen mode Exit fullscreen mode

Fix:

-- Kill the stale session
ALTER SYSTEM KILL SESSION '23,1457' IMMEDIATE;

-- Reset data file state if needed
ALTER DATABASE DATAFILE 5 OFFLINE;

-- Restore from backup via RMAN
RUN {
  RESTORE DATAFILE 5;
  RECOVER DATAFILE 5;
  SQL 'ALTER DATABASE DATAFILE 5 ONLINE';
}
Enter fullscreen mode Exit fullscreen mode

Cause 3: Recovery Attempted While Database is OPEN (Without OFFLINE)

Issuing a RECOVER command against a tablespace or data file while the database is fully open — without first taking the tablespace offline — will trigger this error.

Fix:

-- Option A: Offline the tablespace first
ALTER TABLESPACE users OFFLINE IMMEDIATE;
RECOVER TABLESPACE users;
ALTER TABLESPACE users ONLINE;

-- Option B: Mount-mode full database recovery
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RECOVER DATABASE;
ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary 

-- Step 1: Identify problem files
SELECT file#, name, status FROM v$datafile WHERE recover = 'YES';

-- Step 2: Offline the file
ALTER DATABASE DATAFILE <file#> OFFLINE;

-- Step 3: Recover
RECOVER DATAFILE <file#>;

-- Step 4: Bring online
ALTER DATABASE DATAFILE <file#> ONLINE;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Always use RMAN with a proper backup strategy. Schedule daily backups and validate them regularly. Configure a recovery window to ensure restore points are always available. 
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
Enter fullscreen mode Exit fullscreen mode
  1. Monitor data file status proactively. Set up automated monitoring on V$DATAFILE and V$RECOVER_FILE to catch abnormal file states before they escalate into full outages. 
-- Run this periodically via a scheduler
SELECT file#, name, status, checkpoint_change#
FROM v$datafile
WHERE status NOT IN ('ONLINE', 'SYSTEM')
ORDER BY file#;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01110 – Identifies the specific data file involved, usually accompanies ORA-01124.
  • ORA-01113 – Data file needs media recovery; often a precursor to ORA-01124.
  • ORA-01157 – Cannot identify/lock data file; related file system or OS-level issues.
  • ORA-00283 – Recovery session cancelled due to errors; frequently seen alongside ORA-01124.

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