Learn Oracle


I have created this learn oracle "The step 10 process" Oracle Tutorial to help Database aspirants to accomplish their dream of working as Oracle Database administrator. I have tried to keep it simple and User friendly. Use NEXT and PREVIOUS Button to navaigate away and to a chapter. Since i myself had been a Database adminstrator trainer and has got real life experience of working as a Oracle DBA, I would welcome any sort of queries and doubts or modifications to webmaster@oracleonline.info.

Step 6 - Managing Undo for Your Database

Learn Oracle - Managing Undo for Your Database

This section discusses undo management which involves storing the changes of database transactions long enough to accommodate rollback, read consistency, and flashback features.

When Oracle DBA install the database, Oracle automatically enables auto-tuning of the undo retention period. Auto-tuning of undo retention enables Oracle DBA to get started on building your database without immediately managing undo. Later as your database activity and transaction rate increase, understanding how to manage undo will become more useful to Oracle DBA.

About Undo

Undo data stores changes made to the database by transactions. Undo is necessary for the following:

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 specifically issuing a rollback statement to undo the changes of a misguided or unintentional transaction, or it can be part of a recovery operation.

To provide read consistency. This 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 am and the query lasts for 15 minutes, the query results should reflect the entire state of the data at 10:00 am regardless of any updates or inserts by other users during the 15 minute query.

To enable Oracle’s flashback features, which enables Oracle DBA to view or recover data to a previous point in time. These features include Flashback Query and Flashback Table.

Undo Tablespace Size and Retention Time

In order for changes to be rolled back or undone, Oracle makes a copy of the original data before modifying it. The original copy of the modified data is called undo data. Undo data is stored in a logical database structure called an undo tablespace. The undo tablespace is of a finite size. Space usage within the tablespace is such that records can be overwritten. The undo data needs to be saved at least until the transaction has been committed. Until then the undo data is said to be in the active state. The amount of space available in the undo tablespace should, therefore, be at least large enough to hold the active undo data generated by currently active transactions. Otherwise some of these transactions may fail. After the 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, the undo tablespace should be big enough to hold 15 minutes worth of undo information.

To control the retention of undo records, Oracle maintains an undo retention period, which in turn affects the size of the undo tablespace; the longer the retention period, the bigger the tablespace. The undo retention period should be at least as long as your longest running query. In addition, if Oracle DBA plan to use flashback features to recover from human errors such as unintentional changes, the undo retention should be set equal to the amount of time in the past Oracle DBA want to go back. .

Automatic Undo Management

Oracle Database automatically determines how long undo data should be preserved after the transaction has been committed based on queries running against the database. During this time, the undo data is said to be in the unexpired state. Oracle will keep the undo data in the unexpired state as long as needed by queries currently running against the database or as long as any low undo retention threshold (whichever is longer). After this time, the state of undo data changes to expired. Undo data is a good candidate for overwriting only when it is in the expired state. If the undo tablespace does not have adequate free or expired space to store active undo data generated by current transactions, Oracle may be forced to overwrite the unexpired undo data. As this may cause your queries to fail, this is not a desirable situation. In order to avoid this, Oracle recommends Oracle DBA allow the size of the undo tablespace to be automatically increased by enabling auto-extend.

If Oracle DBA cannot enable auto extension for any reason and choose to size the undo tablespace manually, Oracle Database includes an undo advisor to help Oracle DBA determine the optimal size.

With Enterprise Manager, Oracle DBA can access the Undo Management page from the database Administration page by selecting Undo Management under the Instance heading.

Oracle DBA can use the Undo Management page to view undo configuration, including the auto-tuned undo retention period, its low threshold retention period, and the name and size of the undo tablespace. Auto-tuning of undo retention cannot be disabled. Oracle DBA can also use this page to set the low threshold retention or to extend your tablespace. While Oracle automatically tunes the undo retention period, low threshold undo retention lets Oracle DBA define the floor value under which Oracle should never bring down the undo retention. When Oracle DBA create a database, the low threshold undo retention is set to a default value. You may need to alter this value only when you need to build a recovery strategy using Flashback Query.

To change the low threshold, click its value. The Undo Advisor page appears, which can help you determine a better setting.

