ORA-01113

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/oradata/orcl/system01.dbf'

cause : An attempt was made to online or open a database with a file that is in need of media recovery.

Solution : First apply media recovery to the file and then open the database.

Resolution:

1) Start the DB with mount option.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                                2288872 bytes

Variable Size                      1040188184 bytes

Database Buffers               553648128 bytes

Redo Buffers                         7286784 bytes

Database mounted.

2) Find the name of the redo log file which belongs to the active group.

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

  2  G.STATUS = 'CURRENT';

MEMBER

--------------------------------------------------------------------------------

/oradata/orcl/redo02.log

3)Using a backup control file, start the cancel based recovery. The system may suggest a non-existing archive log -- ignore it and specify the log file name {with full path} that you got in the above step

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 21375038 generated at 09/21/2007 20:12:47 needed for thread 1
ORA-00289: suggestion : /export/pspp/oracle/dbs/arch1_4_633901491.dbf
ORA-00280: change 21375038 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}
/oradata/orcl/redo02.log

Log applied.
Media recovery complete.

4) Finally open the database in RESETLOGS mode. It is recommended to reset the online logs after incomplete recovery or recovery with a backup control file

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

     Take a backup of the database as soon as the recovery is done.