• 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


6,057 views

Recent Posts

See All