UNDO TABLE SPACE

UNDO TABLE SPACE

UNDO MANAGEMENT

Oracle 9i introduced undo tablespace. Undo tablespace is used to store the previous Image of the data similar to rollback segments. Even though both modes are present the default being the rollback segment. Starting with oracle9i , , DBA’s were provided with a new feature called “Undo Tablespace” which allows more control over the undo information as of how long undo information is retained and more over eliminating the need for managing of rollback segments.

What’s new in undo management?

ü  No need to manually create rollback segments

ü  No need to worry about the size of each rollback segments

ü  A separate tablespace is allocated

ü  Managing the undo tablespace is  like other tablespaces

ü  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.

Parameters that are to be modified in init.ora

1.    UNDO_MANAGEMENT: This parameter sets the mode in which oracle manages the undo information. The default value is    manual (9i) which means the instance runs on Rollback segments.

           To use undo tablespace set UNDO_MANAGEMENT=auto

2.    UNDO_TABLESPACE: This parameter indicates which tablespace to use if undo_management=auto is set. f no value is specified oracle grabs the first available Undo Tablespace or if there are none present, Oracle will use the system rollback segment to startup. This value is dynamic and can be changed online

3.    UNDO_RETENTION: This value specifies the amount of time, Undo is kept in the tablespace. This applies to both committed and uncommitted transactions since the introduction of FlashBack Query feature in Oracle needs this information to create a read consistent copy of the data in the past. Default value is 900 Secs.

4.    UNDO_SUPRESS_ERRORS: This is a good thing to know about in case your code has the alter transaction commands that perform manual undo management operations. Set this to true to suppress the errors generated when manual management SQL operations are issued in an automated management mode.

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. Space usage within the tablespace is such that records can 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 currently active 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. It is important to the success of Flashback functionality, and for read consistency for long running transactions, that records not be overwritten too soon. 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 transpire 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 of time to which you want to return.

Creating the undo tablespace

Undo tablespaces use the same syntax used by other tablespaces except that they use undo keyword

These can be created at the time of database creation

UNDO Tablespace <undo tablespace> datafile <datafile name>

Or can be created after the creation of database as

Create UNDO Tablespace <undo tablespace name>
datafile <datafile name size 25m autoextend on>

You can create more than one undo tablespace, but only one of them can be active at any one time.

DROP TABLESPACE <undo tablespace>;

DROP TABLESPACE for undo tablespaces behaves like DROP TABLESPACE...INCLUDING CONTENTS. All contents of the undo tablespace are removed.

      All operations like Renaming a data file, Adding a datafile, Online /Offline Switch or Start Backup / End Backup Switch can be made using the regular alter tablespace command.

V$UNDOSTAT: This view contains statistics for monitoring the effects of transaction execution on Undo Space in the current instance. These are available for space usage, transaction concurrency and length of query operations. This view contains information that spans over a 24 hour period and each row in this view contains data for a 10 minute interval specified by the BEGIN_TIME and END_TIME.

Beyond 4 days the information is stored in DBA_HIST_UNDOSTAT view.

Sizing the undo tablespace:

1. Autoextend on: This is helpful when we are unsure of space requirement of undo tablespace In this case we can enable automatic extension of the undo space so that it automatically increases when needed

 2. Sizing fixed undo tablespaces: If you decide on fixed size undo tablespace then the undo Advisor can help you to estimate the capacity. You can access the Undo Advisor through Enterprise Manager or through the DBMS_ADVISOR PL/SQL package. Enterprise Manager is the preferred method of accessing the 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 

Undo Management page can be used to view the following

ü  Name and size of undo tablespace

ü  Auto-extend enabled or not

ü  Auto –tuned retention period

ü  Minimum Retention Period

If you have a fixed-size undo tablespace then this page can be used to set the undo tablespace size

Undo Advisor

The undo advisor helps in analyzing the various scenarios to determine the undo tablespace size for different retention periods. The analysis might be helpful in following situations.

1. When you have a fixed size undo tablespace and auto-tuned retention value 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 queries.
2. To use flashback feature so that the undo data is not overwritten.

Using the Undo Advisor

The undo tablespace is set to auto-extend by default when you use DBCA to configure your database. If your undo tablespace is not auto-extensible, you may need to extend the tablespace. You can use the undo advisor to obtain advise on the undo tablespace size.

You can access the undo advisor as follows:

Switching Undo Tablespaces

You can switch from using one undo tablespace to another. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.
The following statement switches to a new undo tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

Assuming undotbs_01 is the current undo tablespace, after this command successfully executes, the instance uses undotbs_02 in place of undotbs_01 as its undo tablespace.
If any of the following conditions exist for the tablespace being switched to, an error is reported and no switching occurs:

·         The tablespace does not exist

·         The tablespace is not an undo tablespace

·         The tablespace is already being used by another instance (in a RAC environment only)

The database is online while the switch operation is performed, and user transactions can be executed while this command is being executed. When the switch operation completes successfully, all transactions started after the switch operation began are assigned to transaction tables in the new undo tablespace.
The switch operation does not wait for transactions in the old undo tablespace to commit. If there are any pending transactions in the old undo tablespace, the old undo tablespace enters into a PENDING OFFLINE mode (status). In this mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace.
An undo tablespace can exist in this PENDING OFFLINE mode, even after the switch operation completes successfully. A PENDING OFFLINE undo tablespace cannot be used by another instance, nor can it be dropped. Eventually, after all active transactions have committed, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode. From then on, the undo tablespace is available for other instances (in an Oracle Real Application Cluster environment).
If the parameter value for UNDO TABLESPACE is set to '' (two single quotes), then the current undo tablespace is switched out and the next available undo tablespace is switched in. Use this statement with care because there may be no undo tablespace available.
The following example removes the current undo tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = '';

View	Description
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 uses 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_UNDOSTAT	Contains statistical snapshots of V$UNDOSTAT information.