Link to home
Start Free TrialLog in
Avatar of maxalarie
maxalarieFlag for Canada

asked on

Crash Oracle - Corrupted REDO.LOG

Hi,

An oracle instance (11g on Windows) crashed and it doesn't want to load anymore...

ORACLE instance started.

Total System Global Area  855982080 bytes
Fixed Size                  2180544 bytes
Variable Size             629148224 bytes
Database Buffers          218103808 bytes
Redo Buffers                6549504 bytes
Database mounted.
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 33199 change 20050963 time 02/17/2012
04:07:45
ORA-00312: online log 3 thread 1: 'C:\APP\ENOADM\ORADATA\ENOVIADBS\REDO03.LOG'

SQL> alter database clear logfile 'C:\app\enoadm\oradata\EnoviaDBs\REDO03.LOG';
alter database clear logfile 'C:\app\enoadm\oradata\EnoviaDBs\REDO03.LOG'
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance enoviadbs (thread 1)
ORA-00312: online log 3 thread 1: 'C:\APP\ENOADM\ORADATA\ENOVIADBS\REDO03.LOG'

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3 UNRECOVERABLE DATAFILE ;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3 UNRECOVERABLE DATAFILE
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance enoviadbs (thread 1)
ORA-00312: online log 3 thread 1: 'C:\APP\ENOADM\ORADATA\ENOVIADBS\REDO03.LOG'

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3 UNRECOVERABLE DATAFILE ;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3 UNRECOVERABLE DATAFILE
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance enoviadbs (thread 1)
ORA-00312: online log 3 thread 1: 'C:\APP\ENOADM\ORADATA\ENOVIADBS\REDO03.LOG'

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open


SQL>
SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-01109: database not open

I even tried with the _allow_resetlogs_corruption=true in the pfile, startup mount, then alter open resetlogs, and it didn't work...

Any other idea ?
ASKER CERTIFIED SOLUTION
Avatar of Praveen Kumar Chandrashekatr
Praveen Kumar Chandrashekatr
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of schwertner
So far as I know there is no fix for corrupted online redo log. Thatswhy it is recommended to multiplex the redo groups.

Even the DB is in ARCHIVELOG mode you can do nothing if the redo log is corrupted.

Make a whole cald backup of the DB and try this:

http://searchoracle.techtarget.com/answer/Redo-log-files-corrupted

Or this (Case 2):

http://www.bestremotedba.com/rman-recovery-from-missing-or-corrupted-redo-log-group/

If it doesn't help try to open with  _allow_resetlogs_corruption=true

Possibly the DB will not be consistent. If you have luck only a few records will be unconsistent.

A good explanation here:

http://dba-oracle.fr/dba/index2.php?option=com_content&do_pdf=1&id=14

But the generic reason for your situation is missing multiplexing of the redo logs. Never avoid this if the DB contains import data.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>If it doesn't help try to open with  _allow_resetlogs_corruption=true

As mentioned before, this is dangerous to do.  You should ONLY ever do this if directed to do so by Support.

You will be in an unsupported position with Oracle once you use this option.  If the database does open when you use that, the only option is a full export, drop and rebuild the database, import the data back in.
Avatar of maxalarie

ASKER

@ praveencpk

I thought I had tested this, but I did it anyway to provide you with the outputs, but to my surprise, the clear command actually did work :

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Database altered.

But the open didn't work :
SQL> Alter database open;
Alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

I tried with resetlogs, but I had an error :
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: 'C:\APP\ENOADM\ORADATA\ENOVIADBS\SYSTEM01.DBF'

Should I try with no resetlogs...

I did, it says to use resetlogs...
@ schwertner

Yes, we will look into multiplexing...
I have a backup of the VM in case I mess this one up.

I will read your links, but since the log was actually cleared in appearance by praveencpk's suggestion, I will wait for more suggestions about that new state the DB is in.

But thanks for your input !
@ slightwv

I tried that param, and it didn't even open !
I have a backup of the VM in the first problematic state, but we don't have a backup of the DB (I Know, lol...) and it will not backup now, since it doesn't open.
This is what I would try at this point.  Because it says files need recovery, give this a try:

startup mount
recover database

