Using the Undo Advisor!

doc原话，随手记录一下！

http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/storage003.htm#sthref369

[@more@]

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.

Extending the Undo Tablespace

When auto-extend tablespace is enabled, the system automatically extends the undo tablespace if it is under space pressure. When the auto-extend tablespace feature is disabled, however, you might need to manually extend the undo tablespace. You might discover this information ahead of time while planning with the Undo Advisor. See "Using the Undo Advisor".

You might also need to extend the undo tablespace when you get an undo tablespace alert (warning or critical), or when you get a query too long alert or snapshot too old error.

To resize the tablespace:

1. From the Undo Management page, click Edit Undo Tablespace.

   The Edit Tablespace page appears.

2. Select a datafile to extend and click Edit.

   The Edit Datafile page appears.

3. In the File Size field, enter a new datafile size.

   You can also have the system automatically extend the datafile by enabling Automatically extend datafile when full under Storage and specifying an increment size.

4. Click Continue.

   The Edit Tablespace page appears.

5. Under Datafiles, Click Apply.

   A confirmation message should appear.

Setting Minimum Undo Retention Time

If you are using the Flashback Query or Flashback Table feature and need to configure a flashback recovery strategy to go back in time, then configure the minimum undo retention as follows:

1. In the New Undo Retention field of the Undo Advisor page, enter the new retention period.

   For configuring Flashback Query and Flashback Table, set a value equal to how far back in time you need to go. For example, if you need an eight hour flashback recovery strategy, set the minimum retention to 8 hours.

2. Click OK.

Alternatively, you can set this parameter as you do for other system parameters by navigating to the All Initialization Parameters page. Set the parameter called undo_retention. For more information about this page, see "Viewing and Modifying Initialization Parameters".

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1013151/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1013151/