6 Using Views to Display Oracle ASM Information
Dynamic views display important information about Oracle ASM.
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 dynamic performance views
-
Managing Oracle ASM with ASMCMD for ASMCMD
ls*commands that display Oracle ASM information.
Views Containing Oracle ASM Information
The views listed in Table 6-1 provide information about Oracle ASM.
For information about all dynamic performance views that display information about Oracle ASM, refer to Oracle Database Reference
Note:
The REDUNDANCY_LOWERED column in the V$ASM_FILE view no longer provides information about files with reduced redundancy. This column is deprecated, and it always displays a value of U.
Table 6-1 Oracle ASM dynamic views
| View | Description |
|---|---|
|
Contains one row for every alias present in every disk group mounted by the Oracle ASM instance. |
|
|
Displays one row for each attribute defined. In addition to attributes specified by |
|
|
Displays information about the history of audit trail cleanup or purge events. |
|
|
Displays information about the configured audit trail purge jobs. |
|
|
Displays information about the currently configured audit trail properties. |
|
|
Displays information about the last archive timestamps set for audit trail cleanup or purges. |
|
|
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. |
|
|
In an Oracle ASM instance, shows the relationship between the source database, the cloned database, and their file groups. |
|
|
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. Because performing a discovery is very resource intensive, this view is not recommended for monitoring scripts. |
|
|
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. |
|
|
Contains the same columns as The To display information about all disks, use |
|
|
Describes a disk group (number, name, size related info, state, and redundancy type). This view performs disk discovery every time it is queried. Because performing a discovery is very resource intensive, this view is not recommended for monitoring scripts. |
|
|
Contains the same columns as The To display more complete information about all disks, use |
|
|
Displays an estimate of the work involved in execution plans for Oracle ASM disk group rebalance and resync operations. |
|
|
Contains one row for every Oracle ASM file in every disk group mounted by the Oracle ASM instance. |
|
|
Contains one row for every file group in every disk group mounted by the Oracle ASM instance. |
|
|
Contains one row for each file associated with a file group in every disk group mounted by the Oracle ASM instance. |
|
|
Contains one row for every property associated with the file group or each file type of every file group in every disk group mounted by the Oracle ASM instance. |
|
|
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. |
|
|
Contains one row for every Oracle Automatic Storage Management (Oracle ASM) quota group discovered by the Oracle ASM instance on the node. |
|
|
Contains one row for every template present in every disk group mounted by the Oracle ASM instance. |
|
|
Contains the effective operating system user names of connected database instances and names of file owners. |
|
|
Contains the creator for each Oracle ASM File Access Control group. |
|
|
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.
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
...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.
For more information about Oracle ASM File Access Control, see Managing Oracle ASM File Access Control for Disk Groups.
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
...