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
- Startup the database with the
mountoptionSQL> 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. - 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 - 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. - Finally open the database in
RESETLOGSmode. It is recommended to reset the online logs after incomplete recovery or recovery with a backup control fileSQL> 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
This work like a champ.
ReplyDeleteWhen doing a system copy you will see this. On the host recover up to the last archive log. Then apply each of the redo logs until you get the database opened message. So for example in origloA will be (*.dbf files) just apply each of them until you get the message.
Kelly
This was very useful .
ReplyDeleteBut i have one doubt when i try to restore the database in a different server (Means i took backup from production database and restore in test database) I got this same error ORA-01114.
So i think i should replace the step 3 with
SQL> RECOVER DATABASE USING CONTROLFILE UNTIL CANCEL ;
Am i rite or do i still need to use BACKUP option here.
Please help !!
it helped a lot.thank u very much
ReplyDeleteit helped a lot.thank u very much
ReplyDeleteI also thank you for this. I haven't seen this type of recovery before and it helped me.
ReplyDeleteThis helped me as well. Thanks for the post.
ReplyDeleteYou are great man, its work perfectly, thanks a lot!!!
ReplyDeletethis works if the dabase is not in backup mode...i had this issue and then i saw that the db was in backup mode and i was geting further errors such as:
ReplyDeleteORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/oradata/MGOrcl/system01.dbf
then i issues this command:
alter database end backup;
and followed instruction here...
thank you
thakk you very much i have start my database,thank you agin
ReplyDeletesayeed akhtar
i held it thank u sir
ReplyDeletethanks for the post!
ReplyDeleteit helped me much.
the only thing was that i obtain two redos in the query. I select the last one and the DB was recovered.
regards
Thank you very much. The proposed solution worked perfectly for us.
ReplyDeleteRegards,
Wow....!! Just superb Bro!
ReplyDeleteIt's work great, like a real genius.
Thanks a lot. :)
Thanks a lot!!
ReplyDeleteThanks a lot you literally saved my life!! My Informatica services were not running due to Oracle being down.
ReplyDeleteI tried this but I'm facing the below error.
ReplyDeleteORA-00309: log belongs to wrong database
ORA-00334: archived log: '/oracle/ACC/origlogB/log_g14m1.dbf'
Plz help
Thank you very much for this link you have just saved me hours of work.
ReplyDeleteRegards
John V
the 'alter database end backup' tip helped me to open the database on my refreshs. thanks!
ReplyDeleteTHANKS
ReplyDeleteITS VERY HELPFULL TO ME ALSO
Great job. It worked perfectly
ReplyDeleteWork like a charm!!! Thanks very helpful;;;
ReplyDeleteDB back up after failed Duplicate, thanks for sharing
ReplyDeleteThank you very much!
ReplyDeleteI searched and I searched and tried so many paths to recover my local DB ... hard to believe that there is no easily accessible documentation on how to do this. Followed these instructions and I was back in business. Thank you, thank you, thank you!
ReplyDeletethank you...helpful!!!
ReplyDeleteHey man, just wanted to say thank you. Was in a panic, and this post worked perfectly to help me. RHEL5 with Oracle Enterprise 11.2 in Pelham Alabama.
ReplyDelete