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;
