본문 바로가기
DBMS/ORACLE

redo log 사이즈 변경 RAC ASM

by 드바 2022. 3. 25.

버전 : ORACLE 11.2.0.1

 

요약 :
  1. 신규로그 노드별 logfile group 추가
  2. log switch 및 checkpoint 사용하여 삭제 대상 (CURRENT -> ACTIVE -> INACTIVE) 상태로 만들어 준다
  3. drop logfile group 으로 삭제

 

노드별 3개 그룹 2개 멤버 파일로 구성하여 신규 로그 추가

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 
;  2    3    4    5    6    7    8  

    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
;
  2    3    4  

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
;  2    3    4  

Database altered.

 

기존 redo log 삭제(current 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 
;  2    3    4    5    6    7    8  

    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 상태여서 에러 발생
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.

 

log switch 및 checkpoint 사용하여 INACTIVE 상태로 변경 후 삭제 진행

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 
;  2    3    4    5    6    7    8  

    GROUP#    THREAD#  SEQUENCE# MEMBER      MB ARCHIVED  STATUS
---------- ---------- ---------- -------------------------------------------------- ---------- --------- --------------------
 1     1        0 +DATA/racdb/onlinelog/group_1.256.1100277223     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
 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 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
 4     2        0 +DATA/racdb/onlinelog/group_4.599.1100277241     50 YES  UNUSED
 4     2        0 +DATA/racdb/onlinelog/group_4.600.1100277241     50 YES  UNUSED
 5     2        0 +DATA/racdb/onlinelog/group_5.601.1100277241     50 YES  UNUSED
 5     2        0 +DATA/racdb/onlinelog/group_5.602.1100277243     50 YES  UNUSED
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

16 rows selected.

-- 모든 노드 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 
;  2    3    4    5    6    7    8  

    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 
;  2    3    4    5    6    7    8  

    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.

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 
;  2    3    4    5    6    7    8  

    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.

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 
;  2    3    4    5    6    7    8  

    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>

 

'DBMS > ORACLE' 카테고리의 다른 글

ASM DISK 사이즈 다른 경우 DATAFILE 추가 테스트  (0) 2022.05.10
ARCHIVE(아카이브) HANG 조치, ORA-16020  (0) 2022.03.28
srvctl stop/start database 명령은 어떤 노드부터 동작할까?  (0) 2022.03.25
db_files 파라미터 변경 RAC  (0) 2022.03.23
권한 부여 시 ROLE, PRIVILEGE 차이  (0) 2022.03.14

관련글

댓글

티스토리툴바