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.