Managing Undo Data

About Undo Data

When a transaction modifies data, Oracle Database copies the original data before modifying it. The original copy of the modified data is called undo data. Saving this information is necessary for the following reasons:

• To undo any uncommitted changes made to the database in the event that a rollback operation is necessary. A rollback operation can be the result of a user who wants to undo the changes of a misguided or unintentional transaction, or it can be part of a recovery operation.

• To provide read consistency, which means that each user can get a consistent view of data, even while other changes may be occurring against the data. For example, if a user issues a query at 10:00 a.m. and the query lasts for 15 minutes, then the query results should reflect the entire state of the data at 10:00 a.m., regardless of update or insert operations by other users during the query.

• To enable certain Oracle Flashback features, such as Oracle Flashback Query and Oracle Flashback Table, which enable you to view or recover data to a previous point in time.

Undo Tablespace

With automatic undo management, undo data is stored in an undo tablespace. Undo tablespaces have some additional properties beyond those of permanent tablespaces. There can be multiple undo tablespaces, but only one can be active for an Oracle instance.

When you create the database using DBCA, it creates an autoextending undo tablespace named UNDOTBS1, with a maximum extension size of 35 GB.

Undo Retention

Oracle Database automatically ensures that undo data that is in use by an active transaction is never overwritten until that transaction has been committed. After the transaction has been committed, the space occupied by that undo data can be reused, or overwritten. In this case, that undo data could be overwritten if space in the undo tablespace becomes scarce.

Even after a transaction has been committed, it is useful to retain (not overwrite) its undo data, to ensure the success of Oracle Flashback features and for read consistency for long-running queries. To this end, the database maintains and automatically tunes an undo retention period. Committed undo data whose age is less than the undo retention period is retained for use by queries or Oracle Flashback operations.

About Managing Undo Data

Although by default Oracle Database manages undo data and the undo tablespace automatically, if your installation uses Oracle Flashback features, you may need to perform some undo management tasks to ensure the success of these operations.

Oracle Flashback operations resulting in snapshot too old errors indicate that you must intervene to ensure that sufficient undo data is retained to support these operations.

There are two ways to better support Oracle Flashback operations:

• Set the minimum undo retention period for the autoextending tablespace to be as long as the longest expected Oracle Flashback operation.

  You do this by setting the UNDO_RETENTION initialization parameter. See Oracle Database Administrator's Guide for details.

• Change the undo tablespace to a fixed size.

  For an autoextending undo tablespace, Oracle Database always automatically tunes the undo retention period to be slightly longer than the longest-running active query. However, this autotuned retention period may be insufficient to accommodate Oracle Flashback operations.

  For an undo tablespace of a fixed size, or a tablespace with autoextending disabled, rather than tuning the undo retention period to be slightly longer than the longest-running active query, the database dynamically tunes the undo retention period for the best possible retention, based on system activity and the undo tablespace size. This best possible retention period for an undo tablespace of fixed size can be much longer than the longest-running active query, and can thus better accommodate Oracle Flashback operations.

  Another reason to change the undo tablespace to a fixed size is to prevent the tablespace from growing too large.

  If you decide to change the undo tablespace to a fixed size, you must choose a tablespace size that is sufficiently large. If you choose an undo tablespace size that is too small, the following two errors could occur:

  • DML could fail because there is not enough space to accommodate undo data for new transactions.

  • Long-running queries could fail with a snapshot too old error, which means that there was insufficient undo data for read consistency.

  Oracle Enterprise Manager Database Control (Database Control) includes an Undo Advisor to help you determine the minimum size for the fixed size of the undo tablespace. See "Computing the Minimum Undo Tablespace Size Using the Undo Advisor".

  Note:

  If you want to configure the undo tablespace to have a fixed size, Oracle suggests that you first allow enough time after database creation to run a full workload, thus allowing the undo tablespace to grow to its minimum required size to handle the workload. Then, you can use the Undo Advisor to determine the best size to configure the undo tablespace to allow for future long-running queries and Oracle Flashback operations.

Viewing Undo Information

You can use the Automatic Undo Management page to view the following information about your undo configuration:

