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.
DBA privileges are required to execute this function:. Set the warning and critical alert thresholds for the undo tablespace alert properly. It contains the following topics: What Is Undo? What Is Undo? See Also: "Migration to Automatic Undo Management" for information on how to migrate to automatic undo management. 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 Retention Guarantee Overview of Automatic Undo Management In past releases, when you used the rollback segment method of managing undo space, you were said to be operating in the manual undo management mode.
Notes: You cannot use both methods in the same database instance, although for migration purposes it is possible, for example, to create undo tablespaces in a database that is using rollback segments, or to drop rollback segments in a database that is using undo tablespaces. 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.
Oracle Database also provides proactive help in managing tablespace disk space use by alerting you when tablespaces run low on available space. See "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.
If you are currently using rollback segments to manage undo space, Oracle strongly recommends that you migrate your database to automatic undo management. For instructions, see Oracle Database Upgrade Guide. 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 data file information. 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.
In this chapter: 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 About the Undo Retention Period Overview of Automatic Undo Management Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space.
Note: Space management for rollback segments is complex. Oracle strongly recommends leaving the database in automatic undo management mode. The default is AUTO. Specifies the name of an undo tablespace. Use only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace. Note: Earlier releases of Oracle Database default to manual undo management mode. If your Oracle Database is release 9 i or later and you want to change to automatic undo management, see Oracle Database Upgrade Guide for instructions.
See Also: Oracle Database Reference for complete descriptions of initialization parameters used in undo management. About the Undo Re tention Period After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. Automatic Tun ing of Undo Retention Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured. Notify me of followup comments via e-mail.
All rights reserved Terms of Service. If you have Oracle database running on your environment, even if you are a developer or linux sysadmin, you might end-up doing some basic DBA tasks on development instance. Wolfgang May 2, , am. Did I miss anything or why is this just one datafile?
Naveen Reddy April 21, , am. Excellent Article and Thank you for sharing such wonderful and useful information. I will be posting instruction guides, how-to, troubleshooting tips and tricks on Linux, database, hardware, security and web. 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.
Automatic Tun ing of Undo Retention Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured. Note: Automatic tuning of undo retention is not supported for LOBs. This is because undo information for LOBs is stored in the segment itself and not in the undo tablespace.
However, if space becomes low, unexpired LOB undo information may be overwritten. Sizing the Undo Tablespace 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.
Using Auto-Extensible Tablespaces Oracle Database supports automatic extension of the undo tablespace to facilitate capacity planning of the undo tablespace in the production environment. Sizing Fixed-Size Undo Tablespaces If you have decided on a fixed-size undo tablespace, the Undo Advisor can help you estimate needed capacity.
To use the Undo Advisor, you first estimate these two values: The length of your expected longest running query After the database has been up for a while, you can view the Longest Running Query field on the Undo Management page of Enterprise Manager. The longest interval that you will require for flashback operations For example, if you expect to run Flashback Queries for up to 48 hours in the past, your flashback requirement is 48 hours.
However, since most aspects of undo tablespaces are system managed, you need only be concerned with the following actions: Adding a datafile Renaming a datafile Bringing a datafile online or taking it offline Beginning or ending an open backup on a datafile Enabling and disabling undo retention guarantee These are also the only attributes you are permitted to alter.
Switching Undo Tablespaces You can switch from using one undo tablespace to another.
0コメント