Oracle 11g UNDO 管理 详解

Oracle 11g UNDO 管理 详解

http://blog.csdn.net/tianlesoftware/article/details/7620628

 

            从Oracle 11g 开始，使用默认安装时，Oracle 会自动管理UNDO，之前也整理过几篇有关UNDO的文章，如下：

 

Oracle undo 管理

http://blog.csdn.net/tianlesoftware/article/details/

Oracle undo 表空间管理

http://blog.csdn.net/tianlesoftware/article/details/5689558

 

Currentonline Redo 和 Undo 损坏的处理方法

http://blog.csdn.net/tianlesoftware/article/details/6261475

 

整理的都比较散，所以根据Oracle11gR2的文档重新梳理一遍。

 

 

一．    什么是UNDO

Oracle Databasecreates and manages information that is used to roll back, or undo, changes tothe database. Such information consists of records of the actions oftransactions, primarily before they are committed. These records arecollectively referred to as undo.

 

Undo records are used to:

--Undo 记录使用在如下情况：

(1)    Roll back transactions whena ROLLBACK statement is issued

(2)    Recover the database

(3)    Provide read consistency

(4)    Analyze data as of an earlierpoint in time by using Oracle Flashback Query

(5)    Recover from logicalcorruptions using Oracle Flashback features

 

Whena ROLLBACK statement is issued, undo records are used to undo changesthat were made to the database by the uncommitted transaction. During databaserecovery, undo records are used to undo any uncommitted changes applied fromthe redo log to the datafiles. Undo records provide read consistency bymaintaining the before image of the data for users who are accessing the dataat the same time that another user is changing it.

 

 

二． AUM（Automatic UndoManagement）说明

 

Oracle providesa fully automated mechanism, referred to as automatic undo management, formanaging undo information and space. With automatic undo management, thedatabase manages undo segments in an undo tablespace. Beginning with Release11g, automatic undo management is the default mode for a newly installeddatabase. An auto-extending undo tablespace named UNDOTBS1 isautomatically created when you create the database with Database ConfigurationAssistant (DBCA).

--Oracle 使用了一个完全自动的机制：AUM 来管理undo 的信息和空间。使用AUM之后，数据库在undo表空间里管理undo segments。从Oracle 11g开始，新建的数据库默认就是用AUM。在使用DBCA 创建实例时，会自动创建一个自动扩展的UNDO表空间：UNDOTBS1.

 

When theinstance starts, the database automatically selects the first available undotablespace. If no undo tablespace is available, the instance starts without anundo tablespace and stores undo records in the SYSTEM tablespace.This is not recommended, and an alert message is written to the alert log fileto warn that the system is running without an undo tablespace.

        --当启动实例时，db 会自动选择第一个可用的undo 表空间，如果没有undo 表空间可用，那么实例也会启动，但这种情况undo 记录是存在SYSTEM 表空间的rollback segment里，这种情况是不推荐使用，并且在使用SYSTEM 表空间时，alert log里也会出现警告信息，说没有使用undo 表空间。

 

这种在如下Blog 有说明：

Oracle undo 表空间管理

http://blog.csdn.net/tianlesoftware/article/details/5689558

 

If the databasecontains multiple undo tablespaces, you can optionally specify at startup thatyou want to use a specific undo tablespace. This is done by setting the UNDO_TABLESPACE initializationparameter, as shown in this example:

--如果数据库包含多个undo 表空间，那么可以选择在启动时通过UNDO_TABLESPACE参数来决定使用哪个表空间，如下：

 

UNDO_TABLESPACE= undotbs_01

 

If thetablespace specified in the initialization parameter does not exist,the STARTUP command fails. The UNDO_TABLESPACE parametercan be used to assign a specific undo tablespace to an instance in an OracleReal Application Clusters environment.

--如果在初始化参数里指定的undo 表空间不存在，那么实例就会启动失败，在RAC 环境下，每个实例分别对应一个undo 表空间。

 

The database canalso run in manual undo management mode. In this mode, undo space ismanaged through rollback segments, and no undo tablespace is used.

--数据库也可以运行在手动管理undo 模式，在这种模式下，undo space 的管理是通过rollbacksegments，而不是使用undo tablespace。

 

Note: Space management for rollbacksegments is complex. Oracle strongly recommends leaving the database inautomatic undo management mode.

            --手工管理rollback segment 是很复杂的，Oracle 强烈推荐使用自动管理undo 模式。

 

 

The following is a summary of the initialization parameters for undo management:

--与undo 管理相关的参数和说明如下：

Initialization Parameter	Description
UNDO_MANAGEMENT	If AUTO or null, enables automatic undo management. If MANUAL, sets manual undo management mode. The default is AUTO.
UNDO_TABLESPACE	Optional, and valid only in automatic undo management mode. 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.

 

When automaticundo management is enabled, if the initialization parameter file containsparameters relating to manual undo management, they are ignored.

--这里要注意一点，如果我们启动了自动管理undo，那么在初始化参数里包含的与手工管理undo 相关的参数都会被忽略。

 

Earlier releasesof Oracle Database default to manual undo management mode. To change toautomatic undo management, you must first create an undo tablespace and thenchange the UNDO_MANAGEMENT initialization parameter to AUTO.

--在Oracle 早期的版本中，默认使用手动管理undo的模式，如果要将管理模式从手工改成自动，那么我们必须先创建一个undo 表空间，然后在初始化参数里指定参数：UNDO_MANAGEMENT为AUTO.

           

 

Anull UNDO_MANAGEMENT initialization parameter defaults to automaticundo management mode in Release 11g and later, but defaults to manual undomanagement mode in earlier releases. You must therefore use caution whenupgrading a previous release to Release 11g. 

--在oracle 11g 和之后的版本里，在初始化参数中如果没有指定UNDO_MANAGEMENT参数，那么默认使用AUTO，之前的版本则默认使用手动。

 

 

三．Undo Retention Period 说明

After atransaction is committed, undo data is no longer needed for rollback ortransaction recovery purposes. However, for consistent read purposes,long-running queries may require this old undo information for producing olderimages of data blocks. Furthermore, the success of several Oracle Flashbackfeatures can also depend upon the availability of older undo information. Forthese reasons, it is desirable to retain the old undo information for as longas possible.

            --事务提交之后，UNDO 数据就不需要用来rollback 或者事务恢复，但是为了一致性读，比如long-runing的查询可能还是需要这些旧的undo 数据来获取block的前镜像。此外，Flashback的特性也会受undo信息的影响，因为这些原因，旧的undo 信息还是需要尽可能的长。

 

OracleFlashback 技术 总结

http://blog.csdn.net/tianlesoftware/article/details/4677378

 

When automaticundo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Database attempts toretain old undo information before overwriting it. Old (committed) undoinformation that is older than the current undo retention period is said to be expired andits space is available to be overwritten by new transactions. Old undoinformation with an age that is less than the current undo retention period issaid to be unexpired and is retained for consistent read and OracleFlashback operations.

            --当启用AUM后，就会涉及到undo retention period，其是保存最小的undo信息的时间。 Old undo 信息（已经committed）如果超过了这个retentionperiod，那么其就会被标记为expiered，对应的空间也可以被其他的事务重写。

 

Oracle Databaseautomatically tunes the undo retention period based on undo tablespace size andsystem activity. You can optionally specify a minimum undo retention period (inseconds) by setting the UNDO_RETENTION initialization parameter.

--Oracle 会根据undo 表空间的大小和系统的活动情况自动调整undoretention period的时间。我们也可以手动在初始化参数里修改UNDO_RETENTION参数，该参数单位是秒。

 

 Theexact impact this parameter on undo retention is as follows:

--关于这个参数的2点说明：

(1)    The UNDO_RETENTION parameteris ignored for a fixed size undo tablespace. The database always tunes the undoretention period for the best possible retention, based on system activity andundo tablespace size.

