How to recover from a loss of one redo log member of a multiplexed group

This scenario describes what happens when you lose one redo log member of a multiplexed group.
It could happen for a simply media failure or because some one "needed" to free some space and saw that .log file!!!
It happens to me years ago and that was told to me by a system administrator.

Anyway when you have multiplexed your redo log group using several members as Oracle best practices suggest and your disks begin to experience problems your database will continue to function since it's still able to write to one redo log member.
If your disk is completely out you can opt to some options whether or not you have a new disk!
With a new disk you can drop and create again the "lost" redo log member to the original location, otherwise you have to perform the same two steps (drop and create) choosing a different location.
Even if you have already lost your redo log member Oracle will arise the ORA-01609 error when you try to drop a member of a CURRENT group. Querying the status column of V$LOG view you can have several results and generally they are:
CURRENT when that log file is "currently" written by the log writer process (ps -ef|grep lgwr),
ACTIVE when that log file is required in case of a crash recovery (even if it's already archived)
INACTIVE when that log file is not required in case of a crash recovery.

So when you try to drop your "failed" redo log member and you receive the ORA-01609 error just force the database to switch to the next redo log group. 

Let's look it with an example. Here is my redo log configuration:

SQL> select member, a.group#, a.status, b.status from v$log a, v$logfile b where  a.group# = b.group#
  2  order by a.group#, member;

MEMBER                                                           GROUP# STATUS           STATUS
------------------------------------------------------------ ---------- ---------------- -------
/home/oracle/app/oracle/oradata/orcl/redo01.log                       1 INACTIVE
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 INACTIVE
/home/oracle/app/oracle/oradata/orcl/redo02.log                       2 CURRENT
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 CURRENT
/home/oracle/app/oracle/oradata/orcl/redo03.log                       3 INACTIVE
/home/oracle/app/oracle/oradata/orcl/redo03b.log                      3 INACTIVE

As you can see I have three redo log groups formed by two multiplexed members and currently the redo log group number 2 is the CURRENT one. Now I simulate the lost of the redo log member named 'redo02b.log'.

[oracle@localhost ~]$ cd /home/oracle/app/oracle/oradata/orcl/
[oracle@localhost orcl]$ rm redo02b.log
[oracle@localhost orcl]$ ll redo*
-rw-rw---- 1 oracle oracle 52429312 Sep  5 12:58 redo01b.log
-rw-rw---- 1 oracle oracle 52429312 Sep  5 12:58 redo01.log
-rw-rw---- 1 oracle oracle 52429312 Sep  5 22:30 redo02.log
-rw-rw---- 1 oracle oracle 52429312 Sep  5 12:55 redo03b.log
-rw-rw---- 1 oracle oracle 52429312 Sep  5 12:55 redo03.log

As you cann see from the alert log appears the error

...
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_8172.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo02b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
...

Now have a look at how the status column of V$LOGFILE view change (from NULL to INVALID) for the lost member

SQL> select member, a.group#, a.status, b.status from v$log a, v$logfile b where  a.group# = b.group#
  2  order by a.group#, member;

MEMBER                                                           GROUP# STATUS           STATUS
------------------------------------------------------------ ---------- ---------------- -------
/home/oracle/app/oracle/oradata/orcl/redo01.log                       1 INACTIVE
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 INACTIVE
/home/oracle/app/oracle/oradata/orcl/redo02.log                       2 CURRENT
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 CURRENT          INVALID
/home/oracle/app/oracle/oradata/orcl/redo03.log                       3 INACTIVE
/home/oracle/app/oracle/oradata/orcl/redo03b.log                      3 INACTIVE

I've lost a member of the CURRENT redo log group so I cannot drop it

SQL> alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo02b.log';
alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo02b.log'
*
ERROR at line 1:
ORA-01609: log 2 is the current log for thread 1 - cannot drop members
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo02.log'
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo02b.log'

I have to force a redo log switch:

SQL> alter system switch logfile;

System altered.

Now my redo log group state is as the following:

SQL> select member, a.group#, a.status, b.status from v$log a, v$logfile b where  a.group# = b.group#
  2  order by a.group#, member;

MEMBER                                                           GROUP# STATUS           STATUS
------------------------------------------------------------ ---------- ---------------- -------
/home/oracle/app/oracle/oradata/orcl/redo01.log                       1 INACTIVE
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 INACTIVE
/home/oracle/app/oracle/oradata/orcl/redo02.log                       2 ACTIVE
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 ACTIVE           INVALID
/home/oracle/app/oracle/oradata/orcl/redo03.log                       3 CURRENT
/home/oracle/app/oracle/oradata/orcl/redo03b.log                      3 CURRENT

And I can successfully drop and recreate my lost redo log member:

SQL> alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo02b.log';

Database altered.

SQL> select member, a.group#, a.status, b.status from v$log a, v$logfile b where  a.group# = b.group#
  2  order by a.group#, member;

MEMBER                                                           GROUP# STATUS           STATUS
------------------------------------------------------------ ---------- ---------------- -------
/home/oracle/app/oracle/oradata/orcl/redo01.log                       1 INACTIVE
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 INACTIVE
/home/oracle/app/oracle/oradata/orcl/redo02.log                       2 ACTIVE
/home/oracle/app/oracle/oradata/orcl/redo03.log                       3 CURRENT
/home/oracle/app/oracle/oradata/orcl/redo03b.log                      3 CURRENT

SQL> alter database add logfile member  '/home/oracle/app/oracle/oradata/orcl/redo02b.log' to group 2;

Database altered.

SQL> select member, a.group#, a.status, b.status from v$log a, v$logfile b where  a.group# = b.group#
  2  order by a.group#, member;

MEMBER                                                           GROUP# STATUS           STATUS
------------------------------------------------------------ ---------- ---------------- -------
/home/oracle/app/oracle/oradata/orcl/redo01.log                       1 INACTIVE
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 INACTIVE
/home/oracle/app/oracle/oradata/orcl/redo02.log                       2 ACTIVE
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 ACTIVE           INVALID
/home/oracle/app/oracle/oradata/orcl/redo03.log                       3 CURRENT
/home/oracle/app/oracle/oradata/orcl/redo03b.log                      3 CURRENT

What's happening ? My new redo log member is still INVALID ? What does it mean ? If you look at the Oracle documentation INVALID could mean: inaccessible (as it was before) or just created (as I did few lines above). Let's look what it happens when I force logfiles to switch:

SQL> alter system switch logfile;

System altered.

SQL> select member, a.group#, a.status, b.status from v$log a, v$logfile b where  a.group# = b.group#
  2  order by a.group#, member;

MEMBER                                                           GROUP# STATUS           STATUS
------------------------------------------------------------ ---------- ---------------- -------
/home/oracle/app/oracle/oradata/orcl/redo01.log                       1 CURRENT
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 CURRENT
/home/oracle/app/oracle/oradata/orcl/redo02.log                       2 INACTIVE
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 INACTIVE         INVALID
/home/oracle/app/oracle/oradata/orcl/redo03.log                       3 ACTIVE
/home/oracle/app/oracle/oradata/orcl/redo03b.log                      3 ACTIVE

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> select member, a.group#, a.status, b.status from v$log a, v$logfile b where  a.group# = b.group#
  2  order by a.group#, member;

MEMBER                                                           GROUP# STATUS           STATUS
------------------------------------------------------------ ---------- ---------------- -------
/home/oracle/app/oracle/oradata/orcl/redo01.log                       1 ACTIVE
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 ACTIVE
/home/oracle/app/oracle/oradata/orcl/redo02.log                       2 CURRENT
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 CURRENT
/home/oracle/app/oracle/oradata/orcl/redo03.log                       3 ACTIVE
/home/oracle/app/oracle/oradata/orcl/redo03b.log                      3 ACTIVE

6 rows selected.

As you can see the database marked the 'redo02b.log' as NULL and this means available to be used. That's all.