- 10 Mar, 2021
- 1 Comment
- 2 Mins Read
ASM Important Queries
QUERY TO FIND THE FILES IN USE BY AN ASM INSTANCE
HOW TO ADD DISK
ASM DISK INFORMATION
REBALANCE INFORMATION
QUERY TO DETECT FILES IN AN ASM DISKGROUP BEFORE DROPPING
QUERY TO DETERMINE WHAT DISKGROUPS EXIST AND HOW FULL THEY ARE
QUERY TO DETERMINE THE STATE AND BALANCE OF DISKGROUPS
Starting in 10.2 this can be easily done with one query
QUERY TO DETERMINE THE STATE OF THE DISKS WITHIN A DISKGROUP
1 2 3 4 5 6 7 8 9 10 | col full_path format a50col full_alias_path format a50SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_pathFROM (SELECT g.name gname, a.parent_index pindex, a.name aname,a.reference_index rindex FROM v$asm_alias a, v$asm_diskgroup gWHERE a.group_number = g.group_number)START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex; |
1 2 3 4 | 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, |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | set pages 40000 lines 120col NAME for a15select 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 120col PATH for a30select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,PATH FROM V$ASM_DISK;col PATH for a15col DG_NAME for a15col DG_STATE for a10col FAILGROUP for a10select 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; |
1 | select GROUP_NUMBER, OPERATION, STATE, ACTUAL, SOFAR, EST_MINUTES from v$asm_operation |
1 2 3 4 5 6 7 8 9 | col full_path format a50col full_alias_path format a50SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_pathFROM (SELECT g.name gname, a.parent_index pindex, a.name aname,a.reference_index rindexFROM v$asm_alias a, v$asm_diskgroup gWHERE a.group_number = g.group_numberAND gname = 'MDDX1')START WITH (mod(pindex, power(2, 24))) = 0CONNECT BY PRIOR rindex = pindex; |
1 | SELECT NAME,TOTAL_MB,USABLE_FILE_MB FROM V$ASM_DISKGROUP; |
1 | SELECT NAME,STATE,UNBALANCED FROM V$ASM_DISKGROUP; |
1 2 3 4 5 6 7 8 | col name format a12col path format a25col mount_status format a7col header_status format a12col mode_status format a7col state format a8SELECT 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
1 2 3 | col name for a10col percentage 999.99SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup; |