Skip Headers
Oracle® Automatic Storage Management Administrator's Guide
12c Release 1 (12.1)

E17612-20
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

6 Using Views to Display Oracle ASM Information

This chapter contains information about using dynamic views to display Oracle Automatic Storage Management (Oracle ASM) information.

See Also:

Oracle Database Reference for information about all of the V$ASM* dynamic performance views

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_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.

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

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

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

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.

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
...

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-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
...

Viewing Oracle ASM File Access Control Information

You can view information about Oracle ASM File Access Control in the columns of the V$ASM_USER, V$ASM_USERGROUP, V$ASM_USERGROUP_MEMBER, and V$ASM_FILE views.

Example 6-5 shows information about Oracle ASM File Access Control users displayed in the V$ASM_USER view.

Example 6-5 Viewing Oracle ASM File Access Control information with V$ASM_USER

SELECT dg.name AS diskgroup, u.group_number, u.user_number, u.os_id, u.os_name 
     FROM V$ASM_DISKGROUP dg, V$ASM_USER u 
     WHERE dg.group_number = u.group_number AND dg.name = 'DATA';

DISKGROUP       GROUP_NUMBER USER_NUMBER OS_ID OS_NAME
--------------- ------------ ----------- ----- -------
DATA                       1           1 1001  oracle1
DATA                       1           2 1002  oracle2
DATA                       1           3 1003  grid

Example 6-6 shows information about Oracle ASM File Access Control user groups displayed in the V$ASM_USERGROUP view.

Example 6-6 Viewing File Access Control information with V$ASM_USERGROUP

SELECT dg.name AS diskgroup, ug.group_number, ug.owner_number, u.os_name,
     ug.usergroup_number, ug.name FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP ug 
     WHERE dg.group_number = ug.group_number AND dg.name = 'DATA' 
     AND ug.owner_number = u.user_number;

DISKGROUP         GROUP_NUMBER OWNER_NUMBER OS_NAME         USERGROUP_NUMBER NAME
----------------- ------------ ------------ --------------- ---------------- ------------------
DATA                         1            3 grid                           1 asm_data

Example 6-7 shows information about Oracle ASM File Access Control user groups and members displayed in the V$ASM_USERGROUP_MEMBER view.

Example 6-7 Viewing File Access Control information with V$ASM_USERGROUP_MEMBER

SELECT dg.name AS diskgroup, um.group_number, um.member_number, u.os_name, 
     um.usergroup_number, ug.name FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP_MEMBER um, 
     V$ASM_USERGROUP ug WHERE dg.group_number = um.group_number AND 
     dg.group_number = ug.group_number AND dg.group_number = u.group_number AND dg.name = 'DATA' 
     AND um.member_number = u.user_number AND um.usergroup_number = ug.usergroup_number;

DISKGROUP       GROUP_NUMBER MEMBER_NUMBER OS_NAME            USERGROUP_NUMBER NAME
--------------- ------------ ------------- ------------------ ---------------- ------------------
DATA                       1             1 oracle1                           1 asm_data
DATA                       1             2 oracle2                           1 asm_data

Example 6-8 shows information about Oracle ASM File Access Control file permissions displayed in the V$ASM_FILE view.

Example 6-8 Viewing Oracle ASM File Access Control information with V$ASM_FILE

SELECT dg.name AS diskgroup, a.name, f.permissions, f.user_number, u.os_name,
       f.usergroup_number, ug.name 
     FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP ug, V$ASM_FILE f, V$ASM_ALIAS a 
     WHERE dg.name = 'FRA' AND dg.group_number = u.group_number AND 
       u.group_number = ug.group_number AND ug.group_number = f.group_number AND 
       f.group_number = a.group_number AND 
       f.usergroup_number = ug.usergroup_number AND f.user_number = u.user_number AND 
       f.file_number = a.file_number;

DISKGROUP NAME                   PERMISSIONS USER_NUMBER OS_NAME USERGROUP_NUMBER NAME
--------- ---------------------- ----------- ----------- ------- ---------------- -------------
DATA      USERS.259.685366091    rw-r-----             3 grid                   1 asm_fra 
DATA      TEMP.264.685366227     rw-r-----             3 grid                   1 asm_fra 
...

