In this management mode, you create an undo tablespace , and the server automatically manages undo segments and space among the various active sessions. A default undo tablespace is then created at database creation. An undo tablespace can also be created explicitly. The methods of creating an undo tablespace are explained in "Creating an Undo Tablespace".
When the instance starts, the database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace.
This is not recommended in normal circumstances, and an alert message is written to the alert log file to warn that the system is running without an undo tablespace. If the database contains multiple undo tablespaces, you can optionally specify at startup that you want to use a specific undo tablespace.
The following is a summary of the initialization parameters for automatic undo management:. When automatic undo management is enabled, if the initialization parameter file contains parameters relating to manual undo management, they are ignored. After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks.
Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible. When automatic undo management is enabled, there is always a current undo retention period , which is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it.
Old committed undo information that is older than the current undo retention period is said to be expired. Old undo information with an age that is less than the current undo retention period is said to be unexpired. Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity.
The database makes its best effort to honor the specified minimum undo retention period, provided that the undo tablespace has space available for new transactions. When available space for new transactions becomes short, the database begins to overwrite expired undo. If the undo tablespace has no space for new transactions after all expired undo is overwritten, the database may begin overwriting unexpired undo information. If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the snapshot too old er ror message.
The database may overwrite unexpired undo information when tablespace space becomes low. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends.
If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information. To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.
If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system.
This option is disabled by default. Enabling retention guarantee can cause multiple DML operations to fail. Use with caution. Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured. If the undo tablespace is fixed size, the database tunes the retention period for the best possible undo retention for that tablespace size and the current system load. This tuned retention period can be significantly greater than the specified minimum retention period.
Again, this tuned retention period can be greater than the specified minimum retention period. This view contains one row for each minute statistics collection interval over the last 4 days.
Undo Retention Tuning and Alert Thresholds For a fixed size undo tablespace, the database calculates the maximum undo retention period based on database statistics and on the size of the undo tablespace. For more information on tablespace alert thresholds, see "Managing Tablespace Alerts". This parameter specifies the desired minimum undo retention period in seconds.
You can size the undo tablespace appropriately either by using automatic extension of the undo tablespace or by using the Undo Advisor for a fixed sized tablespace.
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 of the undo tablespace so that it automatically increases in size when more space is needed. If you have decided on a fixed-size undo tablespace, the Undo Advisor can help you estimate needed capacity.
Enterprise Manager is the preferred method of accessing the advisor. It is therefore important that the AWR have adequate workload statistics available so that the Undo Advisor can make accurate recommendations. 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. When the alert is generated, you can check the Undo Advisor Page of Enterprise Manager to get more information about the undo tablespace.
The following dynamic performance views are useful for obtaining space information about the undo tablespace:. Statistics are available for undo space consumption, transaction concurrency, the tuning of undo retention, and the length and SQL ID of long-running queries in the instance.
Each row in the view contains statistics collected in the instance for a ten-minute interval. Each column represents the data collected for the particular statistic in that time interval.
The first row of the view contains statistics for the partial current time period. The view contains a total of rows, spanning a 7 day cycle. Your Oracle Database includes several features that are based upon undo information and that allow administrators and users to access database information from a previous point in time. These features are part of the overall flashback strategy incorporated into the database and include:. The retention period for undo information is an important factor for the successful execution of Flashback features.
It determines how far back in time a database version can be established. Specifically, you must choose an undo retention interval that is long enough to enable users to construct a snapshot of the database for the oldest version of the database that they are interested in.
Using Oracle Flashback Query feature, users or applications can execute queries as of a previous time in the database. Application developers can use Flashback Query to design an application that allows users to correct their mistakes with minimal DBA intervention. You, as the DBA, need only configure the undo tablespace with an appropriate size and undo retention period.
No further action on your part should be required. The Flashback Version Query feature enables users to query the history of a given row. Otherwise, not all rows can be retrieved. One use for this view could be if a user finds, by using the Flashback Transaction Query feature, that a row value has been changed inappropriately.
It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. If you are still using rollback segments to manage undo space, Oracle strongly recommends that you migrate your database to automatic undo management. Oracle Database provides a function that provides information on how to size your new undo tablespace based on the configuration and usage of the rollback segments in your system.
Your email address will not be published. Check Undo Tablespace Usage You can use the following query to check the undo tablespace usage. Author: dbtut We are a team with over 10 years of database management and BI experience. Share Facebook Twitter LinkedIn. Leave a Reply Cancel reply Your email address will not be published.
0コメント