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;