Have you ever noticed that v$asm views are available in all databases? This is true whether using ASM or not because the ASM home and software is the same as the database software. On this note, while the DB and ASM can share a home, they should not in order to preserve the ability to upgrade ASM separately from the database home. Simply, ASM should always be at or above the highest database software version, but that is another post.
Ok, now that I am back from that tangent. I have had users call me in a panic when they run ASM scripts in a database instead of the ASM instance. When querying v$asm_disk and checking on the physical disk/luns, it will always report 100% used:
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name;
Disk Group Name Path File Size (MB) Used Size (MB) Pct. Used
--------------- ----------------- -------------- -------------- ---------
DGROUP1 ORCL:VOL001 33,791 33,791 100.00
ORCL:VOL002 33,791 33,791 100.00
ORCL:VOL003 33,791 33,791 100.00
ORCL:VOL008 33,791 33,791 100.00
ORCL:VOL005 33,791 33,791 100.00
ORCL:VOL006 33,791 33,791 100.00
ORCL:VOL007 33,791 33,791 100.00
ORCL:VOL004 33,791 33,791 100.00
*************** -------------- --------------
270,328 270,328
-------------- --------------
Grand Total: 270,328 270,328Which can induce great panic as it appears that ASM is full, but when properly run from ASM:
Disk Group Name Path File Size (MB) Used Size (MB) Pct. Used
--------------- ----------------- -------------- -------------- ---------
DGROUP1 ORCL:VOL001 33,791 24,925 73.76
ORCL:VOL002 33,791 24,911 73.72
ORCL:VOL003 33,791 24,910 73.72
ORCL:VOL008 33,791 24,921 73.75
ORCL:VOL005 33,791 24,921 73.75
ORCL:VOL006 33,791 24,905 73.70
ORCL:VOL007 33,791 24,932 73.78
ORCL:VOL004 33,791 24,919 73.74
*************** -------------- --------------
270,328 199,344
-------------- --------------
Grand Total: 270,328 199,344Some users, including my own developers, need to know how much space is available on the diskgroup from within their own database. It was thought that we had to query asm via SQL*net or have ASM populate a file to be brought into the database to determine how much space was available. This idea has several problems, it is clumsy and complicated, and it is not up to date and must be refreshed often. I noticed, however, what while you cannot query v$asm_disk and get accurate results, you can query v$asm_diskgroup and get accurate results from any database.
SELECT
name group_name
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name;
Disk Group
Name Type Total Size (MB) Used Size (MB) Pct. Used
---------------- ------ --------------- -------------- ---------
DGROUP1 EXTERN 270,328 199,344 73.74
--------------- --------------
Grand Total: 270,328 199,344This query will report the same data from both the ASM instance as well as any databases on the same server. The interesting thing is, even if the database is NOT using ASM, it will report as above since every (10g or 11g) database on a host communicates with ASM via its own “mini-cluster” (ocssd) if not RAC and the RAC cluster if it is.