Bug in tablespace size/usage monitoring in OEM (DBA_TABLESPACE_USAGE_METRICS)

Despite rarely posting these days, this one is worth trying to get the word out.  We just ran across a bug which is not fixed in the January or April bundle patches/PSUs.  It is also not mentioned in the 12c “you should install these one-off patches” page.  Yet it has the ability to take down your production environment.

The view DBA_TABLESPACE_USAGE_METRICS is used by OEM to drive its alerts on tablespace storage.  It turns out that due to a bug in the code behind the view, it leaves out a number of tablespaces entirely.  You can easily see the problem by comparing DBA_TABLESPACES and DBA_TABLESPACE_USAGE_METRICS.  This means that if any of the missing tablespaces runs out of space (hits the max autoextend) you won’t get any of your percentage-free alerts before it does.  We found anywhere from one to over 70 missing tablespaces, depending on the database.  Every database was missing at least one tablespace from this view.

You can easily see the problem by comparing DBA_TABLESPACES and DBA_TABLESPACE_USAGE_METRICS:

select tablespace_name from dba_tablespaces 
where tablespace_name not in (
    select tablespace_name from DBA_TABLESPACE_USAGE_METRICS

We thought we were smart by having another backup monitor running from cron, in case OEM missed sending a notification or we missed it.  Except we used the same view in our monitor, so it misses these tablespaces as well.  Just goes to show that it’s always tough to judge how paranoid to get when trying to eliminate dependencies in redundant systems.

I’m pretty miffed that this has not been deemed important enough issue to be included in a bundle patch.  It’s a big deal, but apparently Oracle doesn’t think so.  If they’d even just referenced it on their page about important one-offs to apply to 12c, we would have already had it in production as of our most recent patching round.

Edit: Oracle Support recommended the version of the patch to apply on our Oracle home, and it worked, the missing regular tablespaces now show up in the view.  However, the Oracle doc on this says that “DBA_TABLESPACE_USAGE_METRICS describes tablespace usage metrics for all types of tablespaces, including permanent, temporary, and undo tablespaces.”

After applying the patch, all of the normal tablespaces are now included in the view:

select distinct tablespace_name from dba_data_files minus select tablespace_name from dba_tablespace_usage_metrics;

no rows selected

HOWEVER the TEMP tablespaces are still missing – is this a new bug?  Updated SR to find out, will update here when I know.

select distinct tablespace_name from dba_temp_files minus select tablespace_name from dba_tablespace_usage_metrics;







Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s