module10-undo_data.htm; updated 6/14/2009; Figures shown in these notes are from Oracle document D11321GC11, Production 1.1, December 2001, D34286

Module 10 – Managing Undo Data

Objectives

These notes teach you about managing undo data including the method used to implement automatic undo data management. You will also learn to create and modify undo segments and how to query the data dictionary to retrieve undo segment information.

Transactions

Transaction – collection of SQL data manipulation language (DML) statements treated as a logical unit.

· Failure of any statement results in the transaction being "undone".

· If all statements process, SQLPlus or the programming application will issue a COMMIT to make database changes permanent.

· Transactions implicitly commit if a user disconnects from Oracle normally.

· Abnormal disconnections result in transaction rollback.

· The command ROLLBACK is used to cancel (not commit) a transaction that is in progress.

SET TRANSACTION – Transaction boundaries can be defined with the SET TRANSACTION command.

· This has no performance benefit achieved by setting transaction boundaries, but doing so enables defining a savepoint.

· Savepoint – allows a sequence of DML statements in a transaction to be partitioned so you can roll back one or more or commit the DML statements up to the savepoint.

· Savepoints are created with the SAVEPOINT savepoint_name command.

· DML statements since the last savepoint are rolled back with the ROLLBACK TO SAVEPOINT savepoint_name command.

Undo vs. Rollback

In earlier versions of Oracle, the term rollback was used instead of undo, and instead of managing undo segments, the DBA was responsible for managing rollback segments. Rollback segments were one of the primary areas where problems often arose; thus, the conversion to undo management is a significant improvement.

Undo Segments

There are two methods for managing undo data:

(1) automatic undo management – automatic undo management is preferred and is the type used when you create an UNDO tablespace and specify use of automatic undo management

(2) manual undo management – manual undo management is the only method available for Oracle 8i and earlier versions of Oracle and is the type of management that involves use of rollback segments.

Undo data – old data values from tables are saved as undo data by writing a copy of the image from a data block on disk to an undo segment. This also stores the location of the data as it existed before modification.

Undo segment header – this stores a transaction table where information about current transactions using this particular segment is stored. A serial transaction uses only one undo segment to store all of its undo data. A single undo segment can support multiple concurrent transactions.

Purpose of Undo Segments – Undo segments have three purposes: (1) Transaction Rollback, (2) Transaction Recovery, and (3) Read Consistency.

Transaction Rollback: Old images of modified columns are saved as undo data to undo segments. If a transaction is rolled back because it cannot be committed or the application program directs a rollback of the transaction, the Oracle server uses the undo data to restore the original values by writing the undo data back to the table/index row.

Transaction Recovery: Sometimes an Oracle Instance will fail and transactions in progress will not complete nor be committed.

· Redo Logs bring both committed and uncommitted transactions forward to the point of instance failure.

· Undo data is used to undo any transactions that were not committed at the point of failure.

· Recovery is covered in more detail in a later set of notes.

Read Consistency: Many users will simultaneously access a database. These users should be hidden from modifications to the database that have not yet committed. Also, if a system user begins a program statement execution, the statement should not see any changes that are committed after the transaction begins. Old values stored in undo segments are provided to system users accessing table rows that are in the process of being changed by another system user in order to provide a read-consistent image of the data.

In the figure shown above, an UPDATE command has a lock on a data block from the EMPLOYEE table and an undo image of the block is written to the undo segment. The update transaction has not yet committed, so any concurrent SELECT statement by a different system user will result in data being displayed from the undo segment, not from the EMPLOYEE table. This read-consistent image is constructed by the Oracle Server.

Undo Segment Types

A SYSTEM undo segment is created in the SYSTEM tablespace when a database is created. SYSTEM undo segments are used for modifications to objects stored in the SYSTEM tablespace. This type of Undo Segment works identically in both manual and automatic mode.

Databases with more than one tablespace must have at least one non-SYSTEM undo segment for manual mode or a separate Undo tablespace for automatic mode.

