ASM Important Queries

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

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;