UNDO MANAGEMENT
• An undo segment is used by a transaction that is making changes to a database. Before changing the data or index blocks, the old value is stored in the undo segment. This allows a user to undo changes made.
• Used to store undo segments
• Cannot contain any other objects
• Extents are locally managed
• Can only use the DATAFILE and EXTENT
• MANAGEMENT clauses of the CREATE TABLESPACE
Syntax:
• CREATE UNDO TABLESPACE undo1 DATAFILE '/u01/oradata/undo101.dbf' SIZE 40M;
Configure two parameters in the initialization file:
Deprecated Parameters from 10g:
Deprecation of use of rollback segments (manual undo management mode).
Deprecation of the
UNDO_SUPPRESS_ERRORS initialization parameterEnabling Automatic Undo Management
Since the default undo management mode is MANUAL, the instance must be told to use AUTO mode at instance startup. To do this the following initialization parameters can be set:
UNDO_MANAGEMENT = AUTO # Default is MANUAL
UNDO_TABLESPACE = undotbs_01 # The name of the undo tablespace.
UNDO_RETENTION = 900 # The time undo is retained.
# Default is 900 seconds.
UNDO_SUPPRESS_ERRORS = TRUE # Suppress errors when MANUAL undo admin
# SQL statements are issued.
• Create at least one UNDO tablespace.
• If only one UNDO tablespace exists in the database and UNDO_MANAGEMENT is set to AUTO, then UNDO_TABLESPACE parameter is optional; the Oracle Server will automatically choose the UNDO tablespace.
• Automatic undo management requires a locally managed undo tablespace to store undo segments in. Undo tablespaces can be created during database creation or using the
CREATE UNDO TABLESPACE statement:Syntax: Setting parameter using alter system set
• ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;
• Several of these parameters can be altered while the instance is up, but the UNDO_MANAGEMENT parameter is static:
-- Dynamic Parameters.
• ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS_02;
• ALTER SYSTEM SET UNDO_RETENTION=5;
• ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=FALSE;
•
-- Static Parameters.
• ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;
-- Add a datafile.
• ALTER TABLESPACE undotbs_01 ADD DATAFILE C:\Oracle\Ordata\TSH1\undo0102.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
-- Resize an undo datafile.
• ALTER DATABASE DATAFILE 'C:\Oracle\Ordata\TSH1\undo0102.dbf' RESIZE 10M;
-- Perform backup operations
• ALTER TABLESPACE undotbs_01 BEGIN BACKUP;
• ALTER TABLESPACE undotbs_01 END BACKUP;
-- Drop an undo tablespace.
• DROP TABLESPACE undotbs_01;
Monitoring
Undo information can be queried using the following views:
V$UNDOSTATV$ROLLSTATV$TRANSACTIONDBA_UNDO_EXTENTSSELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT,
You can determine the current retention period by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT view. This view contains one row for each 10-minute statistics collection interval over the last 4 days. (Beyond 4 days, the data is available in the DBA_HIST_UNDOSTAT view.) TUNED_UNDORETENTION is given in seconds.
select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention
from v$undostat order by end_time;
BEGIN_TIME END_TIME TUNED_UNDORETENTION
--------------- --------------- -------------------
04-FEB-05 00:01 04-FEB-05 00:11 12100
...
07-FEB-05 23:21 07-FEB-05 23:31 86700
07-FEB-05 23:31 07-FEB-05 23:41 86700
07-FEB-05 23:41 07-FEB-05 23:51 86700
07-FEB-05 23:51 07-FEB-05 23:52 86700
576 rows selected.
## Calculate Needed UNDO Size for given Database Activity
If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:
Again, all in one query:
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'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
NEEDED UNDO SIZE [MByte]
------------------------
131.695313
Execute the following query to determine who is using a UNDO Segment:
SELECT TO_CHAR(s.sid)', 'TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024'K' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'
/
SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON"
FROM v$UNDOSTAT WHERE rownum <= 144;
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TXNCOUNT MAXCON
------------------- ------------------- ---------- ---------- ---------- ----------
10/28/2004 14:25:12 10/28/2004 14:32:17 8 74 12071108 3
10/28/2004 14:15:12 10/28/2004 14:25:12 8 49 12070698 2
10/28/2004 14:05:12 10/28/2004 14:15:12 8 125 12070220 1
10/28/2004 13:55:12 10/28/2004 14:05:12 8 99 12066511 3
...
10/27/2004 14:45:12 10/27/2004 14:55:12 8 15 11831676 1
10/27/2004 14:35:12 10/27/2004 14:45:12 8 154 11831165 2
144 rows selected.