Manual Mode: A non-SYSTEM undo segment is created by a DBA and is used for changes to objects in a non-SYSTEM tablespace. There are two types of non-SYSTEM undo segments: (1) Private and (2) Public.

Private Undo Segments: These are brought online by an instance if they are listed in the parameter file. They can also be brought online by issuing an ALTER ROLLBACK SEGMENT segment_name ONLINE command (prior to Oracle 9i, undo segments were named rollback segments and the command has not changed). Private undo segments are used for a single Database Instance.

Public Undo Segments: These form a pool of undo segments available in a database. These are used with Oracle Real Application Clusters as a pool of undo segments available to any of the Real Application Cluster instances. You can learn more about public undo segments by studying the Oracle9i Real Application Clusters and Administration manual.

Deferred Undo Segments: These are maintained by the Oracle Server so a DBA does not have to maintain them. They can be created when a tablespace is brought offline (immediate, temporary, or recovery) and are used for undo transactions when the tablespace is brought back online. They are dropped by the Oracle Server automatically when they are no longer needed.

Automatic Undo Management

The objective is a "set it and forget it" approach to Undo Management.

· Automatic Undo Management requires the creation of an Undo tablespace.

· Oracle allows a DBA to allocate one active Undo tablespace per Oracle Instance.

· The Oracle Server automatically maintains undo data in the Undo tablespace.

· Oracle automatically creates, sizes, and manages undo segments.

Automatic Undo Segments are named with a naming convention of: _SYSMUn$

For example, they may be named: _SYSMU1$ and _SYSMU2$, etc.

Configuration: When a single Undo tablespace exists in a database, the UNDO_MANAGEMENT parameter in the initialization file is set to AUTO and Oracle will automatically use the single Undo Tablespace.

If more than one Undo tablespace exists (so they can be switched if necessary, but only one can be active), the UNDO_TABLESPACE parameter in the initialization file is used to specify the name of the Undo tablespace to be used by Oracle Server when an Oracle Instance starts up.

If no Undo tablespace exists – Oracle will startup a database and use the SYSTEM tablespace rollback segment for undo. An alert message will be written to the alert file to warn that no Undo tablespace is available.

Examples:

UNDO_MANAGMENT=AUTO or UNDO_MANAGMENT=MANUAL

UNDO_TABLESPACE=UNDO01

You cannot dynamically change UNDO_MANAGEMENT from AUTO to MANUAL or vice-versa. When in MANUAL mode, the DBA must create and manage undo segments for the database.

You can alter the system to change the Undo tablespace that is in use as follows:

ALTER SYSTEM SET undo_tablespace = UNDO02;

Creating the Undo Tablespace: You will recall from our earlier studies that an Undo tablespace can be created by specifying a clause in the CREATE DATABASE command.

CREATE DATABASE USER350

(... more clauses go here ...)

UNDO TABLESPACE undo01

DATAFILE '/u02/student/dbockstd/oradata/USER350undo01.dbf'

SIZE 20M AUTOEXTEND ON NEXT 1M MAXSIZE 50M

(... more clauses follow the UNDO TABLESPACE clause here ...)

In the example command shown above, the Undo tablespace is named UNDO01.

If you do not specify an UNDO TABLESPACE clause within the CREATE DATABASE command, but you do set the UNDO_MANAGEMENT parameter to AUTO in the initialization file, then Oracle will create automatically create an Undo tablespace named SYS_UNDOTBS stored within a file named 'dbu1<oracle_SID>.dbf' – the file is located in the $ORACLE_HOME/dbs directory. The initial size of this Undo tablespace is operating system dependent and AUTOEXTEND will be set to ON.

You can also create an Undo tablespace with the CREATE UNDO TABLESPACE command.

CREATE UNDO TABLESPACE undo02

DATAFILE '/u02/student/dbockstd/oradata/USER350undo02.dbf'

