리두 로그 그룹 추가/삭제를 통해 디렉토리 및 사이즈를 변경해본다.
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>