Pages

▼

Monday, September 10, 2012

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.
190 comments:

How to recover from a loss of all redo log members of an ACTIVE group

According to the Oracle documentation there are more steps to complete when you lose all redo log members of an ACTIVE group and exists the possibility to have lost some transactions.
If you are able to force the database to perform a checkpoint then you can clear the missing redo log group, otherwise you need to execute an incomplete recovery.
I was able to recreate both the scenarios using some scripts and verify when you lose transactions.
Dealing with the loss of ACTIVE and, in the next post, CURRENT redo log group could mean discovering to have lost some transactions.

To simulate the loss of ACTIVE redo log group I use a script and, with a query, I try to identify the redo log members to be deleted manually.

The first scenario, while the instance was still up and running, I was able to execute a checkpoint and perform a complete recovery;
to simulate the second scenario, that is an incomplete recovery, I needed to kill the instance so no checkpoint could be completed.

Let's see the examples.
Take a look at the script first. It executes for three times a query on v$log and v$logfile to show the status of the redo log group, insert 100 rows in three different tables, commit and force a logfile switch.
After the last insert it executes again the previous query. 
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_active4 (a number)';
for i in 1 .. 100
loop
execute immediate 'insert into hr.test_active4 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_active5 (a number)';
for i in 1 .. 100
loop
execute immediate 'insert into hr.test_active5 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_active6 (a number)';
for i in 1 .. 100
loop
execute immediate 'insert into hr.test_active6 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;
Then I executed the script and this was the output: 
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 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 INACTIVE                 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 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 INACTIVE                 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   11  
PL/SQL procedure successfully completed.

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

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 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo02.log                       2 ACTIVE                   NO
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 ACTIVE                   NO
/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.
At this moment redo log group number 2 was ACTIVE and still NOT ARCHIVED... but few second later it was simply ACTIVE and ARCHIVED: 
SQL> 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;

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
Have a look at the contents of the three table before simulating the lost: 
SQL> select a, b, c from (select count(*) a from hr.test_active1), (select count(*) b from hr.test_active2), (select count(*) c from hr.test_active3);

         A          B          C
---------- ---------- ----------
       100        100        100
It was time to delete all redo log members of group 2: 
[oracle@localhost orcl]$ mv redo02.log redo02.log.bck
[oracle@localhost orcl]$ mv redo02b.log redo02b.log.bck
[oracle@localhost orcl]$ ll redo*
-rw-rw---- 1 oracle oracle 52429312 Sep  7 11:42 redo01b.log
-rw-rw---- 1 oracle oracle 52429312 Sep  7 11:42 redo01.log
-rw-rw---- 1 oracle oracle 52429312 Sep  7 11:42 redo02b.log.bck
-rw-rw---- 1 oracle oracle 52429312 Sep  7 11:42 redo02.log.bck
-rw-rw---- 1 oracle oracle 52429312 Sep  7 11:53 redo03b.log
-rw-rw---- 1 oracle oracle 52429312 Sep  7 11:53 redo03.log
The instance was still up and running and I was able to complete a checkpoint as Oracle documentation suggests. Because the operation was successful, the active redo log became inactive. 
SQL> alter system checkpoint;

System altered.

SQL> 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;

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 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo03.log                       3 CURRENT                  NO
/home/oracle/app/oracle/oradata/orcl/redo03b.log                      3 CURRENT                  NO
And now it's time to clear the redo logfile group 2 
SQL> alter database clear logfile group 2;

Database altered.
The query shows those members are just created and never used. 
SQL> 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;

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 UNUSED                   YES
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 UNUSED                   YES
/home/oracle/app/oracle/oradata/orcl/redo03.log                       3 CURRENT                  NO
/home/oracle/app/oracle/oradata/orcl/redo03b.log                      3 CURRENT                  NO
Will the rows still be there if we perform a restore of our database ? Let's look with an example 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Connecting with RMAN I restore and recover the database: 
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 7 12:51:18 2012

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                394267320 bytes
Database Buffers              54525952 bytes
Redo Buffers                   6008832 bytes

RMAN> restore database;

Starting restore at 07-09-2012 12:51:57
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 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_07/o1_mf_nnndf_TAG20120907T100935_84nblj6n_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_09_07/o1_mf_nnndf_TAG20120907T100935_84nblj6n_.bkp tag=TAG20120907T100935
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:30
Finished restore at 07-09-2012 12:55:30

RMAN> recover database;

Starting recover at 07-09-2012 12:55:52
using channel ORA_DISK_1
datafile 7 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_2_84nbr9nj_.arc
archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_3_84nj18pt_.arc
archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_4_84nj19gn_.arc
archived log for thread 1 with sequence 5 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_5_84nj1b58_.arc
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_2_84nbr9nj_.arc thread=1 sequence=2
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_3_84nj18pt_.arc thread=1 sequence=3
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_5_84nj1b58_.arc thread=1 sequence=5
media recovery complete, elapsed time: 00:00:07
Finished recover at 07-09-2012 12:56:05

RMAN> alter database open;

database opened
The database is open and the rows are still there. 
SQL> select a, b, c from (select count(*) a from hr.test_active1), (select count(*) b from hr.test_active2), (select count(*) c from hr.test_active3);

         A          B          C
---------- ---------- ----------
       100        100        100