See what it asks for.  It may just be instance recovery and you'll get lucky that it will get past your original problem.  If that works, then you should be able to open the database (may still need resetlogs, but try without first).
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
Try it with the backup controlfile option as suggested:

recover database using backup controlfile;

It may ask you to supply archive logs.  In this situation, the log it is looking for could be one of your online redo logs, you would need to specify those files.
>>  As mentioned before, this is dangerous to do.  You should ONLY ever do this if directed to do so by Support.

You will be in an unsupported position with Oracle once you use this option.  If the database does open when you use that, the only option is a full export, drop and rebuild the database, import the data back in.

>> If you have read more carefully the preceeding sentences i am sure you will notice that before this option I suggest seriosly to make whole cold backup of the database.
So this experiment can be done either on a clonning of the data base or on the same db, but after that there will be possible to put the db back in its starting possition.
As mentioned back in post 37641767, they have a backup of the VM before they started doing anything.  That should suffice.
Agree!
Thanks for your help guys !

It didn't work though.

SQL> recover database using backup controlfile;
ORA-00279: change 20037355 generated at 02/17/2012 00:01:09 needed for thread 1
ORA-00289: suggestion :
C:\APP\ENOADM\FLASH_RECOVERY_AREA\ENOVIADBS\ARCHIVELOG\2012_02_26\O1_MF_1_879_%U

_.ARC
ORA-00280: change 20037355 for thread 1 is in sequence #879


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\app\enoadm\oradata\EnoviaDBs\REDO01.LOG
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: 'C:\APP\ENOADM\ORADATA\ENOVIADBS\REDO01.LOG'

I tried with all 3 of them, same result.
I tried the other options with no success...

Should I go with a recover until ?
And there are no .ARC files in the archivelog folders...
SQL> RECOVER DATABASE UNTIL TIME '2012-02-17:00:00:01';
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
did you tried what i posted in my pervious post 37638934 i.e

recover database until cancel;
then opening the database in resetlogs.
Until cancel would be my next suggestion as well.

If that doesn't work, then Oracle Support is probably going to be your best option.  They have tools we don't have access to.
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
Use

recover database until cancel using backup controlfile;

You have to put it all together.
When it asks for the first log, just type in "CANCEL".
It didn't ask...

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 20037355 generated at 02/17/2012 00:01:09 needed for thread 1
ORA-00289: suggestion :
C:\APP\ENOADM\FLASH_RECOVERY_AREA\ENOVIADBS\ARCHIVELOG\2012_02_27\O1_MF_1_879_%U

_.ARC
ORA-00280: change 20037355 for thread 1 is in sequence #879


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'C:\APP\ENOADM\FLASH_RECOVERY_AREA\ENOVIADBS\ARCHIVELOG\2012_02_27\O1_MF_1_879_%

U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\APP\ENOADM\ORADATA\ENOVIADBS\SYSTEM01.DBF'
>>It didn't ask...

Looks like it did to me:  Specify log: {<RET>=suggested | filename | AUTO | CANCEL}


Looks like you hit enter.  You should have typed: CANCEL
I guess I did...

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 20037355 generated at 02/17/2012 00:01:09 needed for thread 1
ORA-00289: suggestion :
C:\APP\ENOADM\FLASH_RECOVERY_AREA\ENOVIADBS\ARCHIVELOG\2012_02_27\O1_MF_1_879_%U

_.ARC
ORA-00280: change 20037355 for thread 1 is in sequence #879


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\APP\ENOADM\ORADATA\ENOVIADBS\SYSTEM01.DBF'

But still no luck !
Try manually recovering it:
recover datafile 1;
SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover datafile 1 using backup controlfile;
ORA-00274: illegal recovery option USING

hehe
Based on this message:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

I believe that you are stuck.  I'm out of ideas.  Other than Oracle support, I cannot think of anything else.
One last thing to try.  I believe this syntax is still valid:

alter database recover datafile 1 using backup controlfile;
It didn't work...

SQL> alter database recover datafile 1 using backup controlfile;
alter database recover datafile 1 using backup controlfile
*
ERROR at line 1:

Thanks for your time, we will rebuild the environnement !
I will award the points to the one who gave the solution to clear the redo...
It cleared the redo log, but we were never able to open the DB since there was a recovery nessary on the system01.dbf also !

Thanks for your help !