Two methods for managing undo data exists
-Automatic Undo Management(The recommended method from 9i)
-Manual Undo Management(The only method available prior to Oracle9i)

1.Undo segment
An undo segment is used to save old value(undo data) when a process chages data in a database.
It stores the location of the data and the data as it existed before being modified.

The header of an undo segment contains a transaction table where informaiton about the current transactions using the undo segment is stored.
A serial transaction uses only one undo segment to store all of its undo data.
Many concurrent transactions can write to one undo segment.

2.Undo Segments:Purpose
–Transaction Rollback
–Transaction Recovery
If the instance fails while trnasaction are in process,the oracle server needs to undo any uncommitted changes when the database is opened again.

Recovery is possiable only because changes made to the undo segment are also protected by the redo log files.

–Read Consistency
When the Oracle server begins executing a SELECT statement ,it determines the current system change number(SCN) and ensure that any changes not
committed before this SCN are not processed by the statement.

You can set Transaction Read Consistency by issuing the following command at the beginning of the transaction:

1
2
3

SET TRANSACTION READ ONLY;
 
SET TRANSACTION ISOLATION LEVEL SERIALICABLE;

In either case,the Oracle server provides data that is read consistent from the start of the transaction.Using SERIALISZBLE can have negative impact on performance.

3.Types of Undo Segments

.SYSTEM:Used for objects in the SYSTEM tablespace
.Non-SYSTEM:Used for objects in other tablespace:
- Auto Mode:Requireds an UNDO tablespace
- Manual Mode:
- Private:Acquired by a single instance
- Public:Acquired by any instance

.Deferred:Used when tablespaces are taken offline immediate,temporary,or for recovery
Deferred undo segments may be created when tablespace is brought offline.They are used to roll back transactions
when the tablespace is brought back online.They are dropped automatically when they are no longer needed.
Because deferred undo segments wre matained by the Oracle server,ni maintenance is required on your part.

4.Automatic Undo Management Concepts
Undo segments are created with the naming convention:
_SYSSMUn$

Configure two parameters in the initializatoin file;
- UNDO_MANAGEMENT([AUTO[MANUAL]])
UNDO_MANAGEMENT cannot be changed dynamically after the database starts.
In MANUAL mode, the default value,you can create and manage undo segments when needed whithin the database
as in privious versions of the Oracle server.
- UDNO_TABLESPACE
This parameter can be dynamically alterd using the ALTER SYSTEM command.

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.

5.Alter on UNDO tablespace

6.DROP UNDO tablespace
An undo tablespace can only be dropped if it is currently not in use by any instance.
To drop an active UNDO tablespace:
-Switch to a new UNDO talespace

ALTER SYSTEM SET undo_tablespace = UNDOTBS2;

-Drop the tablespace after all current transactons are complete
To determine whether any active transactions exists use the following query:

SELECT a.name,b.STATUS
FROM v$rollname a, v$rollstat b
WHERE a.name IN ( SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS'
)
AND a.usn = b.usn;

-Drop undo tablespace

DROP TABLESPACE UNDOTBS;

7.Automatic Undo Management:Other Parameters
.UNDO_SUPRESS_ERRORS(nolonger exist on 10g)
Set to TRUE,this parameter suppresses errors while attempting to execute manual operatoin in AUTO mode
.UNDO_RETENTION
Controls the amount of undo data to retain for consistent read

8.Undo Data Statistics
V$UNDOSTAT

This VIEW displays a histogram of statistical DATA TO SHOW how well the system IS working.
Each row IN the VIEW keeps statistics collected IN the instance FOR a 10-minute interval.You
can USE this VIEW TO estimate the amount of undo space required FOR the current workload.
The DATABASE uses this VIEW TO tune undo usage IN the system. This VIEW IS available IN BOTH
auto mode AND manual mode.
Although the time interval IS normally 10 minutes, the most recent row will RETURN the time
since its interval started, usually less than 10 minutes.

9.Sizing an UNDO Tablespace
Determining a size for the UNDO tablespace requires three pieces fo information

.(UR)UNDO_RETENTION in seconds
.(UPS)Number of undo data blocks generaged per second
.(DBS)Overhead varies based on extent and file size(db_block_size)
 
Undospace = [UR * (UPS * DBS) ] + (DBS * 24)

SQL to get UPS:

1
2

SELECT (SUM(undoblks) / SUM(end_time - begin_time) * 86400)
FROM v$undostat;

The following query calculates the number of bytes needed:

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 value AS DBS
          FROM v$parameter
         WHERE name = 'db_block_size');

For best results,the caculation should be made during the time of day when the database has its heaviest workload.

10.Undo Quota
-Long transactions and improperly written transactions can consume valuable resurces
-With undo quota users can be grouped and a maximum undo space limit can be assigned to the group
-UNDO_POOL,a resource Manager directive,defines the amount of space allowed for resource group
-When a group exceeds its limit no new transactions are possible,for the group,until undo space
is freed by current transactions either completing or aborting
The amount of undo data generated by a group can be limited by setting a value for UNDO_POOL:
the defaul value is unlimited.

11.Obtaining Undo Segments Information
Data Dictionary View
DBA_ROLLBACK_SEGS
You can obtain infrmation about all the undo segments in the database
Information about undo segments that are offline can be seen only in this view

Dynamic Performance Views
The dynamic performance views show only undo segments that are on line.
V$ROLLNAME
V$ROLLSTAT
Join the V$ROLLSTAT and V$ROLLNAME views to obtain the statistics of the undo segments currently used by the instance

1
2
3
4
5
6
7
8

SELECT n.name,
       s.extents,
       s.rssize,
       s.hwmsize,
       s.xacts,
       s.STATUS
  FROM v$rollname n, v$rollstat s
 WHERE n.usn = s.usn;

V$SESSION
V$TRANSACTION
To check the use of a undo segment by currently active transactions ,join the V$TRANSACTION and V$SESSION views:

1
2
3
4
5
6
7

SELECT s.username,
       t.xidusn,
       t.ubafil,
       t.ubablk,
       t.used_ublk
  FROM v$session s, v$transaction t
 WHERE s.saddr = t.ses_addr;

V$UNDOSTAT

Popularity: 26% [?]