Ghost's Oracle Database !

리두 로그 그룹을 아래와 같이 구성해본다.
　　　　　group1　　　group2　　　group3
Disk1　　member1　　member2　　member3
--------------------------------------------
Disk2　　member1　　member2　　member3


#현재 리두 로그 그룹 및 멤버와 상태를 확인한다.
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/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE

#위와 같이 리두 로그 그룹을 구성하기 위한 임시 리두 로그 그룹 4, 5를 추가한다.
SQL> alter database add logfile group 4
  2  '/home/oracle/oradata/testdb/log/redo04.log' size 5m;

Database altered.

SQL> alter database add logfile group 5
  2  '/home/oracle/oradata/testdb/log/redo05.log' size 5m;

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/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 YES UNUSED
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 YES UNUSED

#리두 로그 그룹 1, 2, 3을 삭제하기 위해 CURRENT를 리두 로그 그룹 4, 5 둘 중 하나로 이동시킨다.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

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/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

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/log/redo01.log                  5 NO  ACTIVE
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  CURRENT
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

#리두 로그 그룹 1, 2, 3 삭제를 위해 강제로 체크포인트를 발생시켜 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/log/redo01.log                  5 NO  INACTIVE
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  CURRENT
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

#리두 로그 그룹 1, 2, 3을 재구성하기 위해 삭제한다.
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

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
---------- -------------------------------------------------- ---------- --- ----------------
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  CURRENT
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

#재구성할 디렉토리를 생성해준다. (실무에서는 새로 추가한 디스크의 경로를 입력하면 된다.)
SQL> !mkdir -p $ORACLE_BASE/disk1/

SQL> !mkdir -p $ORACLE_BASE/disk2/

#리두 로그 그룹 1의 멤버 2개를 각각의 디스크에 할당하고 생성한다.
SQL> alter database add logfile group 1
  2  ( '$ORACLE_BASE/disk1/redo01a.rdo',
  3    '$ORACLE_BASE/disk2/redo01b.rdo')
  4  size 5m;

Database altered.

#리두 로그 그룹 2의 멤버 2개를 각각의 디스크에 할당하고 생성한다.
SQL> alter database add logfile group 2
  2  ( '$ORACLE_BASE/disk1/redo02a.rdo',
  3    '$ORACLE_BASE/disk2/redo02b.rdo')
  4  size 5m;

Database altered.

#리두 로그 그룹 3의 멤버 2개를 각각의 디스크에 할당하고 생성한다.
SQL> alter database add logfile group 3
  2  ( '$ORACLE_BASE/disk1/redo03a.rdo',
  3    '$ORACLE_BASE/disk2/redo03b.rdo')
  4  size 5m;

Database altered.

#리두 로그 그룹 1, 2, 3은 각각 멤버 2개로 구성되어 있으며, 각 멤버는 안전성을 위해 disk1과 disk2로 나뉘어 사용된다.
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/disk1/redo01a.rdo                              5 YES UNUSED
         1 /home/oracle/disk2/redo01b.rdo                              5 YES UNUSED
         2 /home/oracle/disk1/redo02a.rdo                              5 YES UNUSED
         2 /home/oracle/disk2/redo02b.rdo                              5 YES UNUSED
         3 /home/oracle/disk1/redo03a.rdo                              5 YES UNUSED
         3 /home/oracle/disk2/redo03b.rdo                              5 YES UNUSED
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  CURRENT
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

8 rows selected.

#임시 리두 로그 그룹 4, 5를 삭제하기 위해 강제로 로그 스위치를 발생시킨다.
SQL> alter system switch logfile;

System altered.

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/disk1/redo01a.rdo                              5 NO  CURRENT
         1 /home/oracle/disk2/redo01b.rdo                              5 NO  CURRENT
         2 /home/oracle/disk1/redo02a.rdo                              5 YES UNUSED
         2 /home/oracle/disk2/redo02b.rdo                              5 YES UNUSED
         3 /home/oracle/disk1/redo03a.rdo                              5 YES UNUSED
         3 /home/oracle/disk2/redo03b.rdo                              5 YES UNUSED
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

8 rows selected.

#임시로 생성한 리두 로그 그룹 4, 5를 삭제한다.
SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

#처음 의도한 바와 같이 3개의 그룹에 각각 2개의 멤버가 존재하며 각각의 멤버는 서로 다른 디스크에 할당된 것을 확인할 수 있다.
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/disk1/redo01a.rdo                              5 NO  CURRENT
         1 /home/oracle/disk2/redo01b.rdo                              5 NO  CURRENT
         2 /home/oracle/disk1/redo02a.rdo                              5 YES UNUSED
         2 /home/oracle/disk2/redo02b.rdo                              5 YES UNUSED
         3 /home/oracle/disk1/redo03a.rdo                              5 YES UNUSED
         3 /home/oracle/disk2/redo03b.rdo                              5 YES UNUSED

