ASM disk statistics

Job details

Parameters

Job Summary

• Purpose: The job serves to monitor and report on the statistics and statuses of ASM disks in an Oracle database setup, ensuring that data storage systems maintain good health and operational status.
• Why: Monitoring ASM disks is crucial as it allows database administrators to proactively handle potential issues such as disk failures, configuration errors, or performance bottlenecks. The automated checks and reports generated by this job help in maintaining the integrity and efficiency of data management, which are vital for business operations depending on these databases.
• Manual checking: To manually check the ASM disk statistics, you can use the following SQL commands:

select name, path, group_number, disk_number, mount_status, header_status, mode_status, state, reads, writes, read_errs, write_errs, total_mb, free_mb from v$asm_disk_stat;

Implementation Details

Implementation of the monitoring job involves the creation of tables (“dbw_asm_disk_stat” and “dbw_asm_disk_stat_history”) to hold current and historical data concerning ASM disk activities. There’s also the implementation of the “ASM_disk_statistics_check” procedure which pulls data from Oracle’s ASM views and populates these tables.

The procedure includes logic to:

• Ignore disks based on certain statuses or if specific devices are mentioned.
• Record changes between the current and last snapshot of disk statistics.
• Manage counters for execution and recognition of “old” historical data beyond a specified threshold.
• Generate alerts if discrepancies or signs of potential issues are found (e.g., disk not found, incorrect mode status).

Reporting

The job includes a reporting template structured into different chapters and presentations. This process is designed to represent data effectively for end-users by providing valuable insights into ASM disk performance and safety.

Report Sections

• “ASM disk information”: Tabulates basic details about each ASM disk, including status and statistics.
• “ASM disk read rate”: Charts the read operations per second per disk, providing insights into read performance trends over time.
• “ASM disk write rate”: Similarly charts write operations, enabling trend analysis and detection of anomalies in disk write behaviors.
• “ASM disk read time”: Represents the average read time, helping in assessing latency and performance issues.
• “ASM disk write time”: Focuses on write times, crucial for performance tuning and diagnostics.

SQL Commands for Reports

The select commands for each section (e.g. the basic disk info and performance metrics) pull data from the above-mentioned tables, allowing dynamic generation of historical and real-time performance data.

Dependencies and Compatibility

The job requires specific Oracle privileges (e.g., select on v$asm_disk_stat) and is compatible with Oracle database environments that use Automatic Storage Management (ASM) for disk management. Dependencies include proper schema permissions and operational ASM setups. Clean-up routines and failure management scripts ensure the accuracy and cleanliness of logged data, reinforcing the reliability of the monitoring process.

Overall, this dbWatch Control Center job represents a comprehensive approach to ASM disk monitoring, leveraging SQL procedures and custom reporting to maintain database health and performance.