DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01113 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01113: File Needs Media Recovery — What It Means and How to Fix It

ORA-01113 is one of the most critical Oracle errors a DBA can encounter. It occurs when Oracle detects that a data file requires media recovery before it can be brought online or before the database can be opened. This typically happens because the file's SCN (System Change Number) is behind what the control file expects, meaning the file is out of sync with the rest of the database.

Top 3 Causes

1. Restoring a Backup Without Performing Recovery

When you restore a data file or database from a backup (via RMAN or OS copy), the restored file carries the SCN from the time of the backup. Without applying archived redo logs to bring the file current, Oracle refuses to open the database and throws ORA-01113.

-- Check which files need recovery
SELECT file#, name, status, checkpoint_change#
FROM v$datafile
WHERE status IN ('RECOVER', 'OFFLINE');

-- Check SCN mismatch in file headers
SELECT file#, error, recover, checkpoint_change#
FROM v$datafile_header
ORDER BY file#;
Enter fullscreen mode Exit fullscreen mode

2. Abnormal Database Shutdown (Crash)

A power failure, OS crash, or SHUTDOWN ABORT can leave data files in an inconsistent state — particularly files that were offline at the time of the crash. Oracle detects the missing checkpoint and raises ORA-01113 when the database is restarted.

-- Start database in MOUNT mode to diagnose
STARTUP MOUNT;

-- Check file status
SELECT file#, name, status
FROM v$datafile
WHERE status != 'ONLINE';
Enter fullscreen mode Exit fullscreen mode

3. Bringing an Offline Tablespace Online Without Recovery

If a tablespace or data file was taken offline and archived logs were generated during the offline period, Oracle requires those logs to be applied before the file can come back online.

-- Incorrect approach (will trigger ORA-01113)
ALTER TABLESPACE users ONLINE; -- Fails without recovery

-- Correct approach
RECOVER TABLESPACE users;
ALTER TABLESPACE users ONLINE;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Fix 1: Using RMAN (Recommended) 

-- Connect to RMAN
RMAN> CONNECT TARGET /

-- Recover a specific datafile
RMAN> RECOVER DATAFILE 5;

-- Or recover an entire tablespace
RMAN> RECOVER TABLESPACE users;

-- Open the database after recovery
RMAN> ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

Fix 2: Manual Recovery via SQL*Plus 

-- Start in MOUNT mode
STARTUP MOUNT;

-- Recover specific datafile
RECOVER DATAFILE 5;

-- Or use full path
RECOVER DATAFILE '/u01/oradata/ORCL/users01.dbf';

-- Auto-apply archive logs
RECOVER AUTOMATIC DATAFILE 5;

-- Open the database
ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

Fix 3: Incomplete Recovery (When Archive Logs Are Missing) 

-- Recover up to a specific SCN
RECOVER DATABASE UNTIL CHANGE 9876543;

-- Or recover to a point in time
RECOVER DATABASE UNTIL TIME '2024-06-01:12:00:00';

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

Prevention Tips

1. Always Run in ARCHIVELOG Mode with Regular RMAN Backups

Enable ARCHIVELOG mode and schedule regular RMAN full and incremental backups. Validate your backups periodically to ensure they are usable in a real recovery scenario.

-- Confirm ARCHIVELOG mode
SELECT log_mode FROM v$database;

-- Validate backup in RMAN
-- RMAN> VALIDATE DATABASE;
Enter fullscreen mode Exit fullscreen mode

2. Follow Safe Offline/Online Procedures

Always use OFFLINE NORMAL when taking a tablespace offline, and never skip the recovery step before bringing it back online. Document and enforce this as a standard DBA operating procedure.

-- Safe offline procedure
ALTER TABLESPACE users OFFLINE NORMAL;

-- Proper online procedure after maintenance
RECOVER TABLESPACE users;
ALTER TABLESPACE users ONLINE;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-01110 Identifies the specific file name associated with ORA-01113
ORA-01157 Cannot identify/lock data file — often appears alongside ORA-01113
ORA-00283 Recovery session cancelled — occurs when archive logs are missing during recovery
ORA-00279 Change needed for thread — guides you to the required archive log sequence

Pro Tip: After any OPEN RESETLOGS, immediately take a full database backup. The resetlogs operation starts a new incarnation, and older archive logs will no longer be applicable to future recovery 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)