Undo management in Oracle 10g

Learning objective

After completing this topic, you should be able to identify factors to take into consideration when managing undo.

1. Undo data

When a change is made to a data value in a database, Oracle stores the previous value so that the change can be reversed.

This is useful, for example, when you need to recover from failed transactions, such as when a session ends abnormally or the instance crashes.

Oracle can use undo data to restore the system to its state before these failed transactions began.

Oracle Database 10 g's undo functionality supports

• read-consistent queries
• flashback queries

read-consistent queries

There are situations in which changes are made to data in the database while a query is in progress. This leads to the possibility of an inaccurate query result. Oracle Database 10 g uses undo data to ensure that queries can return results on the basis of data committed at the start of query execution, preventing the possibility of this kind of inaccuracy. Such queries are said to be read-consistent.

flashback queries

Flashback queries retrieve information from a specific point in time. As long as the undo data still exists for that time, the query can retrieve information exactly as though it were the current time.

Oracle retains undo data at least until the current transaction ends.

This can occur when

• the user rolls the transaction back
• the user commits the transaction
• the user session terminates abnormally – in such a case, the system rolls back the transaction
• the user session terminates normally – in such a case, the system commits the transaction

You can also configure the database to retain undo data for longer intervals.

Question

Match each type of query with the most appropriate property.

Options:

0. Flashback query
0. Read-consistent query

Targets:

0. Returns results on the basis of data committed at the start of query execution
0. Retrieves information from a specific point in time

Answer

Flashback queries retrieve information from a specific point in time. Read-consistent queries return results on the basis of data committed at the start of query execution.

The query can retrieve the requested information if the necessary undo data still exists for that time.

This prevents the possibility of inaccuracy stemming from changes made to data in the database while the query is in progress.

2. Types of undo management

There are two approaches to implementing undo management.

• Automatic undo management
• Manual undo management

Automatic undo management

With administrator privileges, you can enable automatic undo management by setting the UNDO_MANAGEMENT initialization parameter to AUTO. This is the recommended way of managing undo data.

You use the UNDO_TABLESPACE initialization parameter to specify which undo tablespace is assigned to a particular instance. You should ensure that sufficient space is made available.

You can then set the undo retention interval as appropriate.

In automatic undo management, undo data is stored in undo segments, which are composed of multiple extents. The V$TRANSACTION dynamic performance view specifies which undo segment stores the undo data of which transaction.

Undo segments are stored in undo tablespaces. These are permanent tablespaces with automatic extent allocation.

The maximum allowable number of extents in a segment depends on the block size. In the case of eight-kilobyte blocks, 32,765 extents are allowed.

In most respects, an undo tablespace is a locally managed tablespace administered in the same manner as other tablespaces.

But because recovery from a failed transaction requires undo data, an undo tablespace can only be recovered when the instance is in the MOUNT state.

Undo segments are always owned by SYS.

Their segment type is TYPE 2 UNDO .

Manual undo management

You can enable manual undo management by setting the UNDO_MANAGEMENT initialization parameter to MANUAL. This is the default value.

While this ensures backward compatibility with Oracle8 i and earlier versions, it places a greater burden on the administrator than those versions.

You carry out all management tasks manually, including

• segment sizing, including setting extent maximums and extent sizes
• identifying and eliminating blocking transactions
• ensuring that enough rollback segments are available
• specifying the tablespace that will contain the rollback segments

In manual undo management, undo data is stored in rollback segments.

And rollback segments are stored in ordinary tablespaces.

Question

Match these considerations to the correct type of undo management.

Options:

0. This ensures backward compatibility with Oracle8 i
0. The instance must be in MOUNT state for recovery to be possible
0. You need to carry out segment sizing
0. You need to change the UNDO_TABLESPACE initialization parameter from its default value

Targets:

0. Manual
0. Automatic

Answer

In automatic undo management, the instance must be in MOUNT state for recovery to be possible. You need to change the UNDO_TABLESPACE initialization parameter from its default value. Manual undo management ensures backward compatibility with Oracle8 i. It also requires you to carry out segment sizing.

