1Z0-052 - Managing UNDO Data

Main

Local Posts on Undo:
"Shrink" UNDO Tablespace
UNDO Brain Damage by Kellyn Pedersen

Overview of UNDO

Oracle Database creates and manages information that is used to roll back, or undo, changes to the database. Such information consits of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

• Roll back transaction 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 corrputions using Oracle Flashback features.

When a ROLLBACK is issued, undo records are used to undo chnages that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

Transactions and undo data

Managing undo

• Creating an Undo Tablespace

  CREATE UNDO TABLESPACE undo_tablespace_02
    DATAFILE '/u01/oracle/db1/undo0201.dbf'
    SIZE 2M
    REUSE
    AUTOEXTEND ON;

• Altering an Undo Tablespace
  • Actions Permitted (UNDO is system managed, so only the following are permitted)
    • Adding a datafile

      ALTER TABLESPACE undo_tablespace_02
        ADD DATAFILE '/u01/oracle/db1/undo0202.dbf'
        AUTOEXTEND ON
        NEXT 1M
        MAXSIZE UNLIMITED;

    • Renaming a datafile
    • Bringing a datafile online or taking it offline
    • Beginning or ending an open backup on a datafile
    • Enabling or disabling undo retention guarantee
• Dropping an Undo Tablespace - You need to have one UNDO tablespace at any given time, otherwise, this statement will fail with ORA-30013: undo tablespace 'UNDO_TABLESPACE_02' is currently in use

  DROP TABLESPACE undo_tablespace_01;

  Since this is an UNDO tablespace, it has the same effect as DROP TABLESPACE...INCLUDING CONTENTS;
• Switching Undo Tablespaces

  ALTER SYSTEM SET undo_tablespace = undo_tablespace_02;

• Establishing User Quotas for Undo Space - The Oracle Database Resource Manager can be used to establish user quotas for undo space. The Database Resource Manager directive UNDO_POOL allows DBAs to limit the amount of undo space consumed by a group of users (resource consumer group)
  
  You can specify an undo pool for each consumer group. An undo pool controls the amount of total undo that can be generated by a consumer group. When the total undo generated by a consumer group exceeds its undo limit, the current UPDATE transaction generating the undo is terminated. No other members of the consumer group can perform further updates until undo space is freed from the pool.
  
  When no UNDO_POOL directive is explicitly defined, users are allowed unlimited undo space.
• Undo Space Data Dictionary Views
  • V$UNDOSTAT - Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also used this information to help tune undo usage in the system. This view is meaningful only in automatic undo management mode.
  • V$ROLLSTAT - For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace.
  • V$TRANSACTION - Contains undo segment information.
  • DBA_UNDO_EXTENTS - Shows the status and size of each extent in the undo tablespace.
  • DBA_HIST_UNDOSTATS - Contains statistics snapshots of V$UNDOSTAT information.

Main