How to monitor space usage on ASM diskgroups
Last night the recovery area on one of our Oracle Database Appliances went full. This was reported in one of the database alert logs, and we were able to clear out some space before the next log switch, at which point the production would have come to a halt.
It certainly would have been nice to have a little more warning, like when the disk group was 70% full.
What options do we have for monitoring disk usage inside ASM?
@Patrick, interestingly we spent more time managing OEM than we did Oracle DB, so we discontinued our use of OEM some time back.
Just use
asmcmd. For example:[[email protected] ~]$ . oraenv ORACLE_SID = [+ASM] ? +ASM The Oracle base remains unchanged with value /u01/app/oracle [[email protected] ~]$ asmcmd lsdg State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 4096 1048576 30708 28479 0 28479 0 N DATA/ [[email protected] ~]$Or get the info directly from the ASM Oracle instance:
[[email protected] ~]$ . oraenv ORACLE_SID = [+ASM] ? +ASM The Oracle base remains unchanged with value /u01/app/oracle [[email protected] ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 7 10:44:44 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Automatic Storage Management option SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup; NAME FREE_MB TOTAL_MB PERCENTAGE ------------------------------ ---------- ---------- ---------- DATA 28479 30708 92.7413052 SQL>OEM also has the info when you have that, and can probably be coerced into raising alerts. The `v$asm_diskgroup` view is also available on the "client" database(s) (might depend on exact versions), which is pretty practical if you already have custom monitoring scripts in there.
The v$asm_diskgroup view is very convenient. Thanks! Although, the TOTAL_MB and FREE_MB columns appear to show raw capacity as opposed to usable space, is that right?
See Managing Capacity in Disk Groups for the details on that view, @Roy.
2nd option useful. Hope this may also useful here: https://ora-data.blogspot.com/2016/12/how-to-find-details-of-tablespace.html
Try this...
col gname form a10 col dbname form a10 col file_type form a14 SELECT gname, dbname, file_type, round(SUM(space)/1024/1024) mb, round(SUM(space)/1024/1024/1024) gb, COUNT(*) "#FILES" FROM ( SELECT gname, regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname, file_type, space, aname, system_created, alias_directory FROM ( SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path, system_created, alias_directory, file_type, space, level, gname, aname FROM ( SELECT b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, a.alias_directory, c.type file_type, c.space FROM v$asm_alias a, v$asm_diskgroup b, v$asm_file c WHERE a.group_number = b.group_number AND a.group_number = c.group_number(+) AND a.file_number = c.file_number(+) AND a.file_incarnation = c.incarnation(+) ) START WITH (mod(pindex, power(2, 24))) = 0 AND rindex IN ( SELECT a.reference_index FROM v$asm_alias a, v$asm_diskgroup b WHERE a.group_number = b.group_number AND ( mod(a.parent_index, power(2, 24))) = 0 and a.name like '&&db_name' ) CONNECT BY prior rindex = pindex ) WHERE NOT file_type IS NULL and system_created = 'Y' ) WHERE dbname like '&db_name' GROUP BY gname, dbname, file_type ORDER BY gname, dbname, file_type /
License under CC-BY-SA with attribution
Content dated before 6/26/2020 9:53 AM
Patrick one year ago
If you want monitoring and alerting of oracle components, I would really recommend setting up an Oracle Enterprise Manager installation. It will provide you with ASM monitoring, can send alerts via email, snmp, etc. https://oracle-base.com/articles/13c/cloud-control-13cr1-installation-on-oracle-linux-6-and-7