Select Page

OMS File System Capacity Warning Script

Author: Peter Schroeder | | March 3, 2016

Sometimes you can have multiple databases sharing file systems on a server in your enterprise. Alone all the databases may not have enough auto extend datafiles to show you will exceed capacity of the file system. But if you took all the auto extend datafiles in all the databases, you will find, you have some over allocated file systems, and if they all extend to their max size, you will fill up the file system.

The script below will take advantage of your OMS to query the underlying tables to show you all file systems that are above threshold and have auto extend datafiles that can exceed the capacity of the filesystem.

This query takes 2 bind variables:

oms_group_name -> and OMS group name

pct-thershold -> the pct full a files system hast to be equal or above to be reported on

WITH data_files
     AS (  SELECT t.target_name,
                  t.host_name,
                  df.file_name,
                  MAX (mc.key_value) file_system,
                  df.file_size,
                  df.max_file_size,
                  df.autoextensible
             FROM MGMT$GROUP_MEMBERS gm,
                  MGMT$TARGET t,
                  MGMT$TARGET th,
                  MGMT$METRIC_CURRENT mc,
                  MGMT$DB_DATAFILES df
            WHERE     gm.group_name = :oms_group_name
                  AND gm.target_type = 'oracle_database'
                  AND t.target_guid = gm.target_guid
                  AND th.target_name = t.host_name
                  AND df.target_guid = t.target_guid
                  AND mc.target_type = 'host'
                  AND mc.target_name = t.host_name
                  AND mc.metric_name = 'Filesystems'
                  AND mc.metric_column = 'available'
                  AND df.file_name LIKE mc.key_value || '%'
                  AND (   (    th.type_qualifier1 = 'Windows'
                           AND df.file_name LIKE mc.key_value || '%')
                       OR (df.file_name LIKE mc.key_value || '/%'))
         GROUP BY t.target_name,
                  t.host_name,
                  df.file_name,
                  df.file_size,
                  df.max_file_size,
                  df.autoextensible),
     file_systems
     AS (  SELECT mc.target_name host_name,
                  mc.key_value file_system,
                  ROUND (MAX (mc3.VALUE) / 1024, 2) file_system_size,
                  ROUND (MAX (mc3.VALUE - mc.VALUE) / 1024, 2) file_system_used,
                  ROUND (MAX (mc.VALUE) / 1024, 2) file_system_avail,
                  ROUND ( (100 - MAX (mc2.VALUE)), 1) file_system_capacity
             FROM MGMT$GROUP_MEMBERS gm,
                  MGMT$METRIC_CURRENT mc,
                  MGMT$METRIC_CURRENT mc2,
                  MGMT$METRIC_CURRENT mc3
            WHERE     gm.group_name = :oms_group_name
                  AND gm.target_type = 'host'
                  AND mc.target_guid = gm.target_guid
                  AND mc.metric_name = 'Filesystems'
                  AND mc.metric_column = 'available'
                  AND mc2.target_guid = mc.target_guid
                  AND mc2.key_value = mc.key_value
                  AND mc2.metric_name = 'Filesystems'
                  AND mc2.metric_column = 'pctAvailable'
                  AND mc3.target_guid = mc.target_guid
                  AND mc3.key_value = mc.key_value
                  AND mc3.metric_name = 'Filesystems'
                  AND mc3.metric_column = 'size'
         GROUP BY mc.target_name, mc.key_value),
     combined_size
     AS (  SELECT fs.host_name,
                  fs.file_system,
                  fs.file_system_size,
                  fs.file_system_used,
                  fs.file_system_avail,
                  fs.file_system_capacity capacity,
                  ROUND (SUM (df.file_size) / 1024 / 1024 / 1024, 1)
                     Current_db_usage,
                  ROUND (
                       SUM (
                          CASE
                             WHEN df.file_size > NVL (df.max_file_size, 0)
                             THEN
                                df.file_size
                             ELSE
                                df.max_file_size
                          END)
                     / 1024
                     / 1024
                     / 1024,
                     1)
                     max_db_usage
             FROM data_files df, file_systems fs
            WHERE     df.host_name = fs.host_name
                  AND df.file_system = fs.file_system
         GROUP BY fs.host_name,
                  fs.file_system,
                  fs.file_system_size,
                  fs.file_system_used,
                  fs.file_system_avail,
                  fs.file_system_capacity)
  SELECT cs.*
    FROM combined_size cs
   WHERE     (max_db_usage - current_db_usage) > file_system_avail
         AND capacity > :pct_threshold
ORDER BY UPPER (host_name), UPPER (file_system);

If you try out the script, let us know how it works for you. We’d love to hear from you.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

Using Nulls in DB2

If a column “value” can be null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known.

Craig Mullins | April 6, 2015

MongoDB Best Practices: Design, Deployment & More

This post provides a rundown of best practices to use when running MongoDB.

Esayas Aloto | February 28, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS