Manage UNDO tablespace in Oracle

Manage UNDO Tablespace in Oracle

Undo tablespace is used for rollback the transaction when commit or rollback is occurred during the transaction.
System will give preference to the DML operations againt the undo retention low threshold may not be achieved.
If you want to must achieve the undo retention threshold then you have to use the clause RETENTION GUARANTEE while creating the undo tablespace.
By default it is NO GURANTEE state.

Parameter

UNDO_MANAGEMENT = AUTO/MANUAL (manually or automatic managment) default is automatic
UNDO_TABLESPACE = Name of UNDO table-space

Create undo tablespace in database

CREATE UNDO TABLESPACE undotbs01 DATAFILE 'D:\oradata\undo01.dbf' SIZE 200M REUSE AUTOEXTEND ON;

Add datafile in undo tablespace

ALTER TABLESPACE undotbs01 ADD DATAFILE 'D:\oradata\undo01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Drop the undo tablespace

DROP TABLESPACE undotbs_01;

Switch undo tablespace

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs02;

--Unassign the undo tablespace
ALTER SYSTEM SET UNDO_TABLESPACE = '';

Check the guarantee minimum threshold is maintained or not for undo tablespace.

SELECT tablespace_name, retention FROM dba_tablespaces;

Make undo TABLESPACE to GURANTEE mode

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

Default is no guarantee mode

ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

Check Optimal Value for the parameter UNDO RETENTION
For Calculating the Undo retention parameter based on the UNDO Size, then run the following query for get undo retention parameter value.
Formula:
Optimal Undo Retention = ACTUAL UNDO SIZE / (DB_BLOCK_SIZE × UNDO_BLOCK_PER_SEC)
Query:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
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'
/

Get the optimal size of undo tablespace according to the undo retention parameter then you will fire the following query
Formula:
Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_PER_ESC
Query:
Check Optimal Size for UNDO table-space needed

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
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'
/

Change/Alter the value of UNDO RETENTION

SHOW PARAMETER UNDO_RENTENTION
ALTER SYSTEM SET UNDO_RETENTION = 2400 scope=both;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.