ASM Disk Group Information and Capacity Analysis (vasmdgrp.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 queries Oracle views to provide database information via the vasmdgrp.sql script.

The Script

set lines 132
col group_number form 99 head 'GRP'
col name format a10
col ALLOCATION_UNIT_SIZE form 999,999,999 head 'ALLOCATION|UNIT|SIZE'
col TOTAL_MB form 999,999,999 head 'TOTAL_MB'
col FREE_MB form 999,999,999 head 'FREE_MB'
col USABLE_FILE_MB form 999,999,999 head 'USABLE|FILE|MB'
col OFFLINE_DISKS form 999,999,999 head 'OFFLINE|DISKS'
col capacity form 990 head 'Cpcty'

select
   GROUP_NUMBER                   ,
   NAME                           ,
   -- SECTOR_SIZE                    ,
   -- BLOCK_SIZE                     ,
   ALLOCATION_UNIT_SIZE           ,
   STATE                          ,
   TYPE                           ,
   TOTAL_MB                       ,
   FREE_MB                        ,
   -- REQUIRED_MIRROR_FREE_MB        ,
   USABLE_FILE_MB                 ,
   (total_mb - usable_file_mb) / total_mb *100 capacity,
   OFFLINE_DISKS                  ,
   UNBALANCED
   --, COMPATIBILITY                  ,
   -- DATABASE_COMPATIBILITY
from v$asm_diskgroup
order by
1,2
/

What This Script Does

This script provides comprehensive information about ASM (Automatic Storage Management) disk groups, including capacity details, utilization percentages, and operational status. It’s essential for ASM storage monitoring, capacity planning, and ensuring optimal storage performance in Oracle database environments.

Key Features

• Capacity Analysis: Shows total, free, and usable space in MB
• Utilization Calculation: Automatic capacity percentage calculation
• Status Monitoring: Displays state and balance information
• Operational Insight: Shows offline disks and unbalanced status
• Clean Formatting: Well-formatted output for easy reading

Usage

@vasmdgrp.sql

No parameters required - shows all ASM disk groups.

Required Privileges

SELECT on V$ASM_DISKGROUP

Note: This script must be run while connected to an ASM instance or a database instance with ASM access.

Sample Output

GRP NAME       ALLOCATION   STATE    TYPE     TOTAL_MB   FREE_MB    USABLE     Cpcty OFFLINE UNBALANCED
                UNIT                                                 FILE                DISKS
--- ---------- ----------- -------- -------- ---------- ---------- ---------- ----- ------- ----------
  1 DATA         1,048,576 MOUNTED  EXTERN    2,097,152  1,048,576  1,048,576    50       0 N
  2 FRA          1,048,576 MOUNTED  NORMAL    1,048,576    524,288    262,144    75       0 N
  3 RECO         1,048,576 MOUNTED  NORMAL      524,288    419,430    209,715    20       0 N
  4 TEMP         1,048,576 MOUNTED  EXTERN      262,144    196,608    196,608    25       0 N
  5 REDO         1,048,576 MOUNTED  HIGH      1,048,576    943,718    314,573    70       0 N

Key Output Columns

• GRP: ASM disk group number
• NAME: Disk group name
• ALLOCATION UNIT SIZE: Size of allocation units in bytes
• STATE: Current state (MOUNTED, DISMOUNTED, etc.)
• TYPE: Redundancy type (EXTERN, NORMAL, HIGH)
• TOTAL_MB: Total disk group capacity in megabytes
• FREE_MB: Available free space in megabytes
• USABLE FILE MB: Space actually available for files (after redundancy)
• Cpcty: Capacity utilization percentage
• OFFLINE DISKS: Number of offline disks in the group
• UNBALANCED: Whether disk group needs rebalancing (Y/N)

Understanding ASM Disk Groups

Redundancy Types

EXTERNAL (EXTERN)

• No ASM Mirroring: Relies on external storage redundancy
• Space Efficiency: 100% of raw space available
• Use Case: Storage arrays with built-in redundancy

NORMAL

• Two-Way Mirroring: ASM maintains two copies of data
• Space Efficiency: ~50% of raw space available
• Use Case: Standard production environments

HIGH

• Three-Way Mirroring: ASM maintains three copies of data
• Space Efficiency: ~33% of raw space available
• Use Case: Critical systems requiring maximum protection

State Information

MOUNTED

• Disk group is online and available
• Normal operational state

DISMOUNTED

• Disk group is offline
• Not available for database use

DISMOUNTING/MOUNTING

• Transitional states during operations
• May indicate maintenance activities

Capacity Analysis

Utilization Monitoring

-- Capacity thresholds to monitor:
-- < 70%: Normal operation
-- 70-85%: Monitor closely
-- 85-95%: Plan expansion
-- > 95%: Critical - immediate action needed

Space Calculations

-- Understanding the space metrics:
-- TOTAL_MB: Raw disk space allocated to group
-- FREE_MB: Unallocated space within group
-- USABLE_FILE_MB: Space available for new files (considers redundancy)
-- Capacity%: (TOTAL_MB - USABLE_FILE_MB) / TOTAL_MB * 100

Performance Monitoring

Rebalancing Status

• UNBALANCED = ‘Y’: Disk group needs rebalancing
• UNBALANCED = ‘N’: Disk group is balanced
• Monitor After: Disk additions, replacements, or failures

Offline Disk Detection

• OFFLINE_DISKS > 0: Failed or maintenance mode disks
• Impact: Reduced redundancy and performance
• Action: Investigate and replace failed disks

Common Use Cases

1. Capacity Planning

   • Monitor space utilization trends
   • Plan for storage expansion
   • Identify growth patterns

2. Performance Monitoring

   • Check for unbalanced disk groups
   • Monitor offline disk impacts
   • Validate storage configuration

3. Health Checks

   • Regular ASM storage assessment
   • Proactive issue identification
   • Capacity threshold monitoring

4. Maintenance Planning

   • Identify disk groups needing rebalancing
   • Plan storage maintenance windows
   • Assess redundancy status

Advanced Analysis

Capacity Trending

-- Run periodically and compare results:
-- Track capacity growth over time
-- Identify rapidly growing disk groups
-- Plan for future storage needs

Redundancy Validation

-- Verify redundancy configuration:
-- NORMAL groups should have even number of failure groups
-- HIGH groups should have adequate failure groups for 3-way mirroring
-- EXTERN groups rely on external redundancy

Performance Impact Assessment

-- Correlate with performance metrics:
-- High capacity utilization may impact performance
-- Unbalanced groups can cause I/O hotspots
-- Offline disks reduce available bandwidth

Troubleshooting

High Capacity Utilization

Symptoms: Capacity > 85% Actions:

• Plan immediate storage expansion
• Review space usage patterns
• Consider file cleanup or archival

Unbalanced Disk Groups

Symptoms: UNBALANCED = ‘Y’ Actions:

-- Initiate manual rebalancing
ALTER DISKGROUP disk_group_name REBALANCE;

Offline Disks

Symptoms: OFFLINE_DISKS > 0 Actions:

• Check ASM alert log for disk failures
• Investigate hardware issues
• Plan disk replacement

Incorrect Redundancy

Symptoms: Unexpected TYPE values Actions:

• Verify storage requirements
• Check disk group configuration
• Plan redundancy adjustments if needed

Integration with Monitoring

Automated Monitoring

-- Create alerts for:
-- Capacity > 85%
-- UNBALANCED = 'Y'
-- OFFLINE_DISKS > 0
-- STATE != 'MOUNTED'

Trending Analysis

-- Track over time:
-- Capacity growth rates
-- Free space trends
-- Performance correlation

Related Scripts

• gvasmdsk.sql - ASM disk performance details
• vasmdsk.sql - Individual ASM disk information
• vasmdskg.sql - ASM disk group membership
• vasm_disk.sql - Comprehensive ASM disk metrics

Best Practices

1. Regular Monitoring

   • Run weekly during normal operations
   • Monitor daily during high growth periods
   • Include in automated health checks

2. Capacity Thresholds

   • Set alerts at 75% capacity
   • Plan expansion at 80% capacity
   • Emergency procedures at 90% capacity

3. Balance Maintenance

   • Monitor UNBALANCED status regularly
   • Schedule rebalancing during maintenance windows
   • Plan for performance impact during rebalancing

4. Redundancy Verification

   • Validate TYPE matches requirements
   • Ensure adequate failure group distribution
   • Test recovery procedures regularly