Oracle : Query to find ASM Freespace with Redundancy

Below Query will show how much space is available to use incase of High or Normal Redundancy

TOTAL_MB:- Refers to Total Capacity of the Diskgroup
FREE_MB :- Refers to raw Free Space Available in Diskgroup in MB.

FREE_MB = (TOTAL_MB – (HOT_USED_MB + COLD_USED_MB))

REQUIRED_MIRROR_FREE_MB :- Indicates how much free space is required in an ASM disk group to restore redundancy after the failure of an ASM disk or ASM failure group.In exadata it is the disk capacity of one failure group.

USABLE_FILE_MB :- Indicates how much space is available in an ASM disk group considering the redundancy level of the disk group.

Its 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


Query to Run:

column total format 999,999 Heading "Total(G)"
column free format 999,999 Heading "Free (G)"
column Mirror_GB format 999,999 Heading "Space Used |for Mirroring(G)"
column Usable_GB format 999,999 Heading "Space Available |to Use(G)"
column pct format 999.0 Heading "% Free |in DG" 
column pct2 format 999.0 Heading "Real % Free |in DG" 
column type format a10
column name format a20
set linesize 200
set colsep '|'
prompt
Prompt "NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"
prompt
select name,type, TOTAL_MB/1024 total, FREE_MB/1024 free, REQUIRED_MIRROR_FREE_MB/1024 Mirror_GB, USABLE_FILE_MB/1024 Usable_GB ,100-((total_MB-FREE_MB)/total_mb)*100 pct, 100-((total_MB-USABLE_FILE_MB)/total_mb)*100 pct2  from v$asm_diskgroup;


Sample Output :

"NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"


                    |          |        |        |     Space Used |Space Available |% Free |Real % Free
NAME                |TYPE      |Total(G)|Free (G)|for Mirroring(G)|       to Use(G)|  in DG|       in DG
--------------------|----------|--------|--------|----------------|----------------|-------|------------
DATA1              |HIGH      | 260,496|  57,951|          14,472|          14,493|   22.2|         5.6
REDO1              |HIGH      |  65,124|  33,322|           3,618|           9,901|   51.2|        15.2