General Query for find ASM disk and diskgroup details

Note: All disks in the disk group must be of equal size.

Check rolling patch mode and patch level

SELECT SYS_CONTEXT('SYS_CLUSTER_PROPERTIES', 'CLUSTER_STATE') FROM DUAL;
SELECT SYS_CONTEXT('SYS_CLUSTER_PROPERTIES', 'CURRENT_PATCHLVL') FROM DUAL;


Check the disk group attributes

SELECT SUBSTR(dg.name,1,12) AS diskgroup, SUBSTR(a.name,1,24) AS name,
SUBSTR(a.value,1,24) AS value, read_only FROM V$ASM_DISKGROUP dg,
V$ASM_ATTRIBUTE a WHERE dg.name = 'DATA' AND dg.group_number = a.group_number
AND a.name NOT LIKE '%template%';


Check size of disk group

SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;


Check candidate disk present for add disk

SELECT name, header_status, path FROM V$ASM_DISK WHERE path LIKE '/devices/disk0%';


Check the compatibility of a disk group

SELECT name AS diskgroup, substr(compatibility,1,12) AS asm_compat,
substr(database_compatibility,1,12) AS db_compat FROM V$ASM_DISKGROUP;

DISKGROUP ASM_COMPAT DB_COMPAT
--------- ---------- ----------
DATA      18.0.0.0.0 18.0.0.0.0
DATA1     18.0.0.0.0 18.0.0.0.0

Check disks present in disk groups

SELECT SUBSTR(dg.name,1,16) AS diskgroup, SUBSTR(d.name,1,16) AS asmdisk,
d.mount_status, d.state, SUBSTR(d.failgroup,1,16) AS failgroup
FROM V$ASM_DISKGROUP dg, V$ASM_DISK d WHERE dg.group_number = d.group_number;

DISKGROUP ASMDISK MOUNT_S STATE  FAILGROUP
--------- ------- ------- ------ ---------
DATA      DATA_01 CACHED  NORMAL DATA_0008
DATA      DATA_02 CACHED  NORMAL DATA_0000
DATA      DATA_03 CACHED  NORMAL DATA_0004

Check stats of disks in disk groups

SELECT SUBSTR(dgs.name,1,10) AS diskgroup, SUBSTR(ds.name,1,10) AS asmdisk,
ds.mount_status, ds.state, ds.reads, ds.writes, ds.read_time, ds.write_time,
bytes_read, bytes_written
FROM V$ASM_DISKGROUP_STAT dgs, V$ASM_DISK_STAT ds
WHERE dgs.group_number = ds.group_number AND dgs.name = 'DATA';


Check disk group clients with V$ASM_CLIENT

SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance,
SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS software,
SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible
FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c
WHERE dg.group_number = c.group_number;