6 rows selected.

SQL>

리두 로그 그룹 추가/삭제를 통해 디렉토리 및 사이즈를 변경해본다.

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, 5를 추가한다.
#리두 로그 그룹 1, 2, 3을 삭제하기 위해 리두 로그 그룹은 최소 2개가 존재해야 한다.
SQL> alter database add logfile group 4
  2  '/home/oracle/oradata/testdb/redo04.log' size 5m;

Database altered.

SQL> alter database add logfile group 5
  2  '/home/oracle/oradata/testdb/redo05.log' size 5m;

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
         4 /home/oracle/oradata/testdb/redo04.log                      5 YES UNUSED
         5 /home/oracle/oradata/testdb/redo05.log                      5 YES UNUSED

#리두 로그 그룹 4, 5를 추가한 후 CURRENT를 새로 추가한 리두 로그 그룹으로 변경한다.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

#리두 로그 그룹 1, 2, 3을 삭제하기 위해 수동으로 checkpoint를 발생시킨다.
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  INACTIVE
         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                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  CURRENT

#리두 로그 그룹 5를 CURRENT 상태로 만든 후 리두 로그 그룹 1, 2, 3을 삭제한다.
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

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
---------- -------------------------------------------------- ---------- --- ----------------
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  CURRENT

#새로운 리두 로그 그룹 1, 2, 3을 추가하기 위한 새로운 디렉토리를 생성한다.
#디렉토리를 생성하지 않고 추가할 경우 디렉토리 문제로 에러가 발생한다.
SQL> !mkdir -p /home/oracle/oradata/testdb/log

#새로운 디렉토리에 리두 로그 그룹 1, 2, 3을 추가한다.
SQL> alter database add logfile group 1
  2  '/home/oracle/oradata/testdb/log/redo01.log' size 5m;

Database altered.

SQL> alter database add logfile group 2
  2  '/home/oracle/oradata/testdb/log/redo02.log' size 5m;

Database altered.

SQL> alter database add logfile group 3
  2  '/home/oracle/oradata/testdb/log/redo03.log' size 5m;

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/log/redo01.log                  5 YES UNUSED
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 YES UNUSED
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 YES UNUSED
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  CURRENT

#기존 리두 로그 그룹 4, 5를 삭제하기 위해 강제로 로그 스위치를 발생시킨다.
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/log/redo01.log                  5 NO  ACTIVE
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  CURRENT
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  ACTIVE

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/log/redo01.log                  5 NO  ACTIVE
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  ACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  CURRENT

#강제 로그 스위치 발생 경과로 보아 UNUSED에서 바로 CURRENT가 되지 않는다.
#CURRENT의 순서는 4 -> 5 -> 4 -> 1 순이며 세번째 4가 CURRENT일때 1, 2, 3의 상태는 ACTIVE가 된다.
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/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  ACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  ACTIVE

#기존 리두 로그 그룹 4, 5를 삭제하기 위해 강제로 checkpoint를 발생시킨다.
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/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  INACTIVE

#기존 리두 로그 그룹 4, 5를 삭제한다.
SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

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/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE

#SQL에서 리두 로그 그룹을 DROP 하더라도 실제 OS 파일은 삭제되지 않는다.
#따라서 확인한 후 해당 리두 로그 파일을 삭제한다.
SQL> !ls /home/oracle/oradata/testdb/
control01.ctl  example01.dbf  redo02.log  redo05.log    temp01.dbf
control02.ctl  log            redo03.log  sysaux01.dbf  undotbs01.dbf
control03.ctl  redo01.log     redo04.log  system01.dbf  users01.dbf

SQL> !rm -rf /home/oracle/oradata/testdb/redo*.log

SQL> !ls /home/oracle/oradata/testdb/
control01.ctl  control03.ctl  log           system01.dbf  undotbs01.dbf
control02.ctl  example01.dbf  sysaux01.dbf  temp01.dbf    users01.dbf

SQL>

[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>

Redo log group은 최소 2개, member 는 그룹당 최소 1개이다.

■ 수동 로그 스위치
SQL> alter system switch logfile;

■ 수동 checkpoint
SQL> alter system checkpoint;

■ 리두 로그 그룹 추가
SQL> alter database add logfile group 그룹번호
  2  '경로/파일명.log' size 크기;

■ 리두 로그 그룹별 멤버 추가
SQL> alter database add logfile member
  2  '경로/파일명.log' to group 그룹번호;

■ 리두 로그 그룹 삭제
SQL> alter database drop logfile group 그룹번호;

■ 리두 로그 그룹의 멤버 삭제
SQL> alter database drop logfile member
  2  '경로/파일명_b.log';

■ log file 상태 조회 (그룹번호, 멤버, 용량, 상태)
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>