FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU) (Doc ID 461480.1)

FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU) (Doc ID 461480.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.0.1.4 to 18.3.0.0.0 [Release 9.0.1 to 18]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Oracle RDBMS Server

PURPOSE

This article is intended for Database Administrators with  knowledge on Automatic Undo Management (AUM).

本文适用于具有 Automatic Undo Management (AUM) 知识的数据库管理员。

Also covered major questions related to Automatic Undo Management (AUM) which will be helpful during creation (or) monitoring the undo tablespaces.

还介绍了与 Automatic Undo Management (AUM) 有关的主要问题，这些问题在创建(or)监视 undo tablespaces 时将很有帮助

QUESTIONS AND ANSWERS

What is Undo?  什么是Undo？

Oracle maintains information to nullify changes made to the database. Such information consists of records of the actions of transactions, collectively known as undo. Oracle uses the undo to do the following: 

Oracle 维护信息以使对数据库所做的更改无效。这些信息包含事务动作的记录，统称为undo。Oracle使用undo来执行以下操作
-  Rollback an active transaction     回滚活动事务
-  Recover a terminated transaction  恢复终止的事务
-  Provide read consistency   提供读取一致性 
-  Recovery from logical corruptions  从逻辑损坏中恢复

What is AUM / SMU?  什么是AUM / SMU

Automatic Undo Management(AUM) is introduced in Oracle 9i, which replaces the rollback segments.  Oracle 9i 中引入了 Automatic Undo Management(AUM)，它代替了回滚段
This is also called System Managed Undo(SMU) as the undo is managed by oracle.  由于undo由oracle管理，因此也称为 System Managed Undo(SMU)

Automatic undo management is undo-tablespace based. You allocate space in the form of an undo tablespace, instead of allocating many rollback segments in different sizes.

Automatic undo management 基于 undo-tablespace。您以 undo tablespace 的形式分配空间，而不是分配许多不同大小的回滚段

Oracle strongly recommends their customers to use Automatic Undo Management (AUM).

Oracle 强烈建议其客户使用 Automatic Undo Management (AUM)

Which are the major initialization parameters that controls AUM?  哪些是控制AUM的主要初始化参数？

UNDO_MANAGEMENT Initialization Parameter

UNDO_MANAGEMENT specifies which undo space management mode the system should use. When set to AUTO, the instance starts in automatic undo management mode. In manual undo management mode, undo space is allocated externally as rollback segments.

UNDO_MANAGEMENT 指定系统应使用的undo空间管理模式。设置为AUTO时，实例以 automatic undo management  模式启动。在 manual undo management 模式下，undo空间在外部分配为回滚段。

By default, this parameter is set to MANUAL. Set this parameter to AUTO to enable automatic undo management mode.

默认情况下，此参数设置为 MANUAL。将此参数设置为AUTO以启用 automatic undo management  模式

This is a static parameter and cannot be modified dynamically using alter system command.  这是一个静态参数，不能使用alter system命令动态修改
So if you wish to switch between Rollback Segments and AUM, then you need to restart the instance.  因此，如果要在 Rollback Segments and AUM 之间切换，则需要重新启动实例

In RAC, multiple instances must have the same value.  在RAC中，多个实例必须具有相同的值

UNDO_TABLESPACE Initialization Parameter

When an instance starts up in automatic undo management mode, it attempts to select an undo tablespace for storage of undo data.

当实例以 automatic undo management 模式启动时，它将尝试选择一个undo表空间来存储undo数据

UNDO_RETENTION Initialization Parameter

This parameter specifies (in seconds) the low threshold value of undo retention.

此参数（以秒为单位）指定undo保留的下阈值

The UNDO_RETENTION parameter can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with a "snapshot too old" message.

仅当当前undo表空间具有足够的空间时，才可以使用UNDO_RETENTION参数。如果活动事务需要undo空间并且undo表空间没有可用空间，则系统将开始重新使用未过期的undo空间。此操作可能会导致某些查询失败，并显示 "snapshot too old" 消息

The amount of time for which undo is retained for the Oracle Database for the current undo tablespace can be obtained by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic performance view.

可以通过查询 V$UNDOSTAT 动态性能视图的 TUNED_UNDORETENTION 列来获取针对当前undo表空间为Oracle数据库保留的undo时间

How to set the undo_retention value ?  如何设置undo_retention值

In case you are using Automatic tuning of undo retention (_undo_autotune=TRUE), The best way to set your undo retention is to determine the average value of maximum query length in your database as follow :

如果您正在使用 undo retention 的自动调整(_undo_autotune=TRUE)，则设置 undo retention 的最佳方法是确定数据库中最大查询长度的平均值，如下所示

a.  Let the database run without bounce for at least one week or more for your normal DB workload.

a. 对于正常的数据库工作负载，让数据库运行至少一周或更长时间不跳动

b. Run the following query to determine the average maximum query in your database and set undo_retention to that value.

b. 运行以下查询以确定数据库中的平均最大查询，并将 undo_retention 设置为该值

SQL> select avg(maxquerylen) from v$undostat;

In case you are not using Automatic tuning of undo retention (_undo_autotune=FALSE), You will need to set undo_retention to a value larger than the maximum query length in your database.

如果您没有使用 undo retention 的自动调整(_undo_autotune=FALSE)，则需要将undo_retention设置为大于数据库中最大查询长度的值

NOTE: If you have very long running queries or you set undo_retention to large value, this will require/allocate large undo space. So it is highly recommended to tune your long running queries and set undo_retention to a resonable value.

注意：如果查询的运行时间很长，或者将undo_retention设置为较大的值，则将需要/分配较大的undo空间。因此，强烈建议您调整长期运行的查询并将undo_retention设置为合理的值

What is the Fast Ramp-up Routine ?   

The concept of Fast Ramp-Up indicates the following:  快速启动的概念表示以下内容

"In previous versions, when the instance was restarted or when you switched undo tablespaces, the ramp-up time for the system to grow the number of online undo segments could be several minutes.  Generally, this time delay was unacceptable.

在以前的版本中，当实例重新启动或切换undo表空间时，系统增加在线undo段数的启动时间可能是几分钟。通常，这个时间延迟是不可接受的

After an instance startup or a switch of undo tablespaces in Oracle Database 10g, the database decides how many segments to place online, based on existing data stored in the the AWR.

在启动实例或切换Oracle数据库10g中的undo表空间之后，数据库根据AWR中存储的现有数据确定要online多少段。

The algorithm to compute the # of undo segments to online for "Fast Ramp Up" is:  计算 "Fast Ramp Up"  的undo段数以online的算法为

NOTE! Below algorithm could be changed version to version, and it is not necessary to rely on it

注意！下面的算法可以在不同版本之间进行更改，因此不必依赖它

Has the instance been up for more than 7 days?  实例是否运行了7天以上
    Yes- use max(maxconcurrency) from v$undostat    是- 从 v$undostat 使用 max(maxconcurrency)
     No- Is this he first time this function is called?   否- 这是他第一次调用此函数吗
        Yes - Is there select_workload_repository function (SWRF) snaphot data?  是- 是否存在select_workload_repository函数（SWRF）快照数据
                     No- online minimal # of undo segs   否- 在线最小数量的undo段
                     Yes- attempt to get the max(maxconcurrency) from wrh$_undostat for the last 7 days.   是- 尝试从最近7天的wrh$_undostat获取最大值（maxconcurrency）
                          If we cannot find that information, attempt to get the max(rbs cnt) from wrh$_rollstat for the last 7 days.  如果找不到该信息，请尝试从最近7天的wrh$_rollstat中获取max（rbs cnt）。
                  Store the value in internal variable.   将值存储在内部变量中。

          No- use an internal variable.  请勿使用内部变量。

SMON decides on the # of undo segs to offline and drop based on the max transaction concurrency over a 12 hour period in 9i.  This behavior is altered in 10g where the max concurrency is maintained over a 7-day period.  Moreover, in 10g SMON doesn't drop the extra undo segs, but simply offlines them. 

SMON 根据9i中12个小时内的最大事务并发性，决定undo段的数量以脱机并下降。在10g的情况下，此行为已更改，其中在7天的时间内保持了最大并发性。此外，在10g中，SMON不会丢弃多余的undo段，而只是使它们offline

SMON uses the same values with "fast ramp up" to adjust the number of undo segments online.

SMON使用与 "fast ramp up" 相同的值来在线调整undo段的数量

The 10511 event is actually used as a workaround to avoid excessive onlines of undo segments.  

实际上，将10511事件用作解决方法，以避免过多的undo段online

The 10511 event does not skip "Fast Ramp Up", it only disables SMON's workload for undo segments.  Once the 10511 event is set, we leave all undo segments created at that point online.

10511事件不会跳过 "Fast Ramp Up" ，它仅对undo段禁用SMON的工作负载。设置10511事件后，我们会将在线创建的所有undo段保持在线

With 10g, "Fast Ramp Up" avoids problems seen in earlier releases with waits/performance issues associated with SMON and undo segments. 

使用10g，"Fast Ramp Up" 可以避免早期版本中出现的与SMON和undo段相关的等待/性能问题

The fast startup can be bypassed by setting following parameter in the parameter file:

通过在参数文件中设置以下参数可以跳过快速启动

_rollback_segment_count=10

This will place 10 segments online at startup. You can change th value as per your requirement on undo segments.

这将在启动时在线放置10个segments。您可以根据需要对undo段进行更改

How many Undo tablespaces can we have for a database?  一个数据库可以有多少个undo表空间

We can have many undo tablespaces in a database, but only one can be Active per instance.

一个数据库中可以有许多undo表空间，但是每个实例只能有一个是活动的

In Oracle Real Application Clusters (RAC) enviornment, we need to have one Active undo tablespace per instance.  The UNDO_TABLESPACE parameter will be used for assigning a particular undo tablespace to an instance.

在 Oracle Real Application Clusters（RAC）环境中，我们需要每个实例具有一个Active undo表空间。UNDO_TABLESPACE参数将用于为实例分配特定的undo表空间

How to switch to a new undo tablespace?  如何切换到新的undo表空间

You can switch from using one undo tablespace to another. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.

您可以从使用一个undo表空间切换到另一个undo表空间。由于 UNDO_TABLESPACE 初始化参数是动态参数，因此 ALTER SYSTEM SET 语句可用于分配新的undo表空间

The following statement switches to a new undo tablespace:  以下语句切换到新的undo表空间

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02; 

Assuming undotbs_01 is the current undo tablespace, after this command successfully executes, the instance uses undotbs_02 in place of undotbs_01 as its undo tablespace.

假设 undotbs_01 是当前的undo表空间，成功执行此命令后，实例将使用 undotbs_02 代替 undotbs_01 作为其undo表空间

If any of the following conditions exist for the tablespace being switched to, an error is reported and no switching occurs:

如果要切换到的表空间存在以下任一情况，则报告错误并且没有切换发生

The tablespace does not exist   表空间不存在

The tablespace is not an undo tablespace  表空间不是undo表空间

The tablespace is already being used by another instance (in a RAC environment only)  该表空间已被另一个实例使用(仅在RAC环境中)

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.

执行切换操作时，数据库处于联机状态，并且可以在执行此命令时执行用户事务。切换操作成功完成后，将在切换操作开始之后启动的所有事务分配给新的undo表空间中的事务表

The switch operation does not wait for transactions in the old undo tablespace to commit. If there are any pending transactions in the old undo tablespace, the old undo tablespace enters into a PENDING OFFLINE mode (status) mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace.

切换操作不等待旧的undo表空间中的事务提交。如果旧的undo表空间中有任何待处理的事务，则旧的undo表空间将进入PENDING OFFLINE模式（状态），现有事务可以继续执行，但是新用户事务的undo记录不能存储在此undo表空间中

An undo tablespace can exist in this PENDING OFFLINE mode, even after the switch operation completes successfully. A PENDING OFFLINE undo tablespace cannot be used by another instance, nor can it be dropped. Eventually, after all active transactions have committed, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode. From then on, the undo tablespace is available for other instances (in an Oracle Real Application Cluster environment).

即使在切换操作成功完成之后，在此PENDING OFFLINE模式下也可以存在一个undo表空间。PENDING OFFLINE undo表空间不能被另一个实例使用，也不能删除。最终，在所有活动事务都已提交之后，undo表空间将自动从PENDING OFFLINE模式变为OFFLINE模式。从那时起，undo表空间可用于其他实例（在Oracle Real Application Cluster环境中）。

If the parameter value for UNDO TABLESPACE is set to '' (two single quotes), then the current undo tablespace is switched out and the next available undo tablespace is switched in. Use this statement with care because there may be no undo tablespace available.

如果将 UNDO TABLESPACE 的参数值设置为''(两个单引号)，则将切换当前的undo表空间，并切入下一个可用的undo表空间。请谨慎使用此语句，因为可能没有可用的undo表空间

The following example unassigns the current undo tablespace:  以下示例取消分配当前的undo表空间

ALTER SYSTEM SET UNDO_TABLESPACE = ''; 

NOTE:  There is not a way to shrink an existing UNDO tablespace.  You can change UNDO tablespaces instead.  See Note 268870.1 How to Shrink the datafile of Undo Tablespace

注意：无法缩小现有的UNDO表空间。您可以改为更改UNDO表空间。请参见 Note 268870.1 如何缩小撤消表空间的数据文件

What is UNDO Retention?  什么是 UNDO Retention

Undo Retention refers to duration of retaining the undo data after a transaction. 

Undo Retention 是指在事务处理后保留undo数据的持续时间

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.

提交事务后，出于回滚或事务恢复的目的，不再需要undo数据。但是，出于一致的读取目的，长时间运行的查询可能需要此旧的undo信息才能生成数据块的旧图像。此外，某些 Oracle Flashback 功能的成功还取决于较早的undo信息的可用性。由于这些原因，理想的是尽可能长时间地保留旧的undo信息。

Automatic undo management eliminates the complexities of managing rollback segment space and lets you exert control over how long undo is retained before being overwritten.

Automatic undo management 消除了管理回滚段空间的复杂性，使您可以控制undo在被覆盖之前保留的时间。

You can set the UNDO_RETENTION parameter to a low threshold value so that the system retains the undo for at least the time specified in the parameter.

您可以将UNDO_RETENTION参数设置为较低的阈值，以便系统至少在参数中指定的时间内保留undo操作

What is Automatic UNDO Retention (10g New Feature)? Explain.  

There is no parameter for this,  Automatic UNDO Retention is enabled by default in 10g.

对此没有任何参数，默认情况下10g中启用了 Automatic UNDO Retention

In Oracle Database 10g 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 - Which means the undo information for committed transactions can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size.

在Oracle数据库10g中，启用了 automatic undo management 后，总是存在一个当前的undo保留期，这是Oracle数据库在覆盖旧的undo信息之前试图保留旧的undo信息的最短时间-这意味着已提交事务的undo信息可以是被覆盖。数据库收集使用情况统计信息，并根据这些统计信息和undo表空间大小调整undo保留期限

Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity. You can specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION initialization parameter. 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

Oracle Database 根据undo表空间大小和系统活动自动调整undo保留期。您可以通过设置UNDO_RETENTION初始化参数来指定最小undo保留期限（以秒为单位）。如果undo表空间具有可用于新事务的空间，则数据库将尽最大努力遵守指定的最小undo保留期限

The current value for tuned undo retention can be viewed by following query.

可以通过以下查询查看调整的undo保留的当前值

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
    TUNED_UNDORETENTION FROM V$UNDOSTAT;

For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries.

对于AUTOEXTEND undo 表空间，系统至少在该参数指定的时间内保留undo，并自动调整undo保留期以满足查询的undo要求。

This could lead to excessive undo generation, to honor undo retention

这可能导致过多的undo生成，以兑现undo保留

For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

对于固定大小的undo表空间，系统会根据undo表空间的大小和使用历史记录，自动调整最大可能的undo保留期，并忽略UNDO_RETENTION，除非启用了保留保证

Automatic tuning of undo retention is not supported for LOBs. Because we don't store any undo information in undo tablespace for transactions on LOBs.

LOB不支持自动调整undo保留。因为我们不在LOB上的事务中将任何undo信息存储在undo表空间中

 Why TUNED_UNDORETENTION is calculated so high making undo space grow fast ?  为什么计算 TUNED_UNDORETENTION 如此之高，使得undo空间快速增长

When non-autoextensible undo space is used, tuned_undoretention is calculated based on a percentage of the undo tablespace size. In some cases especially with large undo tablespace, This will make it to be calculated so large.

当使用不可自动扩展的undo空间时，tuned_undoretention 是根据undo表空间大小的百分比计算的。在某些情况下，尤其是对于较大的undo表空间，这将使其计算得很大

To fix this behaviour, Set the following instance parameter:  要解决此问题，请设置以下实例参数

_smu_debug_mode=33554432

 With this setting, TUNED_UNDORETENTION is not calculated based on a percentage of the fixed size undo tablespace. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.

使用此设置，不会基于固定大小的undo表空间的百分比来计算TUNED_UNDORETENTION。而是将其设置为最大值 (MAXQUERYLEN secs + 300) and UNDO_RETENTION

 What is Guaranteed UNDO Retention? Explain.  什么是保证的UNDO保留率？说明

 Oracle Database 10g lets you guarantee undo retention. In Oracle 10g Release 2, you can enable and disable undo retention. Oracle Database 10g使您可以保证undo保留。在Oracle 10g第2版中，您可以启用和禁用undo保留
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.  启用此选项后，数据库将永远不会覆盖未过期的undo数据。即年龄小于undo保留期的undo数据。

This option is disabled by default, which means that the database can overwrite the unexpired undo data to avoid failure of DML operations if there is not enough free space left in the undo tablespace.

默认情况下，此选项是禁用的，这意味着如果undo表空间中没有足够的可用空间，则数据库可以覆盖未过期的undo数据，以避免DML操作失败

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 enabling this feature.

通过启用保证选项，可以指示数据库不要覆盖未过期的undo数据，即使这意味着冒着当前活动DML操作失败的风险。因此，启用此功能时请小心

To enable do the following against the undo tablespace.  要启用，请对undo表空间执行以下操作

ALTER TABLESPACE UNDOTBS RETENTION GUARANTEE; 

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.

保证选项的典型用法是当您想通过保证所需的undo数据的可用性来确保闪回查询的确定性和可预测行为时

Explain V$UNDOSTAT, and usage?  解释V$UNDOSTAT和用法

This view is a replacement / enhancement for V$ROLLSTAT.  该视图是V$ROLLSTAT的替代/增强功能

This view contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also uses this information to help tune undo usage in the system. This view is meaningful only in automatic undo management mode.

该视图包含用于监视和调整undo空间的统计信息。使用此视图可以帮助估计当前工作负载所需的undo空间量。数据库还使用此信息来帮助调整系统中的undo使用情况。该视图仅在 automatic undo management 模式下才有意义

The V$UNDOSTAT view is useful for monitoring the effects of transaction execution on undo space in the current instance. 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.

V$UNDOSTAT视图对于监视事务执行对当前实例中的undo空间的影响很有用。统计信息可用于undo空间消耗，事务并发，undo保留的调整以及实例中长时间运行的查询的长度和SQL ID

Each row in the view contains statistics collected in the instance for a ten-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval.

视图中的每一行都包含实例中每隔十分钟收集一次的统计信息。这些行按BEGIN_TIME列值的降序排列。每行都属于以（BEGIN_TIME，END_TIME）标记的时间间隔。每列表示在该时间间隔内针对特定统计信息收集的数据

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME, 
  TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME, 
  UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON", 
  MAXQUERYLEN, TUNED_UNDORETENTION 
  FROM v$UNDOSTAT; 

 The following table explains other useful columns of V$UNDOSTAT view  下表解释了V$UNDOSTAT视图的其他有用列

UNXPSTEALCNT	The number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests 从其他undo段窃取未到期的块以满足空间请求的尝试次数
UNXPBLKRELCNT	The number of unexpired blocks removed from undo segments to be used by other transactions 从undo段中删除以供其他事务使用的未到期块数
UNXPBLKREUCNT	The number of unexpired undo blocks reused by transactions 事务重用的未过期undo块数
EXPSTEALCNT	The number of attempts when expired extents were stolen from other undo segments to satisfy a space requests 为了满足空间请求而从其他undo段中窃取了过期的扩展区时的尝试次数
EXPBLKRELCNT	The number of expired extents stolen from other undo segments to satisfy a space request 为了满足空间要求而从其他undo段窃取的过期扩展区的数量
EXPBLKREUCNT	The number of expired undo blocks reused within the same undo segments 在相同undo段内重用的已过期undo块数
SSOLDERRCNT	The number of ORA-1555 errors that occurred during the interval 间隔期间发生的ORA-1555错误的数量
NOSPACEERRCNT	The number of Out-of-Space errors 空间不足错误数

When the columns UNXPSTEALCNT through EXPBLKREUCNT hold non-zero values, it is an indication of space pressure.

当列 UNXPSTEALCNT 到 EXPBLKREUCNT 保持非零值时，表示空间压力。

If the column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.

如果列 SSOLDERRCNT 不为零，则未正确设置 UNDO_RETENTION。

If the column NOSPACEERRCNT is non-zero, then there is a serious space problem.

如果 NOSPACEERRCNT 列不为零，则存在严重的空间问题。

In 10g DBA_HIST_UNDOSTAT view contains statistical snapshots of V$UNDOSTAT information.

在10g中，DBA_HIST_UNDOSTAT 视图包含 V$UNDOSTAT 信息的统计快照。

Note: If the parameter _undo_autotune=FALSE, there will be no data generated in table X$KTUSMST2 which is the source table of view dba_hist_undostats

注意：如果参数 _undo_autotune = FALSE，则表 X$KTUSMST2 中将不生成任何数据，该表是视图 dba_hist_undostats 的源表

Explain the DBA_UNDO_EXTENTS View, and usage?  解释一下DBA_UNDO_EXTENTS视图及其用法

DBA_UNDO_EXTENTS describes the extents comprising the segments in all undo tablespaces in the database.  This view shows the status and size of each extent in the undo tablespace.

DBA_UNDO_EXTENTS 描述了组成数据库中所有undo表空间中的段的范围。此视图显示undo表空间中每个扩展区的状态和大小

What are the various statuses for Undo Extents? Explain.  Undo Extents 的各种状态是什么？说明

Transaction Status of the undo in the extent can be any of the following:

范围内的undo事务状态可以是以下任意一项

SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)  
   FROM DBA_UNDO_EXTENTS GROUP BY STATUS;  

