ORA-01113: file n needs media recovery - ORA-01110: data file n: \SYSTEM01.dbf′

After incomplete recovery of my one of the database, I have encounter “ORA-01113: file n needs media recovery” and “ORA-01110: data file n: ‘SYSTEM01.dbf′” while alter database to open mode with reset-logs. After diagnosis, I came to know that, while incomplete recovery oracle database ask for non existing archive log file, in this case provide current log file as an input to “RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;”.

In my case, Workaround for ORA-01113 & ORA-01110 are as follows:

Startup your database in mount state:

SQL> shutdown immediate;
 ORA-01109: database not open
 Database dismounted.
 ORACLE instance shut down.

SQL> startup mount;
 ORACLE instance started.
 Total System Global Area 7917113344 bytes
 Fixed Size 2198064 bytes
 Variable Size 3976201680 bytes
 Database Buffers 3925868544 bytes
 Redo Buffers 12845056 bytes
 Database mounted.

Following query will help to retrieve name of the current redo log files, i.e. redo log belongs to active group.

SQL> SELECT MEMBER FROM V$LOG G, V$LOGFILE F WHERE G.GROUP# = F.GROUP# AND G.STATUS = 'CURRENT';

MEMBER
 --------------------------------------------------------------------------------
 /oracle/<oracle_sid>/origlogA/log_g17m1.dbf
 /oracle/<oracle_sid>/mirrlogA/log_g17m2.dbf

Above redo files will help to recover database.

Recover your database with the help of following command, when oracle suggested for non existing archive log with full path (i.e. invalid file) Ignore it and provide above redo log file as an input.
Note: There is no file available on specified path. i.e. <oracle_sid>arch1_66606_758161962.dbf

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
 ORA-00279: change 1041939961 generated at 08/11/2014 16:31:04 needed for thread 1
 ORA-00289: suggestion : /oracle/<oracle_sid>/oraarch/<oracle_sid>arch1_66606_758161962.dbf
 ORA-00280: change 1041939961 for thread 1 is in sequence #66606
 Specify log: {=suggested | filename | AUTO | CANCEL}
 /oracle/<oracle_sid>/origlogA/log_g17m1.dbf
 Log applied.
 Media recovery complete.

Media recovery completed successfully, While backup with controlfile OR in case of incomplete recovery, you need to open your database with reset-logs, its recommended.

 SQL> alter database open resetlogs;
 Database altered.

SQL> SQL> select open_mode from v$database;
 OPEN_MODE
 --------------------
 READ WRITE

Database open successfully.

After database recovery, Kindly consider full database backup for future safety.

Stay Tun

ORA-01113: file n needs media recovery – ORA-01110: data file n: \SYSTEM01.dbf′

Similar Posts:

1 thought on “ORA-01113: file n needs media recovery – ORA-01110: data file n: \SYSTEM01.dbf′”

Leave a Comment Cancel Reply