Instructor's Notes

(If applicable)

• Lesson 16: Managing Undo
• We will start by defining the term undo and introducing the two methods of managing: manual and automatic. We will then learn the new method, Automatic Undo Management (AUM) in detail. Automatic Undo Management (AUM) is a new Oracle9i feature that deprecates the configuration and use of Rollback Segments.
• Specifically, in this lesson we will:
• Provide a brief review of rollback segments (and remind ourselves what a pain in the neck they are!).
• Learn about the concepts and architecture of AUM. This will also uncover the benefits of AUM.
• Provide a brief review of locally managed tablespaces. Locally managed tablespaces were introduced in Oracle8i and are required by AUM.
• Provide procedures for implementing AUM.
• Look at the new data dictionary views available for monitoring and sizing the AUM tablespace.
• Let’s get started!

Instructor's Notes

(If applicable)

• “Undo” is before-images of changed data. Undo records are automatically created by the database whenever a change is made. As the Oracle Concepts manual states "When an update occurs, the original data values changed by the update are recorded in the database's undo records."
• Undo exists to:
• Support the ROLLBACK SQL statement.
• Support statement-level read consistency. A good definition of statement-level read consistency can be found in this quote from Kevin Loney: “Queries by other users against the data that is being changed will return the data as it existed before the change began.*” Another way to explain it is to say that if a query starts at 12 Noon and executes for 15 minutes, all data retrieved by that query will be exactly as it was at 12 noon; updates made between 12 and 12:15 will not be included in the result set.
• Support database recovery operations. Recovery involves applying all changes in the redo logs (optionally up to a point-in-time or system change number), then undoing changes that have not been committed (i.e. a commit record is not found in the redo).
• * Quote from Oracle8 DBA Handbook, Kevin Loney, Oracle Press ISBN 0-07-882406

Instructor's Notes

(If applicable)

• Undo records need disk space. With Oracle9i, there are two methods of acquiring and managing the disk space, automatic and manual.
• In Automatic Undo Management mode, we will create an undo tablespace and let the database create undo segments within the tablespace. In manual mode, we are responsible for creating and managing the rollback segments within the rollback tablespace.
• According to Oracle9i Database Concepts Release 2, Appendix B Information on Deprecated Features, “Oracle strongly recommends the use” of AUM as well as locally managed tablespaces.The implication is that, at some point, in the future manually managed undo will not be supported. When that will happen is anyone’s guess. (It took many releases for Oracle to remove support for “connect internal.” As Mark Twain once remarked, “News of my death has been greatly exaggerated.”) However, you can still use rollback segments if you want to. Simply set UNDO_MANAGEMENT=MANUAL.

Instructor's Notes

(If applicable)

• Rollback segments have always been a thorn in the side of Oracle DBAs. Planning for, creation, monitoring, and maintenance of rollback segments can sometimes be an onerous task even for experienced DBAs. Until precise statistics are available, configuring rollback segments is, at best, educated guesses based on anticipated number and size of transactions your database will support. The considerations involve:
• how big to make the tablespace(s) that will contain the rollback segments
• how many rollback segments to create inside that tablespace(s)
• what the segments’ size should be
• max number of extents the rollback segments should be allowed
• what size to make the rollback segment parameter OPTIMAL
• Monitoring rollback segments involves tracking waits for segments (a bad thing!), too many extends (rollback segments are too small), and excessive shrinks (OPTIMAL parameter needs to be increased).

Instructor's Notes

(If applicable)

• And let’s not forget about the dreaded “snapshot too old” errors. These typically occur when a long running query is accessing data that is being changed by another transaction; the before image of the data is overwritten in the rollback segment before the query gets to it.
• In summary, tuning rollback segments is an ongoing task and a hassle. So, let’s move on to AUM!

Instructor's Notes

(If applicable)

• In automatic undo management mode, an undo segments are used instead of rollback segments. The benefit is that Oracle automatically allocates and maintains the undo segments within the undo tablespace.
• Automatic undo management mode greatly simplifies undo management. Our greatest concern in this mode is the size of the undo tablespace. We no longer need to be concerned with number of rollback segments, size of the segments, transaction per segment, and so forth.
• Automatic undo management mode also provides a simple way to specify, in seconds, a retention time. i.e. The period of time that undo records should be kept. This can reduce – possibly eliminate – “snapshot too old” errors.

Instructor's Notes

(If applicable)

