"Redo aws 사이즈조정"의 두 판 사이의 차이
DB CAFE
(새 문서: →Query V$LOG to see the logs.: →You start with 4 logs of 128 MB each.: select GROUP#, BYTES, STATUS from V$LOG; GROUP# BYTES STATUS ---------- ---------- -...) |
|||
(같은 사용자의 중간 판 4개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
+ | = AWS REDO 로그 사이즈 변경 = | ||
+ | * 128MB 로그 파일을 512MB로 변경하기 | ||
+ | |||
+ | == 현재 로그 확인 == | ||
/* Query V$LOG to see the logs. */ | /* Query V$LOG to see the logs. */ | ||
/* You start with 4 logs of 128 MB each. */ | /* You start with 4 logs of 128 MB each. */ | ||
− | + | <source lang=sql> | |
select GROUP#, BYTES, STATUS from V$LOG; | select GROUP#, BYTES, STATUS from V$LOG; | ||
− | + | </source> | |
+ | <source lang=bash> | ||
GROUP# BYTES STATUS | GROUP# BYTES STATUS | ||
---------- ---------- ---------------- | ---------- ---------- ---------------- | ||
10번째 줄: | 15번째 줄: | ||
3 134217728 INACTIVE | 3 134217728 INACTIVE | ||
4 134217728 INACTIVE | 4 134217728 INACTIVE | ||
+ | </source> | ||
+ | == 로그파일 추가(512MB) == | ||
/* Add four new logs that are each 512 MB */ | /* Add four new logs that are each 512 MB */ | ||
− | + | <source lang=sql> | |
+ | AWS >>>> | ||
exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912); | exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912); | ||
exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912); | exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912); | ||
exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912); | exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912); | ||
exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912); | exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912); | ||
− | + | </source> | |
+ | * 오라클 명령 | ||
+ | -- 그룹추가 | ||
+ | <source lang=sql> | ||
+ | alter database add logfile group 4 | ||
+ | '/app/oracle/oradata/testdb/redo04_a.log' size 5M ; | ||
+ | </source> | ||
+ | -- 멤버추가 | ||
+ | <source lang=sql> | ||
+ | alter database add logfile member | ||
+ | '/app/oracle/oradata/testdb/redo04_b.log' to group 4 ; | ||
+ | </source> | ||
/* Query V$LOG to see the logs. */ | /* Query V$LOG to see the logs. */ | ||
/* Now there are 8 logs. */ | /* Now there are 8 logs. */ | ||
− | + | <source lang=sql> | |
select GROUP#, BYTES, STATUS from V$LOG; | select GROUP#, BYTES, STATUS from V$LOG; | ||
+ | </source> | ||
+ | <source lang=bash> | ||
GROUP# BYTES STATUS | GROUP# BYTES STATUS | ||
---------- ---------- ---------------- | ---------- ---------- ---------------- | ||
35번째 줄: | 56번째 줄: | ||
7 536870912 UNUSED | 7 536870912 UNUSED | ||
8 536870912 UNUSED | 8 536870912 UNUSED | ||
+ | </source> | ||
− | + | == 128MB사이즈 로그 파일 삭제 == | |
/* Drop each inactive log using the group number. */ | /* Drop each inactive log using the group number. */ | ||
− | + | /* 2번은 사용중이므로 삭제 불가 */ | |
+ | <source lang=sql> | ||
+ | AWS >>>> | ||
exec rdsadmin.rdsadmin_util.drop_logfile(grp => 1); | exec rdsadmin.rdsadmin_util.drop_logfile(grp => 1); | ||
exec rdsadmin.rdsadmin_util.drop_logfile(grp => 3); | exec rdsadmin.rdsadmin_util.drop_logfile(grp => 3); | ||
exec rdsadmin.rdsadmin_util.drop_logfile(grp => 4); | exec rdsadmin.rdsadmin_util.drop_logfile(grp => 4); | ||
+ | </source> | ||
+ | --멤버 삭제 | ||
+ | <source lang=sql> | ||
+ | alter database drop logfile member | ||
+ | '/app/oracle/oradata/testdb/redo04_b.log' ; | ||
+ | </source> | ||
+ | -- 그룹삭제 | ||
+ | <source lang=sql> | ||
+ | alter database drop logfile group 4; | ||
+ | </source> | ||
/* Query V$LOG to see the logs. */ | /* Query V$LOG to see the logs. */ | ||
/* Now there are 5 logs. */ | /* Now there are 5 logs. */ | ||
− | + | <source lang=sql> | |
select GROUP#, BYTES, STATUS from V$LOG; | select GROUP#, BYTES, STATUS from V$LOG; | ||
− | + | </source> | |
+ | <source lang=bash> | ||
GROUP# BYTES STATUS | GROUP# BYTES STATUS | ||
---------- ---------- ---------------- | ---------- ---------- ---------------- | ||
56번째 줄: | 91번째 줄: | ||
7 536870912 UNUSED | 7 536870912 UNUSED | ||
8 536870912 UNUSED | 8 536870912 UNUSED | ||
− | + | </source> | |
/* Switch logs so that group 2 is no longer current. */ | /* Switch logs so that group 2 is no longer current. */ | ||
− | + | == 스위치 로그 발생 == | |
+ | <source lang=sql> | ||
+ | AWS >>>> | ||
exec rdsadmin.rdsadmin_util.switch_logfile; | exec rdsadmin.rdsadmin_util.switch_logfile; | ||
+ | </source> | ||
− | + | <source lang=sql> | |
+ | alter system switch logfile; | ||
+ | </source> | ||
/* Query V$LOG to see the logs. */ | /* Query V$LOG to see the logs. */ | ||
/* Now one of the new logs is current. */ | /* Now one of the new logs is current. */ | ||
− | + | <source lang=sql> | |
SQL>select GROUP#, BYTES, STATUS from V$LOG; | SQL>select GROUP#, BYTES, STATUS from V$LOG; | ||
− | + | </source> | |
+ | <source lang=bash> | ||
GROUP# BYTES STATUS | GROUP# BYTES STATUS | ||
---------- ---------- ---------------- | ---------- ---------- ---------------- | ||
75번째 줄: | 116번째 줄: | ||
7 536870912 UNUSED | 7 536870912 UNUSED | ||
8 536870912 UNUSED | 8 536870912 UNUSED | ||
+ | </source> | ||
− | + | == 체크포인트 발생 == | |
/* Issue a checkpoint to clear log 2. */ | /* Issue a checkpoint to clear log 2. */ | ||
− | + | <source lang=sql> | |
+ | AWS >>>> | ||
exec rdsadmin.rdsadmin_util.checkpoint; | exec rdsadmin.rdsadmin_util.checkpoint; | ||
− | + | </source> | |
− | + | <source lang=sql> | |
+ | alter system checkpoint; | ||
+ | </source> | ||
/* Query V$LOG to see the logs. */ | /* Query V$LOG to see the logs. */ | ||
/* Now the final original log is inactive. */ | /* Now the final original log is inactive. */ | ||
− | + | <source lang=sql> | |
select GROUP#, BYTES, STATUS from V$LOG; | select GROUP#, BYTES, STATUS from V$LOG; | ||
− | + | </source> | |
+ | <source lang=bash> | ||
GROUP# BYTES STATUS | GROUP# BYTES STATUS | ||
---------- ---------- ---------------- | ---------- ---------- ---------------- | ||
94번째 줄: | 140번째 줄: | ||
7 536870912 UNUSED | 7 536870912 UNUSED | ||
8 536870912 UNUSED | 8 536870912 UNUSED | ||
− | + | </source> | |
# Drop the final inactive log. | # Drop the final inactive log. | ||
− | + | <source lang=sql> | |
+ | AWS >>>> | ||
exec rdsadmin.rdsadmin_util.drop_logfile(grp => 2); | exec rdsadmin.rdsadmin_util.drop_logfile(grp => 2); | ||
− | + | </source> | |
/* Query V$LOG to see the logs. */ | /* Query V$LOG to see the logs. */ | ||
/* Now there are four 512 MB logs. */ | /* Now there are four 512 MB logs. */ | ||
− | + | <source lang=sql> | |
select GROUP#, BYTES, STATUS from V$LOG; | select GROUP#, BYTES, STATUS from V$LOG; | ||
+ | </source> | ||
+ | <source lang=bash> | ||
GROUP# BYTES STATUS | GROUP# BYTES STATUS | ||
---------- ---------- ---------------- | ---------- ---------- ---------------- | ||
112번째 줄: | 161번째 줄: | ||
7 536870912 UNUSED | 7 536870912 UNUSED | ||
8 536870912 UNUSED | 8 536870912 UNUSED | ||
+ | </source> | ||
+ | [[Category:oracle]] |
2019년 12월 19일 (목) 12:25 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 AWS REDO 로그 사이즈 변경[편집]
- 128MB 로그 파일을 512MB로 변경하기
1.1 현재 로그 확인[편집]
/* Query V$LOG to see the logs. */ /* You start with 4 logs of 128 MB each. */
select GROUP#, BYTES, STATUS from V$LOG;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 134217728 INACTIVE
2 134217728 CURRENT
3 134217728 INACTIVE
4 134217728 INACTIVE
1.2 로그파일 추가(512MB)[편집]
/* Add four new logs that are each 512 MB */
AWS >>>>
exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912);
exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912);
exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912);
exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912);
- 오라클 명령
-- 그룹추가
alter database add logfile group 4
'/app/oracle/oradata/testdb/redo04_a.log' size 5M ;
-- 멤버추가
alter database add logfile member
'/app/oracle/oradata/testdb/redo04_b.log' to group 4 ;
/* Query V$LOG to see the logs. */ /* Now there are 8 logs. */
select GROUP#, BYTES, STATUS from V$LOG;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 134217728 INACTIVE
2 134217728 CURRENT
3 134217728 INACTIVE
4 134217728 INACTIVE
5 536870912 UNUSED
6 536870912 UNUSED
7 536870912 UNUSED
8 536870912 UNUSED
1.3 128MB사이즈 로그 파일 삭제[편집]
/* Drop each inactive log using the group number. */ /* 2번은 사용중이므로 삭제 불가 */
AWS >>>>
exec rdsadmin.rdsadmin_util.drop_logfile(grp => 1);
exec rdsadmin.rdsadmin_util.drop_logfile(grp => 3);
exec rdsadmin.rdsadmin_util.drop_logfile(grp => 4);
--멤버 삭제
alter database drop logfile member
'/app/oracle/oradata/testdb/redo04_b.log' ;
-- 그룹삭제
alter database drop logfile group 4;
/* Query V$LOG to see the logs. */ /* Now there are 5 logs. */
select GROUP#, BYTES, STATUS from V$LOG;
GROUP# BYTES STATUS
---------- ---------- ----------------
2 134217728 CURRENT
5 536870912 UNUSED
6 536870912 UNUSED
7 536870912 UNUSED
8 536870912 UNUSED
/* Switch logs so that group 2 is no longer current. */
1.4 스위치 로그 발생[편집]
AWS >>>>
exec rdsadmin.rdsadmin_util.switch_logfile;
alter system switch logfile;
/* Query V$LOG to see the logs. */ /* Now one of the new logs is current. */
SQL>select GROUP#, BYTES, STATUS from V$LOG;
GROUP# BYTES STATUS
---------- ---------- ----------------
2 134217728 ACTIVE
5 536870912 CURRENT
6 536870912 UNUSED
7 536870912 UNUSED
8 536870912 UNUSED
1.5 체크포인트 발생[편집]
/* Issue a checkpoint to clear log 2. */
AWS >>>>
exec rdsadmin.rdsadmin_util.checkpoint;
alter system checkpoint;
/* Query V$LOG to see the logs. */ /* Now the final original log is inactive. */
select GROUP#, BYTES, STATUS from V$LOG;
GROUP# BYTES STATUS
---------- ---------- ----------------
2 134217728 INACTIVE
5 536870912 CURRENT
6 536870912 UNUSED
7 536870912 UNUSED
8 536870912 UNUSED
- Drop the final inactive log.
AWS >>>>
exec rdsadmin.rdsadmin_util.drop_logfile(grp => 2);
/* Query V$LOG to see the logs. */ /* Now there are four 512 MB logs. */
select GROUP#, BYTES, STATUS from V$LOG;
GROUP# BYTES STATUS
---------- ---------- ----------------
5 536870912 CURRENT
6 536870912 UNUSED
7 536870912 UNUSED
8 536870912 UNUSED