| ASM DISK 추가 시 리밸런싱 작업 중 일시중지 후 다시 재개하는 테스트 |
작업 명령어
SQL> select group_number, disk_number, name, mount_status, header_status, state, path, os_mb, total_mb, cold_used_mb, free_mb
from v$asm_disk
WHERE 1=1
order by 1,3 ;
GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS HEADER_STATUS STATE PATH OS_MB TOTAL_MB COLD_USED_MB FREE_MB
------------ ----------- -------------------- --------------------- ------------------------------------ ------------------------ -------------------- ---------- ---------- ------------ ----------
0 0 CLOSED FORMER NORMAL AFD:DATA 10239 0 0 0
1 1 DATA02 CACHED MEMBER NORMAL AFD:DATA02 10239 10236 2836 7400
-- add disk, PATH경로를 적어줌
SQL> alter diskgroup DATA add disk 'AFD:DATA' ;
Diskgroup altered.
-- 리밸런싱 중지
SQL> alter diskgroup DATA rebalance power 0 ;
Diskgroup altered.
-- 리밸런싱 재개
SQL> alter diskgroup DATA rebalance power 1 ;
Diskgroup altered.
v$asm_operation 모니터링
| REBALANCE 작업이 RUN -> (일시중지) -> REAP -> WAIT -> (재개) -> RUN 으로 변하는 것을 확인 할 수 있다 |
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:14:37 2023
GROUP_NUMBER OPERATION PASS STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ --------------- --------------------------- ------------ ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL COMPACT WAIT 1 1 0 0 0 0
1 REBAL REBALANCE RUN 1 1 5 351 1987 0
1 REBAL REBUILD DONE 1 1 0 0 0 0
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:14:39 2023
GROUP_NUMBER OPERATION PASS STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ --------------- --------------------------- ------------ ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL COMPACT WAIT 1 1 0 0 0 0
1 REBAL REBALANCE RUN 1 1 9 351 2651 0
1 REBAL REBUILD DONE 1 1 0 0 0 0
**********************************
일시중지
**********************************
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:14:41 2023
GROUP_NUMBER OPERATION PASS STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ --------------- --------------------------- ------------ ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL COMPACT WAIT 1 1 0 0 0 0
1 REBAL REBALANCE REAP 1 1 87 351 3879 0
1 REBAL REBUILD DONE 1 1 0 0 0 0
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:14:43 2023
GROUP_NUMBER OPERATION PASS STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ --------------- --------------------------- ------------ ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL COMPACT WAIT 1 1 0 0 0 0
1 REBAL REBALANCE REAP 1 1 87 351 3879 0
1 REBAL REBUILD DONE 1 1 0 0 0 0
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:14:45 2023
GROUP_NUMBER OPERATION PASS STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ --------------- --------------------------- ------------ ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL COMPACT WAIT 1 1 0 0 0 0
1 REBAL REBALANCE REAP 1 1 87 351 3879 0
1 REBAL REBUILD DONE 1 1 0 0 0 0
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:14:47 2023
GROUP_NUMBER OPERATION PASS STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ --------------- --------------------------- ------------ ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL COMPACT WAIT 0
1 REBAL REBALANCE WAIT 0
1 REBAL REBUILD WAIT 0
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:15:00 2023
GROUP_NUMBER OPERATION PASS STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ --------------- --------------------------- ------------ ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL COMPACT WAIT 0
1 REBAL REBALANCE WAIT 0
1 REBAL REBUILD WAIT 0
**********************************
작업재개
**********************************
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:15:02 2023
GROUP_NUMBER OPERATION PASS STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ --------------- --------------------------- ------------ ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL COMPACT WAIT 1 1 0 0 0 0
1 REBAL REBALANCE RUN 1 1 64 255 4548 0
1 REBAL REBUILD DONE 1 1 0 0 0 0
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:15:04 2023
GROUP_NUMBER OPERATION PASS STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ --------------- --------------------------- ------------ ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL COMPACT WAIT 1 1 0 0 0 0
1 REBAL REBALANCE RUN 1 1 137 255 4952 0
1 REBAL REBUILD DONE 1 1 0 0 0 0
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:15:06 2023
GROUP_NUMBER OPERATION PASS STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ --------------- --------------------------- ------------ ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL COMPACT REAP 1 1 0 0 0 0
1 REBAL REBALANCE DONE 1 1 255 255 0 0
1 REBAL REBUILD DONE 1 1 0 0 0 0
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:15:08 2023
no rows selected
v$asm_disk 모니터링
| COLD_USED_MB, FREE_MB 컬럼의 값이 변경되다가 일시중지 시 멈췄다가 작업재개 시 이어서 변경되는 것을 확인할 수 있다 |
GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS HEADER_STATUS STATE PATH OS_MB TOTAL_MB COLD_USED_MB FREE_MB
------------ ----------- -------------------- --------------------- ------------------------------------ ------------------------ -------------------- ---------- ---------- ------------ ----------
0 0 CLOSED FORMER NORMAL AFD:DATA 10239 0 0 0
1 1 DATA02 CACHED MEMBER NORMAL AFD:DATA02 10239 10236 2836 7400
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:14:36 2023
GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS HEADER_STATUS STATE PATH OS_MB TOTAL_MB COLD_USED_MB FREE_MB
------------ ----------- -------------------- --------------------- ------------------------------------ ------------------------ -------------------- ---------- ---------- ------------ ----------
1 0 DATA CACHED MEMBER NORMAL AFD:DATA 10239 10236 12 10224
1 1 DATA02 CACHED MEMBER NORMAL AFD:DATA02 10239 10236 2836 7400
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:14:38 2023
GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS HEADER_STATUS STATE PATH OS_MB TOTAL_MB COLD_USED_MB FREE_MB
------------ ----------- -------------------- --------------------- ------------------------------------ ------------------------ -------------------- ---------- ---------- ------------ ----------
1 0 DATA CACHED MEMBER NORMAL AFD:DATA 10239 10236 32 10204
1 1 DATA02 CACHED MEMBER NORMAL AFD:DATA02 10239 10236 2816 7420
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:14:40 2023
GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS HEADER_STATUS STATE PATH OS_MB TOTAL_MB COLD_USED_MB FREE_MB
------------ ----------- -------------------- --------------------- ------------------------------------ ------------------------ -------------------- ---------- ---------- ------------ ----------
1 0 DATA CACHED MEMBER NORMAL AFD:DATA 10239 10236 388 9848
1 1 DATA02 CACHED MEMBER NORMAL AFD:DATA02 10239 10236 2460 7776
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
**********************************
일시중지
**********************************
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:14:42 2023
GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS HEADER_STATUS STATE PATH OS_MB TOTAL_MB COLD_USED_MB FREE_MB
------------ ----------- -------------------- --------------------- ------------------------------------ ------------------------ -------------------- ---------- ---------- ------------ ----------
1 0 DATA CACHED MEMBER NORMAL AFD:DATA 10239 10236 396 9840
1 1 DATA02 CACHED MEMBER NORMAL AFD:DATA02 10239 10236 2452 7784
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:14:44 2023
GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS HEADER_STATUS STATE PATH OS_MB TOTAL_MB COLD_USED_MB FREE_MB
------------ ----------- -------------------- --------------------- ------------------------------------ ------------------------ -------------------- ---------- ---------- ------------ ----------
1 0 DATA CACHED MEMBER NORMAL AFD:DATA 10239 10236 396 9840
1 1 DATA02 CACHED MEMBER NORMAL AFD:DATA02 10239 10236 2452 7784
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:14:46 2023
GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS HEADER_STATUS STATE PATH OS_MB TOTAL_MB COLD_USED_MB FREE_MB
------------ ----------- -------------------- --------------------- ------------------------------------ ------------------------ -------------------- ---------- ---------- ------------ ----------
1 0 DATA CACHED MEMBER NORMAL AFD:DATA 10239 10236 396 9840
1 1 DATA02 CACHED MEMBER NORMAL AFD:DATA02 10239 10236 2452 7784
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:15:00 2023
GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS HEADER_STATUS STATE PATH OS_MB TOTAL_MB COLD_USED_MB FREE_MB
------------ ----------- -------------------- --------------------- ------------------------------------ ------------------------ -------------------- ---------- ---------- ------------ ----------
1 0 DATA CACHED MEMBER NORMAL AFD:DATA 10239 10236 396 9840
1 1 DATA02 CACHED MEMBER NORMAL AFD:DATA02 10239 10236 2452 7784
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
**********************************
작업재개
**********************************
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:15:02 2023
GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS HEADER_STATUS STATE PATH OS_MB TOTAL_MB COLD_USED_MB FREE_MB
------------ ----------- -------------------- --------------------- ------------------------------------ ------------------------ -------------------- ---------- ---------- ------------ ----------
1 0 DATA CACHED MEMBER NORMAL AFD:DATA 10239 10236 992 9244
1 1 DATA02 CACHED MEMBER NORMAL AFD:DATA02 10239 10236 1856 8380
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:15:04 2023
GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS HEADER_STATUS STATE PATH OS_MB TOTAL_MB COLD_USED_MB FREE_MB
------------ ----------- -------------------- --------------------- ------------------------------------ ------------------------ -------------------- ---------- ---------- ------------ ----------
1 0 DATA CACHED MEMBER NORMAL AFD:DATA 10239 10236 1416 8820
1 1 DATA02 CACHED MEMBER NORMAL AFD:DATA02 10239 10236 1432 8804
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 26 19:15:06 2023
GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS HEADER_STATUS STATE PATH OS_MB TOTAL_MB COLD_USED_MB FREE_MB
------------ ----------- -------------------- --------------------- ------------------------------------ ------------------------ -------------------- ---------- ---------- ------------ ----------
1 0 DATA CACHED MEMBER NORMAL AFD:DATA 10239 10236 1416 8820
1 1 DATA02 CACHED MEMBER NORMAL AFD:DATA02 10239 10236 1432 8804
alert_+ASM.log
더보기
2023-10-26T19:14:35.049471+09:00
SQL> alter diskgroup DATA add disk 'AFD:DATA'
2023-10-26T19:14:35.104871+09:00
NOTE: Assigning number (1,0) to disk (AFD:DATA)
NOTE: requesting all-instance membership refresh for group=1
NOTE: Disk 0 in group 1 is assigned fgnum=1
NOTE: discarding redo for group 1 disk 0
NOTE: initializing header (replicated) on grp 1 disk DATA
NOTE: initializing header on grp 1 disk DATA
NOTE: requesting all-instance disk validation for group=1
2023-10-26T19:14:35.221625+09:00
NOTE: skipping rediscovery for group 1/0x5d60152e (DATA) on local instance.
2023-10-26T19:14:35.222963+09:00
NOTE: requesting all-instance disk validation for group=1
2023-10-26T19:14:35.223028+09:00
NOTE: skipping rediscovery for group 1/0x5d60152e (DATA) on local instance.
2023-10-26T19:14:35.223427+09:00
GMON updating for reconfiguration, group 1 at 42 for pid 25, osid 7257
2023-10-26T19:14:35.224055+09:00
NOTE: group 1 PST updated.
2023-10-26T19:14:35.224464+09:00
NOTE: membership refresh pending for group 1/0x5d60152e (DATA)
2023-10-26T19:14:35.226201+09:00
GMON querying group 1 at 43 for pid 19, osid 3737
NOTE: cache opening disk 0 of grp 1: DATA label:DATA
GMON querying group 1 at 44 for pid 19, osid 3737
2023-10-26T19:14:35.285157+09:00
SUCCESS: refreshed membership for 1/0x5d60152e (DATA)
2023-10-26T19:14:35.285433+09:00
SUCCESS: alter diskgroup DATA add disk 'AFD:DATA'
2023-10-26T19:14:36.970165+09:00
NOTE: Attempting voting file refresh on diskgroup DATA
2023-10-26T19:14:36.974944+09:00
NOTE: starting rebalance of group 1/0x5d60152e (DATA) at power 1
NOTE: starting process ARBA
Starting background process ARBA
2023-10-26T19:14:36.980358+09:00
ARBA started with pid=26, OS id=12362
NOTE: starting process ARB0
Starting background process ARB0
2023-10-26T19:14:36.985432+09:00
ARB0 started with pid=31, OS id=12364
NOTE: assigning ARBA to group 1/0x5d60152e (DATA) to compute estimates
NOTE: assigning ARB0 to group 1/0x5d60152e (DATA) with 1 parallel I/O
2023-10-26T19:14:40.785743+09:00
SQL> alter diskgroup DATA rebalance power 0
2023-10-26T19:14:40.786173+09:00
NOTE: stopping process ARBA
2023-10-26T19:14:40.816804+09:00
NOTE: ORA-1013 thrown in ARB0 for group number 1
2023-10-26T19:14:45.986401+09:00
NOTE: rebalance interrupted for group 1/0x5d60152e (DATA)
2023-10-26T19:14:45.986542+09:00
NOTE: requesting all-instance membership refresh for group=1
2023-10-26T19:14:45.987052+09:00
NOTE: membership refresh pending for group 1/0x5d60152e (DATA)
2023-10-26T19:14:45.989918+09:00
GMON querying group 1 at 45 for pid 19, osid 3737
2023-10-26T19:14:45.990261+09:00
SUCCESS: refreshed membership for 1/0x5d60152e (DATA)
2023-10-26T19:14:45.990467+09:00
SUCCESS: alter diskgroup DATA rebalance power 0
2023-10-26T19:14:48.989992+09:00
NOTE: Attempting voting file refresh on diskgroup DATA
2023-10-26T19:15:00.136843+09:00
SQL> alter diskgroup DATA rebalance power 1
2023-10-26T19:15:00.137225+09:00
NOTE: requesting all-instance membership refresh for group=1
2023-10-26T19:15:00.137850+09:00
NOTE: membership refresh pending for group 1/0x5d60152e (DATA)
2023-10-26T19:15:00.142550+09:00
GMON querying group 1 at 46 for pid 19, osid 3737
2023-10-26T19:15:00.142915+09:00
SUCCESS: refreshed membership for 1/0x5d60152e (DATA)
2023-10-26T19:15:00.143187+09:00
SUCCESS: alter diskgroup DATA rebalance power 1
NOTE: Attempting voting file refresh on diskgroup DATA
2023-10-26T19:15:01.050738+09:00
NOTE: starting rebalance of group 1/0x5d60152e (DATA) at power 1
NOTE: starting process ARBA
Starting background process ARBA
2023-10-26T19:15:01.056382+09:00
ARBA started with pid=26, OS id=12514
NOTE: starting process ARB0
Starting background process ARB0
2023-10-26T19:15:01.061529+09:00
ARB0 started with pid=28, OS id=12516
NOTE: assigning ARBA to group 1/0x5d60152e (DATA) to compute estimates
NOTE: assigning ARB0 to group 1/0x5d60152e (DATA) with 1 parallel I/O
2023-10-26T19:15:04.239023+09:00
NOTE: stopping process ARB0
NOTE: stopping process ARBA
NOTE: Starting expel slave for group 1/0x5d60152e (DATA)
2023-10-26T19:15:04.241991+09:00
NOTE: requesting all-instance membership refresh for group=1
2023-10-26T19:15:04.242973+09:00
NOTE: membership refresh pending for group 1/0x5d60152e (DATA)
2023-10-26T19:15:04.245073+09:00
GMON querying group 1 at 47 for pid 19, osid 3737
2023-10-26T19:15:04.245252+09:00
SUCCESS: refreshed membership for 1/0x5d60152e (DATA)
2023-10-26T19:15:07.064855+09:00
SUCCESS: rebalance completed for group 1/0x5d60152e (DATA)
NOTE: Attempting voting file refresh on diskgroup DATA
참고글
ASM DISK 추가/삭제 성능 테스트(신규추가, 기존삭제)
ASM DISK 사이즈 다른 경우 DATAFILE 추가 테스트
'DBMS > ORACLE' 카테고리의 다른 글
| [oracle] datapump 이기종버전 원격지 데이터 로컬로 가져오기 (1) | 2023.12.03 |
|---|---|
| oracle expdp 원격지(remote) 데이터 로컬로 가져오기 (0) | 2023.12.01 |
| ASM DISK 추가/삭제 성능 테스트(신규추가, 기존삭제) (0) | 2023.10.26 |
| 오라클 NTILE 함수 설명 및 사용법 (0) | 2023.10.03 |
| 오라클 START WITH ... CONNECT BY(계층쿼리) 사용법 (0) | 2023.09.15 |
댓글