Oracle : ASM Important Queries

Rebalance status :

column “Diskgroup” format A30
column “Imbalance” format 99.9 Heading “Percent|Imbalance”
column “Variance” format 99.9 Heading “Percent|Disk Size|Variance”
column “MinFree” format 999.99 heading “Minimum|Percent|Free”
column “DiskCnt” format 9999 Heading “Disk|Count”
column “Type” format A10 Heading “Diskgroup|Redundancy”

SELECT g.name “Diskgroup”,
100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) “Imbalance”,
100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) “Variance”,
100*(min(d.free_mb/d.total_mb)) “MinFree”,
count(*) “DiskCnt”,
g.type “Type”
FROM v$asm_disk d, v$asm_diskgroup g
WHERE d.group_number = g.group_number and
d.group_number <> 0 and
d.state = ‘NORMAL’ and
d.mount_status = ‘CACHED’
GROUP BY g.name, g.type;

Checking ASM free space:

set linesize 300
col name for a30

select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB, FREE_MB,(FREE_MB/TOTAL_MB)*100 PCt_FREE from v$asm_diskgroup order by PCt_FREE ;
select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB/1024 TOTAL_GB ,FREE_MB/1024 FREE_GB,(FREE_MB/TOTAL_MB)*100 PCt_FREE from v$asm_diskgroup order by PCt_FREE ;

what instances connected to ASM :

set linesize 300
col instance_name for a15
col SOFTWARE_VERSION for a15
col COMPATIBLE_VERSION for a15
SELECT * FROM V$ASM_CLIENT;

Checking candidate disks :

BREAK ON report ON disk_group_name SKIP 1
set pagesize 50
set linesize 300
col DISK_GROUP_NAME for a20
col DISK_FILE_PATH for a50
col DISK_FILE_FAIL_GROUP for a40
col DISK_FILE_NAME for a25
SELECT
NVL(a.name, ‘[CANDIDATE]’) disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
FROM
v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name;

Checking ASM compatibility Version :

set linesize 300
select group_number, name,compatibility, database_compatibility from v$asm_diskgroup;

Disk group details :

set lines 192
set pages 2000
col GROUP_NUMBER format 9999 heading”GRP_NO”
col “GRP_NAME” format a24
col “DSK_NAME” format a24
col path format a80
col ALLOC_MB format 999,999,999
col FREE_MB format 999,999,999
col PCT_FREE format 999.00

select d.GROUP_NUMBER,g.name “GRP_NAME”,d.name “DSK_NAME”,d.path,d.OS_MB “ALLOC_MB”,d.free_MB “FREE_MB”,d.free_MB*100/d.OS_MB “PCT_FREE”
from V$asm_disk d , v$asm_diskgroup g where d.GROUP_NUMBER=g.GROUP_NUMBER and g.name like ‘&Diskgroup_Name’;