OMS File System Capacity Warning Script
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.
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.
Popular Posts
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.
Oracle EPM Cloud Vs. On-Premises: What’s the Difference?
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.
12c Upgrade Bug with SQL Tuning Advisor
Learn the steps to take on your Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.