--如果UNDO 表空间的大小是固定的，即不可自动扩展，那么在这种情况下，UNDO_RETENTION参数是会自动忽略，不会生效，数据库根据系统活动情况和undo表空间大小来调整undo retention period为最佳值。

 

(2)    For an undo tablespace with the AUTOEXTEND optionenabled, the database attempts to honor the minimum retention period specifiedby UNDO_RETENTION. When space is low, instead of overwriting unexpired undoinformation, the tablespace auto-extends. If the MAXSIZE clause isspecified for an auto-extending undo tablespace, when the maximum size isreached, the database may begin to overwrite unexpired undo information. The UNDOTBS1 tablespacethat is automatically created by DBCA is auto-extending.

--如果undo 表空间是自动扩展的，那么数据会尝试使用UNDO_RETENTION 参数，如果表空间不足时，不会去重写没有过期（仅仅是没有过期的committed的数据）的undo数据，而是会自动扩展undo 表空间。当undo 表空间扩展达到MAXSIZE值时，数据库开始重写没有过期的undo 数据。

 

 

Automatic tuningof undo retention is not supported for LOBs. This is because undo informationfor LOBs is stored in the segment itself and not in the undo tablespace. ForLOBs, the database attempts to honor the minimum undo retention periodspecified by UNDO_RETENTION. However, if space becomes low, unexpired LOBundo information may be overwritten.

            --注意一点，自动调整undo retention 不适用与LOBs。因为LOBs的undo 信息存储在其自己的segments里，而不是在undo 表空间里。

 

Oracle LOB 详解

http://blog.csdn.net/tianlesoftware/article/details/6905406

 

3.1 Retention Guarantee

To guarantee thesuccess of long-running queries or Oracle Flashback operations, you can enableretention guarantee. If retention guarantee isenabled, the specified minimum undo retention is guaranteed; the database neveroverwrites unexpired undo data even if it means that transactions fail due tolack of space in the undo tablespace. If retention guarantee is not enabled,the database can overwrite unexpired undo when space is low, thus lowering theundo retention for the system. This option is disabled by default.

为了保护long-running 查询的成功执行或者Flashback的操作，我们需要启动retention guarantee。在启动retention guarantee之后，在指定的最小undo retention 内，即使事务因为undo 空间的不足而失败，数据库不会重写没有过期的undo 数据。如果没有启动retentionguarantee，那么当undo 空间不够时，数据库就会重新那些已经提交且没有过期的数据。

默认情况下，retention guarantee 是禁用的。

 

WARNING:

Enablingretention guarantee can cause multiple DML operations to fail. Use withcaution.

--注意：如果启动retention guarantee，可能会导致多个DML 操作的失败。

 

You enableretention guarantee by specifying the RETENTION GUARANTEE clause forthe undo tablespace when you create it with either the CREATE DATABASE or CREATEUNDO TABLESPACE statement. Or, you can later specify this clause in an ALTERTABLESPACE statement. You disable retention guarantee with the RETENTIONNOGUARANTEE clause.

--我们可以在创建数据库或者创建undo 表空间时指定retention guarantee 子句，也可以在创建完毕后修改undo 表空间的属性。命令如下：

 

SQL> Alter tablespace undotbs1 retention guarantee;

 

You can use the  viewto determine the retention guarantee setting for the undo tablespace. A columnnamed RETENTION contains a value ofwhere NOT APPLY is used fortablespaces other than the undo tablespace.

--可以使用DBA_TABLESPACES视图来查看undo 表空间的retention值。其有如下类型： GUARANTEE, NOGUARANTEE, NOT APPLY。

 

3.2 Undo Retention Tuning and Alert Thresholds

For a fixed-sizeundo tablespace, the database calculates the best possible retention based ondatabase statistics and on the size of the undo tablespace. For optimal undomanagement, rather than tuning based on 100% of the tablespace size, thedatabase tunes the undo retention period based on 85% of the tablespace size,or on the warning alert threshold percentage for space used, whichever islower. (The warning alert threshold defaults to 85%, but can be changed.)Therefore, if you set the warning alert threshold of the undo tablespace below85%, this may reduce the tuned size of the undo retention period.

