ORA-01113: file 1 needs media recovery

[Former Member]

Hello,

I've a problem during restore of the offline backup of our PRD system on a SandBox.

Here is the error message :

SQL> startup
ORACLE instance started.
Total System Global Area 1124073472 bytes
Fixed Size                  1250092 bytes
Variable Size             546106580 bytes
Database Buffers          573775872 bytes
Redo Buffers                2940928 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORACLE\ECC\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

I tried to recover the database using backup controlfile and applied a day of archive but the problem is still the same.

I already had this error message in the past and that was due to Auto Undo Management, but now the spfile is correct.

Does anyone have an idea ?

Thanks in advance,

Alex

[Former Member]

Hi,

From my experience, you should have done a 'stopsap r3 ' when an online bacup is already running..

SQL>alter database end backup;

SQL> stopsap;

SQL>startsap

In future avoid doing this, before stopping SAP, check whether online backup is not running.. If you want to stop the system, kill the online back-up and then stop..

Regards

Thilip Kumar

[Former Member]

Hi,

I am facing the same issue. I am restoring from an online consistent backup .

I applied many archive logs still it says it needs more file for recovery .

ORA-01113: file 1 needs media recovery

How do i resolve it. Where will it stop ?

Thanks,

Chitta

[Former Member]

Alex,

Check to see that the SID name for the online / archive files on the destination are correct. Also verify the permission / ownership of these files.

thanks

BMG

[Former Member]

Thanks a lot all for you answers.

I've found the issue, it was because my oracle database software wasn't at the same level as the souce one.

I patched it and everything is ok now.

[Former Member]

Hi Alexandre

Did you restore your controlfile as well? From the same backup as your datafiles?

If the controlfile is newer, than the datafiles, oracle wants you to recover them. If this is the case, then move all controlfiles away and either restore it from that backup or create a new one with 'alter database backup controlfile to trace'.

Regards, Michael

Edit: if you are already in the situation above, try a 'recover database until cancel' followed by an immediate cancel and 'alter database open resetlogs'

Edited by: mho on Feb 6, 2008 9:50 AM

[Former Member]

I generated a new controlfile from source DB and run the script on the new db.

Using that command :

CREATE CONTROLFILE REUSE set DATABASE "ECC" RESETLOGS  ARCHIVELOG

May that be a problem of rollback segment ?

Edit : I already tried the "UNTIL CANCEL" method.

Edited by: Alexandre Belgrand on Feb 6, 2008 10:03 AM

[Former Member]

Ok, the reuse clause will overwrite the controlfiles, should be ok then. I don't think this is an issue with rollback / AUM, unless you are not using manual undo management on the source db and have undo_management = AUTO in the new spfile.

Did you try this?

SQL> alter database recover until cancel;
...
CANCEL

SQL> alter database open resetlogs [using backup controlfile];

Regards, Michael

[stefan_koehler]

Hello,

you have said that you have applied a day of archive.

Have you applied archivelogs in which the database was in backup mode (for example for online backup). In this case you get this behavior too.

View V$BACKUP:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1026.htm#i1399625

Regards

Stefan

[Former Member]

Dear Michael Hofmänner

Thank you for pointing out about UNTIL CANCEL and immediate cancel.  It didnt spark to my mind and your answer saved me today though it is after 7 years.

Regards,

Senthil

[Former Member]

Dear both,

You made my day and I was only trying for a couple of hours to solve the problem but I could n't figure out why Oracle requires more archive logs to be applied to recover database tables despite the fact that each of these archives were in a consistent state. In a sense, the recovery process must be closed somehow and this is achieved by Until cancel .... CANCEL.

Thanks a lot.