Monday, September 10, 2012

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:

oakleyses said...

jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet

oakleyses said...

sac vanessa bruno, new balance, vans pas cher, ray ban uk, nike blazer pas cher, true religion outlet, michael kors outlet, true religion outlet, replica handbags, polo lacoste, oakley pas cher, coach purses, hollister uk, abercrombie and fitch uk, nike free uk, north face uk, louboutin pas cher, polo ralph lauren, hollister pas cher, nike air max uk, michael kors pas cher, nike air max, true religion jeans, timberland pas cher, nike air max uk, coach outlet, air max, michael kors, jordan pas cher, sac hermes, north face, lululemon canada, coach outlet store online, nike roshe, sac longchamp pas cher, nike air force, mulberry uk, hogan outlet, ralph lauren uk, longchamp pas cher, michael kors, converse pas cher, burberry pas cher, nike roshe run uk, true religion outlet, kate spade, nike free run, nike tn, ray ban pas cher, guess pas cher

oakleyses said...

asics running shoes, babyliss, soccer jerseys, hermes belt, reebok outlet, ipad cases, oakley, iphone cases, soccer shoes, iphone 5s cases, nfl jerseys, north face outlet, abercrombie and fitch, ghd hair, vans outlet, iphone 6 cases, hollister, nike roshe run, wedding dresses, mac cosmetics, lululemon, new balance shoes, jimmy choo outlet, instyler, giuseppe zanotti outlet, p90x workout, s6 case, chi flat iron, iphone 6s cases, longchamp uk, baseball bats, mcm handbags, iphone 6 plus cases, bottega veneta, ferragamo shoes, timberland boots, mont blanc pens, insanity workout, nike air max, nike trainers uk, herve leger, nike huaraches, celine handbags, north face outlet, beats by dre, iphone 6s plus cases, valentino shoes, ralph lauren, hollister clothing, louboutin

oakleyses said...

moncler uk, louis vuitton, thomas sabo, wedding dresses, barbour, moncler, gucci, montre pas cher, supra shoes, hollister, barbour uk, nike air max, karen millen uk, pandora uk, moncler, canada goose uk, coach outlet, ugg, juicy couture outlet, swarovski, canada goose, louis vuitton, moncler outlet, louis vuitton, hollister, ugg,ugg australia,ugg italia, canada goose outlet, replica watches, pandora jewelry, ugg,uggs,uggs canada, moncler, canada goose outlet, ugg pas cher, louis vuitton, juicy couture outlet, swarovski crystal, louis vuitton, canada goose, pandora charms, canada goose outlet, links of london, marc jacobs, lancel, converse, converse outlet, toms shoes, doudoune moncler, moncler, pandora jewelry, canada goose jackets, vans, canada goose