Opening the database with corrupted redo log

(this tip is from Mohammad Abu Gharbieh)

This is a good one especially for non-production env. 

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

While trying to open or development database, I encountered the following error message

ORA-00333: redo log read error block 8194 count 8192

In production environment, your best guess is to restore a backup.  however, when the data in the database is not important, the following steps are applicable

*you will be using an undocumented parameter

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-00279: change 10135007257258 generated at 04/12/2011 04:48:38 needed for
thread 1
ORA-00289: suggestion :
/home/oracle/infra/flash_recovery_area/RSDB/archivelog/2011_04_12/o1_mf_1_10770_
%u_.arc
ORA-00280: change 10135007257258 for thread 1 is in sequence #10770


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
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;

SQL>Startup;