Also, note that the automatic undo management feature introduced in Oracle 9i has been improved with the following capabilities. Some of the undo management related initialization parameters are eliminated starting in Oracle 10g because the UNDO size is automatically calculated. Oracle Training from Don Burleson The best on site " Oracle training classes " are just a phone call away!
You can get personalized Oracle training by Donald Burleson, right at your shop! Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience! 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 in time to which you want to return.
The Oracle database automatically determines how long undo data should be kept based on the time your queries take to run. Undo data preserved within this window of time is said to be in the unexpired state. After this time, the state of the undo data changes to expired. Undo data is a good candidate for overwriting only when it is in the expired state. The length of time that Oracle keeps undo data in the unexpired state depends on your tablespace configuration.
When you create your database with DBCA, the undo tablespace is set by default to automatically extend itself to maintain unexpired undo for the longest-running query. With a fixed-sized undo tablespace, Oracle automatically keeps the undo data in the unexpired state for the longest possible time for the tablespace of the specified size.
If the undo tablespace does not have adequate free or expired space to store active undo data generated by current transactions, however, then Oracle might be forced to overwrite the unexpired undo data.
This situation might cause long-running queries to fail with an error and an alert. To avoid situations in which long-running queries can fail, it is recommended that you let Oracle automatically extend the size of the undo tablespace. By default, the undo tablespace is set to auto-extend when you use DBCA to configure your database. You may choose to disable auto-extension and adjust the size of the tablespace manually. In this case, ensure that the tablespace is large enough to meet the read-consistency requirements for your longest-running query.
Also, if you use Flashback features, then the tablespace must be large enough to accommodate Flashback operations. Oracle Enterprise Manager includes an Undo Advisor to help you determine the optimal size. See "Using the Undo Advisor". In Undo Tablespace for this Instance, the Auto-Extensible field shows Yes if auto-extending the tablespace is enabled, which is the default. When the undo tablespace is auto-extensible, Oracle automatically increases the size of the tablespace when more space is needed.
By combining automatic extension of the undo tablespace with automatically tuned undo retention, you can ensure that long-running queries succeed by guaranteeing the undo for such queries. If you have a fixed-size tablespace, then you can use this page as a starting point for determining space requirements and extending the tablespace. The Undo Retention Settings section describes your minimum undo retention period.
While Oracle automatically tunes the undo retention period, minimum undo retention enables you to define the lowest value allowable for your database. When you create a database, the minimum undo retention is set to a default value. You might need to alter this value to build a recovery strategy using Flashback Query. The Recommendations section of the Undo Management page gives you recommendations on undo tablespace size based on your system activity.
Recommendations and alerts can arise when you are using a fixed-size tablespace and queries have been failing because of insufficient undo space.
On rarer occasions, when using an auto-extend tablespace, the same recommendations can arise if the system has reached it maximum disk limit. If Oracle recommends that you extend the undo tablespace, then you can use the Undo Advisor to determine a better size. 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". Therefore, use caution when using this feature. A typical use of the guarantee option is when you want to ensure deterministic and predictable behavior of Flashback Query by guaranteeing the availability of the required undo data.
You can size the undo tablespace appropriately either by using automatic extension of the undo tablespace or by manually estimating the space you will need for undo. This section discusses both methods. Oracle Database supports automatic extension of the undo tablespace to facilitate capacity planning of the undo tablespace in the production environment. When the system is first running in the production environment, you may be unsure of the space requirements of the undo tablespace.
In this case, you can enable automatic extension for datafiles of the undo tablespace so that they automatically increase in size when more space is needed.
By combining automatic extension of the undo tablespace with automatically tuned undo retention, you can ensure that long-running queries will succeed by guaranteeing the undo required for such queries. If you have decided on a fixed-size undo tablespace, the Undo Advisor can help you estimate needed capacity, and you can then calculate the amount of retention your system will need.
Enterprise Manager is the preferred method of accessing the advisor. An adjustment to the collection interval and retention period for AWR statistics can affect the precision and the type of recommendations the advisor produces. Please refer to "Automatic Workload Repository" for additional information. Oracle Database provides an Undo Advisor that provides advice on and helps automate the establishment of your undo environment.
You activate the Undo Advisor by creating an undo advisor task through the advisor framework. The following example creates an undo advisor task to evaluate the undo tablespace.
The name of the advisor is 'Undo Advisor'. Once you have created the advisor task, you can view the output and recommendations in the Automatic Database Diagnostic Monitor in Enterprise Manager. Oracle Database 2 Day DBA for more information on using advisors and "Segment Advisor" for an example of creating an advisor task for a different advisor.
You can calculate space requirements manually using the following formula:. This value should take into consideration long-running queries and any flashback requirements. In the steady state, you can query the view to obtain the transaction rate.
The overhead figure can also be obtained from the view. This section describes the various steps involved in undo tablespace management and contains the following sections:.
There are two methods of creating an undo tablespace. The second method is used with an existing database. You cannot create database objects in an undo tablespace. It is reserved for system-managed undo data. Oracle Database enables you to create a single-file undo tablespace. Single-file, or bigfile, tablespaces are discussed in "Bigfile Tablespaces". You can create more than one undo tablespace, but only one of them can be active at any one time. However, since most aspects of undo tablespaces are system managed, you need only be concerned with the following actions:.
If an undo tablespace runs out of space, or you want to prevent it from doing so, you can add more files to it or resize existing datafiles. An undo tablespace can only be dropped if it is not currently used by any instance. All contents of the undo tablespace are removed. You can switch from using one undo tablespace to another. If any of the following conditions exist for the tablespace being switched to, an error is reported and no switching occurs:. 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. In this mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace.
From then on, the undo tablespace is available for other instances in an Oracle Real Application Cluster environment. The Oracle Database Resource Manager can be used to establish user quotas for undo space. You can specify an undo pool for each consumer group. An undo pool controls the amount of total undo that can be generated by a consumer group.
When the total undo generated by a consumer group exceeds its undo limit, the current UPDATE transaction generating the redo is terminated. No other members of the consumer group can perform further updates until undo space is freed from the pool. This section lists views that are useful for viewing information about undo space in the automatic undo management mode and provides some examples. In addition to views listed here, you can obtain information from the views available for viewing tablespace and datafile information.
Please refer to " Viewing Datafile Information" for information on getting information about those views. Oracle Database also provides proactive help in managing tablespace disk space use by alerting you when tablespaces run low on available space. Please refer to "Managing Space in Tablespaces " for information on how to set alert thresholds for the undo tablespace. To prevent excessive alerts, the long query alert is issued at most once every 24 hours.
0コメント