How to recover from a loss of all redo log members of the CURRENT group

When your database loses all members of the CURRENT redo log group, it means you have lost transactions contained in that group: to be able opening your database you have to perform an incomplete recovery until latest valid SCN. Let's simulate this scenario with an example. I will get information about redo log groups, create a table, insert some rows and commit them, force a log switch, create another table, insert other rows and commit them on the second table. I'm expecting dropping the CURRENT redo log group will force me to perform an incomplete recovery, being able to get back rows of first table, but no rows of the second... Here is the script:

set linesize 180
set pagesize 999
col member format a60
select member, a.group#, a.status, b.status, a.archived  from v$log a, v$logfile b where  a.group# = b.group# order by a.group#, member;
declare
begin
execute immediate 'create table hr.test_active7 (a number)';
for i in 1 .. 100
loop
execute immediate 'insert into hr.test_active7 values (:a)' using i;
end loop;
commit;
execute immediate 'alter system switch logfile';
end;
/
select member, a.group#, a.status, b.status, a.archived  from v$log a, v$logfile b where  a.group# = b.group# order by a.group#, member;
declare
begin
execute immediate 'create table hr.test_active8 (a number)';
for i in 1 .. 100
loop
execute immediate 'insert into hr.test_active8 values (:a)' using i;
end loop;
commit;
end;
/
select member, a.group#, a.status, b.status, a.archived  from v$log a, v$logfile b where  a.group# = b.group# order by a.group#, member;

The output of the script:

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

6 rows selected.

SQL>   2    3    4    5    6    7    8    9   10   11  

PL/SQL procedure successfully completed.

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

6 rows selected.

SQL>   2    3    4    5    6    7    8    9   10  
PL/SQL procedure successfully completed.

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

My CURRENT redo log group is number 1 and rows are inserted.

select a, b from (select count(*) a from hr.test_active7), (select count(*) b from hr.test_active8);

         A          B
---------- ----------
       100        100

Let's remove all members of the CURRENT redo log group

[oracle@localhost orcl]$ rm redo01*
[oracle@localhost orcl]$ ll redo0*
-rw-rw---- 1 oracle oracle 52429312 Sep  9 08:43 redo02b.log
-rw-rw---- 1 oracle oracle 52429312 Sep  9 08:43 redo02.log
-rw-rw---- 1 oracle oracle 52429312 Sep  9 08:49 redo03b.log
-rw-rw---- 1 oracle oracle 52429312 Sep  9 08:49 redo03.log

Killing the instance

[oracle@localhost orcl]$ ps -ef|grep smon
oracle   13562     1  0 08:28 ?        00:00:02 ora_smon_orcl
oracle   13848  3164  0 08:49 pts/5    00:00:00 grep smon
[oracle@localhost orcl]$ kill -9 13562

Let's start up the instance

SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             373295800 bytes
Database Buffers           75497472 bytes
Redo Buffers                6008832 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'/home/oracle/app/oracle/oradata/orcl/redo01b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1:
'/home/oracle/app/oracle/oradata/orcl/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

The instance alerts you it cannot open your database because of the missing files. It's time to perform an incomplete recovery. Query the v$log view to obtain the latest valid SCN of the missing group. In my case it was 14133031

SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log;

    GROUP# STATUS           ARC    THREAD#  SEQUENCE# FIRST_CHANGE#
---------- ---------------- --- ---------- ---------- -------------
         1 CURRENT          NO           1          4      14133031
         3 ACTIVE           YES          1          3      14132968
         2 ACTIVE           YES          1          2      14132860

Issue the following commands from RMAN

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Sep 9 09:16:48 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655, not open)

RMAN> restore database until scn 14133031;

Starting restore at 09-09-2012 09:16:53
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK

skipping datafile 7; already restored to file /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_09_09/o1_mf_nnndf_TAG20120909T090659_84sho4fc_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_09_09/o1_mf_nnndf_TAG20120909T090659_84sho4fc_.bkp tag=TAG20120909T090659
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:20
Finished restore at 09-09-2012 09:20:15

RMAN> recover database until scn 14133031;

Starting recover at 09-09-2012 09:27:45
using channel ORA_DISK_1
datafile 7 not processed because file is read-only

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 09-09-2012 09:27:49

RMAN> alter database open resetlogs;

database opened

As you can see I've lost table and rows "recorded" in the previous CURRENT redo log group I have "inadvertently" deleted; the first table and its rows is instead recovered...

SQL> select a, b from (select count(*) a from hr.test_active7), (select count(*) b from hr.test_active8);
select a, b from (select count(*) a from hr.test_active7), (select count(*) b from hr.test_active8)
                                                                                      *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(*) a from hr.test_active7;

         A
----------
       100

That's all.