Database Reference
In-Depth Information
Each ASM diskgroup can store multiple database files and Clusterware and ASM instance files. For example:
+VOCR stores the files of the KHDB database and the kr720n-scan cluster's files:
ASMCMD> pwd
+VOCR
ASMCMD> ls
KHDB/
KHRN/
kr720n-scan/
Manage ASM Using SQL Command and V$ASM Views
A set of SQL commands and V$ ASM views can be used to administer ASM diskgroups and ASM disks as well as the
ASM instance itself. In the last section, I showed the SQL commands that are used to create and alter diskgroups.
This section covers some V$ ASM views and how to write queries using these views to monitor the capacity and
performance information of ASM disks and ASM diskgroup. Some of the commonly used V$ ASM views include
V$ASM_DISKGROUP, V$ASM_DISKS, V$ASM_DISK_STAT, V$ASM_DISKGROUP_STAT.
The following query shows the capacity and space usage of ASM diskgroups and the ASM disks:
SQL>SELECT D.PATH, D.TOTAL_MB DISKSIZE, G.NAME GROUP_NAME,
G.TOTAL_MB GROUPSIZE, G.FREE_MB GROUP_FREE
FROM V$ASM_DISK D, V$ASM_DISKGROUP G WHERE
D.GROUP_NUMBER = G.GROUP_NUMBER;
PATH FAILGROUP DISKSIZE GROUP_NA GROUPSIZE GROUP_FREE
--------------- --------------- -------- -------- ---------
----------
/dev/dm-7 DATA_0000 1433589 DATA 1433589 515833
/dev/dm-5 VOCR_0000 138231 VOCR 138231 19298
And this query shows the I/O performance on each ASM disk:
SQL> SELECT PATH, READS, WRITES, READ_TIME, WRITE_TIME,
READ_TIME/ READS AVEREADTIME, WRITE_TIME/ WRITES AVGWRITETIME
FROM V$ASM_DISK_STAT;
PATH READS WRITES READ_TIME WRITE_TIME AVEREADTIME AVGWRITETIME
---------- ---------- ---------- ---------- - --------- ------
----- ------------
/dev/dm-7 2371 193779 .747134 258.353864 .000315113 .00133324
/dev/dm-5 2237295 600232 635.231634 453.596385 .000283928 .000755702
Store OCR and Voting Disk in ASM
This section will look at ways of storing the two most important components of Oracle Clusterware: OCR and voting
disk files in ASM.
