1) ASM Dictionary Views for Viewing disk group attributes with V$ASM_ATTRIBUTE:-
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
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.03) Viewing disks in disk groups with V$ASM_DISK
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
...
4) Viewing disks in disk groups with V$ASM_DISK_STAT
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
5) Viewing disk group clients with V$ASM_CLIENT
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
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.
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 views.
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
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 views.
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
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 ASM Dictionary Views.
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
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 ASM Dictionary Views.
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
...
No comments:
Post a Comment