Installing and checking the Automatic Undo Management (AUM)

DBAs have the choice to manage rollback segments as they used to do under versions Oracle7, Oracle8, and Oracle8i, or to let the RDBMS do it.

1-How to enable AUM

a)Create undo tablespace

create undo tablespace UNDORBS datafile '/data01/undorbs.dbf' size 2048m;

(@undo1.sql)
select TABLESPACE_NAME, CONTENTS,
EXTENT_MANAGEMENT, ALLOCATION_TYPE,
SEGMENT_SPACE_MANAGEMENT
from dba_tablespaces where contents='UNDO';


b) Change init.ora (or spfile )
-Set these parameters

undo_management = AUTO
undo_retention = 900 #15 minutes
undo_tablespace = UNDORBS

-Unset rollback_segments

c)Restart database


2-Determine the size of undo tablespace and tuned undo retention

· requiered undo for UNDO_RETENTION now
(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

The following query calculates the number of bytes needed: (@undo3)

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));


· tuned undoretention and max query (@undo2)
select
to_char(begin_time,'yyyy-mm-dd hh24:mi:ss') starttime,
to_char(end_time,'yyyy-mm-dd hh24:mi:ss') endtime,
undoblks, maxquerylen maxqrylen,maxqueryid,
tuned_undoretention from v$undostat
order by begin_time;

· To gurantee undo_retention (e.g 1440 sec) how much undo rbs is needed in KB (@undo6)
SELECT dbms_undo_adv.required_undo_size(1440) FROM dual

3-Undo advisor

· Current Undo Info (@undo5)
set serveroutput on
DECLARE
tsn VARCHAR2(40);
tss NUMBER(10);
aex BOOLEAN;
unr NUMBER(5);
rgt BOOLEAN;
retval BOOLEAN;
BEGIN
retval := dbms_undo_adv.undo_info(tsn, tss, aex, unr, rgt);
dbms_output.put_line('UNDO Tablespace is: ' tsn);
dbms_output.put_line('UNDO Tablespace size is: ' TO_CHAR(tss));

IF aex THEN
dbms_output.put_line('Undo Autoextend is set to: TRUE');
ELSE
dbms_output.put_line('Undo Autoextend is set to: FALSE');
END IF;

dbms_output.put_line('Undo Retention is: ' TO_CHAR(unr));

IF rgt THEN
dbms_output.put_line('Undo Guarantee is set to: TRUE');
ELSE
dbms_output.put_line('Undo Guarantee is set to: FALSE');
END IF;
END;

· To gurantee undo_retention (e.g 1440 sec) how much undo rbs is needed in KB after running with current undo (@undo6)
SELECT dbms_undo_adv.required_undo_size(1440) FROM dual;
· Best possible retension with current size of Undo(@undo7)
SELECT dbms_undo_adv.best_possible_retention FROM dual;

4-Useful tips for AUM

· You cannot use UNDO tablespaces for other purposes than UNDO SEGMENTS and cannot do any operation on system generated undo segments
· Only one UNDO tablespace can be used at the instance level
alter system set undo_tablespace=undo_rbs1;

· If you choose to use AUM, you have no chance to manage any undo or rollback even on an non UNDO tablespace.

· For Real Application Clusters environments.
a)All instances within Real Application Cluster environments must run in the same undo mode.
b)Set the global parameter UNDO_MANAGEMENT to AUTO in your server parameter file.
c)Set the UNDO_TABLESPACE parameter to assign the appropriate undo tablespace to each respective instance. Each instance requires its own undo
tablespace. If you do not set the UNDO_TABLESPACE parameter, each instance uses the first available undo tablespace


· The undo segments in AUM(@undo4)
select USN,RSSIZE,HWMSIZE,OPTSIZE,SHRINKS,segment_name from v$rollstat,dba_rollback_segs where usn=segment_id;

· Automatic tuning will help to avoid ORA-01555, but if your UNDO tablespace has autoextend off, then you might get into a situation where active DML needs more space--not reusing expired UNDO segments. The database will be under space pressure and Oracle will give higher priority to finishing the DML, and not queries. In that case, users might get ORA-01555 errors, and for this special scenario, you will see the following entry in alert log

“system is under space pressure, now=XXXXXXX”