ASM Disk Group Information (vasmdskg.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides comprehensive information about ASM disk groups including configuration parameters, redundancy settings, and capacity utilization. Essential for monitoring ASM disk group health and planning capacity expansion.

The Script

rem  vasmdskg.sql
rem
ttitle 'ASM Disk Group Allocations'
rem
set lines 132
rem
col group_number format 999 heading 'GRP|NBR'
col name format a25
col sector_size format 999999 heading 'SECTOR|SIZE_BYTES'
col block_size format 999999 heading 'BLOCK|SIZE_KB'
col allocation_unit_size format 9,999,999,999 heading 'ALLOC_UNIT|SIZE_KB'
col type format a10 heading 'REDUNDANCY|TYPE'
col total_mb format 9999,999,999 heading 'TOTAL|CAPACITY_MB'
col free_mb format 9999,999,999 heading 'FREE|CAPACITY_MB'
rem
select
       GROUP_NUMBER
      ,NAME
      ,SECTOR_SIZE
      ,BLOCK_SIZE/1024 as BLOCK_SIZE
      ,ALLOCATION_UNIT_SIZE/1024 as ALLOCATION_UNIT_SIZE
      ,STATE
      ,TYPE
      ,TOTAL_MB
      ,FREE_MB
 from v$asm_diskgroup
where nvl(upper(name),'null') like upper(nvl('&diskgroup_nm', '%'))
/
rem
rem set linesize 80
ttitle off

Parameters

The script prompts for:

• diskgroup_nm: Disk group name pattern to filter results (optional, defaults to all disk groups)

Usage

-- Basic usage (will prompt for disk group name)
@vasmdskg.sql

-- Example values when prompted:
-- diskgroup_nm: DATA
-- diskgroup_nm: (empty for all disk groups)

Sample Output

                               ASM Disk Group Allocations

GRP                       SECTOR  BLOCK    ALLOC_UNIT           REDUNDANCY     TOTAL         FREE
NBR NAME                  SIZE    SIZE_KB     SIZE_KB STATE      TYPE       CAPACITY_MB  CAPACITY_MB
--- ------------------------- ------ ------- ----------- ---------- ---------- ----------- -----------
  1 DATA                     512       4           1,024 MOUNTED    EXTERN      30,720      15,350
  2 FRA                      512       4           1,024 MOUNTED    NORMAL      40,960      36,090
  3 REDO                     512       4           1,024 MOUNTED    NORMAL       4,096       2,180
  4 GRID                     512       4           1,024 MOUNTED    EXTERN       2,048       1,850

Key Information

• Configuration: Sector size, block size, and allocation unit size
• Redundancy: External (no mirroring), Normal (2-way), or High (3-way)
• State: MOUNTED, DISMOUNTED, or other operational states
• Capacity: Total and free space in MB
• Group Numbers: Unique identifiers for each disk group

Redundancy Types

• EXTERN: No ASM mirroring (relies on external storage redundancy)
• NORMAL: 2-way mirroring within ASM
• HIGH: 3-way mirroring within ASM