Wednesday, November 16, 2011

OEM: "Tablespace Used (%)" alert is not triggered

There was an incident, where a tablespace filled completely, but I did not get any alert from OEM.

There is Oracle Note 403264.1:"Troubleshooting a Database Tablespace Used(%) Alert problem", where several issues about this alert are discussed.
In my case, there was no entry in DBA_OUTSTANDING_ALERTS,
although in DBA_TABLESPACE_USAGE_METRICS the particular tablespace had exceeded the thresholds I have set.

So, according to Section 4 in this note, this is probably caused by database bugs 7462407, 5245039.
Additionally, I have a "gap" in my v$tablespace.

Unfortunately, there are no patches for these bugs for Windows platforms, so I created a user defined metric to get these alerts.
The new metric was based on DBA_TABLESPACE_USAGE_METRICS, which is very nice, because it queries v$filespace_usage, which keeps track of any MAXSIZE value you may have set in your autoextended datafiles.
If it is set to UNLIMITED, then this value is equal to 32 GB for 8 KB block size (2^22-1 * 8 KB).

Keep in mind, columns referencing sizes in these 2 views are blocks.
So, if your tablespace has block size of 8 KB, multiply these values by 8 to get its size in KB.

Of course, there is no way for your database to know, if your filesystems will fill, e.g. if in your filesystem you have 10 GB of free space and an autoextended datafile in it with MAXSIZE 15 GB, you will not get any alert, even from the default OEM metric, when your filesystem's free space depletes.
You should use "Filesystem Space Available (%)" alert at host level, to check your filesystem's free space.

No comments:

Post a Comment