ACTIVE - Undo Extent is Active, Used by a transaction.  undo活动范围是活动的，由事务使用
EXPIRED - Undo Extent is expired (Exceeded the Undo Retention).  undo范围已过期（超过了undo保留）
UNEXPIRED - Undo Extent will be required to honor UNDO_RETENTION.  要求undo权限以兑现UNDO_RETENTION

Explain V$TRANSACTION, and usage?  解释V$TRANSACTION和用法

 V$TRANSACTION lists the active transactions in the system.  V$TRANSACTION列出系统中的活动事务

(a)  The following columns together points to a transaction.  (ie) The combination of the following should give unique transaction id for that database.

(a) 以下各列共同指向一项事务。（即）以下各项的组合应为该数据库提供唯一的事务ID

XIDUSN - Undo segment number  undo段号
XIDSLOT - NUMBER Slot number  编号插槽号
XIDSQN - NUMBER Sequence number  NUMBER序列号

(b)  The following columns explains the number of undo blocks / undo records used per transaction.

(b) 下面的列说明了每个事务使用的undo块/undo记录的数量

USED_UBLK - Number of undo blocks used  使用的undo块
USED_UREC - Number of undo records used  使用的undo记录数

In the case of transaction rollback, the above columns will give estimation about the number of undo blocks that needs to be rolled back.

