Wednesday, July 11, 2012

Opening the database with corrupted redo log

ORA-00333: redo log read error block 9233 count 2312

Opening the database with corrupted redo log can cause a loss of committed transactions, therefore, you need to do it at your own risk

Steps :

SQL> Startup Mount;
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 779000 bytes
Variable Size 229383432 bytes
Database Buffers 50331648 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel;

ORA-00280: change 101350984923848 for thread 1 is in sequence #2333

Specify log: {=suggested | filename | AUTO | CANCEL}
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: '/home/oracle/oradata/rsdb/system01.dbf'

ORA-01112: media recovery not started

SQL> alter database open resetlogs;

SQL>shutdown immediate;

You can Do this Steps When Data Its Not Important ,  Such As Test Evn , ....


Osama Mustafa


  1. I would recommend you recover corrupted sql database owing to following software repair database sql coming back databases since Microsoft SQL Server 2000

    1. Tom, your link is for MS SQL databases so doesn't apply here. Thanks anyhow...

  2. Thank's a lot. You save our a..., regards!!!

  3. Great post sir!! Saved a lot of time..

  4. 1. Does this work when DB is not in archive log mode?
    2. How to prevent the ORA-0333 which it is happening when OS crash?

  5. Good work Osama, worked a treat on my test DB!


  6. I got different reply messages from sqlplus but db works fine now. Thank you Mustafa!

  7. Thank u so much I m too happy when I recover my database w/o dmp files.