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>