Query to monitor the ASM diskgroup daily

Script :

spool /oracle/app/script/log/prod_report_asm.log_asm.log
set serveroutput on
declare
v_diskgroup_name varchar2(50);
v_total_space number(12);
v_free_space number(12);
v_pct_free number(6,3);
begin
for diskgroup_rec in (select name from v$asm_diskgroup)
loop
—- Get the total space for the current diskgroup–
select (total_mb/1024) into v_total_space
from v$asm_diskgroup
where NAME = diskgroup_rec.name;
— Get the free space for the current diskgroup–
select (free_mb/1024) into v_free_space
from v$asm_diskgroup
where NAME = diskgroup_rec.name;
— calculate the percent free for the current diskgroup—
v_pct_free := (v_free_space / v_total_space) * 100;
if v_pct_free < 10 then
  dbms_output.put_line(diskgroup_rec.name|| ‘ Percentage Free is ‘ || v_pct_free ||’%’||’ as on ‘||to_char(sysdate,’dd-mm-yy hh24:mi:ss’));
else
 dbms_output.put_line(‘No Diskgroup below 10%’);
end if;
end loop;
end;
/
spool off
exit;

Output :

DG01 Percentage Free is 9.033% as on 30-09-18 16:06:19
No Diskgroup below 10%
No Diskgroup below 10%
DG06 Percentage Free is 7.3% as on 30-09-18 16:06:19
No Diskgroup below 10%

  • September 30, 2018 | 17 views
  • Comments