Negative Values of USABLE_FILE_MB

When you lose multiple disks from multiple failure groups, then you could lose both the primary and the redundant copies of your data. In addition, if you do not have enough capacity to restore redundancy, then Oracle ASM can continue to operate. However, if another disk fails, then the system may not be able to tolerate additional failures.
 
The V$ASM_DISKGROUP view contains the following columns that contain information to help you manage capacity:
 
SQL> SELECT name, type, total_mb, free_mb, required_mirror_free_mb,
     usable_file_mb FROM V$ASM_DISKGROUP;

NAME         TYPE     TOTAL_MB    FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------ ------ ---------- ---------- ----------------------- --------------
DATA         NORMAL      4194304   189923328   398204   2260992  
-931394

 

• REQUIRED_MIRROR_FREE_MB indicates the amount of space that must be available in a disk group to restore full redundancy after the worst failure that can be tolerated by the disk group without adding additional storage. This requirement ensures that there are sufficient failure groups to restore redundancy. Also, this worst failure refers to a permanent failure where the disks must be dropped, not the case where the disks go offline and then back online.
  The amount of space displayed in this column takes the effects of mirroring into account. The value is computed as follows:
• A normal redundancy disk group with more than two REGULAR failure groups
  The value is the total raw space for all of the disks in the largest failure group. The largest failure group is the one with the largest total raw capacity. For example, if each disk is in its own failure group, then the value would be the size of the largest capacity disk.
• A high redundancy disk group with more than three REGULAR failure groups
  The value is the total raw space for all of the disks in the two largest failure groups. 
• USABLE_FILE_MB indicates the amount of free space, adjusted for mirroring, that is available for new files to restore redundancy after a disk failure. USABLE_FILE_MB is computed by subtracting REQUIRED_MIRROR_FREE_MB from the total free space in the disk group and then adjusting the value for mirroring
• TOTAL_MB is the total usable capacity of a disk group in megabytes. The calculations for data in this column take the disk header overhead into consideration. The disk header overhead depends on the number of Oracle ASM disks and Oracle ASM files. This value is typically about 1% of the total raw storage capacity.
• FREE_MB is the unused capacity of the disk group in megabytes, without considering any data imbalance.

USABLE_FILE_MB calculation:
(FREE_MB - REQUIRED_MIRROR_FREE_MB) / 2 = USABLE_FILE_MB => Normal redundancy
(FREE_MB - REQUIRED_MIRROR_FREE_MB) / 3 = USABLE_FILE_MB => High redundancy

Due to the relationship between FREE_MB, REQUIRED_MIRROR_FREE_MB, and USABLE_FILE_MB, USABLE_FILE_MB can become negative. Although this is not necessarily a critical situation, it does mean that:

• Depending on the value of FREE_MB, you may not be able to create new files.
• The next failure might result in files with reduced redundancy.

If USABLE_FILE_MB becomes negative, it is strongly recommended that you add more space to the disk group as soon as possible.