在事务回滚的情况下，以上各列将提供有关需要回滚的undo块数的估计。

The number of undo records and undo blocks (USED_UREC and USED_UBLK) decrease while the transaction is rolling back. When they reach 0, the transaction disappears from v$transaction.

事务回滚时，undo记录和undo块（USED_UREC和USED_UBLK）的数量减少。当它们达到0时，事务将从v$transaction中消失。

The following query can be used to monitor the transaction rollback.  以下查询可用于监视事务回滚。

SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK  
 FROM V$SESSION A, V$TRANSACTION B  
 WHERE A.SADDR=B.SES_ADDR; 

(c) The STATUS following column explains the status of a transaction.

(c)  以下状态栏说明了事务的状态

ACTIVE - Explains the transaction is active.  说明事务处于活动状态

Before performing a normal/transactional shutdown, we can check this view to understand if we have any ACTIVE transactions.  

在执行正常/事务关闭之前，我们可以检查此视图以了解我们是否有任何ACTIVE事务

SELECT XIDUSN, XIDSLT, XIDSEQ , SES_ADDR, STATUS FROM V$TRANSACTION;

Explain DBA_ROLLBACK_SEGS, and usage?  请解释DBA_ROLLBACK_SEGS，以及用法

This view explains the various status of Undo Segments.  该视图说明了undo段的各种状态
In RAC, we can also see the Instance number, and its associated tablespaces.  在RAC中，我们还可以看到实例号及其关联的表空间

