Query to find ASM Freespace with Redundancy

Below Query will show how much space is available to use in case 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