Managing Undo for Your Database

About Undo Data

When a transaction modifies the database, Oracle 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 uncommitted 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 updates or inserts by other users during the query.

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

Undo Tablespace Size and Retention Time

Undo data is stored in a logical database structure called an undo tablespace. The undo tablespace is of finite size, so records might be overwritten as transactions occur.

Oracle saves undo data at least until the transaction has been committed. Until this time, the undo data is in the active state. Therefore, the amount of space available in the undo tablespace should be at least large enough to hold the active undo data generated by current transactions. Otherwise, some of these transactions might fail. When active undo data is stored in the undo tablespace, Oracle automatically ensures that it is never overwritten until the corresponding transaction has been committed.

Even after the transaction has been committed, the undo data still cannot be overwritten immediately to ensure the success of Flashback functionality, and for read consistency for long running transactions. For example, if your longest query takes 15 minutes, then the undo tablespace should be large enough to hold 15 minutes worth of undo data.

To control the retention of undo records, Oracle maintains an undo retention period. This period indicates the amount of time that must pass before Oracle overwrites undo data. The undo retention period affects the size of the undo tablespace; the longer the retention period, the more space is needed.

The undo retention period should be at least as long as your longest-running query. By default, Oracle automatically extends the undo tablespace to accommodate the longest-running query based on your ongoing system activity. Nevertheless, you might need to manually increase the size of your undo tablespace in the following circumstances:

• When your undo tablespace is set to a fixed size (auto-extend disabled) and long running queries are failing with snapshot too old errors.

• When you plan to use Flashback features to recover from user errors such as unintentional changes. In this case, the undo retention should be set equal to the period between the present and the earliest point in time to which you want to return. For more details on Flashback features, see Oracle Database Administrator's Guide.

Automatic Undo Management

The Oracle database automatically determines how long undo data should be kept based on the time your queries take to run. Undo data preserved within this window of time is said to be in the unexpired state. After this time, the state of the undo data changes to expired. Undo data is a good candidate for overwriting only when it is in the expired state.

The length of time that Oracle keeps undo data in the unexpired state depends on your tablespace configuration. When you create your database with DBCA, the undo tablespace is set by default to automatically extend itself to maintain unexpired undo for the longest-running query.

With a fixed-sized undo tablespace, Oracle automatically keeps the undo data in the unexpired state for the longest possible time for the tablespace of the specified size. If the undo tablespace does not have adequate free or expired space to store active undo data generated by current transactions, however, then Oracle might be forced to overwrite the unexpired undo data. This situation might cause long-running queries to fail with an error and an alert.

To avoid situations in which long-running queries can fail, it is recommended that you let Oracle automatically extend the size of the undo tablespace. By default, the undo tablespace is set to auto-extend when you use DBCA to configure your database.

You may choose to disable auto-extension and adjust the size of the tablespace manually. In this case, ensure that the tablespace is large enough to meet the read-consistency requirements for your longest-running query. Also, if you use Flashback features, then the tablespace must be large enough to accommodate Flashback operations. Oracle Enterprise Manager includes an Undo Advisor to help you determine the optimal size. See "Using the Undo Advisor".

Managing Undo with Enterprise Manager

With Enterprise Manager, you can manage undo as follows:

1. From the Database Control home page, click Administration.

   The Administration property page appears.

2. In the Database Configuration section, click Undo Management.

   The Undo Management page appears, as shown in Figure 6-5.

Figure 6-5 Undo Management page

Description of "Figure 6-5 Undo Management page"

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

• Name and size of undo tablespace

• Auto-extend tablespace setting

• Auto-tuned undo retention period

• Minimum retention period

In Undo Tablespace for this Instance, the Auto-Extensible field shows Yes if auto-extending the tablespace is enabled, which is the default. When the undo tablespace is auto-extensible, Oracle automatically increases the size of the tablespace when more space is needed. By combining automatic extension of the undo tablespace with automatically tuned undo retention, you can ensure that long-running queries succeed by guaranteeing the undo for such queries.

