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

This is an example of the use of the V$ASM_ATTRIBUTE and V$ASM_DISKGROUP views. 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.

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         idp.type                 dynamic                  N
DATA         idp.boundary             auto                     N
DATA         vam_migration_done       false                    Y
DATA         scrub_metadata.enabled   TRUE                     N
DATA         scrub_async_limit        1                        N
DATA         content_hardcheck.enable FALSE                    N
DATA         access_control.umask     066                      N
DATA         access_control.enabled   FALSE                    N
DATA         cell.sparse_dg           allnonsparse             N
DATA         cell.smart_scan_capable  FALSE                    N
DATA         compatible.advm          19.0.0.0.0               N
DATA         compatible.rdbms         19.0.0.0.0               N
DATA         compatible.asm           19.0.0.0.0               N
DATA         appliance._partnering_ty GENERIC                  Y
DATA         au_size                  1048576                  Y
DATA         content.check            FALSE                    N
DATA         content.type             data                     N
DATA         logical_sector_size      512                      N
DATA         sector_size              512                      N
DATA         ate_conversion_done      true                     Y
DATA         preferred_read.enabled   FALSE                    N
DATA         thin_provisioned         FALSE                    N
DATA         failgroup_repair_time    24.0h                    N
DATA         phys_meta_replicated     true                     Y
DATA         disk_repair_time         12.0h                    N

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

This is an example of displaying the compatibility for a disk group with the V$ASM_DISKGROUP view.

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                           19.0.0.0.0   19.0.0.0.0
FRA                            19.0.0.0.0   19.0.0.0.0

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

This is an example of the use of the V$ASM_DISK and V$ASM_DISKGROUP views run on an Oracle ASM instance. The example displays the disks associated with a disk group, plus the mount status, state, and failure group of the disks.

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_0008        CACHED  NORMAL   DATA_0008
DATA             DATA_0000        CACHED  NORMAL   DATA_0000
DATA             DATA_0004        CACHED  NORMAL   DATA_0004
DATA             DATA_0015        CACHED  NORMAL   DATA_0015
DATA             DATA_0003        CACHED  NORMAL   DATA_0003
DATA             DATA_0012        CACHED  NORMAL   DATA_0012
DATA             DATA_0017        CACHED  NORMAL   DATA_0017
DATA             DATA_0013        CACHED  NORMAL   DATA_0013
DATA             DATA_0007        CACHED  NORMAL   DATA_0007
DATA             DATA_0014        CACHED  NORMAL   DATA_0014
DATA             DATA_0009        CACHED  NORMAL   DATA_0009
DATA             DATA_0001        CACHED  NORMAL   DATA_0001
DATA             DATA_0016        CACHED  NORMAL   DATA_0016
DATA             DATA_0011        CACHED  NORMAL   DATA_0011
DATA             DATA_0005        CACHED  NORMAL   DATA_0005
DATA             DATA_0010        CACHED  NORMAL   DATA_0010
DATA             DATA_0002        CACHED  NORMAL   DATA_0002
DATA             DATA_0006        CACHED  NORMAL   DATA_0006
FRA              FRA_0012         CACHED  NORMAL   FRA_0012
FRA              FRA_0013         CACHED  NORMAL   FRA_0013
FRA              FRA_0007         CACHED  NORMAL   FRA_0007
FRA              FRA_0006         CACHED  NORMAL   FRA_0006
FRA              FRA_0010         CACHED  NORMAL   FRA_0010
FRA              FRA_0000         CACHED  NORMAL   FRA_0000
FRA              FRA_0003         CACHED  NORMAL   FRA_0003
...

Example 6-4 Viewing disks in disk groups with V$ASM_DISK_STAT

This is an example of the use of the V$ASM_DISK_STAT and V$ASM_DISKGROUP_STAT views run on an Oracle ASM instance. The example displays the disks associated with a specific disk group along with the mount status, state, and various read and write statistics.

