Home > Oracle in general > Useful ASM scripts and queries

Useful ASM scripts and queries

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
Categories: Oracle in general Tags:
  1. chi
    November 25th, 2010 at 01:58 | #1

    The commands are really helpfull, Thank you.

  1. October 7th, 2009 at 08:41 | #1
  2. October 21st, 2009 at 07:04 | #2