SIZE 25M REUSE AUTOEXTEND ON;

Altering and Dropping an Undo Tablespace

The ALTER TABLESPACE command can be used to modify an Undo tablespace. For example, the DBA may need to add an additional datafile to the Undo tablespace.

ALTER TABLESPACE undo01

ADD DATAFILE '/u02/student/dbockstd/oradata/USER350undo02.dbf'

SIZE 30M AUTOEXTEND ON;

The DBA can also use the following clauses:

· RENAME

· DATAFILE [ONLINE | OFFLINE]

· BEGIN BACKUP

· END BACKUP

Use the ALTER SYSTEM command to switch between Undo tablespaces – remember only one Undo tablespace can be active at a time.

ALTER SYSTEM SET UNDO_TABLESPACE=undo03;

The DROP TABLESPACE command can be used to drop an Undo tablespace that is no longer needed.

DROP TABLESPACE undo02

INCLUDING CONTENTS AND DATAFILES;

· The Undo tablespace to be dropped cannot be in use.

· The clause INCLUDING CONTENTS AND DATAFILES causes the contents (segments) and datafiles at the operating system level to be deleted.

· If it is active, you must switch to a new Undo tablespace and drop the old one only after all current transactions are complete.

· The following query will display any active transactions. The PENDING OFFLINE status indicates that the Undo segment within the Undo tablespace has active transactions. There are no active transactions when the query returns no rows.

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 = 'UNDOTBS1')

AND a.usn = b.usn;

NAME STATUS

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

_SYSSMU1$ ONLINE

_SYSSMU2$ ONLINE

_SYSSMU3$ ONLINE

_SYSSMU4$ ONLINE

_SYSSMU5$ ONLINE

_SYSSMU6$ ONLINE

_SYSSMU7$ ONLINE

_SYSSMU8$ ONLINE

_SYSSMU9$ ONLINE

_SYSSMU10$ ONLINE

10 rows selected.

Other Undo Management Parameters

Older application programs may have programming code (PL/SQL) that use the SET TRANSACTION USE ROLLBACK SEGMENT statement to specify a specific rollback segment to use when processing large, batch transactions. Such a program has not been modified to Automatic Undo Management and normally this command would return an Oracle error: ORA-30019: Illegal rollback segment operation in Automatic Undo mode.

You can suppress these errors by specifying the UNDO_SUPPRESS_ERRORS parameter in the initialization file with a value of TRUE.

A DBA can also determine how long to retain undo data to provide consistent reads. If undo data is not retained long enough, and a system user attempts to access data that should be located in an Undo Segment, then an Oracle error: ORA-1555 snapshot too old error is returned – this means read-consistency could not be achieved by Oracle.

Undo Retention

If Undo Segment data is to be retained a long time, then the Undo tablespace will need larger datafiles.

· The retention period is set with the UNDO_RETENTION parameter that defines the period in seconds.

· You can set this parameter in the initialization file or you can dynamically alter it with the ALTER SYSTEM command:

ALTER SYSTEM SET UNDO_RETENTION = 43200;

· The above command will retain undo segment data for 720 minutes (12 hours) – the default value is 900 seconds (15 minutes).

· If the tablespace is too small to store Undo Segment data for 720 minutes, then the data is not retained – instead space is recovered by the Oracle Server to be allocated to new active transactions.

Oracle 10g automatically tunes undo retention by collecting database use statistics.

· Specifying UNDO_RETENTION sets a low threshold so that undo data is retained at a minimum for the threshold value specified, providing there is sufficient Undo tablespace capacity.

· The RETENTION GUARANTEE clause of the CREATE UNDO TABLESPACE statement can guarantee retention of Undo data to support DML operations, but may cause database failure if the Undo tablespace is not large enough – unexpired Undo data segments are not overwritten.

· The TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic performance view can be queries to determine the amount of time Undo data is retained for an Oracle database.