SELECT INSTANCE_NUM,TABLESPACE_NAME,SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS;

In AUM DBA's don't have privileges to offline/online undo segments. And this is controlled by SMON process.   So this will be useful only in few scenarios, where we have internal errors with undo segments.

在AUM中，DBA没有脱机/在线undo段的特权。而这是由SMON过程控制的。因此，这仅在少数情况下有用，在这些情况下我们存在undo段的内部错误

Do we have scripts to monitor the undo growth/usage of the database?  我们是否有脚本来监视数据库的undo增长/使用情况

To understand the free space with undo tablespace.  用undo表空间了解可用空间

SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='&UNDOTBS'; 

To understand state of the extents, space-used in the current undo tablespace.

要了解扩展区的状态，请在当前undo表空间中使用空间

SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) 
   FROM DBA_UNDO_EXTENTS GROUP BY STATUS; 

To understand the no of active transactions and its undo usage.  了解活动事务的编号及其undo用法

SELECT XIDUSN, XIDSLOT, XIDSQN, USED_UBLK FROM V$TRANSACTION WHERE STATUS='ACTIVE' ;

What are the possible causes for excessive undo growth?  undo过度增长的可能原因是什么

 There could be various causes for excessive undo growth.  To start the diagnosis we need to understand the following.

undo过度增长可能有多种原因。要开始诊断，我们需要了解以下内容

Transactions with huge undo  具有大量undo的事务
It is obvious to see high undo usage when there are huge transactions.  显而易见，当发生大量事务时，undo使用率很高。
If that is going to be the case this growth should be expected behavior.   如果真是这样，那么这种增长应该是预期的行为。 

UNDO RETENTION  

Higher undo retention will cause higher undo growth.  Because we wont mark the undo extents as EXPIRED till the duration of undo retention.

更高的undo保留率将导致更高的还原增长。因为在undo保留之前，我们不会将undo范围标记为EXPIRED。

Disabling autoextend on datafiles of  active undo tablespace will reuse the UNEXPIRED extents when it has space crunch. It is a trade-off between undo retention and undo space.

在活动undo表空间的数据文件上禁用自动扩展将在空间不足时重用UNEXPIRED扩展区。这是undo保留和undo空间之间的权衡。

If you wish to satisfy Undo Retention, switch on autoextend in undo tablespace datafiles.

如果希望满足 Undo Retention 的要求，请在undo 表空间数据文件中启用自动扩展。

SELECT FILE_ID, AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE
   TABLESPACE_NAME='&UNDOTBS';

To make those datafile auto extensible, run the following command.  要使这些数据文件可自动扩展，请运行以下命令

ALTER DATABASE DATAFILE '&FILE_ID' AUTOEXTEND ON;