• Name and current size of the undo tablespace

• Autoextend tablespace setting (Yes or No)

• Current autotuned undo retention period

To view undo information:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click the Server link to view the Server subpage.

3. In the Database Configuration section, click Automatic Undo Management.

   The Automatic Undo Management page appears.

   For more information about a page, at any time, click Help.

Computing the Minimum Undo Tablespace Size Using the Undo Advisor

If you decide to change the undo tablespace to a fixed size, use the Undo Advisor to help determine the minimum required size. You can also use the Undo Advisor to set the minimum undo retention period.

To compute the minimum undo tablespace size using the Undo Advisor:

1. Go to the Automatic Undo Management page.

   See "Viewing Undo Information".

2. Decide whether you want to compute the minimum size of the undo tablespace based on statistics gathered over a designated time period (such as the last 7 days), or based on an undo retention period of a duration that you choose.

   The automatically gathered statistics include the duration of the longest-running query and the undo generation rate. Computing the minimum undo tablespace size based on these statistics is advisable if you do not use Oracle Flashback features or if you do not expect future long-running queries to exceed the duration of previous long-running queries.

   If you prefer to choose and enter the duration of a desired undo retention period, the duration must be based on your expectations of the duration of future long-running queries or Oracle Flashback operations.

3. If you want to compute the minimum undo tablespace size based on statistics gathered over a period of time, complete the following steps:

   1. In the Analysis Period section, in the Analysis Time Period list, select the desired analysis time period.

      If you select Customize Time Period, a page appears that enables you to enter the starting and ending date and time for the period.

   2. Select Automatically chosen based on longest query in analysis period.

   3. Click Run Analysis.

      The minimum required undo tablespace size is displayed in the Analysis Results section.

   4. (Optional) Click Show Graph to view a graph of the analysis results.

4. If you want to compute the minimum undo tablespace size based on a duration that you enter, complete the following steps:

   1. In the Analysis Period section, select Specified manually to allow for longer duration queries or flashback.

   2. In the Duration field, enter the desired duration of the undo retention period in seconds, minutes, hours or days.

      You may compute this duration as follows:

      • Determine the duration of the expected longest-running query for your workload.

        You can base your determination on the longest-running query recorded during a previous workload period. This information is available from the System Activity subpage of the Automatic Undo Management page. You choose the analysis period for this subpage by selecting from the Analysis Time Period list on the General subpage. You can choose a custom analysis period that corresponds to your typical workload period.

      • Determine the longest duration that is expected for an Oracle Flashback operation.

      • Take the maximum of these two durations.

      
      Description of the illustration undo_management_activity.gif
      
      

   3. In the Analysis Period section of the General subpage, click Run Analysis.

      The minimum required undo tablespace size is displayed in the Analysis Results section.

   4. (Optional) Click Show Graph to view a graph of the analysis results.

      
      Description of the illustration undo_management_graph.gif
      
      

5. (Optional) Click the tick-mark, or point, on the curve that corresponds to the desired undo retention period.

   The Duration field changes to match the selected undo retention period, and the Minimum Required Undo Tablespace Size field above the graph changes to reflect the matching required size.

6. (Optional) Click Apply.

   The minimum undo retention period is set to the value of the Duration field.

Changing the Undo Tablespace to a Fixed Size

You change the undo tablespace to a fixed size to prevent the tablespace from growing too large or to better support Oracle Flashback operations.

To change the undo tablespace to a fixed size:

1. On the Automatic Undo Management page, after determining the minimum required undo tablespace size, click Edit Undo Tablespace.

   The Edit Tablespace page appears, displaying the properties of the undo tablespace.

2. In the Datafiles section, click Edit.

   The Edit Datafile page appears.

3. In the File Size field, enter the computed minimum size for the undo tablespace.

   See "Computing the Minimum Undo Tablespace Size Using the Undo Advisor".

4. In the Storage section, deselect Automatically extend datafile when full (AUTOEXTEND).

5. Click Continue.

   The Edit Tablespace page returns.

6. Click Apply.

   A confirmation message appears.