maxalarie
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\ENO VIADBS\RED O03.LOG'
SQL> alter database clear logfile 'C:\app\enoadm\oradata\Eno viaDBs\RED O03.LOG';
alter database clear logfile 'C:\app\enoadm\oradata\Eno viaDBs\RED O03.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\ENO VIADBS\RED O03.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\ENO VIADBS\RED O03.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\ENO VIADBS\RED O03.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_corruptio n=true in the pfile, startup mount, then alter open resetlogs, and it didn't work...
Any other idea ?
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\ENO
SQL> alter database clear logfile 'C:\app\enoadm\oradata\Eno
alter database clear logfile 'C:\app\enoadm\oradata\Eno
*
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\ENO
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\ENO
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\ENO
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_corruptio
Any other idea ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>If it doesn't help try to open with _allow_resetlogs_corruptio n=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.
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.
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\ENO VIADBS\SYS TEM01.DBF'
Should I try with no resetlogs...
I did, it says to use resetlogs...
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\ENO
Should I try with no resetlogs...
I did, it says to use resetlogs...
ASKER
@ 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 !
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 !
ASKER
@ 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.
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).
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).
ASKER
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
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.
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.
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!
ASKER
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_RECOVE RY_AREA\EN OVIADBS\AR CHIVELOG\2 012_02_26\ O1_MF_1_87 9_%U
_.ARC
ORA-00280: change 20037355 for thread 1 is in sequence #879
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\app\enoadm\oradata\Enov iaDBs\REDO 01.LOG
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: 'C:\APP\ENOADM\ORADATA\ENO VIADBS\RED O01.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 ?
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_RECOVE
_.ARC
ORA-00280: change 20037355 for thread 1 is in sequence #879
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\app\enoadm\oradata\Enov
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: 'C:\APP\ENOADM\ORADATA\ENO
I tried with all 3 of them, same result.
I tried the other options with no success...
Should I go with a recover until ?
ASKER
And there are no .ARC files in the archivelog folders...
ASKER
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
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.
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.
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.
ASKER
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
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.
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".
ASKER
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_RECOVE RY_AREA\EN OVIADBS\AR CHIVELOG\2 012_02_27\ O1_MF_1_87 9_%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_RECOV ERY_AREA\E NOVIADBS\A RCHIVELOG\ 2012_02_27 \O1_MF_1_8 79_%
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\ENO VIADBS\SYS TEM01.DBF'
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_RECOVE
_.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_RECOV
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\ENO
>>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
Looks like it did to me: Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Looks like you hit enter. You should have typed: CANCEL
ASKER
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_RECOVE RY_AREA\EN OVIADBS\AR CHIVELOG\2 012_02_27\ O1_MF_1_87 9_%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\ENO VIADBS\SYS TEM01.DBF'
But still no luck !
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_RECOVE
_.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\ENO
But still no luck !
Try manually recovering it:
recover datafile 1;
recover datafile 1;
ASKER
SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
ASKER
SQL> recover datafile 1 using backup controlfile;
ORA-00274: illegal recovery option USING
hehe
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.
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;
alter database recover datafile 1 using backup controlfile;
ASKER
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...
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...
ASKER
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 !
Thanks for your help !
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_corruptio
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.