Querying ASM Disk Information in Oracle

calendar Jan 7, 2024 / · 6 min read · Oracle ASM Oracle Disk Information Storage Management v$asm_disk v$asm_disk_stat v$asm_diskgroup v$asm_file v$asm_operation  ·
Share on: linkedin copy

Querying ASM Disk Information in Oracle

A disk group that reports plenty of free space can still be one disk failure away from trouble, and a LUN the storage team says is ready can still be invisible to ASM. Both show up in one query against v$asm_disk: the per-disk free space, and the group 0 rows for disks not yet in a group.

Purpose

This script lists every disk that Oracle Automatic Storage Management (ASM) knows about, with the disk group it belongs to, its number inside that group, and its total and free space in megabytes. It is the per-disk view: it shows whether space is spread evenly across the disks in a group and whether a newly added disk has joined a group yet.

Run it from the ASM instance or a database instance that uses ASM. The order by group number puts all the disks of one group together, with any unassigned disks first.

Code

1select  name
2,       group_number
3,       disk_number
4,       total_mb
5,       free_mb
6from    v$asm_disk
7order   by group_number
8/

A companion query from the same source shows the mount status, state and path of each disk:

 1set lines 100
 2col name	format a10
 3col path 	format a30
 4select	name
 5,	group_number
 6,	disk_number
 7,	mount_status
 8,	state
 9,	path
10from 	v$asm_disk
11order 	by group_number
12/

Code Breakdown

The columns

  • name — the ASM disk name, for example DATA_0003.
  • group_number — the number of the disk group containing the disk.
  • disk_number — the disk's number within its group.
  • total_mb — Oracle documents this as the total capacity of the disk in megabytes.
  • free_mb — the unused capacity of the disk in megabytes.

Group number zero

Oracle's reference notes that GROUP_NUMBER and DISK_NUMBER are only valid when the disk belongs to a disk group the instance has mounted. Otherwise GROUP_NUMBER is 0. The original source puts it plainly: a group number of zero indicates a disk that is available but not yet assigned to a disk group. So new LUNs show up here as group 0 rows, which is how you confirm ASM has discovered them before running ALTER DISKGROUP ... ADD DISK.

The companion query

mount_status and state show whether each disk is part of a mounted group and in normal use, and path is the operating system device path. The col commands set column widths so the long device paths fit on one line.

Key Points

  • v$asm_disk performs disk discovery every time it is queried. Oracle calls discovery very resource intensive and recommends against using this view in monitoring scripts.
  • v$asm_disk_stat is the cheap version. Oracle documents it as showing the same columns as v$asm_disk without discovering new disks, so it is less expensive but does not show disks that are new to the system.
  • Free space per disk is not usable space per group. With normal or high redundancy, mirroring means a group cannot use all of its free megabytes for new files.
  • Run as a privileged user. The v$asm_ views need a DBA-level connection, or SYSASM on the ASM instance.

Insights and Best Practices

Use the right view for the job

Use v$asm_disk when you need discovery: after new storage is presented, or when checking for a disk that has gone missing. Use v$asm_disk_stat with the same select list for anything scheduled, such as a capacity report that runs every hour. Oracle's own note is the reason: discovery costs real work on every query, and a monitoring job does not need it.

Watch the group, not only the disk

For capacity alerts, query v$asm_diskgroup. The type column shows the redundancy (EXTERN, NORMAL, HIGH, and in newer releases FLEX and EXTEND). Its USABLE_FILE_MB column is the free space that can be safely used taking mirroring into account while still being able to restore redundancy after a disk failure, and REQUIRED_MIRROR_FREE_MB is the space the group must keep free to do that. A group can show plenty of free_mb across its disks and still be near zero USABLE_FILE_MB.

1select  name
2,       type
3,       total_mb
4,       free_mb
5,       required_mirror_free_mb
6,       usable_file_mb
7from    v$asm_diskgroup
8order   by name
9/

Check the header status before adding a disk

Group 0 tells you a disk is not in a mounted group; it does not tell you whether the disk is safe to use. Add header_status to the companion query before you run ALTER DISKGROUP ... ADD DISK. Oracle documents the values that matter here:

  • CANDIDATE — not part of a disk group, and may be added to one.
  • PROVISIONED — the same, except an administrator has taken an extra platform-specific step to make the disk available to ASM.
  • FORMER — was once part of a disk group and was dropped cleanly; it may be added to a new group.
  • MEMBER — belongs to an existing disk group. Oracle warns that no attempt should be made to add it to a different group, and ALTER DISKGROUP rejects the addition unless forced.
  • FOREIGN — holds data created by an Oracle product other than ASM; leave it alone until you know what wrote it.

A MEMBER disk showing group 0 can simply belong to a disk group that is not mounted on this instance, since Oracle only fills in the group number for mounted groups. Treat it as in use until you have found its group, and never add it elsewhere with FORCE to save time.

Look for uneven disks

ASM spreads data across the disks in a group. If one disk in a group shows far less free_mb than its neighbours, a rebalance may still be running or may have stopped. v$asm_operation shows one row for every active long-running ASM operation, such as a rebalance, so check it before adding more disks.

When to Run This

  • After new storage is presented, to confirm ASM sees the new disks as group 0.
  • Before adding a disk to a group, to pick the right candidate disk.
  • When a disk group is filling up, to see whether space is even across its disks.
  • After a disk failure or a disk drop, to confirm the remaining disks and their space.

Troubleshooting Common Issues

If a newly presented disk does not appear, check that it matches the ASM disk discovery string and that the ASM software owner can read the device; v$asm_disk only lists what discovery can find. If a disk shows group 0 when you expected it in a group, the group may be dismounted on this instance, since group and disk numbers are only valid for mounted groups. If the query is slow, remember it runs discovery every time, and switch to v$asm_disk_stat unless you need to see new disks.

References

Posts in this series