If you wish to switch off auto extend and to reuse the UNEXPIRED space, do the following 

如果要关闭自动扩展并重新使用UNEXPIRED空间，请执行以下操作

ALTER DATABASE DATAFILE '&FILE_ID' AUTOEXTEND OFF;

State of undo extents

The status of the undo extents needs to be closely monitored.

undo 扩展区的状态需要密切监视。

There are few bugs with different releases where EXPIRED extents are not being reused.

在不同的发行版中，几乎没有重复使用EXPIRED范围的错误

(a) If good number of extents in UNEXPIRED status, it could be due to high undo_retention.

(a) 如果处于UNEXPIRED状态的扩展区数量很多，可能是由于较高的undo保留率

SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)  
   FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

(b) There are few bugs associated with undo usage,  与撤消用法相关的错误很少

The unpublished bug 5442919 affects 10.2.0.3, 10.1.0.5 , 9.2.0.8 and its lesser patch levels, and the issue is fixed in 10.2.0.4.

未发布的错误5442919影响10.2.0.3，10.1.0.5，9.2.0.8及其较小的补丁程序级别，并且此问题已在10.2.0.4中得到解决

Bug 5442919 EXPIRED EXTENTS NOT BEING REUSED
The above bug is unpublished, and the details can be reviewed through Note:5442919.8

