Oracle: Fixing ORA-01113: file x needs media recovery Error

Disclaimer

The following steps are intended for a quick recovery of the database. Use them at your own risk.

Symptom

Oracle mounts the database, but refuses to open the database with an ORA-01113: file x needs media recovery Error.

% sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 24 09:29:10 2007
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2684354560 bytes
Fixed Size                  1981064 bytes
Variable Size             436209016 bytes
Database Buffers         2231369728 bytes
Redo Buffers               14794752 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/OraData/SIAMST/siamst_system.dbf'

% oerr ora 01113
01113, 00000, "file %s needs media recovery"
// *Cause:  An attempt was made to online or open a database with a file that
//         is in need of media recovery.
// *Action: First apply media recovery to the file.

Resolution

1. Startup the database with the mount option
   

   SQL> startup mount
   ORACLE instance started.
   
   Total System Global Area 2684354560 bytes
   Fixed Size                  1981064 bytes
   Variable Size             436209016 bytes
   Database Buffers         2231369728 bytes
   Redo Buffers               14794752 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# 
     2  AND G.STATUS = 'CURRENT';
   
   MEMBER
   --------------------------------------------------------------------------------
   /OraRedo/RedoLogFiles/siamst_log01.dbf

   
   
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}
   /OraRedo/RedoLogFiles/siamst_log01.dbf
   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.

See Also:

• Metalink Note 1020262.102 ORA-01113, ORA-01110: Trying To Startup Database After Incomplete Recovery
  
• Performing Cancel-Based Incomplete Recovery

_______________
Technorati Tags:
Oracle | Troubleshooting