--对于固定大小的undo tablespace，数据库根据统计信息和undo 表空间的大小来计算最佳的retention. 管理undo最理想的情况，不是根据100%的undo表空间来计算，而是根据undo 表空间的85%来计算，或者设置的空间警告值来计算，默认情况下空间警告值是85%。

 

3.3 Tracking the Tuned Undo Retention Period

You can determine the current retention period by querying the TUNED_UNDORETENTION columnof the V$UNDOSTAT view. This view contains one row for each 10-minutestatistics collection interval over the last 4 days. (Beyond 4 days, the datais available in the DBA_HIST_UNDOSTAT view.)TUNED_UNDORETENTION isgiven in seconds.

--我们可以通过V$UNDOSTAT视图的TUNED_UNDORETENTION字段来查看retentionperiod 变化的历史记录。

 

SQL> select to_char(begin_time,'DD-MON-RR HH24:MI') begin_time,

 2  to_char(end_time, 'DD-MON-RRHH24:MI') end_time, tuned_undoretention

 3  from v$undostat order byend_time;

 

BEGIN_TIME           END_TIME             TUNED_UNDORETENTION

-------------------- ---------------------------------------

31-5月 -12 15:33     31-5月 -12 15:43                    1173

31-5月 -12 15:43     31-5月 -12 15:53                    1775

31-5月 -12 15:53     31-5月 -12 16:03                    1173

31-5月 -12 16:03     31-5月 -12 16:13                    1775

31-5月 -12 16:13     31-5月 -12 16:23                    1172

31-5月 -12 16:23     31-5月 -12 16:33                    1774

31-5月 -12 16:33     31-5月 -12 16:43                    1171

31-5月 -12 16:43     31-5月 -12 16:53                    1773

31-5月 -12 16:53     31-5月 -12 17:03                    1112

 

9 rows selected.

 

 

四．与undo space 相关的数据字典

 

The followingdynamic performance views are useful for obtaining space information about theundo tablespace:

 

View	Description
V$UNDOSTAT	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.
V$ROLLSTAT	For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace
V$TRANSACTION	Contains undo segment information
DBA_UNDO_EXTENTS	Shows the status and size of each extent in the undo tablespace.
DBA_HIST_UNDOSTAT	Contains statistical snapshots of V$UNDOSTAT information. See  for more information.

 

 

The V$UNDOSTAT viewis useful for monitoring the effects of transaction execution on undo space inthe current instance. Statistics are available for undo space consumption,transaction concurrency, the tuning of undo retention, and the length and SQLID of long-running queries in the instance.

 

Each row in theview contains statistics collected in the instance for a ten-minute interval.The rows are in descending order by the BEGIN_TIME column value. Eachrow belongs to the time interval marked by (BEGIN_TIME, END_TIME). Eachcolumn represents the data collected for the particular statistic in that timeinterval. The first row of the view contains statistics for the (partial)current time period. The view contains a total of 576 rows, spanning a 4 daycycle.

 

The following example shows the results ofa query on the V$UNDOSTAT view.

 

/* Formatted on 2012/5/31 17:07:39(QP5 v5.185.11230.41888) */

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"

  FROM v$UNDOSTAT

 WHERE ROWNUM <= 144;

 

 

五．Undo 相关的操作示例

 

5.1 修改UNDO_RETENTION

 

（1）    在初始化参数中修改：

UNDO_RETENTION = 1800

 

（2）使用SQL 语句修改：

ALTER SYSTEM SET UNDO_RETENTION = 2400;

 

            修改之后立即生效。

 

5.2 设置UNDO 表空间retention guarantee

 

SQL> Alter tablespace undotbs1 retention guarantee;

SQL> Alter tablespace undotbs1 retention noguarantee;

 

 

5.3 UNDO 表空间创建，修改，删除等操作

 

这部分操作参考：

Oracle undo 表空间管理

http://blog.csdn.net/tianlesoftware/article/details/5689558

 

或者官方文档：