Views Containing Oracle ASM Disk Group Information

You can use the views in Table 6-1 to obtain information about Oracle ASM disk groups.

Table 6-1 Oracle ASM dynamic views for disk group information

View Description

V$ASM_ALIAS

Contains one row for every alias present in every disk group mounted by the Oracle ASM instance.

V$ASM_ATTRIBUTE

Displays one row for each attribute defined. In addition to attributes specified by CREATE DISKGROUP and ALTER DISKGROUP statements, the view may show other attributes that are created automatically. Attributes are only displayed for disk groups where COMPATIBLE.ASM is set to 11.1 or higher.

V$ASM_AUDIT_CLEAN_EVENTS

Displays information about the history of audit trail cleanup or purge events.

V$ASM_AUDIT_CLEANUP_JOBS

Displays information about the configured audit trail purge jobs.

V$ASM_AUDIT_CONFIG_PARAMS

Displays information about the currently configured audit trail properties.

V$ASM_AUDIT_LAST_ARCH_TS

Displays information about the last archive timestamps set for audit trail cleanup or purges.

V$ASM_CLIENT

In an Oracle ASM instance, identifies databases using disk groups managed by the Oracle ASM instance.

In an Oracle Database instance, contains information about the Oracle ASM instance if the database has any open Oracle ASM files.

V$ASM_DISK

Contains one row for every disk discovered by the Oracle ASM instance, including disks that are not part of any disk group.

This view performs disk discovery every time it is queried.

V$ASM_DISK_IOSTAT

Displays information about disk I/O statistics for each Oracle ASM client.

In an Oracle Database instance, only the rows for that instance are shown.

V$ASM_DISK_STAT

Contains the same columns as V$ASM_DISK, but to reduce overhead, does not perform a discovery when it is queried. It only returns information about any disks that are part of mounted disk groups in the storage system. To see all disks, use V$ASM_DISK instead.

V$ASM_DISKGROUP

Describes a disk group (number, name, size related info, state, and redundancy type).

This view performs disk discovery every time it is queried.

V$ASM_DISKGROUP_STAT

Contains the same columns as V$ASM_DISKGROUP, but to reduce overhead, this view does not perform a discovery when it is queried. To see more complete information on all disks, use V$ASM_DISKGROUP.

V$ASM_ESTIMATE

Displays an estimate of the work involved in execution plans for Oracle ASM disk group rebalance and resync operations.

V$ASM_FILE

Contains one row for every Oracle ASM file in every disk group mounted by the Oracle ASM instance.

V$ASM_OPERATION

In an Oracle ASM instance, contains one row for every active Oracle ASM long running operation executing in the Oracle ASM instance.

In an Oracle Database instance, contains no rows.

V$ASM_TEMPLATE

Contains one row for every template present in every disk group mounted by the Oracle ASM instance.

V$ASM_USER

Contains the effective operating system user names of connected database instances and names of file owners.

V$ASM_USERGROUP

Contains the creator for each Oracle ASM File Access Control group.

V$ASM_USERGROUP_MEMBER

Contains the members for each Oracle ASM File Access Control group.

When querying V$ASM views, the value of the disk group number is not a static value. When a disk group is mounted, a disk group number is chosen. This number may change across disk group mounts. A disk group number is not recorded in any persistent structure, but the current value can be viewed in the GROUP_NUMBER column of the V$ASM views.

An example of the use of the V$ASM_ATTRIBUTE and V$ASM_DISKGROUP views is shown in Example 6-1. The COMPATIBLE.ASM value must be set to 11.1 or higher for the disk group to display in the V$ASM_ATTRIBUTE view output. Attributes that are designated as read-only (Y) can only be set during disk group creation.

You can view the compatibility for a disk group with the V$ASM_DISKGROUP view, as shown in Example 6-2.

An example of the use of the V$ASM_DISK and V$ASM_DISKGROUP views is shown in Example 6-3. This example displays the disks associated with a disk group, plus the mount status, state, and failure group of the disks.

An example of the use of the V$ASM_CLIENT and V$ASM_DISKGROUP views on an Oracle ASM instance is shown in Example 6-4. This example displays disk groups with information about the connected database client instances.

Example 6-1 Viewing disk group attributes with V$ASM_ATTRIBUTE

