Managing undo retention in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize how to estimate required tablespace size and resize a tablespace in a given scenario.

1. Configuring and guaranteeing retention

Undo retention involves setting the amount of time that undo information is maintained – after this time has elapsed, the information can be overwritten.

In the context of the undo retention time, there are three undo data categories.

Uncommitted undo data supports a transaction that is currently in progress. It enables you to roll back the transaction, or to recover if the transaction fails. Uncommitted undo data can never be overwritten.

Data not required in order to roll back a running transaction, but which is needed in order to adhere to the undo retention time, is known as committed or unexpired undo data.

And undo data no longer needed to support a running transaction, which can be overwritten by new undo data, is known as expired undo data.

When automatic undo management is enabled, the system tries to retain undo data until it has expired.

Even with unexpired data, the undo retention time is not applied in all cases - when undo storage space is required by an active transaction that would otherwise fail, unexpired undo data can be overwritten.

In fact, this is Oracle10 g's default behavior.

With automatic retention tuning enabled, the system retains as much undo data as is required by the longest-running query.

When there is not enough space in the undo tablespace for the required undo data, the system retains as much undo data as possible. Generally, it does so without extending the undo storage space.

But when the amount of undo data supports less than 900 seconds of activity, it begins to add extra extents to the undo datafile.

Oracle recommends that you configure undo tablespace datafiles not to extend automatically. This prevents the possibility of a single user consuming excessive amounts of space by failing to commit transactions.

Rather, you should initially enable automatic extension while determining your undo space requirements, and then size the tablespace according to your observations. When finished, you should disable automatic extension.

You can prevent unexpired undo data from being overwritten by guaranteeing undo data retention. This enforces undo retention settings even when to do so may cause transaction failure.

You guarantee undo data retention by setting the RETENTION GUARANTEE tablespace parameter using a SQL command-line statement.

ALTER TABLESPACE tablespacename RETENTION GUARANTEE

And to disable the undo retention guarantee, you use the RETENTION NOGUARANTEE tablespace parameter.

ALTER TABLESPACE tablespacename RETENTION NOGUARANTEE

Suppose that you want to guarantee undo data retention for the UNDODATA1 undo tablespace.

ALTER TABLESPACE undodata1

MISSING CODE

RETENTION GUARANTEE

You complete the necessary command by typing RETENTION GUARANTEE.

You have guaranteed undo data retention.

Question

You want to enforce undo retention settings using iSQL*Plus.

Complete this command to do so.

ALTER TABLESPACE undo1 MISSING CODE

Answer

You type RETENTION GUARANTEE .

You set the undo retention time using the UNDO_RETENTION initialization parameter.

To enable automatic retention tuning, you set UNDO_RETENTION to 0 .

You type 0 in the Value field and click Apply.

You have enabled automatic retention tuning.

Question

You want to enable automatic undo retention. For this purpose, you have located the UNDO_RETENTION parameter in the Initialization Parameters page.

How do you apply the setting that does this?

Options:

0. Delete the value in the Value field and click Apply
0. Type 0 in the Value field and click Apply
0. Type 900 in the Value field and click Apply

Answer

You type 0 in the Value field and click Apply.

2. Calculating undo retention

Undo tablespaces should be of sufficient size to support the required undo information for a desired retention period.

Suppose you want to estimate the required size of an undo tablespace. You can do this using the Undo Advisor.

You begin by logging onto Enterprise Manager and opening the Administration tabbed page.

Then you click the Undo Management link.

An overview of system undo settings displays. These settings include current undo consumption per minute, as well as the length of the longest-running query during a specified length of time.

The Undo Management page also provides access to the Undo Advisor.

You click the Undo Advisor button.

The Undo Advisor page displays.

It enables you to specify an undo retention time and analysis time period.

It provides recommended values you can use to configure the undo tablespace.

And it displays a graph that enables you to make a visual determination of your undo space needs.

Suppose that you want to determine the required tablespace size for a retention time of 120,000 minutes on the basis of an analysis of activity over the past seven days.

