ASM diskgroup monitoring

ASM  diskgroup  monitoring

For FRA   diskgroup:
SELECT a.name disk_group_name,
    path,
           b.total_mb total_mb,
           (b.total_mb - b.free_mb) used_mb ,
           b.free_mb Free_MB,
           ROUND ( (1 - (b.free_mb / b.total_mb)) * 100, 2) pct_used,
           DECODE (TRUNC ( ( (1 - (b.free_mb / b.total_mb)) * 100 / 32)),
                   0, 'Disk Group OK',
                   1, 'Info Disk Group  entre 32%  et 64% ',
                   2, 'WArning entre  64% et 96% open ticket to DBA P1!',
                   3, 'Urgent   96%  et plus')
              Result
      FROM v$asm_diskgroup a, v$asm_disk b
     WHERE a.group_number = b.group_number AND b.header_status = 'MEMBER'  and
     a.name  in  (select substr(value,2) from  V$parameter  where  name='db_recovery_file_dest')
  ORDER BY ROUND ( (1 - (b.free_mb / b.total_mb)) * 100, 2);

For  diskgroup   in general :

CREATE OR REPLACE FORCE VIEW SYS.RAPPORT_DG
(
  "disk_group_name",
  "path",
  "total_mb",
  "use_MB",
  "Free_MB",
  "pct_used",
  "Result"
)
AS
    SELECT a.name disk_group_name,
    path,
           b.total_mb total_mb,
           (b.total_mb - b.free_mb) used_mb ,
           b.free_mb Free_MB,
           ROUND ( (1 - (b.free_mb / b.total_mb)) * 100, 2) pct_used,
           DECODE (TRUNC ( ( (1 - (b.free_mb / b.total_mb)) * 100 / 32)),
                   0, 'Disk Group OK',
                   1, 'Info Disk Group  entre 32%  et 64%',
                   2, 'WArning entre  64% et 96%',
                   3, 'Urgent   96%  et plus')
              Result
      FROM v$asm_diskgroup a, v$asm_disk b
     WHERE a.group_number = b.group_number AND b.header_status = 'MEMBER'
  ORDER BY ROUND ( (1 - (b.free_mb / b.total_mb)) * 100, 2);

select  *  from  SYS.RAPPORT_DG;