• OK, now that we know what AUM is, why we want to use it, let's get started with implementation. If creating a new database with the Oracle9i Database Configuration Assistant, it is easy; the DBCA automatically creates the database in AUM mode. Implementing AUM on an existing database involves the following steps:
  1. Planning – Check for applications that depend on rollback segments. Code that includes the SET TRANSACTION USE ROLLBACK SEGMENT statement will fail unless errors are suppressed. See initialization parameters later in this lesson to see how to suppress errors.
  2. Create the undo tablespace. We will need to guesstimate the initial size of our undo tablespace. A starting point can be calculated by multiplying UNDO per second (transaction rate - see V$UNDOSTAT) * blocksize * your desired retention period. See chapter 13 of the Oracle9i Administrators Guide for a complete description of this calculation and Managing UNDO in general.
  3. Adjust the initialization parameters.
  4. Bounce the database.
• Let’s look at steps 2 through 4 in turn…

Instructor's Notes

(If applicable)

• The DBA_TABLESPACES view contains a row for every tablespace. To determine what UNDO tablespaces are available, execute the query shown above.
• Note that while more than one may be online, only one can be used at any given time. The exception to this is while switching to a new undo tablespace. See the section Switching Tablespaces later in this lesson for an example.

Instructor's Notes

(If applicable)

• The DBA_DATA_FILES view shows the current size of the datafile(s) associated with the UNDO tablespace.

Instructor's Notes

(If applicable)

• The example above creates a tablespace named SKILLBUILDERS_UNDO utilizing the new 9i keyword UNDO. Other notables:
• UNDO tablespaces must be locally managed tablespaces. The DEFAULT STORAGE clause is not allowed.
• The undo tablespaces cannot be used for anything but undo segments, else an ORA-30022 error is returned.
• Our example omits the EXTENT MANAGEMENT clause; therefore the default ALLOCATION TYPE of AUTOALLOCATE is used.

Instructor's Notes

(If applicable)

