Blog

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;

Check the Size and free space in ASM Diskgroup

col name for a10
col percentage 999.99
SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.