Skip to Content
author's profile photo Former Member
Former Member

Oracle recovery in noarchivelog mode

Hello,

SAP Release : ECC6

Oracle 10G

Solaris 10g

Our SAP system is in noarchivelog mode (formation environnement, with frequent client copies).

We frequently perform an offline backup.

The solaris server that is hosting our Sap system, has been brutally shutdown this morning,

due to a network issue.

Afterwards, we had to restart the sap system.

A fisrt error occured ; inconsistency between the control files ...

ORA-00205: error in identifying control file, check alert log for more info

In alert log :

ORA-00202: control file: '/oracle/ZMA/origlogA/cntrl/cntlrZMA.dbf'

ORA-27037: unable to obtain file status

We regenerated a new control file and replaced it.

The error was solved.

Then a second error occured :

SQL> startup

ORACLE instance started.

Total System Global Area 1660944384 bytes

Fixed Size 2268216 bytes

Variable Size 851207112 bytes

Database Buffers 805306368 bytes

Redo Buffers 2162688 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs ;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/ZMA/sapdata1/system_1/system.data1'

SQL> recover database using backup controlfile until cancel ;

ORA-00279: change 647063400 generated at 04/28/2008 11:10:01 needed for thread

1

ORA-00289: suggestion : /oracle/ZMA/oraarch/ZMAarch1_76005_618576658.dbf

ORA-00280: change 647063400 for thread 1 is in sequence #76005

The redo log #76005 is no more available; I tried to apply the online redo logs, but it didn't help.

We had to restore the whole database from the offline backup.

1/ I still have problems understanding why Oracle is asking for a recovery, when we tried to reopen the database ? The database was consistent this morning, how is it that the file '/oracle/ZMA/sapdata1/system_1/system.data1' became inconsistent ?

2/ In sap note 863417 "If the database operates in NOARCHIVELOG mode, the database can ONLY be completely recovered from instance failure but not from media failure. "

What is exactly the difference between media failure and instance failure ?

Does a network problem and then a reboot of the host is considered a media failure ?

Best Regards.

thank you for your help .

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Apr 28, 2008 at 02:06 PM

    > 1/ I still have problems understanding why Oracle is asking for a recovery, when we tried to reopen the database ? The database was consistent this morning, how is it that the file '/oracle/ZMA/sapdata1/system_1/system.data1' became inconsistent ?

    Because the database was not shut down cleanly. An open database is by definition "inconsistent" without the proper logfiles.

    > 2/ In sap note 863417 "If the database operates in NOARCHIVELOG mode, the database can ONLY be completely recovered from instance failure but not from media failure. "

    > What is exactly the difference between media failure and instance failure ?

    Media = File/Raw-Device. If you database is crashing due to a software error it can be recovered, if a file is corrupt it can't be (since you have no redo logs).

    > Does a network problem and then a reboot of the host is considered a media failure ?

    A corrupt control file (e. g.) is a media failure.

    Markus

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 28, 2008 at 02:14 PM

    try this:

    RECOVER DATABASE USING BACKUP CONTROLFILE

    /oracle/ZMA/origloga\LOG_G11M1.DBF

    CYCLE THROUGH EVERY SINGLE ONLINE REDOLOG...

    you have to get this message: Log applied.

    Media recovery complete.

    THEN:

    ALTER DATABASE OPEN RESETLOGS;

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.