Recover a Lost Datafile in Oracle (RMAN-first)

When a datafile is deleted, corrupted, or inaccessible, the correct response depends on the file’s role (SYSTEM/SYSAUX/UNDO/TEMP vs. user tablespace) and whether the database runs in ARCHIVELOG mode. This guide shows a safe, RMAN-first workflow and the necessary variations for special cases (SYSTEM/SYSAUX, UNDO, TEMP, ASM/OMF, and NOARCHIVELOG).

1) Detect & confirm the missing/corrupt datafile

-- Identify files needing media recovery
SELECT file#, name, error FROM v$recover_file;

-- Cross-check known datafiles
SELECT file#, name, status FROM v$datafile ORDER BY file#;

-- Alert log & trace files (for path and error details)
-- See: $ORACLE_BASE/diag/.../alert_<SID>.log

2) Take the datafile/tablespace offline (when allowed)

For user tablespaces, keep the database open and isolate the failure:

-- Option A: Take just the datafile offline (11g+)
ALTER DATABASE DATAFILE <file_id_or_path> OFFLINE;

-- Option B: Take the affected tablespace offline
ALTER TABLESPACE users OFFLINE IMMEDIATE;

Note: You cannot take SYSTEM or SYSAUX offline; see Special cases.

3) Restore & recover with RMAN (standard path)

This is the safest, most complete approach. In ARCHIVELOG mode, RMAN restores the missing file from backup and applies archived redo to bring it current.

$ rman target /

-- Confirm file numbers & paths
RMAN> REPORT SCHEMA;

-- Restore and recover a single file (by number or path)
RMAN> RESTORE DATAFILE 7;
RMAN> RECOVER DATAFILE 7;

-- Bring online if you took it offline
SQL> ALTER DATABASE DATAFILE 7 ONLINE;

-- If you took the tablespace offline
SQL> ALTER TABLESPACE users ONLINE;

Restore into a new location (optional)

RMAN> RUN {
  SET NEWNAME FOR DATAFILE 7 TO '/u02/oradata/PROD/users01.dbf';
  RESTORE DATAFILE 7;
  SWITCH DATAFILE 7;
  RECOVER DATAFILE 7;
}

4) SQL*Plus recovery (alternative when RMAN isn’t available)

If you must use SQL*Plus, you still need a valid copy of the datafile (from OS-level backup or another location):

-- Copy the backup file to the original (or new) location at the OS level, then:
SQL> RECOVER DATAFILE '<full_path_to_file>';
-- Apply media recovery prompts; then:
SQL> ALTER DATABASE DATAFILE '<full_path_to_file>' ONLINE;

5) Special cases

• SYSTEM datafile lost:
  1. SHUTDOWN IMMEDIATE; STARTUP MOUNT;
  2. RMAN RESTORE DATAFILE <system_file#>; RMAN RECOVER DATABASE;
  3. ALTER DATABASE OPEN;
• SYSAUX datafile lost: Similar to SYSTEM—often requires MOUNT to restore/recover cleanly.
• UNDO datafile lost: You can open the database if another UNDO is available; otherwise MOUNT, RESTORE/RECOVER the UNDO datafile. Avoid manual hacks; prefer RMAN.
• TEMP (temporary) file lost: No media recovery. Recreate TEMP tablespace:

  
      DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
      CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u02/oradata/PROD/temp01.dbf' SIZE 2G;
      

6) ASM / OMF considerations

• ASM: Use disk group aliases or file numbers; RMAN locates backups automatically (no OS path needed).
• OMF: If db_create_file_dest is set, RMAN restores to the managed location; use SET NEWNAME only when relocating.

7) NOARCHIVELOG mode limitations

In NOARCHIVELOG, you can only restore to the time of the last consistent backup. You cannot roll forward with archived redo:

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;   -- minimal/none (no archivelogs)
RMAN> ALTER DATABASE OPEN RESETLOGS;

8) Verification & post-recovery checks

-- Check datafile status
SELECT file#, name, status FROM v$datafile ORDER BY file#;

-- Validate tablespace online
SELECT tablespace_name, status FROM dba_tablespaces ORDER BY 1;

-- Optional: validate backups proactively
RMAN> VALIDATE DATABASE;

FAQ

Do I need to stop the database?

Not for user tablespaces in ARCHIVELOG mode. Take the file/tablespace offline, restore/recover, then bring it online. For SYSTEM/SYSAUX, mount the database and perform recovery.

What’s the difference between RESTORE and RECOVER?

RESTORE copies the backup datafile back to disk; RECOVER applies redo to make the file current.

What if I don’t know the file number?

Use REPORT SCHEMA in RMAN or query v$datafile to map file numbers to paths.

Related Reading

• Recover a Lost Control File (exercise)
• Oracle Trace Files (finding errors fast)