Storage Administration - Oracle Exadata Recipes: A Problem-Solution Approach - page 252

sum(p.inactive)/2
"Inactive"
from
v$asm_diskgroup g ,
(
select
x.grp grp,
x.disk disk,
sum(x.active) cnt,
greatest(sum(x.total_mb/d.total_mb),0.0001) pspace,
d.failgroup fgrp,
count(*)-sum(x.active) inactive
from
v$asm_disk d ,
(
select
y.grp grp,
y.disk disk,
z.total_mb*y.active_kfdpartner total_mb,
y.active_kfdpartner active
from
x$kfdpartner y,
v$asm_disk z
where
y.number_kfdpartner = z.disk_number and
y.grp = z.group_number
) x
where
d.group_number = x.grp and
d.disk_number = x.disk and
d.group_number <> 0 and
d.state = 'NORMAL' and
d.mount_status = 'CACHED'
group by
x.grp, x.disk, d.failgroup
) p
where
g.group_number = p.grp
group by g.name
/
Partner Partner Inactive
Diskgroup Count Space % Failgroup Partnership
Name Imbalance Imbalance Count Count
---------- ---------- --------- --------- ------------
DATA_CM01 0 .0 3 0
DBFS_DG 1 12.5 3 0
RECO_CM01 0 .0 3 0
SDATA_CM01 0 .0 3 0
SQL>