受困於v$asm_dksigroup裡的欄位REQUIRED_MIRROR_FREE_MB！

這個欄位困擾了我很久，現在理解的也是稀裡糊塗的，記錄一個測試過程和大致的結論。[@more@]

SQL> select total_mb,free_mb,name,failgroup,path from v$asm_disk where group_number=1 order by failgroup;

TOTAL_MB FREE_MB NAME FAILGROUP PATH
---------- ---------- -------------------- -------------------- ----------------------------------------
1024 730 DISK2 DG1_0001 G:ASMDISKDISK2
2048 1494 DG1_0002 DG1_0002 G:ASMDISKDISK1
512 365 DISK12 DG1_0002 G:ASMDISKDISK12
1024 747 DISK11 DG1_0003 G:ASMDISKDISK11
2048 1518 DG1_0003 DG1_0003 G:ASMDISKDISK14
512 362 DG1_0004 DG1_0004 G:ASMDISKDISK13

已選擇6行。

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
-------------------- ---------- ---------- ----------------------- --------------
NORMAL 7168 5216 3072 1072

SQL> alter diskgroup dg1 add failgroup DG1_0005 disk 'g:asmdiskdisk15' name DISK15;

磁碟組已變更。

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
-------------------- ---------- ---------- ----------------------- --------------
NORMAL 10668 8714 3500 2607

SQL> alter diskgroup dg1 add failgroup DG1_0006 disk 'g:asmdiskdisk16' name DISK16;

磁碟組已變更。

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
-------------------- ---------- ---------- ----------------------- --------------
NORMAL 14168 12212 3500 4356

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
-------------------- ---------- ---------- ----------------------- --------------
NORMAL 14168 12212 3500 4356

SQL> alter diskgroup dg1 rebalance;

磁碟組已變更。

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
-------------------- ---------- ---------- ----------------------- --------------
NORMAL 14168 12212 3500 4356

SQL> select total_mb,free_mb,name,failgroup,path from v$asm_disk where group_number=1 order by failgroup;

TOTAL_MB FREE_MB NAME FAILGROUP PATH
---------- ---------- -------------------- -------------------- ----------------------------------------
1024 879 DISK2 DG1_0001 G:ASMDISKDISK2
2048 1765 DG1_0002 DG1_0002 G:ASMDISKDISK1
512 437 DISK12 DG1_0002 G:ASMDISKDISK12
1024 880 DISK11 DG1_0003 G:ASMDISKDISK11
2048 1766 DG1_0003 DG1_0003 G:ASMDISKDISK14
512 437 DG1_0004 DG1_0004 G:ASMDISKDISK13
3500 3025 DISK15 DG1_0005 G:ASMDISKDISK15
3500 3023 DISK16 DG1_0006 G:ASMDISKDISK16

已選擇8行。

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
-------------------- ---------- ---------- ----------------------- --------------
NORMAL 14168 12212 3500 4356

SQL>
我不知道oracle是如何計算REQUIRED_MIRROR_FREE_MB 的值的，如何才能使REQUIRED_MIRROR_FREE_MB 變成0，diskgroup 1目前可用空間是12212 ，但是由於oracle考慮了diskgroup 組裡的disk failure之後的mirror redundancy情況，所以透過dbca使用diskgroup 1時真實的可用空間只有 4356

4356＝（12212 － 3500）/2

--=======================================
SQL> select total_mb,free_mb,name,failgroup,path from v$asm_disk where group_number=1 order by failgroup;

TOTAL_MB FREE_MB NAME FAILGROUP PATH
---------- ---------- -------------------- ---------- ------------------------------
1024 879 DISK2 DG1_0001 G:ASMDISKDISK2
2048 1765 DG1_0002 DG1_0002 G:ASMDISKDISK1
512 437 DISK12 DG1_0002 G:ASMDISKDISK12
1024 880 DISK11 DG1_0003 G:ASMDISKDISK11
2048 1766 DG1_0003 DG1_0003 G:ASMDISKDISK14
512 437 DG1_0004 DG1_0004 G:ASMDISKDISK13
3500 3025 DISK15 DG1_0005 G:ASMDISKDISK15
3500 3023 DISK16 DG1_0006 G:ASMDISKDISK16

已選擇8行。

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------ ---------- ---------- ----------------------- --------------
NORMAL 14168 12212 3500 4356

--==================================================
--此處省略大約78個字...
--把disk都刪除只剩下disk15和disk16，這2塊disk都是3500m，而且分別在2個failgroup裡,目前required_mirror_free_mb變成了0
--==================================================
SQL> alter diskgroup dg1 drop disk DG1_0003;

磁碟組已變更。

SQL> select total_mb,free_mb,name,failgroup,path from v$asm_disk where group_number=1 order by failgroup;

TOTAL_MB FREE_MB NAME FAILGROUP PATH
---------- ---------- -------------------- ---------- ------------------------------
3500 2572 DISK15 DG1_0005 G:ASMDISKDISK15
3500 2572 DISK16 DG1_0006 G:ASMDISKDISK16

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------ ---------- ---------- ----------------------- --------------
NORMAL 7000 5144 0 2572
--================================
--下面增加2塊1g的disk繼續測試，3500的disk太大了，rebalance有些慢
SQL> alter diskgroup dg1 add failgroup DG1_0008 disk 'G:asmdiskDISK20' name DISK20;

磁碟組已變更。

SQL>
SQL> select total_mb,free_mb,name,failgroup,path from v$asm_disk where group_number=1 order by failgroup;

TOTAL_MB FREE_MB NAME FAILGROUP PATH
---------- ---------- -------------------- ---------- ------------------------------
3500 2661 DISK15 DG1_0005 G:ASMDISKDISK15
3500 2661 DISK16 DG1_0006 G:ASMDISKDISK16
1024 756 DISK20 DG1_0008 G:ASMDISKDISK20

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------ ---------- ---------- ----------------------- --------------
NORMAL 8024 6078 3500 1289

SQL>
SQL> alter diskgroup dg1 add failgroup DG1_0009 disk 'G:asmdiskDISK22' name DISK22;

Diskgroup altered.

SQL> select total_mb,free_mb,name,failgroup,path from v$asm_disk where group_number=1 order by failgroup;

TOTAL_MB FREE_MB NAME FAILGROUP PATH
---------- ---------- -------------------- ---------- ------------------------------
3500 2751 DISK15 DG1_0005 G:ASMDISKDISK15
3500 2751 DISK16 DG1_0006 G:ASMDISKDISK16
1024 798 DISK20 DG1_0008 G:ASMDISKDISK20
1024 800 DISK22 DG1_0009 G:ASMDISKDISK22

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------ ---------- ---------- ----------------------- --------------
NORMAL 9048 7100 3500 1800

SQL> alter diskgroup dg1 drop disk DISK16;

Diskgroup altered.

SQL> select total_mb,free_mb,name,failgroup,path from v$asm_disk where group_number=1 order by failgroup;

TOTAL_MB FREE_MB NAME FAILGROUP PATH
---------- ---------- -------------------- ---------- ------------------------------
3500 2572 DISK15 DG1_0005 G:ASMDISKDISK15
1024 515 DISK20 DG1_0008 G:ASMDISKDISK20
1024 515 DISK22 DG1_0009 G:ASMDISKDISK22

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------ ---------- ---------- ----------------------- --------------
NORMAL 5548 3602 3500 51

SQL>
SQL> alter diskgroup dg1 drop disk DISK15;

Diskgroup altered.

SQL> select total_mb,free_mb,name,failgroup,path from v$asm_disk where group_number=1 order by failgroup;

TOTAL_MB FREE_MB NAME FAILGROUP PATH
---------- ---------- -------------------- ---------- ------------------------------
1024 96 DISK20 DG1_0008 G:ASMDISKDISK20
1024 96 DISK22 DG1_0009 G:ASMDISKDISK22

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------ ---------- ---------- ----------------------- --------------
NORMAL 2048 192 0 96

SQL>
--==================================
上面查詢結果已經顯示目前有2個failgroup，裡面分別有1塊大小為1g的disk，目前REQUIRED_MIRROR_FREE_MB為0，
否則REQUIRED_MIRROR_FREE_MB的值就是failgroup裡disk總和最大的那個值...有些不可思議...
--===================================
SQL> alter diskgroup dg1 add failgroup DG1_00010 disk 'G:asmdiskDISK23' name DISK23;

Diskgroup altered.

SQL> select total_mb,free_mb,name,failgroup,path from v$asm_disk where group_number=1 order by failgroup;

TOTAL_MB FREE_MB NAME FAILGROUP PATH
---------- ---------- -------------------- ---------- ------------------------------
1024 376 DISK23 DG1_00010 G:ASMDISKDISK23
1024 375 DISK20 DG1_0008 G:ASMDISKDISK20
1024 375 DISK22 DG1_0009 G:ASMDISKDISK22

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------ ---------- ---------- ----------------------- --------------
NORMAL 3072 1126 1024 51

SQL>
SQL> alter diskgroup dg1 add failgroup DG1_00011 disk 'G:asmdiskDISK24' name DISK24;

Diskgroup altered.

SQL> select total_mb,free_mb,name,failgroup,path from v$asm_disk where group_number=1 order by failgroup;

TOTAL_MB FREE_MB NAME FAILGROUP PATH
---------- ---------- -------------------- ---------- ------------------------------
1024 536 DISK23 DG1_00010 G:ASMDISKDISK23
1024 539 DISK24 DG1_00011 G:ASMDISKDISK24
1024 536 DISK20 DG1_0008 G:ASMDISKDISK20
1024 537 DISK22 DG1_0009 G:ASMDISKDISK22

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------ ---------- ---------- ----------------------- --------------
NORMAL 4096 2148 1024 562

SQL>
--=====================================
--上面又增加了2個failgroup，裡面分別有1塊1g的disk，required_mirror_free_mb依然不為0
--=====================================
SQL> alter diskgroup dg1 drop disk DISK24;

Diskgroup altered.

SQL> alter diskgroup dg1 drop disk DISK23;

Diskgroup altered.

SQL> select total_mb,free_mb,name,failgroup,path from v$asm_disk where group_number=1 order by failgroup;

TOTAL_MB FREE_MB NAME FAILGROUP PATH
---------- ---------- -------------------- ---------- ------------------------------
1024 96 DISK20 DG1_0008 G:ASMDISKDISK20
1024 96 DISK22 DG1_0009 G:ASMDISKDISK22

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------ ---------- ---------- ----------------------- --------------
NORMAL 2048 192 0 96

SQL>
--===================================
SQL> alter diskgroup dg1 add failgroup DG1_0008 disk 'G:ASMDISKDISK25' name DISK25;

Diskgroup altered.

SQL> select total_mb,free_mb,name,failgroup,path from v$asm_disk where group_number=1 order by failgroup;

TOTAL_MB FREE_MB NAME FAILGROUP PATH
---------- ---------- -------------------- ---------- ------------------------------
1024 558 DISK20 DG1_0008 G:ASMDISKDISK20
1024 560 DISK25 DG1_0008 G:ASMDISKDISK25
1024 96 DISK22 DG1_0009 G:ASMDISKDISK22

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------ ---------- ---------- ----------------------- --------------
NORMAL 3072 1214 1024 95

SQL>
SQL> alter diskgroup dg1 add failgroup DG1_0009 disk 'G:ASMDISKDISK26' name DISK26;

Diskgroup altered.

SQL> select total_mb,free_mb,name,failgroup,path from v$asm_disk where group_number=1 order by failgroup;

TOTAL_MB FREE_MB NAME FAILGROUP PATH
---------- ---------- -------------------- ---------- ------------------------------
1024 559 DISK25 DG1_0008 G:ASMDISKDISK25
1024 559 DISK20 DG1_0008 G:ASMDISKDISK20
1024 560 DISK26 DG1_0009 G:ASMDISKDISK26
1024 558 DISK22 DG1_0009 G:ASMDISKDISK22

SQL> select type,total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup where group_number=1;

TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------ ---------- ---------- ----------------------- --------------
NORMAL 4096 2236 1024 606

SQL>
--===================================
--上面分別又往failgroup裡新增了2塊1g大小的disk，REQUIRED_MIRROR_FREE_MB還是1024，我無法理解oracle
是如何考慮REQUIRED_MIRROR_FREE_MB的？
--====================================
--最後只能得出一個這樣的結論：要想不"浪費空間"，所謂的不浪費空間就是指REQUIRED_MIRROR_FREE_MB為0，
那麼在normal type的redundancy方式下，我們只能做2個failgroup，同時每個failgroup裡只能有1塊disk；
如果是high type的redundancy方式下，我覺得應該是3個failgroup，同時每個failgroup裡只能有1塊disk。