• Arun Kumar

Oracle ASM Queries

Query to check diskgroups from asm instance:

select group_number, name, state, type from v$asm_diskgroup;

To check the asm instance is connected and running:

select group_number, db_name, status from v$asm_client;

To check the asm disks and their status:

select HEADER_STATUS, NAME, PATH, mount_status, state, header_status from v$asm_disk;


Mount Status


MISSING – Automatic Storage Management metadata indicates that the disk is known to be part of the Automatic Storage Management disk group, but no disk in the storage


CLOSED – Disk is present in the storage system but is not being accessed by Automatic Storage Management


OPENED – Disk is present in the storage system and is being accessed by Automatic Storage Management. This is the normal state for disks in a database instance which are part of a Disk Group being actively used by the instance.


CACHED – Disk is present in the storage system, and is part of a disk group being accessed by the Automatic Storage Management instance. This is the normal state for disks in an Automatic Storage Management instance which are part of a mounted disk group.


Header Status


UNKNOWN – Automatic Storage Management disk header has not been read


CANDIDATE – Can be used


INCOMPATIBLE – Version number in the disk header is not compatible with the Automatic Storage Management software version.


PROVISIONED – Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from theCANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.


MEMBER – Already member of a diskgroup


FORMER – Once used, can be used


CONFLICT – Automatic Storage Management disk was not mounted due to a conflict


Recent Posts

See All

11gR2 Non-RAC to RAC Migration

In this article we will be looking at Non-RAC to RAC migration using DBCA. I have RACN1 and RACN2 where I will show you how to migrate a single instance database running on machine DT_VM. Create Templ

RMAN Backups in RAC

BACKUP DEFAULT LOCATION All backups by default go to FRA disk group TAKE BACKUP TO CUSTOM LOCATION ON ASM RMAN> backup as compressed backupset database format '+RMANDG'; or run{ allocate channel ch1

Find ASM physical disk mapping

When you work in a RAC environment with a list of ASM disks, sometimes you might want to figure out which ASM disk is mapped to which physical device. Below is a simple script that will help you find

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback