Despite rarely posting these days, this one is worth trying to get the word out. We just ran across a 18.104.22.168 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.
- DBA_TABLESPACE_USAGE_METRICS does not list all the expected tablespaces (Doc ID 2068817.1)
- Bug 21239530 : DIFFERENCES IN DBA_TABLESPACES AND DBA_TABLESPACE_USAGE_METRICS
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 22.214.171.124.4 version of the patch to apply on our 126.96.36.199.160119 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;