This action can potentially cause some queries to fail with the "snapshot too old" message. Automatic tuning of undo retention is not supported for LOBs. Oracle Database 10 g lets you guarantee undo retention. When you enable this option, the database never overwrites unexpired undo data--that is, undo data whose age is less than the undo retention period.
This option is disabled by default, which means that the database can overwrite the unexpired undo data in order to avoid failure of DML operations if there is not enough free space left in the undo tablespace. By enabling the guarantee option, you instruct the database not to overwrite unexpired undo data even if it means risking failure of currently active DML operations.
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.
I am also an independent Oracle DBA Consultant providing services and strategies to teams in other organizations across the globe. I love learning new technologies and very enthusiastic to train professionals on Oracle DBA. Save my name, email, and website in this browser for the next time I comment. Currently you have JavaScript disabled. In order to post comments, please make sure JavaScript and Cookies are enabled, and reload the page.
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.
Use this statement with care because there may be no undo tablespace available. 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 undo is terminated.
No other members of the consumer group can perform further updates until undo space is freed from the pool. If you are currently 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.
DBA privileges are required to execute this function:. 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 Tablespace Alerts" 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 4 day cycle. It contains the following topics: What Is Undo? What Is Undo? Introduction to Automatic Undo Management This section introduces the concepts of Automatic Undo Management and discusses the following topics: Overview of Automatic Undo Management Undo Retention Overview of Automatic Undo Management Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space.
This parameter should be used only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace. See Also: Oracle Database Reference for complete descriptions of initialization parameters used in automatic undo management. Undo Re tention After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes.
Reten tion Guarantee To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee.
0コメント