The REQUIRED_MIRROR_FREE_MB and the USABLE_FILE_MB are two very interesting columns in the V$ASM_DISKGROUP[_STAT] view. Oracle Support gets many questions about the meaning of those columns and how the values are calculated. I wanted to write about this, but I realised that I could not do it better than Harald van Breederode, so I asked him for permission to simply reference his write up. He agreed, so please have a look at his excellent post Demystifying ASM REQUIRED_MIRROR_FREE_MB and USABLE_FILE_MB.
How much space can I use
Now that the REQUIRED_MIRROR_FREE_MB and the USABLE_FILE_MB have been explained, I would like to add that the ASM does not prevent you from using all available space - half of the total space for a normal redundancy disk group and one third of the total space for a high redundancy disk group. But if you do fill up your disk group to the brim, there will be no room to grow or add any files, and in the case of a disk failure, there will be no room to restore the redundancy for some data - until the failed disk is replaced and the rebalance completed.
Exadata with ASM version 11gR2
In Exadata with ASM version 11.2 the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest failgroup [1] in the disk group. To demonstrate, let's look at an Exadata system with ASM version 11.2.0.4.
As in most Exadata installations, I have three disk groups.
[grid@exadb01 ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.4.0 Production on [date]
SQL> select NAME, GROUP_NUMBER from v$asm_diskgroup_stat;
NAME GROUP_NUMBER
--------- ------------
DATA 1
DBFS_DG 2
RECO 3
SQL>
For the purpose of this example, we will look at the disk group DBFS_DG. Normally there would be 10 disks per failgroup for disk group DBFS_DG. I have dropped few disks to demonstrate that the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest failgroup.
SQL> select FAILGROUP, count(NAME) "Disks", sum(TOTAL_MB) "MB"
from v$asm_disk_stat
where GROUP_NUMBER=2
group by FAILGROUP
order by 3;
FAILGROUP Disks MB
---------- ---------- ----------
EXACELL04 7 180096
EXACELL01 8 205824
EXACELL02 9 231552
EXACELL03 10 257280
SQL>
Note that the total space in the largest failgroup is 257280 MB.
Finally, we see that the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest failgroup:
SQL> select NAME, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB
from v$asm_diskgroup_stat
where GROUP_NUMBER=2;
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
---------- ---------- ---------- ----------------------- --------------
DBFS_DG 874752 801420 257280 272070
SQL>
The ASM calculates the USABLE_FILE_MB using the following formula:
USABLE_FILE_MB = (FREE_MB - REQUIRED_MIRROR_FREE_MB) / 2
Which gives 272070 MB.
[1] In Exadata, all failgroups are typically of the same size
Exadata with ASM version 12cR1
In Exadata with ASM version 12cR1, the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest disk [2] in the disk group.
Here is an example from an Exadata system with ASM version 12.1.0.2.0.
[grid@exadb03 ~]$ sqlplus / as sysasm
SQL*Plus: Release 12.1.0.2.0 Production on [date]
SQL> select NAME, GROUP_NUMBER from v$asm_diskgroup_stat;
NAME GROUP_NUMBER
-------- ------------
DATA 1
DBFS_DG 2
RECO 3
SQL>
Again, I have the failgroups of different sizes in the disk group DBFS_DG:
SQL> select FAILGROUP, count(NAME) "Disks", sum(TOTAL_MB) "MB"
from v$asm_disk_stat
where GROUP_NUMBER=2
group by FAILGROUP
order by 3;
FAILGROUP Disks MB
---------- ---------- ----------
EXACELL05 8 238592
EXACELL07 9 268416
EXACELL06 10 298240
SQL>
The total space in the largest failgroup is 298240 MB, but this time the REQUIRED_MIRROR_FREE_MB is reported as 29824 MB:
SQL> select NAME, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB
from v$asm_diskgroup_stat
where GROUP_NUMBER=2; 2 3
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
---------- ---------- ---------- ----------------------- --------------
DBFS_DG 805248 781764 29824 375970
SQL>
As we can see, that is the size of the largest disk, in the diskgroup:
SQL> select max(TOTAL_MB) from v$asm_disk_stat where GROUP_NUMBER=2;
MAX(TOTAL_MB)
-------------
29824
SQL>
The USABLE_FILE_MB was calculated using the same formula:
USABLE_FILE_MB = (FREE_MB - REQUIRED_MIRROR_FREE_MB) / 2
Which gives 375970 MB.
[2] In Exadata, all disks are typically of the same size
Conclusion
The REQUIRED_MIRROR_FREE_MB and the USABLE_FILE_MB are intended to assist the DBAs and storage administrators with planning the disk group capacity and redundancy. The values are reported, but not enforced by the ASM.
In Exadata with ASM version 12cR1, the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest disk in the disk group. This is by design, to reflect the experience from the field, which shows that the disks are the components that are failing, not the whole storage cells.
In Exadata with ASM version 12cR1, the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest disk in the disk group. This is by design, to reflect the experience from the field, which shows that the disks are the components that are failing, not the whole storage cells.