· Query the RETENTION column of the DBA_TABLESPACES view to determine the setting for the Undo tablespace – possible values are GUARANTEE, NOGUARANTEE, and NOT APPLY (for tablespaces other than Undo).

Sizing and Monitoring an Undo Tablespace

Three types of Undo data exists in a Undo tablespace:

· Active (unexpired) – these segments are needed for read consistency even after a transaction commits.

· Expired – these segments store undo data that has been committed and all queries for the data are complete and the undo retention period has been reached.

· Unused – these segments have space that has never been used.

The minimum size for an Undo tablespace is enough space to hold before-image versions of all active transactions that have not been committed or rolled back.

When space is inadequate to support changes to uncommitted transactions for rollback operations, the error message ORA-30036: Unable to extend segment by space_qtr in undo tablespace tablespace_name is displayed, and the DBA must increase the size of the Undo tablespace.

Initial Size – enable automatic extension (use the AUTOEXTEND ON clause with the CREATE TABLESPACE or ALTER TABLESPACE commands) for Undo tablespace datafiles so they automatically increase in size as more Undo space is needed.

· After the system stabilizes, Oracle recommends setting the Undo tablespace maximum size to about 10% more than the current size.

· The Undo Advisor software available in Enterprise Manager can be used to calculate the amount of Undo retention disk space a database needs.

Undo Data Statistics

The V$UNDOSTAT view displays statistical data to show how well a database is performing.

· Each row in the view represents statistics collected for a 10-minute interval.

· You can use this to estimate the amount of undo storage space needed for the current workload.

· If workloads vary considerably throughout the day, then a DBA should conduct estimations during peak workloads.

· The column ssolderrcnt displays the number of queries that failed with a "Snapshot too old" error.

SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') end_time, undoblks, ssolderrcnt

FROM v$undostat;

END_TIME UNDOBLKS SSOLDERRCNT

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

2009-06-14 11:58 0 0

2009-06-14 11:56 0 0

2009-06-14 11:46 0 0

More...

In order to size an Undo tablespace, a DBA needs three pieces of information. Two are obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE. The third piece of information is obtained by querying the database: the number of undo blocks generated per second.

SELECT (SUM(undoblks))/SUM((end_time-begin_time) * 86400)

FROM v$undostat;

