Joe's Oracle

Undo & Rollback

In Oracle versions up to 9i undo management is performed by Rollback Segments. From 9i Undo tablespaces can be used, Oracle recommeds using Undo tablespaces as they are easier to manage. You cannot use both methods in the same database instance.

Creating rollback segments
Monitoring rollback segments
Corrupted Rollback segments
Explicitly assign rollback segments

Undo Tablespaces

Rollback segments are used to provide a read consistent view of data to all users. If a user executes a query that uses data that has been changed but not committed by a second user, the query will return the data as it existed before the change was made. The query will read the previous version of the data from the rollback segment. If the data required by the query has been overwritten by data from another transaction, the user executing the query will receive an ORA-01555 ("snapshot too old") error message. Large and small transactions use the same rollback segments unless you specifically use the set transaction use rollback segment command after every commit. Oracle allocates users to rollback segments based on the number of users using the rollback segment. When a transaction's rollback segment entry cannot be stored within a single extent, the entry wraps into a second extent.

Rollback Segment Creation

Guidelines for Creation

Dedicate a seperate tablespace for rollbacks, online rollbacks stop a tablespace being taken offline.

Seperate large and small rollbacks into seperate tablespaces. If some rollbacks in the same tablespace have different extent sizes make sure they are multiples of the other rollbacks extent sizes. This maximises the chances of dropped extents being reused.

Use large rollbacks for long-running updates.If a system has a high number of updates performance is enhanced (up to 50%) with larger rollbacks, so this is a good idea for overnight batch jobs that load data etc.A transaction can be told to use the large rollback by issuing the statement 'SET TRANSACTION ROLLBACK SEGMENT'. Take smaller rollbacks offline when large batch jobs are running if it is not possible to use the 'SET TRANSACTION' command.

Use small rollbacks for OLTP systems.

Do not have too many transactions per rollback (max 4-6).

Assign long running transactions to large rollbacks. Keep the others small so that they have a chance of fitting in the buffer cache (5-10% performance increase).

Use OPTIMAL only if the number of shrinks a day is small (< 10). Usually leave it alone.
CREATE [PUBLIC] ROLLBACK SEGMENT name
TABLESPACE tablespace_name
[STORAGE (
[INITIAL integer K | M]
[NEXT integer K | M]
[MINEXTENTS integer]
[MAXEXTENTS integer | UNLIMITED]
[PCTINCREASE integer]
[FREELISTS integer]
[FREELIST GROUPS integer]
[OPTIMAL integer K | M]
) ]

Explicitly Assigning a Transaction to a Rollback Segment

A transaction can be explicitly assigned to a specific rollback segment using the SET TRANSACTION statement with the USE ROLLBACK SEGMENT clause.
The rollback segment must be online for the current instance, and the SET TRANSACTION USE ROLLBACK SEGMENT statement must be the first statement of the transaction. If a specified rollback segment is not online or a SET TRANSACTION USE ROLLBACK SEGMENT clause is not the first statement in a transaction, an error is returned.
SET TRANSACTION USE ROLLBACK SEGMENT large_rs1;
After the transaction is committed, Oracle will automatically assign the next transaction to any available rollback segment unless the new transaction is explicitly assigned to a specific rollback segment by the user.

Rollback Segment Monitoring

Examine disk I/O on devices that contain rollback segments.

Displaying Whether a Rollback Segment Has Gone Offline
------------------------------------------------------

When you take a rollback segment offline, it does not actually go offline until all active transactions in it have completed. Between the time when you attempt to take it offline and when it actually is offline, its status in DBA_ROLLBACK_SEGS remains ONLINE, but it is not used for new transactions. To determine whether any rollback segments for an instance are in this state, use the following query:
SELECT name, xacts 'ACTIVE TRANSACTIONS'
FROM v$rollname, v$rollstat
WHERE status = 'PENDING OFFLINE'
AND v$rollname.usn = v$rollstat.usn;

Rollback Segment - Corrupted Datafile

Rollback Segment - Snapshot too old

Optimal set too high.
Not enough rollbacks, a large number of rollbacks will mean that rollback data, for completed transactions, will be kept longer.
Rollbacks not big enough, or make sure that cursors are closed appropriately.
Large batch jobs running against OLTP users.In this case start the batch job, determine which rollback it is using and take it offline, this will prevent further users from using it and so prevent snapshot too old).
Too few checkpoints in trusted Oracle.

Undo Tablespaces

The following initialization parameter setting causes the STARTUP command to start an instance in automatic undo management mode:

UNDO_MANAGEMENT = AUTO

If there is no undo tablespace available, the instance starts, but uses the SYSTEM rollback segment. This is not recommended in normal circumstances, and an alert message is written to the alert file to warn that the system is running without an undo tablespace.

Undo Tablespaces parameters

UNDO_MANAGEMENT If AUTO, use automatic undo management mode. If MANUAL, use manual undo management mode.

UNDO_TABLESPACE A dynamic parameter specifying the name of an undo tablespace to use.

UNDO_RETENTION A dynamic parameter specifying the length of time to retain undo. Default is 900 seconds.

UNDO_SUPPRESS_ERRORS If TRUE, suppress error messages if manual undo management SQL statements are issued when operating in automatic undo management mode. If FALSE, issue error message. This is a dynamic parameter.