USABLE_FILE_MB is showing negative values in V$ASM_DISK View
USABLE_FILE_MB in v$asm_diskgroup can be negative for a number of reasons. The most common reason is that the disk group does not have enough free space to meet the redundancy requirements of its files. For example, if a disk group is configured with NORMAL redundancy, then each file in the disk group must have two copies. If the disk group does not have enough free space to store two copies of all of its files, then the USABLE_FILE_MB value will be negative.
Another reason why USABLE_FILE_MB can be negative is if the disk group contains files that are in a FAILED state. FAILED files cannot be used to store data, so they are not counted in the USABLE_FILE_MB calculation.
Finally, USABLE_FILE_MB can also be negative if the disk group contains files that are being migrated to a different disk group. During the migration process, the files are not available for use, so they are not counted in the USABLE_FILE_MB calculation.
To fix a negative USABLE_FILE_MB value, you need to add more free space to the disk group. You can do this by adding new disks to the disk group, or by deleting files that are no longer needed.
If the negative USABLE_FILE_MB value is due to FAILED files, then you need to repair or replace the failed disks.
If the negative USABLE_FILE_MB value is due to file migration, then you need to wait for the migration to complete.
Here are some additional tips for managing disk group space:
- Monitor the free space in your disk groups regularly.
- Set up alerts so that you are notified when the free space in a disk group falls below a certain threshold.
- Use the ASMSPACE and ASMDISK views to identify disk groups and disks that are running low on space.
- Use the ASMSPACE ADVISOR tool to get recommendations on how to manage disk group space.
By following these tips, you can help to ensure that your disk groups always have enough free space to meet the needs of your database.