Friday, May 11, 2012

Losing One Member of an Online Redo Log Group

Looking through a Sybex book, I read that a database continues to function if you lose 1 of the members of an online redo log group. I decided to check this out in an Oracle 9 test database. It had 2 redo log groups with 2 members in each:

SQL> l
  1  select group#, member from v$logfile
  2* order by 1,2
SQL> /
 
GROUP# MEMBER
------ -----------------------------------
     1 /cisdev/mvltst/mvl_redo1/log1a.rdo
     1 /cisdev/mvltst/mvl_redo2/log1b.rdo
     2 /cisdev/mvltst/mvl_redo1/log2a.rdo
     2 /cisdev/mvltst/mvl_redo2/log2b.rdo
 
SQL>
 
I deleted 1 of the members in group 1 at the OS level:
 
MVLTST /cisdev/mvltst/mvl_redo1 >ls
log1a.rdo  log2a.rdo
MVLTST /cisdev/mvltst/mvl_redo1 >rm log1a.rdo
MVLTST /cisdev/mvltst/mvl_redo1 >ls
log2a.rdo
MVLTST /cisdev/mvltst/mvl_redo1 >
 
... and switched redo log groups a few times:
 
MVLTST /cisdev/mvltst/mvl_redo1 >sqlplus /
 
SQL*Plus: Release 9.2.0.5.0 - Production on Fri May 11 14:43:27 2012
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
 
SQL> alter system switch logfile
  2  /
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
SQL>
 
The following messages appeared in the alert log (the Sybex book said this would happen):
 
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/cisdev/mvltst/mvl_redo1/log1a.rdo'
ORA-27037: unable to obtain file status
 
... and
 
ORA-00321: log 1 of thread 1, cannot update log file header
 
The status of the online redo log members in the database was as follows:
 
SQL> l
  1  select group#, member, status
  2  from v$logfile
  3* order by 1,2
SQL> /
 
GROUP# MEMBER                             STATUS
------ ---------------------------------- -------
     1 /cisdev/mvltst/mvl_redo1/log1a.rdo INVALID
     1 /cisdev/mvltst/mvl_redo2/log1b.rdo
     2 /cisdev/mvltst/mvl_redo1/log2a.rdo
     2 /cisdev/mvltst/mvl_redo2/log2b.rdo
 
SQL>
 
I dropped the missing member from the database like this:
 
SQL> alter database drop logfile member
  2  '/cisdev/mvltst/mvl_redo1/log1a.rdo'
  3  /
alter database drop logfile member
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1: '/cisdev/mvltst/mvl_redo1/log1a.rdo'
ORA-00312: online log 1 thread 1: '/cisdev/mvltst/mvl_redo2/log1b.rdo'
 
SQL> alter system switch logfile
  2  /
 
System altered.
 
SQL> alter database drop logfile member
  2  '/cisdev/mvltst/mvl_redo1/log1a.rdo'
  3  /
 
Database altered.
 
SQL>
 
Then recreated it:
 
SQL> alter database add logfile member
  2  '/cisdev/mvltst/mvl_redo1/log1a.rdo' to group 1
  3  /
 
Database altered.
 
SQL>
 
After this, redo log group switches no longer created messages in the alert log.

No comments: