Visualizing Disk Groups in Oracle ASM with SQL

calendar Jan 6, 2024 / · 5 min read · disk space analysis oracle storage capacity monitoring asm disk utilization asm v$asm_disk v$asm_diskgroup v$asm_file v$asm_operation v$asm_disk_stat  ·
Share on: linkedin copy

Visualizing Disk Groups in Oracle ASM with SQL

Purpose

V$ASM_DISK is the view that lists every disk the Oracle ASM instance finds, including disks that are not part of any disk group. The query below reads six of its columns and sorts the rows by disk group number. The result is a plain text listing in SQL*Plus, one row per disk, grouped by disk group. It shows which disks belong to which group, but it does not show the disk groups themselves. For one row per disk group, use V$ASM_DISKGROUP (see the last section).

Run it in the ASM instance. In the 23ai documentation, V$ASM_DISK returns 0 rows when it is queried from a PDB.

Code

 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

Formatting output

  • set lines 100 : Sets the line width to 100 characters.
  • col name format a10 : Formats the name column to a width of 10 characters.
  • col path format a30 : Formats the path column to a width of 30 characters.

Querying ASM disk information

  • select name, group_number, disk_number, mount_status, state, path : Queries six columns from the v$asm_disk view.
    • name : Disk name within ASM.
    • group_number : Number of the disk group that contains the disk.
    • disk_number : Number assigned to the disk within its disk group.
    • mount_status : Per-instance status of the disk relative to group mounts. The documented values are MISSING, CLOSED, OPENED, CACHED, IGNORED and CLOSING. It is not a yes/no "mounted" flag.
    • state : Global state of the disk with respect to the disk group. The documented values are UNKNOWN, NORMAL, ADDING, DROPPING, HUNG and FORCING.
    • path : Operating system path name portion of the name returned by discovery.

Ordering and execution

  • order by group_number : Sorts the output by disk group number.
  • / : In SQL*Plus, runs the most recent SQL statement stored in the buffer.

Key Points

  • A group number of zero does not mean "free". The documentation says GROUP_NUMBER and DISK_NUMBER are only valid if the disk is part of a disk group that the instance has mounted. Otherwise GROUP_NUMBER is 0. A member disk of a dismounted disk group also shows 0, so a zero row is not safe to hand out as unused.
  • Use header_status to check availability. V$ASM_DISK has a HEADER_STATUS column. CANDIDATE and PROVISIONED mean the disk is not part of a disk group and may be added to one. MEMBER means the disk belongs to an existing disk group. FORMER means it was once in a group and was dropped cleanly. This query does not select that column, so add it before you decide that a disk is free.
  • Every query runs disk discovery. The documentation states that V$ASM_DISK performs disk discovery every time it is queried.
  • Disk numbers with group 0. For disks with group number 0, DISK_NUMBER is only unique among the other disks with group number 0.

Insights and Best Practices

Use V$ASM_DISK_STAT for monitoring scripts

Oracle recommends against V$ASM_DISK for monitoring scripts, because discovery is resource intensive. The Oracle ASM Administrator's Guide says V$ASM_DISK_STAT "is recommended for monitoring scripts". It has the same columns, but it does not run discovery. A matching query:

 1select name
 2, group_number
 3, disk_number
 4, header_status
 5, mount_status
 6, state
 7, path
 8from v$asm_disk_stat
 9order by group_number
10/

Know the trade-off. V$ASM_DISK_STAT returns only disks that are part of mounted disk groups, so it will not list new or unassigned disks. Use V$ASM_DISK for an occasional manual check of all disks, and V$ASM_DISK_STAT for anything that runs on a schedule.

Filtering does not remove the discovery cost

A where clause limits the rows returned, but the discovery step happens each time the view is queried. The documented way to reduce the cost is to switch to the cheaper view, not to filter.

Read the status columns together

A healthy disk in a mounted disk group normally shows STATE NORMAL. The documentation describes the normal MOUNT_STATUS as CACHED for disks in an ASM instance that belong to a mounted disk group, and OPENED for a database instance using the group. Values such as MISSING, ADDING, DROPPING or HUNG are worth a closer look. This query does not select error counters, so it shows configuration and state only, not a full health picture.

When to Run This

  • Before adding a disk to a disk group, to check what the instance sees.
  • After a storage change, to confirm that the expected paths appear.
  • When a disk group will not mount, to see which disks the instance finds.

Troubleshooting Common Issues

  • No rows, or no disks you expect: Check that you are connected to the ASM instance and not to a PDB. The 23ai documentation says the view returns 0 rows from a PDB.
  • A disk shows group number 0 but you think it is free: Add header_status to the query. If it shows MEMBER, the disk belongs to a disk group that is not mounted here.
  • V$ASM_DISKGROUP shows one row for every disk group the ASM instance discovered on the node. Use it to see the disk groups themselves.
  • V$ASM_FILE shows one row for each file in each disk group mounted by the instance.
  • V$ASM_OPERATION shows one row for every active long-running ASM operation.

References

Posts in this series