• This excerpt above shows an undo tablespace being created as part of a CREATE DATABASE command. The CREATE DATABASE command is discussed in detail in the lesson Creating a New Database. A complete statement might look like this:
• CREATE DATABASE prod
• DATAFILE 'c:\oradata\tbs\system01.dbf' SIZE 60m
• AUTOEXTEND ON NEXT 10m MAXSIZE unlimited
• LOGFILE
• GROUP 1
• (‘c:\oradata\redo\log01a.log',
• ‘d:\oradata\redo\log01b.log') SIZE 500k,
• GROUP 2
• (‘c:\oradata\redo\log02a.log',
• ‘d:\oradata\redo\log02b.log') SIZE 500k,
• GROUP 3
• (‘c:\oradata\redo\log03a.log',
• ‘d:\oradata\redo\log03b.log') SIZE 500k
• MAXINSTANCES 1 MAXDATAFILES 300
• UNDO TABLESPACE skillbuilders_undo
• DATAFILE ‘d:\oradata\tbs\ skillbuilders_undo.dbf’ SIZE 40m AUTOEXTEND OFF

Instructor's Notes

(If applicable)

• To enable AUM, parameter UNDO_MANAGEMENT=AUTO must be set. The parameter UNDO_TABLESPACE should also be set to a valid undo tablespace name; otherwise, Oracle will use a system-default undo tablespace, which, as a general rule, is not desirable.
• Turn to the next page for details on these parameters.

Instructor's Notes

(If applicable)

• The four AUM-related parameters are described here:
• UNDO_MANAGEMENT - Set equal to MANUAL or AUTO. This parameter is not dynamic. If AUTO is specified, Oracle will use an UNDO tablespace if available (details on the following slides). However, if an UNDO tablespace is not available, Oracle will use the SYSTEM rollback segment which is really NOT a good idea. In fact, Oracle gets so upset about having to use the SYSTEM rollback segment that it complains loudly by writing warnings to the alert log.
• UNDO_TABLESPACE - Set to name of UNDO tablespace. This is a dynamic parameter. Note that if you change the UNDO tablespace with “alter system set UNDO_TABLESPACE = TSNAME;” while there are active transactions, Oracle will continue to use the original tablespace until all active transactions have committed. Undo for new transactions is placed in the tablespace named in the ALTER SYSTEM command.


• Notes continue on the next page…

Instructor's Notes

(If applicable)

• UNDO_RETENTION – Number of seconds Oracle should try to keep UNDO before being overwritten. Undo that is younger that the retention value is said to be “unexpired”. The default is 900 seconds. Set to a minimum of the longest running query in your database. Range is 0 to 2 Gb (approx 25,000 days!). Increasing provides a larger window for Flashback Query, but will cause Oracle to keep more undo, requiring a larger undo tablespace. If an out-of-space condition occurs in the tablespace, Oracle will start using unexpired undo, which can cause “snapshot too old” errors to occur. This is a dynamic parameter.
• UNDO_SUPPRESS_ERRORS - (TRUE|FALSE) – Provides support for applications written using manual UNDO commands such as SET TRANSACTION USE ROLLBACK SEGMENT. Suppresses errors such as ORA-30019. This parameter can be set at the system level (in the database parameter file or ALTER SYSTEM) or at the session level with ALTER SESSION.
• After adding these parameters to your database parameter file, shutdown and re-start your database to implement AUM.

Instructor's Notes

(If applicable)

• Assume that UNDO_TABLESPACE = SKILLBUILDERS_OTHER_UNDO has been specified and we would like to change the UNDO tablespace. To do so, we would issue the commands shown in this example.
• Oracle places the old undo tablespace in PENDING OFFLINE mode which means that existing transactions continue but the tablespace will not accept new transaction undo. When all active transactions have committed, the tablespace is placed in OFFLINE mode.

Instructor's Notes

(If applicable)

• An UNDO tablespace may also be dropped provided that it is not in use by any instance and does not contain information about incomplete transactions.
• Additionally, the UNDO_RETENTION parameter is ignored during DROP. Oracle does not care that you may have specified a six month retention period. When the DROP is processed, the tablespace is history! It will, however, wait for active transactions to complete.

Instructor's Notes

(If applicable)

• Data dictionary view DBA_UNDO_EXTENTS provides commit information about each extent in UNDO tablespaces. See the example in the Tuning AUM section of this lesson.
• Dynamic performance view V$UNDOSTAT provides Oracle-generated statistics for monitoring and maintenance of undo tablespace sizing problems. It works for both AUM and manual (rollback segment) undo.

Instructor's Notes

(If applicable)

• The whole point of AUM is the little maintenance required, especially compared to rollback segments. However, certain columns in the V$UNDOSTAT dynamic view will help the DBA keep abreast, or better yet ahead, of any potential problems due to lack of space in the undo tablespace.
• A general rule-of-thumb provided by the Oracle Concepts manual is to make the size of the undo tablespace 20% larger than what is needed for your specified retention value (see parameter UNDO_RETENTION). Use the V$UNDOSTAT.UNDOBLKS to determine the number of undo blocks used, and translate that into the undo tablespace size.
• Oracle Enterprise Manager will recommend a size for the UNDO tablespace too.

Instructor's Notes

(If applicable)

• A description of helpful columns in V$UNDOSTAT:
• UNDOBLKS – this column contains the total number of undo blocks generated during period of statistical collection. If this number is far larger then the UNDO tablespace’s size, then the size of the undo tablespace needs to be increased.


• Additional information is found on the next page…

Instructor's Notes

(If applicable)

• UNXPSTEALCNT – Number of unexpired blocks (as set in parameter UNDO_RETENTION) that are being expired prematurely; that is, other transactions have taken over those blocks because not enough UNDO space was available for those transactions. This column’s value is particularly useful when a new application and database is brought online: the famous and frustrating “snapshot too old” error can appear as more and more users start to use the system, generating increasing numbers of undo blocks. The Oracle DBA can increase the value of parameter UNDO_RETENTION_TIME to get rid of such errors. Sometimes raising this value is not enough to get rid of all “snapshot too old” errors, and the DBA has to additionally add more space to the undo tablespace.
• MAXQUERYLEN – this column contains the value, in seconds, of the longest-running transaction in the past 24 hours. The undo management parameter UNDO_RETENTION should be set to a value larger than the value in MAXQUERYLEN.
• NOSPACERRCNT – This column keep track of the number of any space unavailable errors generated when Oracle writes undo transaction blocks to an undo tablespace. If this value is not zero, increase the size of the undo tablespace.

Instructor's Notes

(If applicable)

• There is also some useful transaction-related information in V$TRANSACTION and V$ROLLSTAT. V$TRANSACTION contains information on active transactions. V$ROLLSTAT contains information about undo segments if operating in AUM mode; rollback segment information if running in manual mode.

Instructor's Notes

(If applicable)

• If you determine that the undo tablespace is not large enough, adding space can be accomplished by adding a datafile to the tablespace as shown in this example.

Instructor's Notes

(If applicable)

Instructor's Notes

(If applicable)

Instructor's Notes

(If applicable)

• AUM Workshop
  1. What are the current values of the four AUM parameters for your database?
  2. Create a new undo tablespace called UNDO_DATA_TS. Use OMF for the location and filename if OMF is active. Create a 1M datafile with AUTOEXTEND OFF.
  3. Make the tablespace UNDO_DATA_TS the undo tablespace for your database. Verify that the change worked.
  4. Execute the supplied script AUM1.SQL. Ignore any “ORA-00942: table or view does not exist” error messages. Note that you should get an error: ORA-30036: unable to extend segment by 16 in undo tablespace ‘UNDO_DATA_TS’.
  5. Query V$UNDOSTAT to determine the number of undo blocks consumed in the last 60 minutes. Was there any attempted stealing of unexpired blocks? If you experienced any attempted stealing of unexpired blocks, what would you do to correct this?
  6. Revert to the original undo tablespace. Rerun supplied script AUM1.SQL. Insure there are no errors.