And we also have Patch:5442919 for most of the latest versions. Kindly check metalink for patch availability.

How to resize the undo datafile?  如何调整undo数据文件的大小

It is possible to increase an undo datafile. For example, to increase the undo datafile size from 2000 MB to 3000MB we can do the following

可以增加undo数据文件。例如，要将undo数据文件的大小从2000 MB增加到3000MB，我们可以执行以下操作

ALTER DATABASE DATAFILE 39 RESIZE 3000M;

But it may not be possible to resize to lesser value, when a undo datafile got auto extended to higher value. Even after the transactions are completed those undo extents will remain in EXPIRED status.

但是，当undo数据文件自动扩展到更高的值时，可能无法将其调整为更低的值。即使在事务完成后，这些undo范围仍将保持为EXPIRED状态

As the blocks are being used by undo extents, oracle will not allow you to resize, It will result in errors similar to following. In the following case the datafile size was 3500MB and a resize to 3000MB results in following errors. 

由于undo扩展正在使用这些块，因此oracle将不允许您调整大小，这将导致类似于以下内容的错误。在以下情况下，数据文件大小为3500MB，将大小调整为3000MB会导致以下错误

SQL> alter database datafile 39 resize 3000m;
alter database datafile 39 resize 3000m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

The best way to SHRINK Undo datafile is to switch to a new Undo tablespace. Refer: How to Shrink the datafile of Undo Tablespace (Doc ID 268870.1)