SELECT SUBSTR(dgs.name,1,10) AS diskgroup, SUBSTR(ds.name,1,10) AS asmdisk, 
       ds.mount_status, ds.state, ds.reads, ds.writes, ds.read_time, ds.write_time, 
       bytes_read, bytes_written 
       FROM V$ASM_DISKGROUP_STAT dgs, V$ASM_DISK_STAT ds 
       WHERE dgs.group_number = ds.group_number AND dgs.name = 'DATA';

DISKGROUP  ASMDISK    MOUNT_S STATE    READS WRITES READ_TIME WRITE_TIME BYTES_READ BYTES_WRITTEN
---------- ---------- ------- ------- ------ ------ --------- ---------- ---------- -------------
DATA       DATA_0000  CACHED  NORMAL     841  10407  1.212218   3.511977   23818240     178369024
DATA       DATA_0008  CACHED  NORMAL   26065   1319  1.592524    .297728  436203520      38358528
DATA       DATA_0010  CACHED  NORMAL     561    868   .794849    .337575   18631680      22584320
DATA       DATA_0004  CACHED  NORMAL     695  10512  1.282711   3.351801   23240704     177246208
DATA       DATA_0006  CACHED  NORMAL     484   1642  1.506733     .45724   19857408      30191616
DATA       DATA_0016  CACHED  NORMAL     583   1028  2.283268    .263629   21012480      17682432
DATA       DATA_0007  CACHED  NORMAL     724   2316  1.259379    .546318   26017792      42283008
DATA       DATA_0009  CACHED  NORMAL     537    757  1.146663    .241434   19893248      20633088
DATA       DATA_0014  CACHED  NORMAL    1049   1464  7.346259    .677313   25378816      27578368
DATA       DATA_0017  CACHED  NORMAL    1440   1326  1.132886   2.541013   25899008      26537984
DATA       DATA_0013  CACHED  NORMAL     714   1391  1.527926    .371432   18169856      22814720
DATA       DATA_0001  CACHED  NORMAL     713    807   .790505    .219565   20406272      28561408
DATA       DATA_0012  CACHED  NORMAL     617   1206  1.016893    3.60425   24477696      25391104
DATA       DATA_0003  CACHED  NORMAL   15567  11500  5.642053   3.328861  266956800     183625728
DATA       DATA_0015  CACHED  NORMAL     642   1357  2.545441    .403455   22179840      24973312
DATA       DATA_0011  CACHED  NORMAL    7585   1685  1.121678    .359123  135217152      37572608
DATA       DATA_0005  CACHED  NORMAL     513   1431  1.007476     .47202   26427392      21344256
DATA       DATA_0002  CACHED  NORMAL   11368   2196  1.209433    .861601  199213056      32090624

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

This is an example of the use of the V$ASM_CLIENT and V$ASM_DISKGROUP views on an Oracle ASM instance. The example displays disk groups with information about the connected database client instances.

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     19.0.0.0.0   19.0.0.0.0
DATA                           orcl         orcl     19.0.0.0.0   19.0.0.0.0
DATA                           +ASM         asmvol   19.0.0.0.0   19.0.0.0.0
FRA                            orcl         orcl     19.0.0.0.0   19.0.0.0.0
...

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

This example shows information about Oracle ASM File Access Control users displayed in the V$ASM_USER view.

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-7 Viewing File Access Control information with V$ASM_USERGROUP

This example shows information about Oracle ASM File Access Control user groups displayed in the V$ASM_USERGROUP view.

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-8 Viewing File Access Control information with V$ASM_USERGROUP_MEMBER

This example shows information about Oracle ASM File Access Control user groups and members displayed in the V$ASM_USERGROUP_MEMBER view.

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-9 Viewing Oracle ASM File Access Control information with V$ASM_FILE

This example shows information about Oracle ASM File Access Control file permissions displayed in the V$ASM_FILE view.

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