Oracle recovery in noarchivelog mode

[Former Member]

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 .

[markus_doehr2 · Accepted solution]

> 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

[former_member204746]

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;