缩小undo数据文件的最佳方法是切换到新的undo表空间。请参阅 How to Shrink the datafile of Undo Tablespace (Doc ID 268870.1)

Drop Undo Tablespace  删除撤消表空间

To drop the existing undo tablespace, we have to perform following steps:

要删除现有的undo表空间，我们必须执行以下步骤：

• Check whether the Undo tablespace is in use. If yes,  检查undo表空间是否正在使用。如果 是
  •  Create new undo tablespace  创建新的undo表空间
  •  Make it default tablespace  使其成为默认表空间
• Check the all segment of old undo tablespace to be offline.  检查旧的undo表空间的所有段是否处于脱机状态。
• Drop the old tablespace.  删除旧表空间

Check whether the Undo tablespace is in use.  检查undo表空间是否正在使用

Show parameter UNDO 

If the UNDO_TABLESPACE is set to the one to be dropped, create a new Undo tablespace:

如果将UNDO_TABLESPACE设置为要删除的空间，请创建一个新的Undo表空间

create undo tablespace UNDOTBS2 datafile '/oradata/undotbs2.dbf' size 400M;

and set it as the default undo tablespace.  并将其设置为默认的undo表空间

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2 scope=both;

If the undo tablespace to be dropped is not the default one, you can skip the above steps.

如果要删除的undo表空间不是默认表空间，则可以跳过上述步骤

Check the status of the undo segments  检查undo段的状态

Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline.

检查undo段的状态，并确定旧undo表空间中的所有段是否都处于脱机状态。新表空间中的段也可能脱机显示

SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name=<undo tablespace to be dropped>;

OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ --------------------------- ------------------------------ -----------
PUBLIC _SYSSMU3$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU2$ UNDOTBS1 OFFLINE

....etc.

If the old segments are online, then you will have to wait for the undo retention (or tuned retention) duration.

如果旧的段在线，则您必须等待undo保留（或调整的保留）持续时间

Provided all the segments in the old undo tablespace are offline, you can now drop the old undo tablespace:

如果旧的undo表空间中的所有段都处于脱机状态，则现在可以删除旧的undo表空间

SQL>drop tablespace UNDOTBS1 including contents and datafiles;

Can the Undo Tablespace be set to Autoextend?  可以将 Undo Tablespace  设置为“自动扩展”吗

It should be noted that by default Oracle will not auto extend the undo tablespace unless in GUARANTEE  mode, as extending the undo tablespace is an expensive operation and would lock all undo segments in the database.  This would thereby effectively hang the database, and so the default is to re-use all unexpired undo extents first which can therefore result in ORA-1555 errors when customers expect the tablespace to have auto extended.

应当注意，默认情况下，除非在GUARANTEE模式下，否则Oracle不会自动扩展undo表空间，因为扩展undo表空间是一项昂贵的操作，并且会锁定数据库中的所有undo段。因此，这将有效地挂起数据库，因此默认设置是首先重用所有未过期的undo范围，因此，当客户期望表空间具有自动扩展功能时，可能导致ORA-1555错误。

What is In Memory Undo?  什么是Memory Undo

In Oracle 10g and higher, some of the top level DML's were performed in memory without any disk undo data. Which is termed as In Memory Undo (IMU). This can be controlled by an Underscore Parameter. For more information, Please contact Oracle Support

在Oracle 10g及更高版本中，一些顶级DML在内存中执行，而没有任何磁盘undo数据。这被称为“内存中undo”（IMU）。这可以由下划线参数控制。有关更多信息，请联系Oracle Support

REFERENCES

NOTE:268870.1 - How to Shrink the datafile of Undo Tablespace

NOTE:5387030.8 - Bug 5387030 - Automatic tuning of undo_retention causes unusual extra space allocation
NOTE:5442919.8 - Bug 5442919 - Expired extents not being reused (ORA-30036)
NOTE:240746.1 - 10g NEW FEATURE on AUTOMATIC UNDO RETENTION
NOTE:4070480.8 - Bug 4070480 - Unexpired extents used when there is free space available in the UNDO tablespace

NOTE:135090.1 - Managing Rollback/Undo Segments in AUM (Automatic Undo Management)