(SUM(UNDOBLKS))/SUM((END_TIME-

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

.028608781

In this next query, the END_TIME and BEGIN_TIME columns are DATE data and subtractions of these results in days – converting days to seconds is done by multiplying by 86,400, the number of seconds in a day. This value needs to be multiplied by the size of an undo block – the same size as the database block defined by the DB_BLOCK_SIZE parameter.

The number of bytes of Undo tablespace storage needed is calculated by this query:

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');

Bytes

----------

407391.527

Convert this figure to megabytes of storage by dividing by 1,048,576 (the number of bytes in a megabyte). The Undo tablespace needs to be about 0.38 MB according to this calculation, although this is because the sample database has very few transactions.

Undo Quota

An object called a resource plan can be used to group users and place limits on the amount of resources that can be used by a given group. This may become necessary when long transactions or poorly written transactions consume limited database resources. If the database has no resource bottlenecks, then the allocating of quotas can be ignored.

Sometimes undo data space is a limited resource. A DBA can limit the amount of undo data space used by a group by setting the UNDO_POOL parameter which defaults to unlimited. If the group exceeds the quota, then new transactions are not processed until old ones complete. The group members will receive the ORA-30027: Undo quota violation – failed to get %s (bytes) error message.

Resource plans are covered in more detail in a later set of notes.

Undo Segment Information

The following views provide information about undo segments:

· DBA_ROLLBACK_SEGS

· V$ROLLNAME -- the dynamic performance views only show data for online segments.

· V$ROLLSTAT

· V$UNDOSTAT

· V$SESSION

· V$TRANSACTION

This query lists information about undo segments in the SIUE DBORCL database. Note the two segments in the SYSTEM tablespace and the remaining segments in the UNDO tablespace.

COLUMN segment_name FORMAT A15;

COLUMN owner FORMAT A10;

COLUMN tablespace_name FORMAT A15;

COLUMN status FORMAT A10;

SELECT segment_name, owner, tablespace_name, status

FROM dba_rollback_segs;

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS

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

SYSTEM SYS SYSTEM ONLINE

_SYSSMU1$ PUBLIC UNDOTBS1 ONLINE

_SYSSMU2$ PUBLIC UNDOTBS1 ONLINE

_SYSSMU3$ PUBLIC UNDOTBS1 ONLINE

_SYSSMU4$ PUBLIC UNDOTBS1 ONLINE

_SYSSMU5$ PUBLIC UNDOTBS1 ONLINE

_SYSSMU6$ PUBLIC UNDOTBS1 ONLINE

_SYSSMU7$ PUBLIC UNDOTBS1 ONLINE

_SYSSMU8$ PUBLIC UNDOTBS1 ONLINE

_SYSSMU9$ PUBLIC UNDOTBS1 ONLINE

_SYSSMU10$ PUBLIC UNDOTBS1 ONLINE

11 rows selected.

The owner column above specifies the type of undo segment. SYS means a private undo segment.

This query is a join of the V$ROLLSTAT and V$ROLLNAME views to display statistics on undo segments currently in use by the Oracle Instance. The usn column is a sequence number.

COLUMN name FORMAT A12;

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;

NAME EXTENTS RSSIZE HWMSIZE XACTS STATUS

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

SYSTEM 6 385024 385024 0 ONLINE

_SYSSMU1$ 2 122880 2220032 0 ONLINE

_SYSSMU2$ 3 188416 2285568 0 ONLINE

_SYSSMU3$ 3 1171456 2220032 0 ONLINE

_SYSSMU4$ 3 1171456 2220032 0 ONLINE

_SYSSMU5$ 3 1171456 2220032 0 ONLINE

_SYSSMU6$ 3 188416 2220032 0 ONLINE

_SYSSMU7$ 3 1171456 2220032 0 ONLINE

_SYSSMU8$ 3 188416 2088960 0 ONLINE

_SYSSMU9$ 4 253952 2220032 0 ONLINE

_SYSSMU10$ 5 319488 2220032 0 ONLINE

11 rows selected.

This query checks the use of an undo segment by any currently active transaction by joining the V$TRANSACTION and V$SESSION views.

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;

USERNAME XIDUSN UBAFIL UBABLK USED_UBLK

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

USER001 3 2 181 1

Flashback Features

Flashback features allow DBAs and users to access database information from a previous point in time.

· Undo information must be available so the retention period is important.

· Example: If an application requires a version of the database that is up to 12 hours old, the UNDO_RETENTION must be set to 43200.

· The RETENTION GUARANTEE clause needs to be specified.

The Oracle Flashback Query option is supplied through the DBMS_FLASHBACK package at the session level.

At the object level, Flashback Query uses the AS OF clause to specify the point in time for which data is viewed.

Flashback Version Query enables users to query row history through use of a VERSIONS clause of a SELECT statement.

Example: This SELECT statement retrieves the state of an employee record for an employee named Sue at 9:30 AM on June 13, 2009 because it was discovered that Sue's employee record was erroneously deleted.

SELECT * FROM employee AS OF TIMESTAMP

TO_TIMESTAMP('2009-06-13 09:30:00', 'YYYY-MM-DD HH:MI:SS')

WHERE name = 'SUE';

This INSERT statement restores Sue's employee table information.

INSERT INTO employee

(SELECT * FROM employee AS OF TIMESTAMP

TO_TIMESTAMP('2009-06-13 09:30:00', 'YYYY-MM-DD HH:MI:SS')

WHERE name = 'SUE');

Other information about Flashback features will be covered in other notes dealing with database recovery.

END OF NOTES