updated June 10, 2013; Some figures shown in these notes are from Oracle
document D11321GC11; Reference: Oracle® Database Administrator's Guide
11g Release 2 (11.2) E25494-03
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.
· Beginning with Release 11g, for a default installation, Oracle Database automatically manages undo.
· There is typically no need for DBA intervention.
· If your installation uses Oracle Flashback operations, you may need to perform some undo management tasks to ensure the success of these operations.
Undo records are used to:
· Roll back transactions when a ROLLBACK statement is issued
· Recover the database
· Provide read consistency
· Analyze data as of an earlier point in time by using Oracle Flashback Query
· Recover from logical corruptions using Oracle Flashback features
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 automatic undo management is a significant improvement.
· You will see parts of the data dictionary and certain commands still use the term Rollback for backward compatibility.
There are two methods for managing undo data:
(1) automatic undo management automatic undo management is preferred.
· This is the type of undo management used when you create an UNDO tablespace and specify use of automatic undo management.
· Automatic undo management is the default for Oracle 11g for a new database.
(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.
· If you disconnect non-normally, rollback of uncommitted transactions is automatic.
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 Oracle 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).
· They 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.
· An auto-extending undo tablespace named UNDOTBS1 is automatically created when you create the database with Database Configuration Assistant (DBCA).
· 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_<generated number>$
For example, they may be named: _SYSMU1_1872589076$ and _SYSMU2_1517779068$, etc.
Configuration: When a single Undo tablespace exists in a database:
· Automatic Undo Management is the default.
· With 11g, there is no need to set the UNDO_MANAGEMENT parameter in the initialization to AUTO.
· Oracle will automatically use the single Undo Tablespace when in AUTO mode.
· 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 start up a database and use the SYSTEM tablespace undo segment for undo.
· An alert message will be written to the alert file to warn that no Undo tablespace is available.
· If you use the UNDO_TABLESPACE parameter and the tablespace referenced does not exist, the STARTUP command will fail.
UNDO_MANAGMENT=AUTO or UNDO_MANAGMENT=MANUAL
· 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 active Undo tablespace that is in use as follows:
ALTER SYSTEM SET undo_tablespace = UNDO02;
Creating the Undo Tablespace: There are two methods of creating an undo tablespace manually.
1. Create one by specifying a clause in the CREATE DATABASE command.
CREATE DATABASE USER350
(... more clauses go here ...)
UNDO TABLESPACE undo01
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 the Undo tablespace cannot be created, the entire CREATE DATABASE command fails.
2. You can also create an Undo tablespace with the CREATE UNDO TABLESPACE command.
CREATE UNDO TABLESPACE undo02
SIZE 25M REUSE AUTOEXTEND ON;
· This is the same as the normal CREATE TABLESPACE command but with the UNDO keyword added.
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 REUSE AUTOEXTEND ON;
The DBA can also use the following clauses:
· 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;
If any of the following conditions exist for the tablespace being switched to, an error is reported and no switching occurs:
· The tablespace does not exist
· The tablespace is not an undo tablespace
· The tablespace is already being used by another instance (in an Oracle RAC environment only)
The database is online while the switch operation is performed, and user transactions can be executed while this command is being executed.
· When the switch operation completes successfully, all transactions started after the switch operation began are assigned to transaction tables in the new undo tablespace.
· The switch operation does not wait for transactions in the old undo tablespace to commit.
· If there are any pending transactions in the old undo tablespace, the old undo tablespace enters into a PENDING OFFLINE mode (status).
· In this mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace.
The DROP TABLESPACE command can be used to drop an Undo tablespace that is no longer needed it cannot be an active undo tablespace.
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
WHERE tablespace_name = 'UNDOTBS1')
AND a.usn = b.usn;
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.
After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes.
· However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks.
· Several Oracle Flashback features can also depend upon the availability of older undo information.
· For these reasons, it is desirable to retain the old undo information for as long as possible.
Automatic undo management always uses a specified undo retention period.
· This is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it.
· Old (committed) undo information that is older than the current undo retention period is said to be expired and its space is available to be overwritten by new transactions.
· Old undo information with an age that is less than the current undo retention period is said to be unexpired and is retained for consistent read and Oracle Flashback operations.
Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity.
You can optionally
specify a minimum undo retention period (in seconds) by setting the
UNDO_RETENTION initialization parameter.
· The exact impact this parameter on undo retention is as follows:
is ignored for a fixed size undo tablespace. The database always tunes the undo
retention period for the best possible retention, based on system activity and
undo tablespace size.
o For an undo tablespace with the
AUTOEXTEND option enabled, the database attempts to honor the
minimum retention period specified by
o When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends.
o If the
is specified for an auto-extending undo tablespace, when the maximum size is
reached, the database may begin to overwrite unexpired undo information.
If Undo Segment data is to be retained a long time, then the Undo tablespace will need larger datafiles.
· The UNDO_RETENTION parameter 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).
· This sets the minimum undo retention period.
· 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 11g automatically tunes undo retention by collecting database use statistics whenever AUTOEXTEND is on.
· 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, if you decide to used a fixed-size Undo tablespace, then Oracle recommends setting the Undo tablespace maximum size to about 10% more than the current size.
· The Undo Advisor software available in Oracle 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
END_TIME UNDOBLKS SSOLDERRCNT
---------------- ---------- -----------
. . .
. . .
2013-06-07 01:13 0 0
2013-06-07 01:03 65 0
2013-06-07 00:53 0 0
2013-06-07 00:43 1 0
576 rows selected.
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)
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:
FROM (SELECT value As
WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time -
begin_time) * 86400))) As UPS
(SELECT value As DBS
WHERE name = 'db_block_size');
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.64 MB according to this calculation, although this is because the sample database has very few transactions.
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:
· V$ROLLNAME -- the dynamic performance views only show data for online segments.
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
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ ---------- --------------- ----------
SYSTEM SYS SYSTEM ONLINE
_SYSSMU1_1872589076$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU2_1517779068$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU3_1524324367$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU4_2700621624$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU5_709693897$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU6_3285739792$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU7_1962453367$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU8_4260361871$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU9_2502292647$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU10_2550878863$ 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 A22;
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_1872589076$ 4 2220032 3268608 0 ONLINE
_SYSSMU2_1517779068$ 3 1171456 10608640 0 ONLINE
_SYSSMU3_1524324367$ 4 2220032 3268608 0 ONLINE
_SYSSMU4_2700621624$ 3 1171456 11657216 0 ONLINE
_SYSSMU5_709693897$ 4 2220032 3137536 0 ONLINE
_SYSSMU6_3285739792$ 4 2220032 9560064 0 ONLINE
_SYSSMU7_1962453367$ 4 2220032 2220032 0 ONLINE
_SYSSMU8_4260361871$ 17 2088960 3268608 0 ONLINE
_SYSSMU9_2502292647$ 3 1171456 3268608 0 ONLINE
_SYSSMU10_2550878863$ 13 11657216 11657216 0 ONLINE
11 rows selected.
o EXTENTS = number of extents in the rollback segment.
o RSSIZE = rollback segment size (bytes)
o HWMSIZE = high water mark of the rollback segment size (bytes)
o XACTS = number of active transactions (notice in the above there are none).
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;
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, 2013 because it was discovered that Sue's employee record was erroneously deleted.
SELECT * FROM employee AS OF TIMESTAMP
This INSERT statement restores Sue's employee table information.
INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2013-06-13 09:30:00', 'YYYY-MM-DD HH:MI:SS')
Other information about Flashback features will be covered in other notes covering the topic of database recovery.
END OF NOTES