2018-07-31

How To Check if ASM Diskgroup is Not Overbooked


The query below can be used in order to recognize overbooked ASM diskgroups.
The value of column real_avail_gb should be >= 0

-----------------------------------------------------------------------
-- total_gb      : Physical size of the disk group in GB
-- usable_gb     : Corresponds to usable_file_mb in GB
--                 i.e. Amount of free space that can be safely
--                 utilized 
--                 taking mirroring into account and yet be able to
--                 restore redundancy after a disk failure
-- df_maxsizes_gb: Sum of the datafiles max sizes in GB
-- real_avail_gb : (total_gb - real_avail_gb)
--                 Shows if there would be enough space on the
--                 diskgroup even if all files would grow up to their
--                 maxsize
-----------------------------------------------------------------------
SELECT name, total_gb, usable_gb, df_maxsizes_gb,
       (total_gb - df_maxsizes_gb) real_avail_gb
  FROM
  (
    SELECT name, total_mb/1024 total_GB,
           -- free_mb/1024 free_gb,
           usable_file_mb/1024 usable_gb,
           DF.maxsize_gb df_maxsizes_gb
      FROM v$asm_diskgroup,
           ( 
             SELECT SUBSTR(file_name, 2, 
                          INSTR(file_name, '/', 1) - 2) disk_group,
                   SUM(DECODE(maxbytes,0,bytes/(1024*1024*1024),
                              maxbytes/(1024*1024*1024))
                      ) maxsize_gb
               FROM sys.dba_data_files
              GROUP BY SUBSTR(file_name, 2, INSTR(file_name, '/', 1) - 2)
           ) DF
     WHERE DF.disk_group = name
  )
 ORDER BY 1;

Example output:
NAME
TOTAL_GB
USABLE_GB
DF_MAXSIZES_GB
REAL_AVAIL_GB
U01
1600
236.484375
1504.064453125
95.935546875



The query from above was built based on the following queries
-----------------------------------------------------------------------
-- Quering the ASM diskgroups directly from the DB
-- (no need to logon to the ASM DB instance)
-----------------------------------------------------------------------
SELECT name,
       total_mb/1024 total_GB,
       free_mb/1024 free_gb,
       usable_file_mb/1024 usable_gb
  FROM v$asm_diskgroup;


-----------------------------------------------------------------------
-- Tablespace MaxSizes by ASM diskgroup
-- Precondition: Diskgroup starts with +.
-- For example: file_name="+U01/folder/file.ext" => disk_group="U01" 
-----------------------------------------------------------------------
SELECT SUBSTR(file_name, 2, INSTR(file_name, '/', 1) - 2) disk_group,
       tablespace_name,
       SUM(DECODE(maxbytes,0,bytes/(1024*1024*1024),
           maxbytes/(1024*1024*1024))
       ) maxsize_gb
  FROM sys.dba_data_files
 GROUP BY SUBSTR(file_name, 2, INSTR(file_name, '/', 1) - 2), tablespace_name
 ORDER BY 1;


No comments :

Post a Comment