Redgate Monitor: Monitoring Oracle ASM with Custom Metrics
1. Introduction
Oracle Automatic Storage Management (ASM) is Oracle's built-in volume manager and file system, designed specifically for Oracle database files. Instead of relying on a separate volume manager or file system, ASM manages disk groups, automatically stripes and mirrors data across disks, and rebalances storage when disks are added or removed.
For DBAs and operations teams running Oracle databases, ASM is a critical layer of the storage stack. Failures or capacity exhaustion at the ASM level can bring down databases entirely. Despite its importance, ASM health is often not surfaced in general-purpose monitoring tools it requires specific queries against Oracle's internal ASM dynamic views.
Redgate Monitor's Custom Metrics feature bridges this gap. It lets you execute SQL queries against your monitored Oracle instances on a scheduled basis, collect the results as time-series data, visualize them on the Analysis page, and raise alerts when values cross thresholds you define. This guide walks you through building a complete ASM monitoring solution using that feature.
2. What is Oracle ASM?
ASM organizes physical disks into disk groups. Each disk group can be configured with one of several redundancy modes:
- External Redundancy: No ASM mirroring; relies on hardware RAID.
- Normal Redundancy: Two-way mirroring ASM maintains two copies of every extent.
- High Redundancy: Three-way mirroring three copies of every extent.
- Flex / Extended Redundancy: Available from Oracle 12.2 onward; supports mixed redundancy within one disk group.
Within a disk group, individual disks (which may be raw devices, LUNs, or ASM Filter Driver devices) are grouped into failure groups. ASM ensures that mirrored extents never reside in the same failure group, so a single disk or controller failure does not cause data loss.
Key Oracle views used to monitor ASM are:
V$ASM_DISKGROUP– disk group name, type, state, total and free spaceV$ASM_DISK– individual disk status, reads, writes, I/O times, errorsV$ASM_OPERATION– active rebalance operationsV$ASM_CLIENT– databases currently using each disk groupV$ASM_FILE– individual ASM files within disk groups
3. Custom Metrics in Redgate Monitor
3.1 Overview
The Custom Metrics feature in Redgate Monitor lets you supply an Oracle SQL query that returns a single numeric value. Redgate Monitor runs that query against your chosen instances at a frequency you control (from every minute to once a day), stores the results, and plots them as a time series on the Analysis page.
You can also attach an alert to any custom metric. When the collected value crosses a threshold, you set (with optional duration requirements, e.g., the value must exceed the threshold for three consecutive collections before alerting), Redgate Monitor raises an alert that appears in the Alerts feed and can trigger email or other notifications.
3.2 Key requirements for the query
- Single numeric return: The query must return exactly one row containing one numeric column, or
NULL. If no rows are returned, Redgate Monitor recordsNULLfor that collection interval. - Oracle SQL: Queries run against Oracle instances use Oracle SQL, not T-SQL. Standard Oracle SQL and PL/SQL expressions are supported within a
SELECTstatement. - Low impact: Custom metrics run on a schedule against production instances. Always test queries for execution time and resource usage before deploying at scale. Target a sub-second execution time for the most frequently collected metrics.
- Correct instance context: ASM views (V$ASM_*) are accessible from the Oracle database instance. If you are connecting to a CDB, ensure the query targets the correct container.
4. Required Permissions
The Redgate Monitor monitoring user needs SELECT privilege on the relevant V$ASM_* views. Grant these from a DBA account:
|
1
2
3
4
5
6
7
8
9
|
-- Grant access to ASM monitoring views
GRANT SELECT ON V_$ASM_DISKGROUP TO redgate_monitor_user;
GRANT SELECT ON V_$ASM_DISK TO redgate_monitor_user;
GRANT SELECT ON V_$ASM_OPERATION TO redgate_monitor_user;
GRANT SELECT ON V_$ASM_CLIENT TO redgate_monitor_user;
-- Alternative: grant the SYSASM role (provides full ASM access)
-- Only appropriate if the monitoring user connects to the ASM instance
-- GRANT SYSASM TO redgate_monitor_user;
|
Note: If your environment uses an ASM instance separate from the database instance, the V$ASM_* views are available on the database instance via a proxy connection but may require the SELECT_CATALOG_ROLE or explicit grants as shown above. Check with your Oracle DBA if views appear inaccessible. |
5. Recommended ASM Custom Metrics
The table below summarizes the metrics covered in this guide. Each is described in detail in the sections that follow.
| Metric Name | Oracle View | What It Tracks | Alert Threshold |
|---|---|---|---|
| Disk Group Free Space % | V$ASM_DISKGROUP | % usable space remaining per disk group | Alert High < 15% |
| Disk Group State | V$ASM_DISKGROUP | Mounted / dismounted / error state | Alert High ≠ MOUNTED |
| Disk Error Count | V$ASM_DISK | Cumulative read/write errors per disk | Alert High > 0 |
| ASM Rebalance Progress | V$ASM_OPERATION | Active rebalance % complete | Informational |
| Disk I/O Response Time (ms) | V$ASM_DISK | Average read response time per disk | Alert Medium > 20ms |
5.1 Disk Group Free Space Percentage
Purpose
ASM disk groups that run out of free space cause Oracle to halt I/O, resulting in database outages. This metric provides an early warning by tracking the percentage of usable space remaining in each disk group.
How to deploy this as multiple metrics
Because a custom metric must return a single numeric value, create one instance of this metric per disk group. Parameterize by substituting the disk group name in the WHERE clause.
Oracle SQL
|
1
2
3
4
5
6
7
8
9
|
-- Returns usable free space as a percentage of total usable size
-- Replace <DATA> with your target disk group name
SELECT ROUND(
(USABLE_FILE_MB / NULLIF(TOTAL_MB, 0)) * 100,
2
) AS free_pct
FROM V$ASM_DISKGROUP
WHERE NAME = '<DATA>'
AND STATE = 'MOUNTED';
|
Configuration settings
- Metric name: ASM Disk Group Free Space % DATA (substitute disk group name)
- Unit: Percent (%)
- Collection frequency: Every 5 minutes
- Alert threshold: High alert when value drops below 15; Medium alert below 25
- Alert duration: Trigger after 1 collection (space loss can be rapid)
Tip: USABLE_FILE_MB already accounts for mirroring overhead in Normal and High redundancy disk groups. It represents the space available for additional database files, which use it rather than FREE_MB for accurate capacity planning. |
5.2 Disk Group Mount State
Purpose
An ASM disk group that is not in the MOUNTED state is inaccessible to databases. This metric detects dismounted or errored disk groups immediately, returning 1 if the target disk group is healthy and 0 if it is not.
Oracle SQL
|
1
2
3
4
5
|
-- Returns 1 if disk group is MOUNTED, 0 otherwise
-- Replace <DATA> with your target disk group name
SELECT CASE WHEN STATE = 'MOUNTED' THEN 1 ELSE 0 END AS is_mounted
FROM V$ASM_DISKGROUP
WHERE NAME = '<DATA>';
|
Configuration settings
- Metric name: ASM Disk Group State DATA
- Unit: Boolean (1 = Mounted, 0 = Not mounted)
- Collection frequency: Every 1 minute
- Alert threshold: High alert when value = 0
- Alert duration: Trigger after 1 collection
5.3 Disk Error Count
Purpose
ASM tracks read and write errors for each member disk. Any non-zero error count is a sign of a failing or degraded disk and should be investigated immediately. Because the counts are cumulative since the ASM instance started, even a single new error is significant.
Oracle SQL
|
1
2
3
4
5
6
7
8
9
10
|
-- Returns the total read + write error count across all disks
-- in a specified disk group
-- Replace <DATA> with your target disk group name
SELECT NVL(SUM(READ_ERRS + WRITE_ERRS), 0) AS total_errors
FROM V$ASM_DISK
WHERE GROUP_NUMBER = (
SELECT GROUP_NUMBER
FROM V$ASM_DISKGROUP
WHERE NAME = '<DATA>'
);
|
Configuration settings
- Metric name: ASM Disk Errors DATA
- Unit: Count
- Collection frequency: Every 5 minutes
- Alert threshold: High alert when value > 0
- Alert duration: Trigger after 1 collection
Note: Error counts are cumulative from ASM instance startup. If the counts are non-zero but stable over time, this may indicate historic errors that have already been investigated. Consider your baseline when configuring alert thresholds and review the V$ASM_DISK HEADER_STATUS and MODE_STATUS columns for current disk health. |
5.4 ASM Rebalance Progress
Purpose
When a disk is added to or dropped from a disk group, ASM performs a rebalance operation to redistribute extents evenly. Rebalance operations are I/O intensive and can affect database performance. This metric reports the completion percentage of any active rebalance, making it easy to track progress and correlate any performance impact with ongoing rebalance activity.
Oracle SQL
|
1
2
3
4
5
6
7
8
9
10
11
|
-- Returns rebalance % complete for the specified disk group
-- Returns NULL when no rebalance is active (no row collected)
-- Replace <DATA> with your target disk group name
SELECT ROUND(((SOFAR / NULLIF(EST_WORK, 0)) * 100), 1) AS pct_complete
FROM V$ASM_OPERATION
WHERE OPERATION = 'REBAL'
AND GROUP_NUMBER = (
SELECT GROUP_NUMBER
FROM V$ASM_DISKGROUP
WHERE NAME = '<DATA>'
);
|
Configuration settings
- Metric name: ASM Rebalance Progress % DATA
- Unit: Percent (%)
- Collection frequency: Every 2 minutes
- Alert threshold: Informational no alert required, used for visibility and correlation
Tip: When the rebalance completes, V$ASM_OPERATION returns no rows, so the metric records NULL. In the Analysis chart, gaps in the line indicate no active rebalance. This is expected behavior. |
5.5 Disk I/O Response Time
Purpose
High I/O response times at the ASM disk level are an early indicator of storage performance problems. This metric returns the average read response time (in milliseconds) across all disks in a disk group. Sustained high latency often precedes disk failures and can directly impact database query performance.
Oracle SQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
-- Returns average read response time in milliseconds
-- across all disks in the specified disk group
-- Replace <DATA> with your target disk group name
SELECT ROUND(
AVG(CASE WHEN READS > 0 THEN READ_TIME / READS * 1000 ELSE 0 END),
2
) AS avg_read_ms
FROM V$ASM_DISK
WHERE GROUP_NUMBER = (
SELECT GROUP_NUMBER
FROM V$ASM_DISKGROUP
WHERE NAME = '<DATA>'
);
|
Configuration settings
- Metric name: ASM Avg Read Response Time (ms) DATA
- Unit: Milliseconds (ms)
- Collection frequency: Every 5 minutes
- Alert threshold: Medium alert when value > 20ms; High alert when value > 50ms
- Alert duration: Trigger after 3 consecutive collections to avoid alerting on transient spikes
6. Step-by-Step: Installing a Custom Metric
Follow these steps to add any of the metrics above to Redgate Monitor.
- Open Custom Metrics: In Redgate Monitor, navigate to Configuration > Custom metrics and click Add custom metric.
- Enter details: Give the metric a clear, descriptive name (e.g. ASM Disk Group Free Space % DATA) and add a description that explains what the metric measures and why it matters. Good descriptions help future team members maintain the metric.
- Enter the SQL query: Paste the Oracle SQL from this guide into the SQL query field. Substitute your actual disk group name for the placeholder '<DATA>'.
- Select instances: Choose the Oracle instances you want to collect from. Start with a single non-production instance for validation.
- Set collection frequency: Choose an appropriate interval as recommended in the metric’s configuration section.
- Test the metric: Click Test metric collection to run the query once and confirm it returns a single numeric value within an acceptable time.
- Configure alert thresholds: Click Next to proceed to the alert step. Set the thresholds recommended for the metric or adjust them to fit your environment.
- Review and create: Review the summary and click Create metric and alert. The metric is now active.
| Tip: After creating a metric, allow at least two collection intervals to pass, then open the Analysis page and search for the metric name to confirm data is being collected and plotted correctly. |
7. Best Practices
7.1 One metric per disk group
Because custom metrics return a single value, create a separate metric for each disk group you want to monitor. Use a consistent naming convention such as [Category] [Disk Group] to keep the metrics list organized.
7.2 Test before production deployment
Always validate new metrics on a development or test instance before deploying to production. Verify that execution time is under one second and that no unexpected locks or resource contention occurs.
7.3 Set meaningful alert thresholds
Thresholds that are too sensitive generate alert noise and lead teams to ignore alerts. For capacity metrics such as free space, a medium alert at 25% and a high alert at 15% is a reasonable starting point for most environments. Review and tune thresholds after observing a few weeks of historical data.
7.4 Use alert duration for I/O metrics
I/O response time can spike briefly during checkpoint activity or backup jobs without indicating a real problem. Requiring three or more consecutive collections above the threshold before alerting reduces false positives significantly.
7.5 Correlate with existing metrics
ASM metrics become more powerful when viewed alongside Redgate Monitor's built-in Oracle metrics. A spike in ASM read response time that coincides with elevated CPU or a rebalance operation tells a very different story than an isolated spike. Use the Analysis page to overlay multiple metrics and identify patterns.
8. Troubleshooting
No data collected
- Confirm the monitoring user has SELECT on the relevant V$ASM_* views.
- Run the SQL directly in SQL*Plus or SQL Developer as the monitoring user to confirm it returns a single row with one numeric value.
- Verify the correct instance is selected in the Select instances step of the metric wizard.
Query returns no rows (NULL)
- For the rebalance metric, returning NULL when no rebalance is active is correct behavior.
- For other metrics, confirm the disk group name in the WHERE clause exactly matches the name in V$ASM_DISKGROUP, including case.
ORA-01031: insufficient privileges
- Grant explicit SELECT on the V_$ASM_* synonyms (not the underlying X$ tables) as shown in Section 4.
- In some configurations, granting SELECT_CATALOG_ROLE to the monitoring user resolves access issues across all V$ views.
9. Further Resources
- Custom Metrics library: sqlmonitormetrics.red-gate.com, then browse and install community-contributed metrics.
- Custom Metrics getting started guide: sqlmonitormetrics.red-gate.com/guides, which has a walkthrough for installing your first metric.
- Redgate Monitor documentation: documentation.red-gate.com/monitor, with a full product documentation including Custom Metrics reference.
- Share your metrics: If you build additional ASM metrics, consider contributing them at sqlmonitormetrics.red-gate.com/submit-metric/
This document contains proprietary information and is protected by copyright law.
Copyright © 2026 Red Gate Software Limited. All rights reserved
Tools in this post
Redgate Monitor
Real-time multi-platform performance monitoring, with alerts and diagnostics
You may also like
Article
A Faster Way To Spot What's Slowing Down Your PostgreSQL Database
When a PostgreSQL database starts misbehaving, your instinct might be to jump straight into query plans and index statistics. But often, the real story hides one level higher, in wait events: the moments where PostgreSQL pauses and tells you exactly what it’s waiting for. Redgate Monitor now surfaces this data visually for PostgreSQL customers. Here’s why that matters. What Are Wait Events, and Why Should You Care? Every time a PostgreSQL session can’t proceed immediately, it registers a wait. These waits are grouped into Wait Event Types, which are high-level categories that tell you immediately “why” the wait occurred: Lock
Article
How to Detect SQL Injection Attacks using Extended Events and Redgate Monitor
Phil Factor shows how to monitor for the errors indicative of a possible SQL Injection attack on one of your SQL Server databases, using a Redgate Monitor custom metric that uses diagnostic data from Extended Events.
Article
Finding Inactive SQL Server Databases
Phil Factor demonstrates how to identify objects in a database that are not being used. The technique uses Extended Events to detect acquisition of Intent locks, and so determine which databases are active or apparently 'dormant', and then drills down to investigate on which tables data has been modified recently, and which views and stored procedures have been run.
Article
On Quickly Investigating a Redgate Monitor Custom Security Alert
Phil Factor offers a clever way to report on a SQL Server intrusion, with a query that shows a full narrative description of all the security-related changes that have been detected by a set of Redgate Monitor custom metrics.
Article
How to monitor backups and other SQL Agent jobs using Redgate Monitor
How to monitor any SQL job that runs on the SQL Server Agent so that you're alerted quickly if SQL Agent goes offline, or if a job fails, or fails to start, or is running slow.
Article
Is Tempdb Causing a Bottleneck?
One of your SQL Server instance shows a major dip in performance or throughput, affecting all the user databases. You notice that the slow interludes coincide, as if orchestrated. On investigation, it appears that several transactions running over that period were using a lot of space in tempdb. However, which of them, if any, are causing the tempdb bottleneck, and why?
Loading comments...