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 NOHave 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.logThe 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 NOAnd 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 NOWill 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 openedThe 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 smonThen 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 12048The 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 NOQuerying 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 NOI'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 openThe 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 openedThe 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
4 comments:
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
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
nike vapormax
balenciaga
supreme clothing
westbrook shoes
coach handbags
yeezy shoes
jordan retro
nike vapormax
cheap jordans
nike lebron 16
yeezy
adidas yeezy
pandora jewelry
curry shoes
longchamp handbags
canada goose outlet
lebron shoes
goyard
yeezy shoes
pandora bracelet
Post a Comment