Mandalika's scratchpad [ Work blog @Oracle | Stock Market Notes | My Music Compositions ]

Old Posts: 09.04  10.04  11.04  12.04  01.05  02.05  03.05  04.05  05.05  06.05  07.05  08.05  09.05  10.05  11.05  12.05  01.06  02.06  03.06  04.06  05.06  06.06  07.06  08.06  09.06  10.06  11.06  12.06  01.07  02.07  03.07  04.07  05.07  06.07  08.07  09.07  10.07  11.07  12.07  01.08  02.08  03.08  04.08  05.08  06.08  07.08  08.08  09.08  10.08  11.08  12.08  01.09  02.09  03.09  04.09  05.09  06.09  07.09  08.09  09.09  10.09  11.09  12.09  01.10  02.10  03.10  04.10  05.10  06.10  07.10  08.10  09.10  10.10  11.10  12.10  01.11  02.11  03.11  04.11  05.11  07.11  08.11  09.11  10.11  11.11  12.11  01.12  02.12  03.12  04.12  05.12  06.12  07.12  08.12  09.12  10.12  11.12  12.12  01.13  02.13  03.13  04.13  05.13  06.13  07.13  08.13  09.13  10.13  11.13  12.13  01.14  02.14  03.14  04.14  05.14  06.14  07.14  09.14  10.14  11.14  12.14  01.15  02.15  03.15  04.15  06.15  09.15  12.15  01.16  03.16  04.16  05.16  06.16  07.16  08.16  09.16  12.16  01.17  02.17  03.17  04.17 


Monday, September 24, 2007
 
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:

_______________
Technorati Tags:
|


Comments:
This work like a champ.
When 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 .
But 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
 
it helped a lot.thank u very much
 
I also thank you for this. I haven't seen this type of recovery before and it helped me.
 
This helped me as well. Thanks for the post.
 
You are great man, its work perfectly, thanks a lot!!!
 
this 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:
ORA-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

sayeed akhtar
 
i held it thank u sir
 
thanks for the post!
it 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.
Regards,
 
Wow....!! Just superb Bro!
It's work great, like a real genius.
Thanks a lot. :)
 
Thanks a lot!!
 
Thanks a lot you literally saved my life!! My Informatica services were not running due to Oracle being down.
 
I tried this but I'm facing the below error.
ORA-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.

Regards
John V
 
the 'alter database end backup' tip helped me to open the database on my refreshs. thanks!
 
Post a Comment

Links to this post:

Create a Link



<< Home


2004-2017 

This page is powered by Blogger. Isn't yours?