This short post covers some useful ASM queries and scripts.
chk_asm_mapping.sh
The following is a script i found somewhere on the web. Unfortunately it was not totally correct for newer asm libraries. The script itself:
/etc/init.d/oracleasm querydisk -d `/etc/init.d/oracleasm listdisks -d` |
cut -f2,10,11 -d" " | perl -pe 's/"(.*)".*\[(.*), *(.*)\]/$1 $2 $3/g;' |
while read v_asmdisk v_minor v_major
do
v_device=`ls -la /dev | grep " $v_minor, *$v_major " | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor, $v_major]"
done
When started it produces the following output:
ASM disk DISK001A based on /dev/sdc1 [8, 33] ASM disk DISK001B based on /dev/sdb1 [8, 17] ASM disk DISK002A based on /dev/sdd1 [8, 49] ASM disk DISK002B based on /dev/sde1 [8, 65] ASM disk DISK003A based on /dev/sdg1 [8, 97] ASM disk DISK003B based on /dev/sdf1 [8, 81]
Disk Group 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;
DG# NAME AU_SZ STATE TYPE TOTAL_MB FREE_MB OFFLINE_DISKS --- ----- ------- -------- ------ ------ ------- ------------- 1 DATA2 4194304 MOUNTED NORMAL 40952 4168 0 2 DATA 1048576 MOUNTED NORMAL 30717 25667 0
ASM Disk Information
set pages 40000 lines 120 col PATH for a30 select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE, PATH FROM V$ASM_DISK; DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE PATH ----------- ------- ------------ ------- -------- ------------- 0 CLOSED MEMBER ONLINE NORMAL ORCL:DISK001A 3 CLOSED MEMBER ONLINE NORMAL ORCL:DISK003B 1 CACHED MEMBER ONLINE NORMAL ORCL:DISK001B 2 CACHED MEMBER ONLINE NORMAL ORCL:DISK002A 3 CACHED MEMBER ONLINE NORMAL ORCL:DISK002B
Combined ASM Disk and ASM Diskgroup information
The following query combines ASM disk and diskgroup information. You can edit this query to suit your needs easily. If you use ASM files instead of disks you have to use v$asm_file instead of v$asm_disk. If you use ASM files you have to add v$asm_file to the query.
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.state from v$asm_diskgroup dg, v$asm_disk d where dg.group_number=d.group_number order by dg_name, dsk_no; DG_NAME DG_STATE TYPE DSK_NO PATH MOUNT_S FAILGROUP STATE -------- -------- ------ ------ ------------- ------- ---------- -------- DATA MOUNTED NORMAL 1 ORCL:DISK001B CACHED DISK001B NORMAL DATA MOUNTED NORMAL 2 ORCL:DISK002A CACHED DISK002A NORMAL DATA MOUNTED NORMAL 3 ORCL:DISK002B CACHED DISK002B NORMAL DATA2 MOUNTED NORMAL 1 ORCL:DISK003A CACHED DISK003A NORMAL DATA2 MOUNTED NORMAL 2 ORCL:DISK003B CACHED DISK003B NORMAL
Monitoring ASM disk operations
select GROUP_NUMBER, OPERATION, STATE, ACTUAL, SOFAR, EST_MINUTES from v$asm_operation;
If there is a operating going on (like rebalancing) the query will return some rows. For instance for our just added disk the query yields:
GROUP_NUMBER OPERA STAT ACTUAL SOFAR EST_MINUTES ------------ ----- ---- ---------- ---------- ----------- 2 REBAL RUN 1 49 16
