Dream for inspiration

Applies to:

Symptoms

Look for: 

1.) System Managed Undo 
undo_management=auto in init.ora file 

2.) Fixed size undo tablespace
SQL> select autoextensible from dba_data_files where tablespace_name='<current_undo_tablespace >' 

returns "No" for all the undo tablespace datafiles. 

3.) The undo tablespace is already sized such that it always has more than enough space to store all the undo generated within the undo_retention time, and the in-use undo space never exceeds the undo tablespace warning alert threshold (see below for the query to show the thresholds). 

4.) The tablespace threshold alerts recommend that the DBA add more space to the undo tablespace: 

SQL> select creation_time, metric_value, message_type,reason, suggested_action from dba_outstanding_alerts where object_name='<current_undo_ts>'; 

returns a suggested action of: "Add space to the tablespace" 

Or,

This recommendation has been reported in the past but the condition has now cleared: 
SQL> select creation_time, metric_value, message_type, reason, suggested_action, resolution from dba_alert_history where object_name='<current_undo_ts>'; 

5.) The undo tablespace in-use space exceeded the warning alert threshold at some point in time: 

To see the warning alert percentage threshold: 

SQL> select object_type, object_name, warning_value, critical_value from dba_thresholds where object_type='TABLESPACE'; 

To see the (current) undo tablespace percent of space in-use: 
SQL> select 
((select (nvl(sum(bytes),0)) 
from dba_undo_extents 
where tablespace_name='<current_undo_ts>' 
and status in ('ACTIVE','UNEXPIRED')) *100) / 
(select sum(bytes) 
from dba_data_files 
where tablespace_name='<current_undo_ts>') 
"PCT_INUSE" 
from dual;

Cause

Solution

Apply the patch for the Bug 5387030. Check metalink for patch availability for your platform and RDBMS version.

Workaround

There are 3 possible alternate workarounds (any one of these should resolve the problem of the alerts triggering unnecessarily): 

1.) Set the autoextend and maxsize attribute of each datafile in the undo ts so it is autoextensible and its maxsize is equal to its current size so the undo tablespace now has the autoextend attribute but does not autoend: 
SQL> alter database datafile '<datafile_flename>' 
autoextend on maxsize <current_size>; 

With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the undo tablespace size, instead v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.

2.) Set the following hidden parameter in init.ora file: 
_smu_debug_mode=33554432 

or 

SQL> Alter system set "_smu_debug_mode" = 33554432; 

With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the fixed size undo tablespace, instead v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.

3.) Set the following hidden parameter in init.ora: 
_undo_autotune = false 

or 

SQL> Alter system set "_undo_autotune" = false; 

With this setting, v$undostat (and therefore v$undostat.tuned_undoretention) is not maintained and and the undo_retention used is the one specified in init.ora file.

References