The Configuration section also shows if auto-extending the tablespace is enabled, which is the default. When auto-extend tablespace is enabled, Oracle automatically increases the size of the undo tablespace when more space is needed. By combining automatic extension of the undo tablespace with automatically tuned undo retention,

Oracle DBA can ensure that long-running queries will succeed by guaranteeing the undo required for such queries.

The methods for determining the undo retention period are described in the following sections:

Using Oracle Recommendations for Managing Undo

Using the Undo Advisor

Using Oracle Recommendations for Managing Undo

In the Recommendations section of the Undo Management page, you can specify a past time period for Oracle to analyze system activity. The current recommendations for your undo configuration are summarized. To update the analysis, click Update Analysis.

If Oracle recommends that Oracle DBA extend the undo tablespace, you can do so.

To obtain more detailed information about the recommendations, click Undo Advisor.

Extending the Undo Tablespace

When auto-extend tablespace is enabled, the system will automatically extend the undo tablespace if it is under space pressure. However, when the auto-extend tablespace feature is disabled, you might need to manually extend the undo tablespace. You might discover this through pro-active planning with the undo advisor. See "Using the Undo Advisor" on page 6-14. Oracle DBA might also need to extend it when you get an undo tablespace alert (warning or critical), or when you get long query alert or snapshot too old error. To resize the tablespace, click Edit Undo Tablespace. The Edit Tablespace page appears. To extend the tablespace, select a datafile and click Edit. Enter the new size for File Size. You can also opt to have the system automatically extend the datafile by enabling Automatically extend datafile when full under Storage and specifying an increment size.

Click Continue. Click Apply. A confirmation message should appear.

Using the Undo Advisor

Oracle DBA can use the undo advisor to advise on the undo tablespace size and the low threshold undo retention setting. To navigate to the Undo Advisor page, do one of the following from the Undo Management page:

Click Undo Advisor

Click the value adjacent to Low Threshold Undo Retention. The top of the page shows the current auto-tuned undo retention time and undo tablespace size.

The Undo Advisor helps you answer the following questions about your undo configuration:

1. What is the maximum undo retention time required to guarantee successful query? This time period corresponds to the duration of your longest running query. You can find Longest Running Query on the Undo Management page under System Activity and Tablespace Usage.

2. Does my database have enough space to accommodate the maximum undo retention given the current tablespace configuration? To figure this out, note the Best Possible Undo Retention under analysis on the Undo Advisor page. If this time period is longer than your longest running query, your undo tablespace is adequately configured.

3. What if the best possible retention time is less than my longest running query? If this is the case, your undo tablespace is too small. Oracle DBA need to either set your tablespace to auto-extend or manually extend it. To figure out the required new size of your tablespace, on the Undo Advisor page New Undo Retention field, plug in the value of your longest running query. Enter an appropriate Analysis Time Period and click Update Analysis and Graph. Under Analysis, note the Required Tablespace size for New Undo Retention.

4. How do I configure undo to accommodate Flashback operations? If you are interested in using flashback features such as Flashback Query or Flashback Table, you can use the Undo Advisor to advise on how to configure your tablespace and undo retention. In order 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 low threshold parameter, which determines the lowest value for automatic undo tuning. For example, if the low threshold is set to 15 minutes, Oracle never lowers the undo retention time to less than 15 minutes. Consequently, if your flashback recovery strategy requires Oracle DBA to go back 8 hours to recover from human errors, set the low threshold to 8 hours. To learn how to set a new retention time, see "Setting New Threshold Undo Retention Time". 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 and Best Possible Retention.
Oracle Tutorial - BLOGS

STEP 6 - Next Topics
This Step discusses management of the database's storage structure. Click on Oracle tuorial links below:
The Control FileControl file
Redo Log FilesRedo log file
Archive Log FilesArchive log file
Initialization Parameter FileParameter file
Password FilePassword file
Backup FilesBackup file
Viewing Tablespaces in your DatabaseTablespace
Creating a tablespacecreating tablespace
Modifying a TablespaceModifying tablespace
Dropping a TablespaceDropping Tablespace
Managing Undo for Your DatabaseManaging UNDO
Oracle Database - PODCASTS
More Tutorials on Oracle dba ...

Liked it ? Want to share it ? Social Bookmarking
Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information

Source : Oracle Documentation | Oracle DBA

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info