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 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.
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 for disk group information
| 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. |
|
|
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
An example of the use of the V$ASM_ATTRIBUTE and V$ASM_DISKGROUP views is shown in this example. 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 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
You can view the compatibility for a disk group with the V$ASM_DISKGROUP view, as shown in this example.
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
An example of the use of the V$ASM_DISK and V$ASM_DISKGROUP views is shown in this example. This 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_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 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
An example of the use of the V$ASM_CLIENT and V$ASM_DISKGROUP views on an Oracle ASM instance is shown in this example, which 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 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.
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
...Viewing Disk Region Information
Note:
The Oracle ASM Intelligent Data Placement (IDP) feature has been deprecated in Oracle ASM 12c Release 2 (12.2) and may be desupported in a future release.
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.
For information about setting Intelligent Data Placement, see "Intelligent Data Placement".
Example 6-10 Viewing Intelligent Data Placement information with V$ASM_FILE
This example shows queries for Intelligent Data Placement information in the PRIMARY_REGION and MIRROR_REGION columns of the V$ASM_FILE view.
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-11 Viewing Intelligent Data Placement information with V$ASM_TEMPLATE
This example displays Intelligent Data Placement information in the PRIMARY_REGION and MIRROR_REGION columns of the V$ASM_TEMPLATE view.
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.