ORA-01113: file # needs media recovery | Oracle Database

ORA-01113: file # needs media recovery | ORA-01110: data file #: 'E:\Database\TESTDB\#####.DBF'

ORA-01113 Oracle error indicates inconsistent datafiles with respect to Controlfile SCN and hence requiring further recovery in order to be online again. How those datafiles got into this RECOVER state can be because of multiple reasons as we can assume the different possibilities (power failure, server crash etc). But how they can be recovered and brought back online is answerable and simple.


The nature of the problem occuring because of these errors can be smaller to bigger depending upon which datafiles needs recovery. Usually SYSTEM and SYSAUX are the most important tablespace in any Oracle Database and if their datafiles need recovery, the database won't open. Apart from these tablespaces/datafiles, database may be opened but the problems can be reflected in multiple scenarios like,

- Unable to fetch data properly / Missing data because database is not able to read the datafile
- Unable to login to some Application which is linked to the same database.
- Unable to take backups properly - RMAN/EXPDP/EXP

If you are getting troubled with the above mentioned errors, please follow the below mentioned guidelines to resolve your issue here:

1. If your database is in ARCHIVELOG MODE,

a) If you are having all the archivelogs after the last successful RMAN LO or RMAN L1 backup, and the location where archives are present is catalogued, then at the time of Database Startup, the database will automatically recover every datafile which needs recovery. If not, you can always connect to RMAN and run RECOVER from there.

To know how many datafiles are in recovery state,

SQL> select FILE#, status from V$datafile where status 'RECOVER';

To recover the datafile,

RMAN> recover datafile <datafile_No>;

b) In case if you have copied the archives to some separate directory other than from where they were created, you can always register them with database using

RMAN> catalog start with 'Location';

And then try to recover your datafile.

c) In case if you are missing your archivelogs then you'll have to restore and recover the database from the last successful RMAN Level0/Full Backup and applying Other Level1/Incremental Backups if any.

2. If your database is in NOARCHIVELOG Mode and RMAN Backup is there.

a) If you have a RMAN Backup, simply restore the datafiles from the same and recover the database. Data Loss will be there depending upon how old that backup is.

b) Also look for other backups like EXPDP or EXP or any Cold Backup (usually in case of Small Databases) which may help you recreate your database.

3. Database is in NOARCHIVELOG Mode and No Backup is there.

At this state, there is no possibility that you'll be able to recover your database but for a workaround try recovering the database using the CURRENT REDO LOG. This is not a recommended approach but has worked for me sometimes. Strictly Speaking - This method is not for PRODUCTION as PROD Databases are always meant to run in ARCHIVELOG Mode.

I hope this helps !!