Usable_FILE_MB / REQUIRED_MIRROR_FREE_MB ASM negative value in Oracle

V$ASM_DISGROUP is the view used the check the available space and total space present in diskgroups:


SQL> select name, state, type, total_mb, free_mb, required_mirror_free_mb req_free, usable_file_mb use_mb from v$asm_diskgroup where name = 'DATA1';
NAME        STATE      TYPE    TOTAL_MB  FREE_MB REQ_FREE USE_MB
---------- ----------- ------ ---------- ------- -------- ------
DATA1       MOUNTED     NORMAL 15342      9454     5114    -386


Following are the column details:
REQUIRED_MIRROR_FREE_MB value in V$ASM_DISKGROUP indicates how much free space is required in an ASM disk group to restore redundancy after failure of an ASM disk or ASM failure group.

USABLE_FILE_MB value indicates how much space is available in an ASM disk group considering the redundancy level of the disk group.

Example:

If disk of our diskgroup fails then we need 5114 Mbyte disk size space.

But free space is only 9454 / 2 = 4727 MByte for Normal Redundancy.
We end up in missing 5114 – 4724 = 390 Mbytes which means Normal Redundancy can not maintained for this diskgroup after an ASM disk or ASM failgroup failure.

Its means we need more 390 MB space for disk recovery in normal redundancy.

Calculated as:

USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 2 –> For Normal Redundancy
USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 3 –> For High Redundancy

TOTAL_MB means total capacity of the diskgroup.

FREE_MB means the raw free space available in diskgroup in MB.

Calculated as:
FREE_MB = (TOTAL_MB – (HOT_USED_MB + COLD_USED_MB))