If you have a fixed-size tablespace, then you can use this page as a starting point for determining space requirements and extending the tablespace. See "Using the Undo Advisor".

The Undo Retention Settings section describes your minimum undo retention period. While Oracle automatically tunes the undo retention period, minimum undo retention enables you to define the lowest value allowable for your database. When you create a database, the minimum undo retention is set to a default value. You might need to alter this value to build a recovery strategy using Flashback Query. See "Setting Minimum Undo Retention Time".

The Recommendations section of the Undo Management page gives you recommendations on undo tablespace size based on your system activity. Recommendations and alerts can arise when you are using a fixed-size tablespace and queries have been failing because of insufficient undo space. On rarer occasions, when using an auto-extend tablespace, the same recommendations can arise if the system has reached it maximum disk limit.

If Oracle recommends that you extend the undo tablespace, then you can use the Undo Advisor to determine a better size.

Using the Undo Advisor

The amount of undo data that can be retained depends on the size of your undo tablespace. If your tablespace is set to auto-extend, then Oracle automatically acquires space as needed. If you choose to disable auto-extend, however, then you are responsible for ensuring that the undo tablespace has enough space. In this situation, Oracle configures the undo retention to the maximum possible value for that tablespace size.

The Undo Advisor helps you analyze various scenarios to determine an appropriate undo tablespace size for different values of maximum undo retention. This analysis might be required in the following situations:

• You have a fixed-sized tablespace (auto-extend disabled) and the auto-tuned value of undo retention is not large enough to prevent queries from failing. The Undo Advisor can help you determine a better tablespace size to ensure successful completion of your queries. See "Gaining Advice from Undo Advisor".

• You use Flashback features such as Flashback Query or Flashback Table. For Flashback operations to go back in time, the database must ensure that undo data is not overwritten.

  To build a flashback recovery strategy, you can set the minimum undo retention, which determines the lowest value for automatic undo tuning. For example, if the low threshold is set to 15 minutes, then Oracle never lowers the undo retention time to less than 15 minutes. Consequently, if your flashback recovery strategy requires you to go back 8 hours to recover from human errors, then set the minimum undo retention to 8 hours. To learn how to set a new retention time, see "Setting Minimum Undo Retention Time"

Gaining Advice from Undo Advisor

The Undo Advisor can help you determine a better size for your undo tablespace to ensure successful completion of queries. Follow these general steps to determine the required new size of your tablespace:

1. Determine the duration of your longest running query according to your application characteristics. If this duration is longer than the tuned retention, then these long-running queries will encounter an error. In this case, your undo tablespace is too small. You need to either set your tablespace to auto-extend or manually extend it.

2. In the Database Configuration section of the Administration page, click Undo Management.

   The Undo Management page appears, as shown in Figure 6-5.

3. Click Undo Advisor.

   The Undo Advisor page appears. The top of the page shows the current auto-tuned undo retention time and undo tablespace size, as shown in Figure 6-6.

4. In the New Undo Retention field of the Advisor section, plug in the value of your longest-running query.

5. In the Analysis Time Period list, select a time period that best reflects your business cycle.

   The Analysis section displays the Required Tablespace size for New Undo Retention.

For example, suppose we have a fixed size tablespace of 25 MB. We determine the longest running query is 60 minutes. We enter this number in the New Undo Retention field as shown in Figure 6-6. The advisor recommends 10 MB of space to accommodate this query. Because our tablespace is 25 MB, our undo tablespace is adequately configured.

Figure 6-6 Undo Advisor with Fixed-Sized Tablespace

Description of "Figure 6-6 Undo Advisor with Fixed-Sized Tablespace"

Additionally, the Required Tablespace Size by Undo Retention Length graph shows the relationship between retention period and undo tablespace size, highlighting key data points such as the Auto-tuned Undo Retention and Best Possible Undo Retention.