여러가지 이유로 redo log 사이즈 변경이 필요한 경우가 있는데 RAC 환경에서 redo
log 사이즈 변경하는 방법 정리하였습니다.
요약 :
1. 노드별 신규 redo log 파일 추가
2. log switch 및 checkpoint
사용하여 삭제 대상 (CURRENT -> ACTIVE -> INACTIVE) 상태로 만들어 준다
3.
drop logfile group 으로 기존 redo log 삭제
노드별 신규 redo log 파일 추가
노드별 3개 그룹, 2개 멤버 파일로 구성하여 신규 로그 추가
-- 현재 redo 상태 확인
SQL> select a.group#,
THREAD#,
SEQUENCE#,
a.member, b.bytes/1024/1024 MB, b.archived, b.status
from v$logfile a, v$log b
where a.group# = b.group#
order by 2, 3
;
GROUP# THREAD# SEQUENCE# MEMBER MB ARCHIVED STATUS
---------- ---------- ---------- -------------------------------------------------- ---------- --------- --------------------
12 1 169 +DATA/racdb/onlinelog/group_12.581.1100276127 100 YES INACTIVE
12 1 169 +DATA/racdb/onlinelog/group_12.580.1100276125 100 YES INACTIVE
13 1 170 +DATA/racdb/onlinelog/group_13.583.1100276127 100 YES ACTIVE
13 1 170 +DATA/racdb/onlinelog/group_13.582.1100276127 100 YES ACTIVE
11 1 171 +DATA/racdb/onlinelog/group_11.578.1100276125 100 NO CURRENT
11 1 171 +DATA/racdb/onlinelog/group_11.579.1100276125 100 NO CURRENT
21 2 181 +DATA/racdb/onlinelog/group_21.584.1100276171 100 YES INACTIVE
21 2 181 +DATA/racdb/onlinelog/group_21.585.1100276173 100 YES INACTIVE
22 2 182 +DATA/racdb/onlinelog/group_22.586.1100276173 100 YES INACTIVE
22 2 182 +DATA/racdb/onlinelog/group_22.587.1100276173 100 YES INACTIVE
23 2 183 +DATA/racdb/onlinelog/group_23.588.1100276175 100 NO CURRENT
23 2 183 +DATA/racdb/onlinelog/group_23.589.1100276175 100 NO CURRENT
12 rows selected.
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DATA','+DATA') SIZE 50M
, GROUP 2 ('+DATA','+DATA') SIZE 50M
, GROUP 3 ('+DATA','+DATA') SIZE 50M
;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 ('+DATA','+DATA') SIZE 50M
, GROUP 5 ('+DATA','+DATA') SIZE 50M
, GROUP 6 ('+DATA','+DATA') SIZE 50M
;
Database altered.
기존 redo log 삭제 1차
current log 삭제 시 에러 발생
-- redo log 확인
SQL> select a.group#,
THREAD#,
SEQUENCE#,
a.member, b.bytes/1024/1024 MB, b.archived, b.status
from v$logfile a, v$log b
where a.group# = b.group#
order by 2, 3
;
GROUP# THREAD# SEQUENCE# MEMBER MB ARCHIVED STATUS
---------- ---------- ---------- -------------------------------------------------- ---------- --------- --------------------
3 1 0 +DATA/racdb/onlinelog/group_3.598.1100277225 50 YES UNUSED
1 1 0 +DATA/racdb/onlinelog/group_1.257.1100277223 50 YES UNUSED
2 1 0 +DATA/racdb/onlinelog/group_2.264.1100277223 50 YES UNUSED
2 1 0 +DATA/racdb/onlinelog/group_2.261.1100277223 50 YES UNUSED
1 1 0 +DATA/racdb/onlinelog/group_1.256.1100277223 50 YES UNUSED
3 1 0 +DATA/racdb/onlinelog/group_3.597.1100277225 50 YES UNUSED
12 1 169 +DATA/racdb/onlinelog/group_12.580.1100276125 100 YES INACTIVE
12 1 169 +DATA/racdb/onlinelog/group_12.581.1100276127 100 YES INACTIVE
13 1 170 +DATA/racdb/onlinelog/group_13.582.1100276127 100 YES INACTIVE
13 1 170 +DATA/racdb/onlinelog/group_13.583.1100276127 100 YES INACTIVE
11 1 171 +DATA/racdb/onlinelog/group_11.578.1100276125 100 NO CURRENT
11 1 171 +DATA/racdb/onlinelog/group_11.579.1100276125 100 NO CURRENT
6 2 0 +DATA/racdb/onlinelog/group_6.603.1100277243 50 YES UNUSED
6 2 0 +DATA/racdb/onlinelog/group_6.604.1100277243 50 YES UNUSED
5 2 0 +DATA/racdb/onlinelog/group_5.602.1100277243 50 YES UNUSED
5 2 0 +DATA/racdb/onlinelog/group_5.601.1100277241 50 YES UNUSED
4 2 0 +DATA/racdb/onlinelog/group_4.600.1100277241 50 YES UNUSED
4 2 0 +DATA/racdb/onlinelog/group_4.599.1100277241 50 YES UNUSED
21 2 181 +DATA/racdb/onlinelog/group_21.584.1100276171 100 YES INACTIVE
21 2 181 +DATA/racdb/onlinelog/group_21.585.1100276173 100 YES INACTIVE
22 2 182 +DATA/racdb/onlinelog/group_22.587.1100276173 100 YES INACTIVE
22 2 182 +DATA/racdb/onlinelog/group_22.586.1100276173 100 YES INACTIVE
23 2 183 +DATA/racdb/onlinelog/group_23.589.1100276175 100 NO CURRENT
23 2 183 +DATA/racdb/onlinelog/group_23.588.1100276175 100 NO CURRENT
24 rows selected.
-- current log 삭제 시 에러 발생
SQL> alter database drop logfile group 11 ;
alter database drop logfile group 11
*
ERROR at line 1:
ORA-01623: log 11 is current log for instance racdb1 (thread 1) - cannot drop
ORA-00312: online log 11 thread 1: '+DATA/racdb/onlinelog/group_11.578.1100276125'
ORA-00312: online log 11 thread 1: '+DATA/racdb/onlinelog/group_11.579.1100276125'
-- 나머지는 삭제 성공
SQL> alter database drop logfile group 12 ;
Database altered.
SQL> alter database drop logfile group 13 ;
Database altered.
SQL> alter database drop logfile group 21 ;
Database altered.
SQL> alter database drop logfile group 22 ;
Database altered.
기존 redo log 삭제 2차
-- 모든 노드 log switch 실행
SQL> alter system archive log current;
System altered.
SQL> select a.group#,
THREAD#,
SEQUENCE#,
a.member, b.bytes/1024/1024 MB, b.archived, b.status
from v$logfile a, v$log b
where a.group# = b.group#
order by 2, 3
;
GROUP# THREAD# SEQUENCE# MEMBER MB ARCHIVED STATUS
---------- ---------- ---------- -------------------------------------------------- ---------- --------- --------------------
2 1 0 +DATA/racdb/onlinelog/group_2.264.1100277223 50 YES UNUSED
2 1 0 +DATA/racdb/onlinelog/group_2.261.1100277223 50 YES UNUSED
3 1 0 +DATA/racdb/onlinelog/group_3.598.1100277225 50 YES UNUSED
3 1 0 +DATA/racdb/onlinelog/group_3.597.1100277225 50 YES UNUSED
11 1 171 +DATA/racdb/onlinelog/group_11.578.1100276125 100 YES ACTIVE
11 1 171 +DATA/racdb/onlinelog/group_11.579.1100276125 100 YES ACTIVE
1 1 172 +DATA/racdb/onlinelog/group_1.256.1100277223 50 NO CURRENT
1 1 172 +DATA/racdb/onlinelog/group_1.257.1100277223 50 NO CURRENT
6 2 0 +DATA/racdb/onlinelog/group_6.603.1100277243 50 YES UNUSED
6 2 0 +DATA/racdb/onlinelog/group_6.604.1100277243 50 YES UNUSED
5 2 0 +DATA/racdb/onlinelog/group_5.602.1100277243 50 YES UNUSED
5 2 0 +DATA/racdb/onlinelog/group_5.601.1100277241 50 YES UNUSED
23 2 183 +DATA/racdb/onlinelog/group_23.588.1100276175 100 YES ACTIVE
23 2 183 +DATA/racdb/onlinelog/group_23.589.1100276175 100 YES ACTIVE
4 2 184 +DATA/racdb/onlinelog/group_4.600.1100277241 50 NO CURRENT
4 2 184 +DATA/racdb/onlinelog/group_4.599.1100277241 50 NO CURRENT
16 rows selected.
-- checkpoint 실행으로 ACTIVE -> INACTIVE 상태로 변경
SQL> alter system checkpoint;
System altered.
SQL> select a.group#,
THREAD#,
SEQUENCE#,
a.member, b.bytes/1024/1024 MB, b.archived, b.status
from v$logfile a, v$log b
where a.group# = b.group#
order by 2, 3
;
GROUP# THREAD# SEQUENCE# MEMBER MB ARCHIVED STATUS
---------- ---------- ---------- -------------------------------------------------- ---------- --------- --------------------
2 1 0 +DATA/racdb/onlinelog/group_2.264.1100277223 50 YES UNUSED
2 1 0 +DATA/racdb/onlinelog/group_2.261.1100277223 50 YES UNUSED
3 1 0 +DATA/racdb/onlinelog/group_3.598.1100277225 50 YES UNUSED
3 1 0 +DATA/racdb/onlinelog/group_3.597.1100277225 50 YES UNUSED
11 1 171 +DATA/racdb/onlinelog/group_11.578.1100276125 100 YES INACTIVE
11 1 171 +DATA/racdb/onlinelog/group_11.579.1100276125 100 YES INACTIVE
1 1 172 +DATA/racdb/onlinelog/group_1.256.1100277223 50 NO CURRENT
1 1 172 +DATA/racdb/onlinelog/group_1.257.1100277223 50 NO CURRENT
6 2 0 +DATA/racdb/onlinelog/group_6.603.1100277243 50 YES UNUSED
6 2 0 +DATA/racdb/onlinelog/group_6.604.1100277243 50 YES UNUSED
5 2 0 +DATA/racdb/onlinelog/group_5.602.1100277243 50 YES UNUSED
5 2 0 +DATA/racdb/onlinelog/group_5.601.1100277241 50 YES UNUSED
23 2 183 +DATA/racdb/onlinelog/group_23.588.1100276175 100 YES INACTIVE
23 2 183 +DATA/racdb/onlinelog/group_23.589.1100276175 100 YES INACTIVE
4 2 184 +DATA/racdb/onlinelog/group_4.600.1100277241 50 NO CURRENT
4 2 184 +DATA/racdb/onlinelog/group_4.599.1100277241 50 NO CURRENT
16 rows selected.
SQL> alter database drop logfile group 11 ;
Database altered.
SQL> alter database drop logfile group 23 ;
Database altered.
-- UNUSED 상태는 아직 한번도 사용하지 않았기 때문
SQL> select a.group#,
THREAD#,
SEQUENCE#,
a.member, b.bytes/1024/1024 MB, b.archived, b.status
from v$logfile a, v$log b
where a.group# = b.group#
order by 2, 3
;
GROUP# THREAD# SEQUENCE# MEMBER MB ARCHIVED STATUS
---------- ---------- ---------- -------------------------------------------------- ---------- --------- --------------------
3 1 0 +DATA/racdb/onlinelog/group_3.598.1100277225 50 YES UNUSED
3 1 0 +DATA/racdb/onlinelog/group_3.597.1100277225 50 YES UNUSED
2 1 0 +DATA/racdb/onlinelog/group_2.261.1100277223 50 YES UNUSED
2 1 0 +DATA/racdb/onlinelog/group_2.264.1100277223 50 YES UNUSED
1 1 172 +DATA/racdb/onlinelog/group_1.257.1100277223 50 NO CURRENT
1 1 172 +DATA/racdb/onlinelog/group_1.256.1100277223 50 NO CURRENT
6 2 0 +DATA/racdb/onlinelog/group_6.603.1100277243 50 YES UNUSED
6 2 0 +DATA/racdb/onlinelog/group_6.604.1100277243 50 YES UNUSED
5 2 0 +DATA/racdb/onlinelog/group_5.602.1100277243 50 YES UNUSED
5 2 0 +DATA/racdb/onlinelog/group_5.601.1100277241 50 YES UNUSED
4 2 184 +DATA/racdb/onlinelog/group_4.599.1100277241 50 NO CURRENT
4 2 184 +DATA/racdb/onlinelog/group_4.600.1100277241 50 NO CURRENT
12 rows selected.
-- 모든 노드 log switch 수행
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
SQL> select a.group#,
THREAD#,
SEQUENCE#,
a.member, b.bytes/1024/1024 MB, b.archived, b.status
from v$logfile a, v$log b
where a.group# = b.group#
order by 2, 3
;
GROUP# THREAD# SEQUENCE# MEMBER MB ARCHIVED STATUS
---------- ---------- ---------- -------------------------------------------------- ---------- --------- --------------------
1 1 172 +DATA/racdb/onlinelog/group_1.256.1100277223 50 YES ACTIVE
1 1 172 +DATA/racdb/onlinelog/group_1.257.1100277223 50 YES ACTIVE
2 1 173 +DATA/racdb/onlinelog/group_2.264.1100277223 50 YES ACTIVE
2 1 173 +DATA/racdb/onlinelog/group_2.261.1100277223 50 YES ACTIVE
3 1 174 +DATA/racdb/onlinelog/group_3.597.1100277225 50 NO CURRENT
3 1 174 +DATA/racdb/onlinelog/group_3.598.1100277225 50 NO CURRENT
4 2 184 +DATA/racdb/onlinelog/group_4.599.1100277241 50 YES INACTIVE
4 2 184 +DATA/racdb/onlinelog/group_4.600.1100277241 50 YES INACTIVE
5 2 185 +DATA/racdb/onlinelog/group_5.601.1100277241 50 YES INACTIVE
5 2 185 +DATA/racdb/onlinelog/group_5.602.1100277243 50 YES INACTIVE
6 2 186 +DATA/racdb/onlinelog/group_6.603.1100277243 50 NO CURRENT
6 2 186 +DATA/racdb/onlinelog/group_6.604.1100277243 50 NO CURRENT
12 rows selected.
SQL>
댓글
댓글 쓰기