Redo log group & member 추가/삭제

[oracle@ghost sql]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 24 20:13:18 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

#Redo log files 상태 조회
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  INACTIVE
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  CURRENT

#리두 로그 그룹 4 추가
SQL> alter database add logfile group 4
  2  '/home/oracle/oradata/testdb/redo04.log' size 10m;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  INACTIVE
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  CURRENT
         4 /home/oracle/oradata/testdb/redo04.log                     10 YES UNUSED

#리두 로그 그룹 4에 새 멤버 추가
SQL> alter database add logfile member
  2  '/home/oracle/oradata/testdb/redo04_b.log' to group 4;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  INACTIVE
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  CURRENT
         4 /home/oracle/oradata/testdb/redo04.log                     10 YES UNUSED
         4 /home/oracle/oradata/testdb/redo04_b.log                   10 YES UNUSED

#다음 리두 로그 그룹을 CURRENT 상태로 만들기 위해 수동으로 로그 스위치를 발생시킨다.
SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  INACTIVE
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                     10 NO  CURRENT
         4 /home/oracle/oradata/testdb/redo04_b.log                   10 NO  CURRENT

#리두 로그 그룹 4의 멤버 삭제시 에러 발생 예제
#현재 사용중(CURRENT 상태)이기 때문에 멤버 삭제가 불가능하다.
SQL> alter database drop logfile member
  2  '/home/oracle/oradata/testdb/redo04_b.log';
alter database drop logfile member
*
ERROR at line 1:
ORA-01609: log 4 is the current log for thread 1 - cannot drop members
ORA-00312: online log 4 thread 1: '/home/oracle/oradata/testdb/redo04.log'
ORA-00312: online log 4 thread 1: '/home/oracle/oradata/testdb/redo04_b.log'

#리두 로그 그룹 4의 그룹 및 멤버를 삭제하기 위해 리두 로그 그룹 1로 로그 스위치를 발생시킨다.
SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                     10 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04_b.log                   10 NO  ACTIVE

#리두 로그 그룹 4는 멤버가 2개이기 때문에 ACTIVE 상태에 관계 없이 1개의 멤버는 삭제가 가능하다.
SQL> alter database drop logfile member
  2  '/home/oracle/oradata/testdb/redo04_b.log';

Database altered.

#리두 로그 그룹 4 삭제시 에러 발생 예제
#리두 로그 그룹 4가 ACTIVE 상태이기 때문에 삭제가 불가능하다.
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance testdb (thread 1)
ORA-00312: online log 4 thread 1: '/home/oracle/oradata/testdb/redo04.log'

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                     10 NO  ACTIVE

#리두 로그 그룹 4의 상태를 ACTIVE에서 INACTIVE 상태로 변경한다.
#CURRENT 로그 그룹을 제외한 모든 그룹 및 멤버는 INACTIVE 상태가 된다.
SQL> alter system checkpoint;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                     10 NO  INACTIVE

#리두 로그 그룹 4는 INACTIVE 상태이기 때문에 에러가 발생하지 않고 삭제가 가능하다.
SQL> alter database drop logfile group 4;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE

SQL>