You type 120000 in the first New Undo Retention field, and then you click Update Analysis and Graph.

The Required Tablespace Size for New Undo Retention (MB) setting in the Analysis section is now specified as 11034 MB.

You can also see this recommendation lower down the page in the graph.

The new retention time suits your needs, so you want to implement it.

You click OK to implement the new undo retention time.

An Information message confirms the new setting.

Question

You are in Enterprise Manager's Administration tabbed page.

Which of these best describes how you use the Undo Advisor to generate an estimate of the tablespace size required to support an undo retention time of 200,000 minutes on the basis of the past seven days' activity on your system, and then apply the recommended value?

Options:

0. Click Undo Advisor, type 200000 , click Update Analysis and Graph, and click OK
0. Click Undo Management, click Undo Advisor, type 200000 , click Update Analysis and Graph, and click OK
0. Click Update Analysis and Graph, type 200000, and click OK

Answer

You click Undo Management, click Undo Advisor, type 200000 , click Update Analysis and Graph, and click OK.

3. Resizing an undo tablespace

As we have seen, although datafiles in an undo tablespace can be configured to extend automatically when they run out of free space, this is not recommended.

You can, however, add space to a tablespace by either adding a new datafile or by increasing the size of an existing datafile.

Suppose you wish to resize your undo tablespace to support the new undo retention time by increasing the size of an existing datafile.

You first click the Tablespaces link in the Storage section of the Administration page.

The Tablespaces page displays.

You select UNDOTBS1 and click Edit.

The Edit Tablespace: UNDOTBS1 page displays.

The Datafiles section shows that the tablespace contains a single file - undotbs01.dbf. Because it's the only file, it's selected by default.

You want to edit the file to increase its size.

You click Edit.

The Edit Tablespace: UNDOTBS1: Edit Datafile page displays.

Currently, the File Size field shows that undotbs01.dbf is 200 MB in size.

In this example, you want to change the file size to 300 MB.

You type 300 in the first File Size field and click Continue.

The Edit Tablespace: UNDOTBS1 page displays again.

You click Apply to implement the change you have made.

The Edit Tablespace: UNDOTBS1 page displays a message confirming that the change has been implemented.

When you have clearly established that the setting meets your undo space requirements, you should return to the Edit Datafile page and disable automatic extension.

Question

You want to resize your undo tablespace to support the retention period required by a new report. What are the recommended ways of doing this?

Options:

0. Add a new datafile
0. Configure automatic extension
0. Increase the size of a datafile

Answer

You can add a new datafile or Increase the size of an existing datafile.

Option 1 is correct. Adding another datafile to the tablespace increases the space available for undo data.

Option 2 is incorrect. Configuring automatic tablespace extension is possible, but is not a recommended approach to allocating undo storage space.

Option 3 is correct. You can expand an additional datafile to allocate more storage space to the undo tablespace.

Question

You need to increase the amount of storage space allocated to your undo tablespace. For that reason, you have accessed a list of the tablespace's datafiles in Enterprise Manager. The tablespace has a single datafile, which is selected by default.

How do you set the datafile size to 250 MB?

Options:

0. Click Edit, type 250 in the first File Size field, and click Apply
0. Click Edit, type 250 in the first File Size field, click Continue, and click Apply
0. Type 250 in the first File Size field, click Continue, and click Apply

Answer

You click Edit, type 250 in the first File Size field, click Continue, and click Apply.

Summary

There are three types of undo data: uncommitted, committed, and expired. You configure automatic undo retention tuning by setting the UNDO_RETENTION initialization parameter to 0. You guarantee undo data using the RETENTION GUARANTEE tablespace parameter. You can reverse this setting using RETENTION NOGUARANTEE.

Enterprise Manager's Undo Advisor allows you to estimate a tablespace size to suit your undo data retention needs.

You can resize an undo tablespace by increasing the size of one of its datafiles or by adding a datafile. You can do this in Enterprise Manager.