15 Awesome Oracle ASM Queries every DBA must know

Very Useful Oracle ASM queries for day to day activities for Oracle database administrator

HOW TO CLEAR ASM DISKS

The below dd command can be used to zero the disk

dd if=/dev/zero of= path of the disk bs= block size count=10000

QUERY TO FIND THE FILES IN USE BY AN ASM INSTANCE

col full_path format a50
 col full_alias_path format a50
 SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
 FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
 a.reference_index rindex FROM v$asm_alias a, v$asm_diskgroup g
 WHERE a.group_number = g.group_number)
 START WITH (mod(pindex, power(2, 24))) = 0 
 CONNECT BY PRIOR 
 rindex = pindex;

HOW TO ADD DISK

The below queries list down various ways to add the disks in the diskgroup

ALTER DISKGROUP DATA ADD DISK'/devices/hdisk'; 
ALTER DISKGROUP DATA ADD DISK'/devices/hdisk4';
ALTER DISKGROUP DATA ADD DISK'/devices/hdisk*'REBALANCE POWER 5 WAIT;
ALTER DISKGROUP DATA ADD DISK'/devices/hdisk5' NAME DATA5,'/devices/hdisk6' NAME DATA6,'/devices/hdisk7' NAME DATA7,'/devices/hdisk8' NAME DATA8,

ASM DISK INFORMATION

set pages 40000 lines 120
col NAME for a15
select GROUP_NUMBER DG#, name, ALLOCATION_UNIT_SIZE AU_SZ, STATE,TYPE, TOTAL_MB, FREE_MB, OFFLINE_DISKS from v$asm_diskgroup;

set pages 40000 lines 120
col PATH for a30
select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,PATH FROM V$ASM_DISK;

col PATH for a15
col DG_NAME for a15
col DG_STATE for a10
col FAILGROUP for a10
select dg.name dg_name, dg.state dg_state, dg.type, d.disk_number dsk_no,d.path, d.mount_status, d.FAILGROUP, d.statefrom v$asm_diskgroup dg, v$asm_disk dwhere dg.group_number=d.group_numberorder by dg_name, dsk_no;

REBALANCE INFORMATION

select GROUP_NUMBER, OPERATION, STATE, ACTUAL, SOFAR, EST_MINUTES from v$asm_operation

QUERY TO DETECT FILES IN AN ASM DISKGROUP BEFORE DROPPING

col full_path format a50
col full_alias_path format a50
SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,a.reference_index rindex 
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number
AND gname = 'MDDX1')START WITH (mod(pindex, power(2, 24))) = 0 
CONNECT BY PRIOR rindex = pindex;

QUERY TO DETERMINE WHAT DISKGROUPS EXIST AND HOW FULL THEY ARE

SELECT NAME,TOTAL_MB,USABLE_FILE_MB FROM V$ASM_DISKGROUP;

QUERY TO DETERMINE THE STATE AND BALANCE OF DISKGROUPS

Starting in 10.2 this can be easily done with one query

SELECT NAME,STATE,UNBALANCED FROM V$ASM_DISKGROUP;

QUERY TO DETERMINE THE STATE OF THE DISKS WITHIN A DISKGROUP

col name format a12
col path format a25
col mount_status format a7
col header_status format a12
col mode_status format a7
col state format a8
SELECT D.NAME, D.PATH, D.MOUNT_STATUS, D.HEADER_STATUS, D.MODE_STATUS, D.STATE FROM V$ASM_DISK D, V$ASM_DISKGROUP G WHERE G.NAME = '&1'AND D.GROUP_NUMBER = G.GROUP_NUMBER;

Hope you like this article on Oracle ASM queries. Please do provide the feedback

All the Oracle ASM related articles

Oracle ASM : Learn about Oracle Automatic Storage Management Concepts and Implementation
Oracle ASM disks : Check out how to prepare the OS disks for ASM and how to add them to ASM
Oracle ASM Diskgroups : Oracle ASM uses disk groups to store data files; an Oracle ASM disk group is a collection of disks that Oracle ASM manages as a unit.Learn how to create the ASM diskgroups
Oracle ASM Failure Groups and CSS
Oracle ASM Initialization Parameters
Oracle ASM Rebalance
Oracle ASM Metadata
migrate Oracle database from Non ASM to ASM storage : Learn how to migrate the Oracle Database from filesytem or raw devices to the ASM disk with less downtime
Oracle ASM best practice to add disk
https://docs.oracle.com/cd/E11882_01/server.112/e18951/asmcon.htm