The most crucial and vital structure for recovery operations is the online redo log, which consists of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of an Oracle database has an associated online redo log to protect the database in case of an instance failure.
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 to drop the CURRENT redo log group will force me to perform an incomplete recovery, being able to get back rows of the 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 startup 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
Thank you for giving your valuable time to read the above information.
For More Detail , You can join us follow:
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