SELECT SUBSTR(dg.name,1,12) AS diskgroup, SUBSTR(a.name,1,24) AS name,
     SUBSTR(a.value,1,24) AS value, read_only FROM V$ASM_DISKGROUP dg, 
     V$ASM_ATTRIBUTE a WHERE dg.name = 'DATA' AND dg.group_number = a.group_number
     AND a.name NOT LIKE '%template%';
 
DISKGROUP    NAME                     VALUE                    READ_ON
------------ ------------------------ ------------------------ -------
DATA         thin_provisioned         FALSE                    N
DATA         access_control.umask     066                      N
DATA         phys_meta_replicated     true                     Y
DATA         disk_repair_time         3.6h                     N
DATA         idp.boundary             auto                     N
DATA         idp.type                 dynamic                  N
DATA         content.type             data                     N
DATA         content.check            FALSE                    N
DATA         au_size                  1048576                  Y
DATA         sector_size              512                      Y
DATA         compatible.asm           12.1.0.0.0               N
DATA         compatible.rdbms         12.1.0.0.0               N
DATA         compatible.advm          12.1.0.0.0               N
DATA         cell.smart_scan_capable  FALSE                    N
DATA         access_control.enabled   FALSE                    N
DATA         failgroup_repair_time    24.0h                    N

Example 6-2 Viewing the compatibility of a disk group with V$ASM_DISKGROUP

SELECT name AS diskgroup, substr(compatibility,1,12) AS asm_compat,
     substr(database_compatibility,1,12) AS db_compat FROM V$ASM_DISKGROUP;
 
DISKGROUP                      ASM_COMPAT   DB_COMPAT
------------------------------ ------------ ------------
DATA                           12.1.0.0.0   12.1.0.0.0
FRA                            12.1.0.0.0   12.1.0.0.0

Example 6-3 Viewing disks in disk groups with V$ASM_DISK

SELECT SUBSTR(dg.name,1,16) AS diskgroup, SUBSTR(d.name,1,16) AS asmdisk,
     d.mount_status, d.state, SUBSTR(d.failgroup,1,16) AS failgroup 
     FROM V$ASM_DISKGROUP dg, V$ASM_DISK d WHERE dg.group_number = d.group_number;

DISKGROUP        ASMDISK          MOUNT_S STATE    FAILGROUP
---------------- ---------------- ------- -------- ----------------
DATA             DATA_0000        CACHED  NORMAL   DATA_0000
DATA             DATA_0010        CACHED  NORMAL   DATA_0010
DATA             DATA_0001        CACHED  NORMAL   DATA_0001
DATA             DATA_0003        CACHED  NORMAL   DATA_0003
DATA             DATA_0009        CACHED  NORMAL   DATA_0009
DATA             DATA_0007        CACHED  NORMAL   DATA_0007
DATA             DATA_0004        CACHED  NORMAL   DATA_0004
DATA             DATA_0008        CACHED  NORMAL   DATA_0008
DATA             DATA_0006        CACHED  NORMAL   DATA_0006
DATA             DATA_0011        CACHED  NORMAL   DATA_0011
DATA             DATA_0005        CACHED  NORMAL   DATA_0005
DATA             DATA_0002        CACHED  NORMAL   DATA_0002
FRA              FRA_0011         CACHED  NORMAL   FRA_0011
FRA              FRA_0002         CACHED  NORMAL   FRA_0002
FRA              FRA_0001         CACHED  NORMAL   FRA_0001
FRA              FRA_0003         CACHED  NORMAL   FRA_0003
FRA              FRA_0004         CACHED  NORMAL   FRA_0004
FRA              FRA_0000         CACHED  NORMAL   FRA_0000
...

Example 6-4 Viewing disk group clients with V$ASM_CLIENT

SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance,
    SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS software,
    SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible 
    FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c  
    WHERE dg.group_number = c.group_number;
 
DISKGROUP                      INSTANCE     DBNAME   SOFTWARE     COMPATIBLE
------------------------------ ------------ -------- ------------ ------------
DATA                           +ASM         +ASM     12.1.0.1.0   12.1.0.1.0
DATA                           orcl         orcl     12.1.0.1.0   12.1.0.1.0
DATA                           +ASM         asmvol   12.1.0.1.0   12.1.0.1.0
FRA                            orcl         orcl     12.1.0.1.0   12.1.0.1.0
...