Home / Databases / Oracle

DatabasesRunbookAdvanced

Sev1: Oracle database won't open. ORA-01113, ORA-01157 and ORA-01110 media recovery

The database mounts but won't open because a datafile needs recovery, is missing or can't be locked. Find the affected file, decide between recover, restore-and-recover or taking the file offline, and use RMAN to bring the database back with no data loss where possible.

Deepak Bhardwaj3 min read

Applies toOracle Database 19c, 21c, 23ai (single instance; RAC uses the same RMAN steps from one instance)

Symptoms

ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/u02/oradata/PROD/users01.dbf'

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u02/oradata/PROD/users01.dbf'

Step 1: what's wrong with which file

-- as SYSDBA, database in MOUNT
SELECT file#, name, status, error, recover FROM v$datafile_header WHERE error IS NOT NULL OR recover = 'YES';
SELECT * FROM v$recover_file;

And on the OS:

ls -l /u02/oradata/PROD/users01.dbf
df -h /u02
tail -100 $ORACLE_BASE/diag/rdbms/prod/PROD/trace/alert_PROD.log
Finding Meaning
ORA-01113, file exists File is older than the controlfile (restored, or crash during backup mode). Needs recover
ORA-01157, file missing / no permission / filesystem not mounted Fix the OS problem first; if the file is truly gone, restore + recover
File was in hot backup mode when the instance crashed ALTER DATABASE END BACKUP; then open

Step 2a: file exists, so recover it

RECOVER DATAFILE 7;          -- SQL*Plus, applies archived logs; answer AUTO
ALTER DATABASE OPEN;

Or in RMAN:

RMAN> RECOVER DATAFILE 7;
RMAN> ALTER DATABASE OPEN;

Step 2b: file lost, so restore and recover

rman target /
RMAN> RESTORE DATAFILE 7;
RMAN> RECOVER DATAFILE 7;
RMAN> ALTER DATABASE OPEN;

If the original location is unusable, restore elsewhere:

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

Step 2c: open now, fix the file later

If the file belongs to a non-critical tablespace and the business needs the database up now:

ALTER DATABASE DATAFILE 7 OFFLINE;
ALTER DATABASE OPEN;
-- later, with the database open:
-- RMAN> RESTORE DATAFILE 7; RECOVER DATAFILE 7;
ALTER DATABASE DATAFILE 7 ONLINE;

Not possible for SYSTEM or UNDO (in use) datafiles. Those need recovery before open.

Missing archived logs

If RECOVER asks for an archived log that no longer exists, you can't complete recovery of that file to the current point. Options:

  • Find the log (other archive destination, standby, backup set: RESTORE ARCHIVELOG SEQUENCE n;).
  • If the database has a standby, fail over to it, or restore the file from the standby with RESTORE DATAFILE 7 FROM SERVICE standby_tns;.
  • Otherwise, incomplete recovery of the whole database to a point before the gap (data loss; OPEN RESETLOGS).

Caution

Don't run ALTER DATABASE OPEN RESETLOGS or recreate the controlfile to "get past" an error unless you understand the consequences. Both can make further recovery impossible. Take a backup of the current state (files, controlfile, online redo) before any drastic step.

Step 3: after opening

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> VALIDATE DATABASE;

Check the alert log for the root cause: storage path, filesystem not mounted at boot, accidental deletion.

Prevent it next time

  • RMAN backups with archived logs, tested restores, CONTROLFILE AUTOBACKUP ON.
  • Multiple archive log destinations or a Data Guard standby.
  • Filesystems in /etc/fstab with dependencies so the database doesn't start before storage is mounted.

Related fixes