Lesson Objectives

• Define "undo"
• Identify methods of managing
• Learn to manage with automatic undo management (AUM)
• Implement AUM
  • Learn the new initialization parameters for AUM
  • Review Oracle8i locally-managed tablespaces
  • Create, modify & drop an undo tablespace
• Introduce new AUM-related data dictionary views
• Sizing the Undo Tablespace

What is Undo?

• Records contain “before image” of changed data
  • Prior to the start of the transaction
• Undo records are used for
  • ROLLBACK statement
    • Undo UPDATE, DELETE, INSERT statements
  • Statement-level read consistency
    • “Queries by other users against the data that is being changed will return the data as it existed before the change began” *
  • Recovery
    • Rollback uncommitted transactions after redo logs applied

Where is Undo Kept?

• Undo records require disk space
• Two management methods support
• Automatic mode
  • DBA creates undo tablespace
  • Database creates undo segments in undo tablespace
• Manual mode
  • Undo records written to rollback segments
  • DBA creates and manages rollback segments

Why Not Rollback Segments?

• Rollback Segments require lots of attention
• Configuration
  • Size of RBS’s
  • Number of RBS’s
• Monitoring and Tuning
  • Waits, Extends, Shrinks
  • Eliminating “snapshot too old” errors

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

Why AUM?

• AUM uses UNDO tablespaces instead of rollback segments
  • UNDO segments auto-managed
• Easier to administer
  • Less space management headaches
  • Less tuning
• Provides control over retention time
  • Specify number of seconds to retain undo
  • Can reduce “snapshot too old” errors

Implementing AUM

• 9i DBCA creates database with AUM
• Procedure for existing database:
  • Planning
    • Search for apps that use SET TRANSACTION USE ROLLBACK statements
  • Create an “undo tablespace”
    • Guesstimate initial size
    • UNDO per second * desired retention * blocksize
  • Adjust initialization parameters
  • Bounce database

Find Undo Tablespace...

• Query DBA_TABLESPACES to find available undo tablespaces

...Find Undo Tablespace

• DBA_DATA_FILES shows size

Create Undo Tablespace...

• Create an “undo tablespace”
• Use the new UNDO clause on CREATE TABLESPACE

...Create Undo Tablespace

• Undo tablespace created with CREATE DATABASE statement

Initialization Parameters...

• Four parameters control AUM

...Initialization Parameters...

• UNDO_MANAGEMENT
  • AUTO for AUM
  • MANUAL for rollback segments
  • Static parameter
    • Must bounce DB
• UNDO_TABLESPACE
  • Undo tablespace name
  • Dynamic parameter
  • Can change with active transactions
    • New transactions use new
    • Original tablespace in use until all transactions complete

...Initialization Parameters

• UNDO_RETENTION = 3600
  • Number of seconds to attempt to keep UNDO
  • Dynamic
  • Default = 900 seconds
• UNDO_SUPPRESS_ERRORS = TRUE
  • TRUE | FALSE
  • Support 8i apps with manual undo statements
  • e.g., SET TRANSACTION USE RBS
  • TRUE suppresses errors like ORA-30019
  • Can be set at SESSION level
• Bounce database

Switching Undo Tablespace

• UNDO_TABLESPACE parameter is dynamic

Dropping Undo Tablespace

• Drop with DROP TABLESPACE command
• Must be inactive
• Undo retention time ignored

Data Dictionary Views

• DBA_UNDO_EXTENTS
  • Commit information about each extent in UNDO tablespaces
• V$UNDOSTAT
  • Statistics for monitoring and maintenance of undo tablespace sizing problems
  • Updated every 10 minutes

Tuning AUM...

• Except for size, there is little to tune
  • That is a benefit!
• Use V$UNDOSTAT to check for sizing problems
• General Rule Of Thumb: set size 20% larger than needed for retention value
  • Source: Oracle9i Concepts, Chapter 2, Undo Retention Control
• Oracle Enterprise Manager
  • Provides recommended size for given retention

...Tuning AUM...

• V$UNDOSTAT provides a snapshot every 10 minutes
  • UNDOBLKS
    • Total number of undo blocks
  • UNXPSTEALCNT
    • Number of prematurely expired un-expired blocks
  • MAXQUERYLEN
    • Length, in seconds, of the longest-running transaction in the past 24 hours
  • NOSPACERRCNT
    • Number of space unavailable errors from writes of undo blocks

...Tuning AUM...

• This shows not many blocks used by stealing occurring

...Tuning AUM

• V$TRANSACTION and V$ROLLSTAT are also useful for monitoring
  • V$TRANSACTION contains information on active transactions
    • Start time
    • # of UNDO blocks used
    • # of physical I/Os
  • V$ROLLSTAT contains information about undo/rollback segments
    • # of extents in rollback segment
    • Size of rollback segment
    • # of times the rollback segment size increased

Adding Space

• Add space to an undo tablespace by adding a datafile

Resources

• Oracle9i Administration manual
  • lesson 13, Managing Undo Space
• Oracle Concepts Manual
  • lessons 2, 3 and 5
• Article by Jonathan Gennick
  • Oracle8i Locally Managed Tablespaces
  • http://www.oracle.com/oramag/oracle/00-nov/index.html?o60o8i.html
• Metalink.oracle.com
  • Doc ID: Note:135090.1

Summary

• Automatic Undo Management can reduce rollback segment headaches
• Easy to setup and manage
  • Initialization parameters
  • V$UNDOSTAT, DBA_UNDO_EXTENTS
• Provides explicit control over undo retention period
  • UNDO_RETENTION parameter

Automatic Undo Management Workshop

• Automatic Undo Management (AUM)