Undo_retention & Undo Tablespace in Oracle

I will explain Undo_retention & Undo Tablespace in Oracle in this post.

Undo_retention & Undo Tablespace in Oracle

UNDO_RETENTION (in seconds) is very important parameter in Oracle that specifies the low threshold value of undo retention. The system retains undo for at least the time specified in this parameter For AUTOEXTEND undo tablespaces, and automatically tunes the undo retention period to satisfy the undo requirements of the queries.

Oracle database automatically tunes for the maximum possible undo retention period For fixed- size undo tablespaces, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

How to calculate optimum undo_retention Oracle ?

To determine the optimal value for the UNDO_RETENTION parameter, you must first calculate the actual amount of undo that the database is generating. Once you know approximately how much undo the database is generating, you can calculate a more precise value for the UNDO_RETENTIONparameter.

Use the following formula to calculate the optimal value of the UNDO_RETENTION parameter:

OPTIMAL UNDO_RETENTION = UNDO SIZE/(DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC)

Calculate the current space allocated for undo

SQL> select sum(d.bytes) "undo"
 from v$datafile d,
 v$tablespace t,
 dba_tablespaces s
 where s.contents = 'UNDO'
 and s.status = 'ONLINE'
 and t.name = s.tablespace_name
 and d.ts# = t.ts#;

undo
----------
68232937472

Calculate the value of UNDO_BLOCKS_PER_SEC

select max(undoblks/((end_time-begin_time)*3600*24))
 "UNDO_BLOCK_PER_SEC"
 FROM v$undostat;

UNDO_BLOCK_PER_SEC

------------------

359.366667

Check the block size for your database:

SQL> show parameter db_block_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_size integer 8192

Calculate the optimal value for the UNDO_RETENTION parameter using the formula, for example, giving a result in seconds: 23,158 = 68232937472/(359.366667 * 8,192).

In this case, assigning a value of 1,800 seconds for the undo_retentionparameter is appropriate.

If disk space isn’t an issue for you, you can instead choose the ideal value for the UNDO_RETENTION parameter for
your instance, The formula now becomes:

size of undo = undo_retention * db_block_size :undo_block_per_sec

SELECT d.undo_size / (1024 * 1024) "Current UNDO SIZE",
SUBSTR (e.VALUE, 1, 25) "UNDO RETENTION",
(TO_NUMBER (e.VALUE) * TO_NUMBER (f.VALUE) * g.undo_block_per_sec)
/ (1024 * 1024)
"Necessary UNDO SIZE"
FROM (SELECT SUM (a.BYTES) undo_size
FROM v$datafile A, v$tablespace b, dba_tablespaces C
WHERE c.CONTENTS = 'UNDO'
AND c.status = 'ONLINE'
AND b.NAME = c.tablespace_name
AND a.ts# = b.ts#) d,
v$parameter E,
v$parameter f,
(SELECT MAX (undoblks / ( (end_time - begin_time) * 3600 * 24))
undo_block_per_sec
FROM v$undostat) G
WHERE e.NAME = 'undo_retention' AND f.NAME = 'db_block_size';

Current UNDO SIZE     UNDO RETENTION            Necessary UNDO SIZE

---------------------      ---------------------           -------------------------

65072                           86400                                                     242572.5

How to change undo_retention in Oracle RAC

The query shows that you should set the size of the undo tablespace in this database to around 242572.5 MB.
Set the undo retention to 86400  seconds as follows:

SQL> alter system set undo_retention=86400  scope=both sid='*';

If you don’t set the undo_retention the required value, you will get the following error.

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

This error occurs when your undo_retention parameter is small or undo_tablespace size is insufficient. You need to increase undo_retention parameter like below steps in Oracle 19c. Or you should add datafile to the undo_tablespaces to increate tablespace size.

[oracle@MehmetSalih admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 1 14:06:29 2019
Version 19.2.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL>

SQL> alter system set undo_retention=90000 scope=both sid='*';

System altered.

SQL>

Or Add datafile to Undo tablespace as follows.

If you use ASM Storage then run the following.

ALTER TABLESPACE UNDOTBS01 ADD DATAFILE '+DATA' SIZE 15M AUTOEXTEND ON MAXSIZE UNLIMITED;

If you use File system Storage then run the following.

ALTER TABLESPACE UNDOTBS01 ADD DATAFILE '/oradata/MSDB/undotbs0101.dbf' SIZE 15M AUTOEXTEND ON MAXSIZE UNLIMITED;

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )