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