Question

Which of these are features of undo tablespaces?

Options:

0. They are used in the case of automatic undo management
0. They can be recovered like any other tablespace
0. They contain rollback segments
0. They contain undo segments

Answer

Undo tablespaces are used for automatic undo management, and they contain undo segments.

Option 1 is correct. Undo tablespaces have automatic extent allocation.

Option 2 is incorrect. The instance must be in MOUNT state for recovery to be possible.

Option 3 is incorrect. Rollback segments are used in manual undo management.

Option 4 is correct. Each undo segment contains at least two extents.

3. Storing undo data

In automatic undo management, a transaction is assigned to an undo segment, which stores undo data for the transaction.

Undo segments are created dynamically by the Oracle instance to support the transactions that are taking place.

An undo segment acts as a circular storage buffer for one or more transactions.

As with other types of segments, each extent in an undo segment contains multiple datablocks. Undo data fills each extent in the segment one by one until all are filled.

The transaction then wraps around to the first extent in the segment and checks whether the information it contains is

• required
• expired

required

If the data in the extent is still required, the transaction requests a new extent to be added to the segment.

expired

If the data in the extent is now no longer required, the transaction begins to overwrite the old undo data.

Question

Which of these are characteristics of undo data?

Options:

0. It always expires at the end of the transaction
0. It can never be overwritten
0. It operates like a circular storage buffer
0. Undo space can expand automatically

Answer

An undo segment is a circular storage buffer. When required to do so, undo space can expand automatically.

Option 1 is incorrect. This is generally the case, but you can configure your system so that undo data persists longer.

Option 2 is incorrect. When undo data expires, it can be overwritten by new undo data.

Option 3 is correct. An undo segment acts as a circular storage buffer, and automatically expands and contracts as required.

Option 4 is correct. When it is needed, extra space is allocated to an undo segment.

4. Common undo issues

Even when automatic undo management is enabled, some situations require administrator intervention.

• When insufficient undo space is available
• When required undo data has been overwritten

When insufficient undo space is available

You need to ensure that there is enough space allocated in the undo tablespace to accommodate the undo data required by your transaction, for example when all rows from a large table are deleted.

Oracle Database 10 g's proactive monitoring can identify undo space problems before they affect users.

The "ORA-01650: unable to extend rollback segment" error message displays when insufficient undo space is available for the undo data that you need to retain.

When required undo data has been overwritten

An error message displays when undo data required to answer a SQL query has been overwritten.

This may occur when a query executes over a long interval.

Or it may occur if a flashback query refers to a snapshot that can no longer be reconstructed. This generates the "ORA-01555: snapshot too old" error message.

Question

Which of these are common issues encountered in undo data management?

Options:

0. An undo tablespace requiring recovery
0. Insufficient undo space available
0. Snapshot too old
0. Transactions assigned to multiple undo segments

Answer

Common issues encountered in undo data management include insufficient undo space being available, an undo tablespace requiring recovery, and a snapshot being too old.

Option 1 is correct. An undo tablespace can be recovered only when the instance is in MOUNT state.

Option 2 is correct. You need to try to allow for the effect of transactions that require very large amounts of undo data.

Option 3 is correct. A flashback query fails if some of the required undo data has been overwritten.

Option 4 is incorrect. Each transaction is assigned to a single undo segment. You can check assignments via the V$TRANSACTION dynamic performance view.

Summary

When a change is made to a database, Oracle stores the changed data so that the change can be reversed. Oracle 10 g supports read-consistent queries and flashback queries based on the retained undo data.

You can enable automatic or manual undo management on your system. Automatic undo management uses undo segments in undo tablespaces. Manual undo management uses rollback segments in ordinary tablespaces.

An undo segment is a circular memory buffer composed of extents that are filled one by one until the segment is full. If the data at the start of the segment has expired by then, it is overwritten. Otherwise, additional extents are added to the segment as necessary.

Common undo management issues include insufficient undo space and undo data that has been overwritten.