EXADATA 장비에 Free Space가 부족해서 각각의 Cell Server에서 500M짜리 디스크를 10개씩 붙이고 ,
Cell server에 root로 로그인 해서 $T_WORK/raw 디렉터리에서 다음과 같이 링크를 구성
- Cell01 Server
ln -s /dev/sdt cell01_DISK13
ln -s /dev/sdu cell01_DISK14
ln -s /dev/sdv cell01_DISK15
ln -s /dev/sdw cell01_DISK16
ln -s /dev/sdx cell01_DISK17
ln -s /dev/sdy cell01_DISK18
ln -s /dev/sdz cell01_DISK19
ln -s /dev/sdaa cell01_DISK20
ln -s /dev/sdab cell01_DISK21
ln -s /dev/sdac cell01_DISK22
- Cell02 Server
ln -s /dev/sdt cell02_DISK13
ln -s /dev/sdu cell02_DISK14
ln -s /dev/sdv cell02_DISK15
ln -s /dev/sdw cell02_DISK16
ln -s /dev/sdx cell02_DISK17
ln -s /dev/sdy cell02_DISK18
ln -s /dev/sdz cell02_DISK19
ln -s /dev/sdaa cell02_DISK20
ln -s /dev/sdab cell02_DISK21
ln -s /dev/sdac cell02_DISK22
- Cell03 Server
ln -s /dev/sdt cell03_DISK13
ln -s /dev/sdu cell03_DISK14
ln -s /dev/sdv cell03_DISK15
ln -s /dev/sdw cell03_DISK16
ln -s /dev/sdx cell03_DISK17
ln -s /dev/sdy cell03_DISK18
ln -s /dev/sdz cell03_DISK19
ln -s /dev/sdaa cell03_DISK20
ln -s /dev/sdab cell03_DISK21
ln -s /dev/sdac cell03_DISK22
각각의 서버에서 celladmin유저로 로그인 한 후
cellcli -e create celldisk all
cellcli -e create griddisk all harddisk prefix=DATA
cellcli -e list celldisk detail -- lun과 physical disk명이 동일한지 여부 확인
DB 서버에서 grid 유저로 asmca 실행 후 기존 EXA Disk group에 추가시킨 모든 것을 추가 시킴
SQL> SELECT PATH , NAME , TOTAL_MB , FREE_MB ,HEADER_STATUS STATUS FROM v$ASM_DISK ;
PATH NAME TOTAL_MB FREE_MB STATUS
------------------------------------------------- --------------------------------- ---------- ---------- ------------
o/192.168.10.63/DATA_CD_DISK14_cell03 DATA_CD_DISK14_CELL03 448 372 MEMBER
o/192.168.10.63/DATA_CD_DISK13_cell03 DATA_CD_DISK13_CELL03 448 368 MEMBER
o/192.168.10.63/DATA_CD_DISK15_cell03 DATA_CD_DISK15_CELL03 448 372 MEMBER
o/192.168.10.63/DATA_CD_DISK02_cell03 DATA_CD_DISK02_CELL03 448 24 MEMBER
o/192.168.10.63/DATA_CD_DISK08_cell03 DATA_CD_DISK08_CELL03 448 20 MEMBER
o/192.168.10.63/DATA_CD_DISK21_cell03 DATA_CD_DISK21_CELL03 448 372 MEMBER
o/192.168.10.63/DATA_CD_DISK05_cell03 DATA_CD_DISK05_CELL03 448 24 MEMBER
o/192.168.10.63/DATA_CD_DISK09_cell03 DATA_CD_DISK09_CELL03 448 28 MEMBER
o/192.168.10.63/DATA_CD_DISK07_cell03 DATA_CD_DISK07_CELL03 448 24 MEMBER
o/192.168.10.63/DATA_CD_DISK11_cell03 DATA_CD_DISK11_CELL03 448 24 MEMBER
o/192.168.10.63/DATA_CD_DISK18_cell03 DATA_CD_DISK18_CELL03 448 368 MEMBER
o/192.168.10.63/DATA_CD_DISK12_cell03 DATA_CD_DISK12_CELL03 448 28 MEMBER
o/192.168.10.63/DATA_CD_DISK17_cell03 DATA_CD_DISK17_CELL03 448 368 MEMBER
o/192.168.10.63/DATA_CD_DISK10_cell03 DATA_CD_DISK10_CELL03 448 8 MEMBER
o/192.168.10.63/DATA_CD_DISK19_cell03 DATA_CD_DISK19_CELL03 448 372 MEMBER
o/192.168.10.63/DATA_CD_DISK20_cell03 DATA_CD_DISK20_CELL03 448 368 MEMBER
o/192.168.10.63/DATA_CD_DISK06_cell03 DATA_CD_DISK06_CELL03 448 28 MEMBER
o/192.168.10.63/DATA_CD_DISK01_cell03 DATA_CD_DISK01_CELL03 448 16 MEMBER
o/192.168.10.63/DATA_CD_DISK16_cell03 DATA_CD_DISK16_CELL03 448 372 MEMBER
o/192.168.10.63/DATA_CD_DISK04_cell03 DATA_CD_DISK04_CELL03 448 24 MEMBER
o/192.168.10.63/DATA_CD_DISK03_cell03 DATA_CD_DISK03_CELL03 448 32 MEMBER
o/192.168.10.63/DATA_CD_DISK22_cell03 DATA_CD_DISK22_CELL03 448 372 MEMBER
o/192.168.10.62/DATA_CD_DISK05_cell02 DATA_CD_DISK05_CELL02 448 28 MEMBER
o/192.168.10.62/DATA_CD_DISK02_cell02 DATA_CD_DISK02_CELL02 448 40 MEMBER
o/192.168.10.62/DATA_CD_DISK06_cell02 DATA_CD_DISK06_CELL02 448 24 MEMBER
o/192.168.10.62/DATA_CD_DISK13_cell02 DATA_CD_DISK13_CELL02 448 372 MEMBER
o/192.168.10.62/DATA_CD_DISK07_cell02 DATA_CD_DISK07_CELL02 448 28 MEMBER
o/192.168.10.62/DATA_CD_DISK11_cell02 DATA_CD_DISK11_CELL02 448 28 MEMBER
o/192.168.10.62/DATA_CD_DISK04_cell02 DATA_CD_DISK04_CELL02 448 32 MEMBER
o/192.168.10.62/DATA_CD_DISK19_cell02 DATA_CD_DISK19_CELL02 448 372 MEMBER
o/192.168.10.62/DATA_CD_DISK14_cell02 DATA_CD_DISK14_CELL02 448 372 MEMBER
o/192.168.10.62/DATA_CD_DISK12_cell02 DATA_CD_DISK12_CELL02 448 24 MEMBER
o/192.168.10.62/DATA_CD_DISK16_cell02 DATA_CD_DISK16_CELL02 448 372 MEMBER
o/192.168.10.62/DATA_CD_DISK21_cell02 DATA_CD_DISK21_CELL02 448 376 MEMBER
o/192.168.10.62/DATA_CD_DISK03_cell02 DATA_CD_DISK03_CELL02 448 28 MEMBER
o/192.168.10.62/DATA_CD_DISK22_cell02 DATA_CD_DISK22_CELL02 448 372 MEMBER
o/192.168.10.62/DATA_CD_DISK17_cell02 DATA_CD_DISK17_CELL02 448 372 MEMBER
o/192.168.10.62/DATA_CD_DISK01_cell02 DATA_CD_DISK01_CELL02 448 28 MEMBER
o/192.168.10.62/DATA_CD_DISK20_cell02 DATA_CD_DISK20_CELL02 448 368 MEMBER
o/192.168.10.62/DATA_CD_DISK08_cell02 DATA_CD_DISK08_CELL02 448 16 MEMBER
o/192.168.10.62/DATA_CD_DISK10_cell02 DATA_CD_DISK10_CELL02 448 20 MEMBER
o/192.168.10.62/DATA_CD_DISK18_cell02 DATA_CD_DISK18_CELL02 448 376 MEMBER
o/192.168.10.62/DATA_CD_DISK09_cell02 DATA_CD_DISK09_CELL02 448 24 MEMBER
o/192.168.10.62/DATA_CD_DISK15_cell02 DATA_CD_DISK15_CELL02 448 372 MEMBER
o/192.168.10.61/DATA_CD_DISK03_cell01 DATA_CD_DISK03_CELL01 448 24 MEMBER
o/192.168.10.61/DATA_CD_DISK19_cell01 DATA_CD_DISK19_CELL01 448 372 MEMBER
o/192.168.10.61/DATA_CD_DISK11_cell01 DATA_CD_DISK11_CELL01 448 24 MEMBER
o/192.168.10.61/DATA_CD_DISK12_cell01 DATA_CD_DISK12_CELL01 448 16 MEMBER
o/192.168.10.61/DATA_CD_DISK07_cell01 DATA_CD_DISK07_CELL01 448 8 MEMBER
o/192.168.10.61/DATA_CD_DISK09_cell01 DATA_CD_DISK09_CELL01 448 16 MEMBER
o/192.168.10.61/DATA_CD_DISK21_cell01 DATA_CD_DISK21_CELL01 448 376 MEMBER
o/192.168.10.61/DATA_CD_DISK13_cell01 DATA_CD_DISK13_CELL01 448 372 MEMBER
o/192.168.10.61/DATA_CD_DISK20_cell01 DATA_CD_DISK20_CELL01 448 368 MEMBER
o/192.168.10.61/DATA_CD_DISK02_cell01 DATA_CD_DISK02_CELL01 448 248 MEMBER
o/192.168.10.61/DATA_CD_DISK05_cell01 DATA_CD_DISK05_CELL01 448 16 MEMBER
o/192.168.10.61/DATA_CD_DISK14_cell01 DATA_CD_DISK14_CELL01 448 372 MEMBER
o/192.168.10.61/DATA_CD_DISK15_cell01 DATA_CD_DISK15_CELL01 448 376 MEMBER
o/192.168.10.61/DATA_CD_DISK18_cell01 DATA_CD_DISK18_CELL01 448 376 MEMBER
o/192.168.10.61/DATA_CD_DISK01_cell01 DATA_CD_DISK01_CELL01 448 208 MEMBER
o/192.168.10.61/DATA_CD_DISK06_cell01 DATA_CD_DISK06_CELL01 448 20 MEMBER
o/192.168.10.61/DATA_CD_DISK16_cell01 DATA_CD_DISK16_CELL01 448 372 MEMBER
o/192.168.10.61/DATA_CD_DISK08_cell01 DATA_CD_DISK08_CELL01 448 16 MEMBER
o/192.168.10.61/DATA_CD_DISK04_cell01 DATA_CD_DISK04_CELL01 448 12 MEMBER
o/192.168.10.61/DATA_CD_DISK17_cell01 DATA_CD_DISK17_CELL01 448 372 MEMBER
o/192.168.10.61/DATA_CD_DISK10_cell01 DATA_CD_DISK10_CELL01 448 12 MEMBER
o/192.168.10.61/DATA_CD_DISK22_cell01 DATA_CD_DISK22_CELL01 448 372 MEMBER
66 rows selected.
Free 공간의 사이즈가 제 각각임.
SQL> SELECT NAME , TOTAL_MB , FREE_MB , STATE FROM V$ASM_DISKGROUP ;
NAME TOTAL_MB FREE_MB STATE
------------------------------ ------------ ---------- -----------
DATA 896 456 MOUNTED
EXA 28672 11920 CONNECTED
쿼리를 해 보면 정상적으로 추가 되었고, Free 공간이 11G 인 것을 확인 할 수 있는데, 실제 table을 생성하려고 하면 Error가 발생 함.
SQL> alter session set sort_area_size = 1048576000 ;
SQL> create table pba_order AS SELECT rownum pba_id, column_name, owner, table_name
from (SELECT column_name, owner, table_name
from dba_tab_columns where rownum <= 10000),
(select rownum from dual CONNECT BY LEVEL <=20000)
order by pba_id;
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Grid Disk에 변경이 가해지면 자동으로 Rebalance하는거로 알고 있는데, rebalance가 안되서 여유 공간 문제가 발생 하는 것 같음.
oracle 유저로 다음 쿼리를 수행하면 rebalance 하는 프로세스가 없음
SQL> connect / as sysdba
SQL> SELECT NAME , OPERATION , b.STATE, EST_RATE , EST_MINUTES
FROM v$ASM_DISKGROUP a , V$ASM_OPERATION b
WHERE a.GROUP_NUMBER = b.GROUP_NUMBER ;
no rows selected
그래서 grid 유저로 로그인 해서 동일 쿼리를 수행 시켜 봄
SQL> connect / as sysasm
SQL> SELECT NAME , OPERATION , EST_RATE , EST_MINUTES
FROM v$ASM_DISKGROUP a , V$ASM_OPERATION b
WHERE a.GROUP_NUMBER = b.GROUP_NUMBER ;
NAME OPERA EST_RATE EST_MINUTES
------------------------------ ---------- ----------- ----------------
EXA REBAL
위와 같이 rebalancing이 되어야 할 것 같은데 소요 시간이나 이런 게 안 나옴.
다음 쿼리를 이용해서 조회해 보면 결과값에서 알 수 있듯이 사용 가능 공간이 0이 나온다.
- 포스팅 아래에 쿼리 첨부 했음 (실사용가능 공간 확인쿼리)
SQL> 실사용 공간 확인 쿼리 (너무 길어서...)
NAME USABLE_FREE_MB
------------------------------ --------------------
DATA 416
EXA 0
EXA Disk group에 사용 가능 공간이 0 이다.
Oracle 유저로 수행해 보면 여유 공간이 11G가 있다고 나오는데, 실제 사용공간이 0 이라니 ....
그래서 이번에는 v$asm_operation 뷰의 state 칼럼 값을 같이 조회해 보았다.
SQL> SELECT NAME , OPERATION , b.STATE, EST_RATE , EST_MINUTES
FROM v$ASM_DISKGROUP a , V$ASM_OPERATION b
WHERE a.GROUP_NUMBER = b.GROUP_NUMBER ;
NAME OPERA STAT EST_RATE EST_MINUTES
------------------------------ -------- ------ ------------ ---------------
EXA REBAL REAP 0 0
STATE 칼럼 값이 REAP 이다. 확인해 보니 Operation is being run down 이라고 나옴.
rebalance 파라미터를 조회하니 가장 낮은 상태로 되어 있음.
(asm_power_limit 값이 0 이면 auto rebalance를 사용 안하고, 1 - 11 까지 값을 줄 수 있는데 1은 low speed , 11은 full throttle)
SQL> show parameter asm_power_limit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_power_limit integer 1
.
그래서 grid 유저에서 수동으로 rebalancing 해줌
SQL> alter diskgroup EXA rebalance power 11 ;
Diskgroup altered.
SQL> SELECT NAME , OPERATION , b.STATE, EST_RATE , EST_MINUTES
FROM v$ASM_DISKGROUP a , V$ASM_OPERATION b
WHERE a.GROUP_NUMBER = b.GROUP_NUMBER ;
NAME OPERA STAT EST_RATE EST_MINUTES
------------------------------ --------- ------- ------------- -----------
EXA REBAL RUN 312 3
좀 지난 후에 다시 쿼리를 해 보면
SQL> SELECT NAME , OPERATION , b.STATE, EST_RATE , EST_MINUTES
FROM v$ASM_DISKGROUP a , V$ASM_OPERATION b
WHERE a.GROUP_NUMBER = b.GROUP_NUMBER ; 2 3
NAME OPERA STAT EST_RATE EST_MINUTES
------------------------------ ---------- ------- ----------- ---------------
EXA REBAL ERRS
ERROR가 나 있다.
alert_+ASM1.log 로그를 봤는데 특별한 에러는 없는 것 처럼 보임.
|
Fri Jun 10 17:09:33 2016 SQL> alter diskgroup EXA rebalance power 11 NOTE: GroupBlock outside rolling migration privileged region Fri Jun 10 17:13:06 2016 NOTE: Attempting voting file refresh on diskgroup EXA NOTE: Refresh completed on diskgroup EXA. No voting file found. Fri Jun 10 17:14:04 2016 WARNING: process ARB0 terminated via OS NOTE: rebalance interrupted for group 2/0x6c686449 (EXA) Fri Jun 10 17:14:07 2016 NOTE: requesting all-instance membership refresh for group=2 NOTE: membership refresh pending for group 2/0x6c686449 (EXA) Fri Jun 10 17:14:13 2016 GMON querying group 2 at 10 for pid 19, osid 3929 SUCCESS: refreshed membership for 2/0x6c686449 (EXA) SUCCESS: alter diskgroup EXA rebalance power 11 NOTE: starting rebalance of group 2/0x6c686449 (EXA) at power 11 Starting background process ARB0 Fri Jun 10 17:14:13 2016 ARB0 started with pid=30, OS id=12286 NOTE: assigning ARB0 to group 2/0x6c686449 (EXA) with 11 parallel I/Os Fri Jun 10 17:14:16 2016 NOTE: Attempting voting file refresh on diskgroup EXA NOTE: Refresh completed on diskgroup EXA. No voting file found. Fri Jun 10 17:21:21 2016 GMON dumping GMON dumping GMON dumping - Done Fri Jun 10 17:21:21 2016 ASM Health Checker found 1 new failures Fri Jun 10 17:24:58 2016 NOTE: stopping process ARB0 WARNING: rebalance not completed for group 2/0x6c686449 (EXA) Fri Jun 10 17:24:59 2016 NOTE: GroupBlock outside rolling migration privileged region NOTE: requesting all-instance membership refresh for group=2 NOTE: membership refresh pending for group 2/0x6c686449 (EXA) Fri Jun 10 17:25:05 2016 GMON querying group 2 at 11 for pid 19, osid 3929 SUCCESS: refreshed membership for 2/0x6c686449 (EXA) Fri Jun 10 17:25:08 2016 NOTE: Attempting voting file refresh on diskgroup EXA NOTE: Refresh completed on diskgroup EXA. No voting file found. |
다시 실제 사용 가능한 공간을 확인하는 쿼리를 수행해 보니
SQL> 첨부파일 SQL 수행
NAME USABLE_FREE_MB
------------------------------ --------------
DATA 416
EXA 11008
여유 공간이 생겼음.
다시 oracle 유저로 돌아와서 다음 쿼리를 수행시켜 봄
SQL> SELECT PATH , NAME , TOTAL_MB , FREE_MB ,HEADER_STATUS STATUS FROM v$ASM_DISK ;
PATH NAME TOTAL_MB FREE_MB STATUS
------------------------------------------------- --------------------------------- ---------- ---------- ------------
o/192.168.10.63/DATA_CD_DISK14_cell03 DATA_CD_DISK14_CELL03 448 180 MEMBER
o/192.168.10.63/DATA_CD_DISK13_cell03 DATA_CD_DISK13_CELL03 448 180 MEMBER
o/192.168.10.63/DATA_CD_DISK15_cell03 DATA_CD_DISK15_CELL03 448 180 MEMBER
o/192.168.10.63/DATA_CD_DISK02_cell03 DATA_CD_DISK02_CELL03 448 176 MEMBER
o/192.168.10.63/DATA_CD_DISK08_cell03 DATA_CD_DISK08_CELL03 448 180 MEMBER
o/192.168.10.63/DATA_CD_DISK21_cell03 DATA_CD_DISK21_CELL03 448 180 MEMBER
o/192.168.10.63/DATA_CD_DISK05_cell03 DATA_CD_DISK05_CELL03 448 180 MEMBER
o/192.168.10.63/DATA_CD_DISK09_cell03 DATA_CD_DISK09_CELL03 448 176 MEMBER
o/192.168.10.63/DATA_CD_DISK07_cell03 DATA_CD_DISK07_CELL03 448 176 MEMBER
o/192.168.10.63/DATA_CD_DISK11_cell03 DATA_CD_DISK11_CELL03 448 176 MEMBER
o/192.168.10.63/DATA_CD_DISK18_cell03 DATA_CD_DISK18_CELL03 448 180 MEMBER
o/192.168.10.63/DATA_CD_DISK12_cell03 DATA_CD_DISK12_CELL03 448 180 MEMBER
o/192.168.10.63/DATA_CD_DISK17_cell03 DATA_CD_DISK17_CELL03 448 180 MEMBER
o/192.168.10.63/DATA_CD_DISK10_cell03 DATA_CD_DISK10_CELL03 448 176 MEMBER
o/192.168.10.63/DATA_CD_DISK19_cell03 DATA_CD_DISK19_CELL03 448 180 MEMBER
o/192.168.10.63/DATA_CD_DISK20_cell03 DATA_CD_DISK20_CELL03 448 180 MEMBER
o/192.168.10.63/DATA_CD_DISK06_cell03 DATA_CD_DISK06_CELL03 448 176 MEMBER
o/192.168.10.63/DATA_CD_DISK01_cell03 DATA_CD_DISK01_CELL03 448 176 MEMBER
o/192.168.10.63/DATA_CD_DISK16_cell03 DATA_CD_DISK16_CELL03 448 176 MEMBER
o/192.168.10.63/DATA_CD_DISK04_cell03 DATA_CD_DISK04_CELL03 448 176 MEMBER
o/192.168.10.63/DATA_CD_DISK03_cell03 DATA_CD_DISK03_CELL03 448 180 MEMBER
o/192.168.10.63/DATA_CD_DISK22_cell03 DATA_CD_DISK22_CELL03 448 180 MEMBER
o/192.168.10.62/DATA_CD_DISK05_cell02 DATA_CD_DISK05_CELL02 448 176 MEMBER
o/192.168.10.62/DATA_CD_DISK02_cell02 DATA_CD_DISK02_CELL02 448 180 MEMBER
o/192.168.10.62/DATA_CD_DISK06_cell02 DATA_CD_DISK06_CELL02 448 176 MEMBER
o/192.168.10.62/DATA_CD_DISK13_cell02 DATA_CD_DISK13_CELL02 448 180 MEMBER
o/192.168.10.62/DATA_CD_DISK07_cell02 DATA_CD_DISK07_CELL02 448 176 MEMBER
o/192.168.10.62/DATA_CD_DISK11_cell02 DATA_CD_DISK11_CELL02 448 176 MEMBER
o/192.168.10.62/DATA_CD_DISK04_cell02 DATA_CD_DISK04_CELL02 448 176 MEMBER
o/192.168.10.62/DATA_CD_DISK19_cell02 DATA_CD_DISK19_CELL02 448 180 MEMBER
o/192.168.10.62/DATA_CD_DISK14_cell02 DATA_CD_DISK14_CELL02 448 180 MEMBER
o/192.168.10.62/DATA_CD_DISK12_cell02 DATA_CD_DISK12_CELL02 448 176 MEMBER
o/192.168.10.62/DATA_CD_DISK16_cell02 DATA_CD_DISK16_CELL02 448 180 MEMBER
o/192.168.10.62/DATA_CD_DISK21_cell02 DATA_CD_DISK21_CELL02 448 180 MEMBER
o/192.168.10.62/DATA_CD_DISK03_cell02 DATA_CD_DISK03_CELL02 448 180 MEMBER
o/192.168.10.62/DATA_CD_DISK22_cell02 DATA_CD_DISK22_CELL02 448 180 MEMBER
o/192.168.10.62/DATA_CD_DISK17_cell02 DATA_CD_DISK17_CELL02 448 176 MEMBER
o/192.168.10.62/DATA_CD_DISK01_cell02 DATA_CD_DISK01_CELL02 448 180 MEMBER
o/192.168.10.62/DATA_CD_DISK20_cell02 DATA_CD_DISK20_CELL02 448 180 MEMBER
o/192.168.10.62/DATA_CD_DISK08_cell02 DATA_CD_DISK08_CELL02 448 172 MEMBER
o/192.168.10.62/DATA_CD_DISK10_cell02 DATA_CD_DISK10_CELL02 448 176 MEMBER
o/192.168.10.62/DATA_CD_DISK18_cell02 DATA_CD_DISK18_CELL02 448 180 MEMBER
o/192.168.10.62/DATA_CD_DISK09_cell02 DATA_CD_DISK09_CELL02 448 176 MEMBER
o/192.168.10.62/DATA_CD_DISK15_cell02 DATA_CD_DISK15_CELL02 448 180 MEMBER
o/192.168.10.61/DATA_CD_DISK03_cell01 DATA_CD_DISK03_CELL01 448 172 MEMBER
o/192.168.10.61/DATA_CD_DISK19_cell01 DATA_CD_DISK19_CELL01 448 180 MEMBER
o/192.168.10.61/DATA_CD_DISK11_cell01 DATA_CD_DISK11_CELL01 448 176 MEMBER
o/192.168.10.61/DATA_CD_DISK12_cell01 DATA_CD_DISK12_CELL01 448 176 MEMBER
o/192.168.10.61/DATA_CD_DISK07_cell01 DATA_CD_DISK07_CELL01 448 176 MEMBER
o/192.168.10.61/DATA_CD_DISK09_cell01 DATA_CD_DISK09_CELL01 448 172 MEMBER
o/192.168.10.61/DATA_CD_DISK21_cell01 DATA_CD_DISK21_CELL01 448 180 MEMBER
o/192.168.10.61/DATA_CD_DISK13_cell01 DATA_CD_DISK13_CELL01 448 180 MEMBER
o/192.168.10.61/DATA_CD_DISK20_cell01 DATA_CD_DISK20_CELL01 448 180 MEMBER
o/192.168.10.61/DATA_CD_DISK02_cell01 DATA_CD_DISK02_CELL01 448 248 MEMBER
o/192.168.10.61/DATA_CD_DISK05_cell01 DATA_CD_DISK05_CELL01 448 172 MEMBER
o/192.168.10.61/DATA_CD_DISK14_cell01 DATA_CD_DISK14_CELL01 448 180 MEMBER
o/192.168.10.61/DATA_CD_DISK15_cell01 DATA_CD_DISK15_CELL01 448 180 MEMBER
o/192.168.10.61/DATA_CD_DISK18_cell01 DATA_CD_DISK18_CELL01 448 180 MEMBER
o/192.168.10.61/DATA_CD_DISK01_cell01 DATA_CD_DISK01_CELL01 448 208 MEMBER
o/192.168.10.61/DATA_CD_DISK06_cell01 DATA_CD_DISK06_CELL01 448 176 MEMBER
o/192.168.10.61/DATA_CD_DISK16_cell01 DATA_CD_DISK16_CELL01 448 180 MEMBER
o/192.168.10.61/DATA_CD_DISK08_cell01 DATA_CD_DISK08_CELL01 448 176 MEMBER
o/192.168.10.61/DATA_CD_DISK04_cell01 DATA_CD_DISK04_CELL01 448 176 MEMBER
o/192.168.10.61/DATA_CD_DISK17_cell01 DATA_CD_DISK17_CELL01 448 180 MEMBER
o/192.168.10.61/DATA_CD_DISK10_cell01 DATA_CD_DISK10_CELL01 448 176 MEMBER
o/192.168.10.61/DATA_CD_DISK22_cell01 DATA_CD_DISK22_CELL01 448 180 MEMBER
66 rows selected.
위와 같이 rebalance가 되어 있음.
|
- 첨부 실제 사용공간 확인 쿼리 |
|
SELECT /* EXTERNAL REDUNDANCY */ g.name , SUM( d.total_mb ) * MIN( d.free_mb /d.total_mb ) /decode( g.type , 'EXTERN' , 1 , 'NORMAL' , 2 , 'HIGH' , 3 , 1 ) as "USABLE_FREE_MB" FROM v$asm_disk d , v$asm_diskgroup g WHERE d.group_number = g.group_number AND g.type = 'EXTERN' GROUP BY g.name , g.type UNION SELECT /* NON EXTERNAL REDUNDANCY WITH SYMMETRIC FG */ g.name , SUM( d.total_mb ) * MIN( d.free_mb /d.total_mb ) /decode( g.type , 'EXTERN' , 1 , 'NORMAL' , 2 , 'HIGH' , 3 , 1 ) as "USABLE_FREE_MB" FROM v$asm_disk d , v$asm_diskgroup g WHERE d.group_number = g.group_number AND g.group_number NOT IN /* KEEP SYMMETRIC*/ ( SELECT DISTINCT( group_number ) FROM ( SELECT group_number , failgroup , total_mb , count_dsk , GREATEST( LAG( count_dsk , 1 , 0 ) over( PARTITION BY total_mb , group_number ORDER BY total_mb , failgroup ) , LEAD( count_dsk , 1 , 0 ) over( PARTITION BY total_mb , group_number ORDER BY total_mb , failgroup ) ) AS max_lag_lead , COUNT( DISTINCT( failgroup ) ) over( PARTITION BY group_number , total_mb ) AS nb_fg_per_size , count_fg FROM ( SELECT group_number , failgroup , total_mb , COUNT( * ) over( PARTITION BY group_number , failgroup , total_mb ) AS count_dsk , COUNT( DISTINCT( failgroup ) ) over( PARTITION BY group_number ) AS count_fg FROM v$asm_disk ) ) WHERE count_dsk <> max_lag_lead OR nb_fg_per_size <> count_fg ) AND g.type <> 'EXTERNAL' GROUP BY g.name , g.type UNION SELECT /* NON EXTERNAL REDUNDANCY WITH NON SYMMETRIC FG AND DOES EXIST AT LEAST ONE DISK WITH PARTNERS OF DIFFERENT SIZE*/ name , MIN( free ) / decode(TYPE , 'EXTERN' , 1 , 'NORMAL' , 2 , 'HIGH' , 3 , 1 ) as "USABLE_FREE_MB" FROM ( SELECT name , disk_number , free_mb /( factor /sum( factor ) over( PARTITION BY name ) ) AS free , type FROM ( SELECT name , disk_number , AVG( free_mb ) AS free_mb , AVG( total_mb ) AS total_mb , SUM( factor_disk + factor_partner ) AS factor , type FROM ( SELECT g.name , g.type , d.group_number AS group_number , d.disk_number disk_number , d.total_mb AS total_mb , d.free_mb AS free_mb , p.number_kfdpartner "Partner disk#" , f.factor AS factor_disk , fp.factor AS factor_partner FROM x$kfdpartner p , v$asm_disk d , v$asm_diskgroup g , ( SELECT disk_number , group_number , total_mb /min( total_mb ) over( PARTITION BY group_number ) AS factor FROM v$asm_disk WHERE state = 'NORMAL' AND mount_status = 'CACHED' ) f , ( SELECT disk_number , group_number , total_mb /min( total_mb ) over( PARTITION BY group_number ) AS factor FROM v$asm_disk WHERE state = 'NORMAL' AND mount_status = 'CACHED' ) fp WHERE p.disk = d.disk_number AND p.grp = d.group_number AND f.disk_number = d.disk_number AND f.group_number = d.group_number AND fp.disk_number = p.number_kfdpartner AND fp.group_number = p.grp AND d.group_number = g.group_number AND g.type <> 'EXTERN' AND g.group_number IN /* KEEP NON SYMMETRIC */ ( SELECT DISTINCT( group_number ) FROM ( SELECT group_number , failgroup , total_mb , count_dsk , GREATEST( LAG( count_dsk , 1 , 0 ) over( PARTITION BY total_mb , group_number ORDER BY total_mb , failgroup ) , LEAD( count_dsk , 1 , 0 ) over( PARTITION BY total_mb , group_number ORDER BY total_mb , failgroup ) ) AS max_lag_lead , COUNT( DISTINCT( failgroup ) ) over( PARTITION BY group_number , total_mb ) AS nb_fg_per_size , count_fg FROM ( SELECT group_number , failgroup , total_mb , COUNT( * ) over( PARTITION BY group_number , failgroup , total_mb ) AS count_dsk , COUNT( DISTINCT( failgroup ) ) over( PARTITION BY group_number ) AS count_fg FROM v$asm_disk ) ) WHERE count_dsk <> max_lag_lead OR nb_fg_per_size <> count_fg ) AND d.group_number NOT IN /* KEEP DG THAT DOES NOT CONTAIN AT LEAST ONE DISK HAVING PARTNERS OF DIFFERENT SIZE*/ ( SELECT DISTINCT( group_number ) FROM ( SELECT d.group_number AS group_number , d.disk_number disk_number , p.number_kfdpartner "Partner disk#" , f.factor AS factor_disk , fp.factor AS factor_partner , GREATEST( LAG( fp.factor , 1 , 0 ) over( PARTITION BY d.group_number , d.disk_number ORDER BY d.group_number , d.disk_number ) , LEAD( fp.factor , 1 , 0 ) over( PARTITION BY d.group_number , d.disk_number ORDER BY d.group_number , d.disk_number ) ) AS max_lag_lead , COUNT( p.number_kfdpartner ) over( PARTITION BY d.group_number , d.disk_number ) AS nb_partner FROM x$kfdpartner p , v$asm_disk d , v$asm_diskgroup g , ( SELECT disk_number , group_number , total_mb /min( total_mb ) over( PARTITION BY group_number ) AS factor FROM v$asm_disk WHERE state = 'NORMAL' AND mount_status = 'CACHED' ) f , ( SELECT disk_number , group_number , total_mb /min( total_mb ) over( PARTITION BY group_number ) AS factor FROM v$asm_disk WHERE state = 'NORMAL' AND mount_status = 'CACHED' ) fp WHERE p.disk = d.disk_number AND p.grp = d.group_number AND f.disk_number = d.disk_number AND f.group_number = d.group_number AND fp.disk_number = p.number_kfdpartner AND fp.group_number = p.grp AND d.group_number = g.group_number AND g.type <> 'EXTERN' ) WHERE factor_partner <> max_lag_lead AND nb_partner > 1 ) ) GROUP BY name , disk_number , TYPE ) ) GROUP BY name , TYPE ; |