For more information about Oracle ASM File Access Control, see "Managing Oracle ASM File Access Control for Disk Groups".

Viewing Disk Region Information

Information about Intelligent Data Placement is displayed in the columns of the V$ASM_DISK, V$ASM_DISK_IOSTAT, V$ASM_FILE, and V$ASM_TEMPLATE views.

Example 6-9 shows queries for Intelligent Data Placement information in the PRIMARY_REGION and MIRROR_REGION columns of the V$ASM_FILE view.

Example 6-9 Viewing Intelligent Data Placement information with V$ASM_FILE

SELECT dg.name AS diskgroup, f.file_number, f.primary_region, f.mirror_region, f.hot_reads,
       f.hot_writes,  f.cold_reads, f.cold_writes
       FROM V$ASM_DISKGROUP dg, V$ASM_FILE f 
       WHERE dg.group_number = f.group_number and dg.name = 'DATA';
 
DISKGROUP                      FILE_NUMBER PRIM MIRR  HOT_READS HOT_WRITES COLD_READS COLD_WRITES
------------------------------ ----------- ---- ---- ---------- ---------- ---------- -----------
DATA                                   257 COLD COLD          0          0     119770      886575
DATA                                   258 COLD COLD          0          0       1396      222282
DATA                                   259 COLD COLD          0          0       2056         199
DATA                                   260 COLD COLD          0          0      42377     1331016
DATA                                   261 COLD COLD          0          0    4336300     1331027
...

Example 6-10 displays Intelligent Data Placement information in the PRIMARY_REGION and MIRROR_REGION columns of the V$ASM_TEMPLATE view.

Example 6-10 Viewing Intelligent Data Placement information with V$ASM_TEMPLATE

SELECT dg.name AS diskgroup, t.name, t.stripe, t.redundancy, t.primary_region, t.mirror_region 
      FROM V$ASM_DISKGROUP dg, V$ASM_TEMPLATE t 
      WHERE dg.group_number = t.group_number and dg.name = 'DATA' ORDER BY t.name;

DISKGROUP                      NAME                           STRIPE REDUND PRIM MIRR
------------------------------ ------------------------------ ------ ------ ---- ----
DATA                           ARCHIVELOG                     COARSE MIRROR COLD COLD
DATA                           ASMPARAMETERFILE               COARSE MIRROR COLD COLD
DATA                           AUDIT_SPILLFILES               COARSE MIRROR COLD COLD
DATA                           AUTOBACKUP                     COARSE MIRROR COLD COLD
DATA                           AUTOLOGIN_KEY_STORE            COARSE MIRROR COLD COLD
DATA                           BACKUPSET                      COARSE MIRROR COLD COLD
DATA                           CHANGETRACKING                 COARSE MIRROR COLD COLD
DATA                           CONTROLFILE                    FINE   HIGH   COLD COLD
DATA                           DATAFILE                       COARSE MIRROR COLD COLD
DATA                           DATAGUARDCONFIG                COARSE MIRROR COLD COLD
DATA                           DUMPSET                        COARSE MIRROR COLD COLD
DATA                           FLASHBACK                      COARSE MIRROR COLD COLD
DATA                           FLASHFILE                      COARSE MIRROR COLD COLD
DATA                           INCR XTRANSPORT BACKUPSET      COARSE MIRROR COLD COLD
DATA                           KEY_STORE                      COARSE MIRROR COLD COLD
DATA                           OCRFILE                        COARSE MIRROR COLD COLD
DATA                           ONLINELOG                      COARSE MIRROR COLD COLD
DATA                           PARAMETERFILE                  COARSE MIRROR COLD COLD
DATA                           TEMPFILE                       COARSE MIRROR COLD COLD
DATA                           VOTINGFILE                     COARSE MIRROR COLD COLD
DATA                           XTRANSPORT                     COARSE MIRROR COLD COLD
DATA                           XTRANSPORT BACKUPSET           COARSE MIRROR COLD COLD

22 rows selected.

For information about setting Intelligent Data Placement, see "Intelligent Data Placement".