How to calculate the automatically managed UNDO tablespace size

How to calculate the automatically managed UNDO tablespace size

AUTO UNDO

Automatic undo Management (AUM) requires almost no configuration. Basically, you only need to define the time needed to keep the previous image available. This is controlled by the UNDO_RETENTION parameter, defined in seconds. Therefore, the value 900 indicates 15 minutes.

We must be aware that if there is space pressure in the undo tablespace, we cannot guarantee that the previous image will be retained for such a long time.
Therefore, the following formula can be used to calculate the optimal undo tablespace size:
Starting from Oracle 10 Gb, you can choose to use the GUARANTEE option to ensure that the undo information will not be overwritten before the defined undo_retention time.

The UNDO tablespace size consists of three parts:
(UR) UNDO_RETENTION unit: seconds
(UPS) Number of undo data blocks generated per second
(DBS) database data file block size DB_BLOCK_SIZE
Calculation Formula
UndoSpace = UR * (UPS * DBS)
The UNDO_RETENTION and DB_BLOCK_SIZE information can be obtained in the instance configuration parameter information.
The third part of the UPS information needs to be obtained from dynamic performance attempts V $ UNDOSTAT
The following figure shows the number of undo data blocks generated per second by UPS.
SQL> SELECT undoblks/(end_time-begin_time) * 86400) "Peak Undo Block Generation"
FROM v $ undostat WHERE undoblks = (select max (undoblks) FROM v $ undostat );
The column END_TIME and BEGIN_TIME are of the date type and need to be converted to seconds (24 hours * 60 minutes * 60 seconds ).

The following SQL statement calculates the UNDO tablespace size.
SQL> SELECT (UR * (UPS * DBS) AS "Bytes"
FROM (SELECT value as ur from v $ parameter WHERE name = 'undo _ retention '),
(SELECT undoblks/(end_time-begin_time) * 86400) AS UPS
FROM v $ undostat
WHERE undoblks = (select max (undoblks) FROM v $ undostat )),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name = (select upper (value) FROM v $ parameter WHERE name = 'undo _ tablespace '));

For versions of 10g and 10g, you can use the following query:
SQL> SELECT (UR * (UPS * DBS) AS "Bytes"
FROM (select max (tuned_undoretention) as ur from v $ undostat ),
(SELECT undoblks/(end_time-begin_time) * 86400) AS UPS
FROM v $ undostat
WHERE undoblks = (select max (undoblks) FROM v $ undostat )),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name = (select upper (value) FROM v $ parameter WHERE name = 'undo _ tablespace '));

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

Undo tablespace loss caused by rman backup and recovery

About Oracle releasing over-used undo tablespace

Oracle undo

Oracle undo image data exploration

Oracle ROLLBACK and undo)

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

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