diskgroup

--Instance diskgroup--

SELECT c.group_number , g.name , count(distinct instance_name) "No.of Instance Using"
from gv$asm_client c , gv$asm_diskgroup g
where g.group_number=c.group_number group by c.group_number, g.name order by c.group_number

With diskgroup as (
select a.group_number , header_status,MOUNT_STATUS, count(*), round ( sum(total_mb)/(1000) ) "TotalGB"
from v$asm_disk a group by header_status,a.group_number,MOUNT_STATUS )
select a.name , b.*
--from v$asm_diskgroup a,
from v$asm_diskgroup_stat a, diskgroup b
where a.group_number = b.group_number 

--Lunsize--

select NVL(a.name, '[CANDIDATE]') disk_group_name, b.os_mb "LunSize-MB", count(b.name) Luns, sum(os_mb)/(1000) OS_GB
from v$asm_diskgroup a, v$asm_disk b
where a.group_number = b.group_number group by a.name, b.os_mb order by 2,3

 --LUN details--
set pagesize 10000
set lin 400
col header_status format a30
col path format a30
col name format a30
select d.path,
g.name,
d.header_status,
d.total_mb
from
v$asm_diskgroup g,
v$asm_disk d
where
d.group_number=g.group_number
order by g.name,d.path,d.header_status;

 

 --Diskgroup usage--

 SELECT group_number, substr(name,1,7) db_name, name , state, total_mb/(1000) total_GB, free_mb/(1000) free_GB , USABLE_FILE_MB/total_mb *100 "Free%", COMPATIBILITY "ASM-COMP", database_compatibility "DB-COMPATIBLE", ALLOCATION_UNIT_SIZE "AU-MB"
FROM v$asm_diskgroup
WHERE name !='GRID' and state !='DISMOUNTED' UNION
SELECT group_number, substr(name,1,7) db_name, name , state, total_mb/(1000) total_gb, free_mb/(1000) free_gb, USABLE_FILE_MB/total_mb *100 "Free%", COMPATIBILITY "ASM-COMP", database_compatibility "DB-COMPATIBLE", ALLOCATION_UNIT_SIZE "AU-MB"
FROM v$asm_diskgroup
WHERE name ='GRID' and state !='DISMOUNTED' ORDER by 2, total_gb

 

--ASM Opr--

SELECT a.group_number, a.NAME, b.operation, b.state, b.POWER, b.est_minutes
FROM v$asm_diskgroup a, v$asm_operation b
WHERE a.group_number = b.group_number