Tablespace full - alerts not working

We have had an issue with OEM not raising alerts for several 11GR1 databases where the tablespaces had filled past their threshold.  Someone from Oracle who works on OEM has pointed out that OEM is not at fault in this case, EM can only do what it is designed to do, and for 10g and 11g database it is designed to consume alerts that originate from the database.  If the database doesn’t raise them OEM can’t make them up. In one case this had caused a production interruption as it was a small datafile which was not normally used but a new business process had sent a lot of data in at once and autoextend was not enabled.

Oracle pointed us at bug 8538700 OUTSTANDING_ALERTS NOT SHOWING ALERT FOR TABLESPACE THAT EXCEEDED THRESHOLD which is shown as relevant to 10.2.0.4 on any platform with a resolution being to upgrade to either 10.2.0.5 or 11.2.0.2 (when available). See Doc ID 870602.1

There is also a patch for those on 10.2.0.4 who wish to remain there (7462407) but it is only available for Linux x86, AIX and HP Itanium.

 patch 9694200 which includes fixes for bug 8970313 and bug 8970313has been created for 11.1.0.7.3 on HP-UX Itanium. So you will need to install PSU 11.1.0.7.3 (rollback 11.1.0.7.1) then patch 9694200.

Not ideal and a better solution is to remove the problem by setting autoextend on all tablespaces

The SQL to prove the problem is below

<table width="99%">
<tbody>
<tr>
<td>

SELECT TABLESPACE_NAME TBSP_NAME
, USED_SPACE
, TABLESPACE_SIZE TBSP_SIZE
, USED_PERCENT
FROM SYS.DBA_TABLESPACE_USAGE_METRICS;

It shows the tablespace as 100% full.

SELECT METRICS_NAME
, WARNING_OPERATOR WARN_OP
, WARNING_VALUE WARN_VAL
, CRITICAL_OPERATOR CRIT_OP
, CRITICAL_VALUE CRIT_VAL
, OBJECT_TYPE OBJ_TYPE
, OBJECT_NAME OBJ_NAME
, STATUS
FROM SYS.DBA_THRESHOLDS
WHERE metrics_name LIKE ‘%Tablespace%’; -- Do not replace the string ‘Tablespace’

it show the critical limit as 95%

SELECT REASON
, METRIC_VALUE
, MESSAGE_TYPE
, TO_CHAR(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS')
, HOST_ID
FROM SYS.DBA_OUTSTANDING_ALERTS;

No alerts are displayed.

About these ads