So our rows are all there. Now it's time to simulate the second scenario. First of all I need to find the process id of the smon process. 
[oracle@localhost ~]$ ps -ef|grep smon
oracle   12048     1  0 12:51 ?        00:00:01 ora_smon_orcl
oracle   12408  2803  1 13:27 pts/1    00:00:00 grep smon
Then in three different Unix terminals I prepared the following commands (each on one terminal of course). In this way with just one RETURN button I'm able to remove the ACTIVE redo log group and kill the instance, without any checkpoint and "perhaps" loosing transactions. 
[oracle@localhost orcl]$ mv redo01.log redo01.log.bck; mv redo01b.log redo01b.log.bck; kill -9 12048
[oracle@localhost orcl]$ mv redo02.log redo02.log.bck; mv redo02b.log redo02b.log.bck; kill -9 12048
[oracle@localhost orcl]$ mv redo03.log redo03.log.bck; mv redo03b.log redo03b.log.bck; kill -9 12048
The script I'm going to execute inserting rows on others three different tables: 
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_active4 (a number)';
for i in 1 .. 100
loop
execute immediate 'insert into hr.test_active4 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_active5 (a number)';
for i in 1 .. 100
loop
execute immediate 'insert into hr.test_active5 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_active6 (a number)';
for i in 1 .. 100
loop
execute immediate 'insert into hr.test_active6 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;
The output of the script just executed: 
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 UNUSED                   YES
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 UNUSED                   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 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo02.log                       2 CURRENT                  NO
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 CURRENT                  NO
/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   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                   NO
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 ACTIVE                   NO
/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   11  

PL/SQL procedure successfully completed.

SQL> 
MEMBER                                                           GROUP# STATUS           STATUS  ARC
------------------------------------------------------------ ---------- ---------------- ------- ---
/home/oracle/app/oracle/oradata/orcl/redo01.log                       1 ACTIVE                   NO
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 ACTIVE                   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 CURRENT                  NO
/home/oracle/app/oracle/oradata/orcl/redo03b.log                      3 CURRENT                  NO
Querying again v$log and v$logfile views, we see there are two groups ACTIVE and ARCHIVED. 
SQL> 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;

MEMBER                                                           GROUP# STATUS           STATUS  ARC
------------------------------------------------------------ ---------- ---------------- ------- ---
/home/oracle/app/oracle/oradata/orcl/redo01.log                       1 ACTIVE                   YES
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 ACTIVE                   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
I'm going to remove all members of redo log group number 1 and kill the instance. 
[oracle@localhost orcl]$ mv redo01.log redo01.log.bck; mv redo01b.log redo01b.log.bck; kill -9 12048;
[oracle@localhost orcl]$ ps -ef|grep smon
[oracle@localhost orcl]$
Let's start the instance in MOUNT mode and query the v$ views as we don't know what happened. I try to issue a checkpoint but it cannot complete because the database is not open. 
SQL> startup mount;
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             394267320 bytes
Database Buffers           54525952 bytes
Redo Buffers                6008832 bytes
Database mounted.
SQL> 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;
SQL> SQL> SQL> 
MEMBER                                                           GROUP# STATUS           STATUS  ARC
------------------------------------------------------------ ---------- ---------------- ------- ---
/home/oracle/app/oracle/oradata/orcl/redo01.log                       1 ACTIVE                   YES
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 ACTIVE                   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> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-01109: database not open
The Oracle documentation says we have to perform an incomplete recovery up to the latest valid SCN that is indicated into FIRST_CHANGE# column of v$log view of the missing redo log group. 
SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log;

    GROUP# STATUS           ARC    THREAD#  SEQUENCE# FIRST_CHANGE#
---------- ---------------- --- ---------- ---------- -------------
         1 ACTIVE           YES          1          8      14126367
         3 CURRENT          NO           1          9      14126384
         2 ACTIVE           YES          1          7      14126349
In my case I can restore and recover until SCN 14126367. Let's look the following steps: 
[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 7 13:48:47 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 14126367;

Starting restore at 07-09-2012 13:48:56
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 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_07/o1_mf_nnndf_TAG20120907T100935_84nblj6n_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_09_07/o1_mf_nnndf_TAG20120907T100935_84nblj6n_.bkp tag=TAG20120907T100935
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:29
Finished restore at 07-09-2012 13:52:27

RMAN> recover database until scn 14126367;

Starting recover at 07-09-2012 13:52:59
using channel ORA_DISK_1
datafile 7 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_2_84nbr9nj_.arc
archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_3_84nj18pt_.arc
archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_4_84nj19gn_.arc
archived log for thread 1 with sequence 5 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_5_84nj1b58_.arc
archived log for thread 1 with sequence 6 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_6_84nprq6y_.arc
archived log for thread 1 with sequence 7 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_7_84nprqtl_.arc
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_2_84nbr9nj_.arc thread=1 sequence=2
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_3_84nj18pt_.arc thread=1 sequence=3
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_4_84nj19gn_.arc thread=1 sequence=4
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_5_84nj1b58_.arc thread=1 sequence=5
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_6_84nprq6y_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:06
Finished recover at 07-09-2012 13:53:09

RMAN> alter database open resetlogs;

database opened
The database is open... but I'm missing a table and its rows. 
SQL> select a, b, c from (select count(*) a from hr.test_active4), (select count(*) b from hr.test_active5), (select count(*) c from hr.test_active6);
select a, b, c from (select count(*) a from hr.test_active4), (select count(*) b from hr.test_active5), (select count(*) c from hr.test_active6)
                                                                                                                                   *
ERROR at line 1:
ORA-00942: table or view does not exist
There are instead the two other tables. As you can see above following the output of the running script the missing table was created just on the redo log group number 1 
SQL> select a, b from (select count(*) a from hr.test_active4), (select count(*) b from hr.test_active5);

         A          B
---------- ----------
       100        100
To summarize:
1. Try to perform "alter system checkpoint". If successful clear the lost redo log group: "alter database clear logfile group " (you have completely recovered your database) 
2. If unsuccessful query FIRST_CHANGE# column of v$log to know the latest SCN for the missing redo log group. Then using RMAN: startup mount; restore database until scn ; recover database until scn ; alter database open (you have lost some transactions) 

That's all.
4 comments: