The views expressed on this blog are my own and do not necessarily reflect the views of Oracle

September 27, 2014

REQUIRED_MIRROR_FREE_MB

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.