Oracle 11g: Recover the lost of a redo log

Redo log file is part of a redo log group that have more than one file

• Identify the missing or corrupted redo log file from alert log
• Drop the log file member from the group:

  SQL>alter database drop logfile member '<redo_log_file_name>';

• Add a new file redo log file member in the previous group :

  SQL>alter database add logfile member '<redo_log_file_name>' to group #;

Redo log file is unique inside a redo log group

• May be you can drop the group and recreate a new one (if redo log not ACTIVE or CURRENT) :
  

  SQL>alter database drop logfile group #;
  SQL>alter database add logfile group # ('<redo_log_file_name>') SIZE 150M;

• May be you can clear the log file of the group that is corrupted or missing.
  
  • If the database is in NOARCHIVELOG or if the redo log has been archived you can write :

    SQL>alter database clear logfile group #;

  • If the database is in ARCHIVELOG and the redo log file has not been archived :

    SQL>alter database clear unarchived logfile group #;

• If the redo log is ACTIVE then you can try :

  SQL>alter system checkpoint;

  If the checkpoint fails then you need to perform a complete recovery from a previous backup.

Remark : A log cannot be dropped or cleared until the thread's checkpoint has advanced out of the log.
If the database is not open, then open it. Crash recovery will advance the checkpoint.
If the database is open force a global checkpoint. If the log is corrupted so that the database cannot be opened, it may be necessary to do incomplete recovery until cancel at this log.

For an incomplete recovery :

SQL>RECOVER DATABASE UNTIL CANCEL;
SQL>ALTER DATABASE OPEN RESETLOGS;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

Remark : If you are unable to recover a datafile with redolog (cause redolog is corrupted) and you accept to loose data from datafile then you can use :

SQL>alter database clear logfile unrecoverable datafile;

Oracle requires that you use the unrecoverable datafile option when the particular redo log group is both unarchived and you cannot get the data from it. It looks like Oracle expects that you had already tried to recover the datafile and could not because of a bad redo log.

Note seen from another site (Don't remember where cause just have a copy past):
Oracle Recovery with _allow_resetlogs_corruption

Recovery of a database using the undocumented parameter _allow_resetlogs_corruption should be regarded as a last-ditch, emergency recovery scenario only, and should not be attempted until all other avenues of recovery have been exhausted.
Note that Oracle will not support a database that has been recovered using this method unless it is subsequently exported and rebuilt.
Essentially, using _allow_resetlogs_corruption forces the opening of the datafiles even if their SCNs do not match up; then, on the next checkpoint, the old SCN values are overwritten. This could leave the database in an unknown state as far as concurrency.
To use this option:

SQL>alter system set "_allow_resetlogs_corruption"=true scope=spfile;
SQL>shutdown immediate;
SQL>startup mount;
SQL>recover database until cancel;
CANCEL
SQL>alter database open resetlogs;
SQL>shutdown immediate;
SQL>startup;