Oracle - 回滚表空间 Undo 的整理

最新推荐文章于 2024-06-25 09:02:30 发布

袭冷

最新推荐文章于 2024-06-25 09:02:30 发布

阅读量2.9k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： DB 文章标签： Oracle Undo

本文链接：https://blog.youkuaiyun.com/guoxilen/article/details/78611426

DB 专栏收录该内容

52 篇文章

订阅专栏

本文详细介绍了Oracle数据库中回滚表空间（Undo）的管理，包括创建Undo表空间的两种方法，以及其在数据库事务处理中的关键作用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、Undo的概念
Oracle 数据库创建并管理用于回滚或撤销数据库更改的信息。这些信息包括事务中所有操作的记录，主要是在它们被提交之前。这些记录统称为 Undo。

Undo 表空间仅用于存放 Undo 数据，不能创建其他的数据库对象，Undo 表空间只保存系统的 Undo 数据。当执行 DML(INSERT、UPDATE、DELETE) 操作时，Oracle 会将这些操作的旧数据(事务所修改的数据的旧值)写入到 Undo 表空间中。在 Oracle9i 之前，管理 Undo 数据时使用回滚段(Rollback Segment)完成的；从 Oracle9i 开始，管理 Undo 数据不仅可以使用回滚段，还可以使用 Undo 表空间；Oracle11g 开始，因为规划和管理回滚段的复杂性而不推荐用回滚段，并且默认使用 Undo 表空间来管理 Undo 数据。

二、Undo的作用
事务回退：当执行 DML 操作修改数据时，Undo 数据被存放到 Undo 段，而新数据则被存放到数据段中，如果事务操作存在问题，旧需要回退事务，以取消事务变化。

读一致性：用户检索数据库数据时，Oracle 总是使用用户只能看到被提交过的数据(读取提交)或特定时间点的数据(SELECT语句时间点)，这样可以确保数据的一致性。

事务恢复：事务恢复是例程恢复的一部分，它是由Oracle服务器自动完成的。如果在数据库运行过程中出现例程失败(如断电、内存故障、后台进程故障等)，那么当重启Oracle服务器时，后台进程 SMON 会自动执行例程恢复，执行例程恢复时，Oracl会重新做所有未应用的记录，回退未提交事务。

倒叙查询：倒叙查询用于取得特定时间点的数据库数据，它是9i新增加的特性，假定当前时间为上午11:00，某用户在上午10:00执行了修改并提交了事务，为了取得10:00之前的数据，用户可以使用倒叙查询特征。

三、AUM的机制
从 Oracle11g 开始，新建的数据库默认使用自动的机制来管理 Undo 的信息和空间，这个机制就是 AUM(Automatic Undo Management)。

当使用 DBCA 创建数据库的时候，一个自动扩展的名为 UNDOTBS1 的 Undo 表空间将会自动创建。实例启动后，数据库会自动选用第一个可用的 Undo 表空间，如果没有可用的 Undo 表空间，实例将会把 Undo 记录保存在 system 表空间。如果数据库包含多个 Undo 表空间，那么可以选择在启动时通过 UNDO_TABLESPACE 参数来决定使用哪个表空间。如果在初始化参数里指定的 Undo 表空间不存在，那么实例就会启动失败，在 RAC 环境下，每个实例分别对应一个 Undo 表空间。

数据库也可以运行在手动管理 Undo 模式，在这种模式下，Undo Space 的管理是通过 Rollback Segments，而不是使用 Undo TableSpace。但是手工管理 Rollback Segment 是很复杂的，Oracle 强烈推荐使用自动管理 Undo 模式。在Oracle11g 和之后的版本里，在初始化参数中如果没有指定 UNDO_MANAGEMENT 参数，那么默认使用 AUTO，之前的版本则默认使用手动。

在自动管理的 Undo 表空间下，数据库创建时会自动初始化 10 个回滚段，从 V$ROLLNAME 中可以查询到这些自动创建的回滚段信息。此外，Oracle 会根据系统的负载情况自动创建以及释放回滚段。回滚段的大小和数量对于系统至关重要，所有 Online 的回滚段(除了 system 的回滚段)都会被循环使用。而每个回滚段都包含有一些 Extent(扩展)，当某个 Extent 写满后会自动切换到另一个 Extent 继续使用。

回滚段保存的是修改的数据的前镜像数据，对于 DML(INSERT、UPDATE、DELETE) 语句来说，回滚段保存的就是 DML 反向操作的数据，如果是 INSERT 插入一行数据，回滚段保存的就是删除该行的记录，为了节约空间精简数据，回滚段中只保存 INSERT 数据的 Rowid，Undo 只需要删除该 Rowid 的记录即可；如果是 UPDATE 数据，回滚段只保存修改字段的旧值，Undo 只需要把旧值覆盖即可；如果是 DELETE 数据，回滚段则需要保存整行的数据，Undo 需要把整行的数据插入，即 DELETE 操作产生的 Undo(回滚段) 数据最多，大批量删除可以考虑 Truncate 或者分批删除。

AUM的主要初始化参数
UNDO_MANAGEMENT：如果是 AUTO 或者是 NULL，代表启用了自动 Undo 管理；如果是 MANUAL，代表采用手动管理 Undo；默认值为 AUTO
UNDO_TABLESPACE：可选的值，只在自动 Undo 管理模式下有效，指定 Undo 表空间的名字

Undo Retention Period(Undo数据过期时间)
当事务提交后，Undo 数据将不需要回滚或者事务恢复。然而为了数据库一致性读，比如一些时间长的查询可能还是需要这些旧的 Undo 数据来获取 Block 的前镜像。此外，Flashback 特性也将依靠这些可用的旧的 Undo 数据。因此，尽可能长的保存这些旧的 Undo 数据将是非常有用的。

  当启用 AUM 后，就会涉及到 Undo Retention period(撤销数据过期时间)，它是保存 Undo 信息的最小时间。Old Undo 信息(已经Committed)如果超过了这个Retention Period，那么其就会被标记为Expiered，对应的空间也可以被其他的事务重写。Oracle 会根据 Undo 表空间的大小和系统的活动情况自动调整 Undo Retention Period 的时间。我们也可以手动在初始化参数里修改 UNDO_RETENTION 参数，该参数单位是秒。

  注意一点，自动调整 Undo Retention 不适用于 LOBs。因为 LOBs 的 Undo 信息存储在其自己的 Segments 里，而不是在 Undo 表空间里。

  对于自动扩展的 Undo 表空间，Oracle 会根据 Undo_retention 的值作为保留 Undo 数据的最少时间。如果表空间不足时，不会去重写没有过期的 Undo 数据，而是会自动扩展 Undo 表空间。如果 Undo 表空间不足且无法自动扩展时，Undo_retention 设置也将被忽略，因为为了满足系统事务需求，一些状态为 unexpired 的 Undo 数据也有可能被置换重用。

  对于固定大小的 Undo 表空间，Undo_retention 设置的值将被忽略，Oracle 会根据 Undo 表空间大小及 v$Undostat 统计信息自动计算和调整 Undo Retention Period 为最佳的值，以最大可能的保留 Undo 数据。管理最理想的 Undo 和计算最佳的 Retention 值，不是根据 100% 的 Undo 表空间来计算，而是根据 Undo 表空间的 85% 来计算或者设置的空间警告值来计算，默认情况下空间警告值是 85%。

  当一个正在执行的事务需要更多 Undo 空间，而 Undo 表空间不足且无法扩展时，Oracle 优先会重用当前回滚段下状态为 expired 的 Undo extents，如果没有就去其他回滚段下状态为 expired Undo extents，如果没有再回到当前回滚段中查找状态为 unexpired 的 Undo extents，如果还没有再去查找其他回滚段中状态为 unexpired 的 Undo extents，如果还没有就报空间不足的错误消息。

  因此，如果 Undo_retention 为 900 秒（默认值），并不一定意味着 Undo 数据在 Undo 表空间中保存 900 秒，因为如果空间不足就会去覆盖不管是否已经 expired 的已提交事务的Undo 数据。此外，Undo_retention 时间到了之后，并不一定意味这些 Undo 数据在 Undo 表空间中消失，它只是并标示为 expired，只要没有被其他事务的 Undo 数据覆盖，它会依然存在。

  Undo 表空间太小，将会出现的两类错误: DML 操作失败，因为没有足够大的空间来容纳新事务的 Undo 数据；Long-running 查询语句查询失败并且返回 "snnapshot too old" 的错误提示，因为没有足够的 Undo 数据来保证一致性读

设定 UNDO_RETENTION 的方法

-- 通过在初始化参数文件中设定
UNDO_RETENTION = 1800

-- 通过 SQL 语句设定
SQL> ALTER SYSTEM SET UNDO_RETENTION = 2400;

在 V$UNDOSTAT 视图中的 TUNED_UNDORETENTION 字段可以查看 Retention Period 变化的历史记录。V$UNDOSTAT 这个视图包含最近四天每隔10分钟自动采集的数据。TUNED_UNDORETENTION 的单位为秒。

SELECT 
    TO_CHAR(BEGIN_TIME, 'DD-MON-RR HH24:MI') BEGIN_TIME, TO_CHAR(END_TIME, 'DD-MON-RRHH24:MI') END_TIME, TUNED_UNDORETENTION 
FROM 
    V$UNDOSTAT 
ORDER BY
    END_TIME;

Retention Guarantee(保证过期时间有效)
为了保证 Long-running 的查询语句以及 Flashback 操作能够成功执行，可以启用 Retention Guarantee。如果启用了 Retention Guarantee，指定的 UNDO_RETENTION(最小的 Undo 数据过期的时间) 将会得到保证，如果 Undo 表空间大小不足，数据库宁可返回错误也不会去覆盖 Undo 表空间中没有过期的 Undo 数据。如果没有启用 Retention Guarantee，当 Undo 表空间大小不足时数据库就会去覆盖即使没有过期的 Undo 数据，因而间接就缩短了 UNDO_RETENTION 数据过期时间的大小。

数据库默认选项为不启用 Retention Guarantee。注意，启用 Retention Guarantee 可能导致多个 DML 操作失败。可以在 CREATE DATABASE 或者 CREATE UNDO TABLESPACE 语句中增加 Retention Grarantee 语句指定启用 Retention Guarantee，也可以使用 ALTER TABLESPACE 中指定启用还是禁用

设定Retention Guarantee的方法

-- 启用 Retention Guarantee
SQL> ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

-- 禁用 Retention Guarantee
SQL> ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;

在 DBA_TABLESPACES 中 RETENTION 字段可以查询到 Undo 表空间 Retention Guarantee 的设置，RETENTION 取值为 GUARANTEE / NOGUARANTEE / NOT APPLY，其中如果不是 Undo 表空间的表空间都是 NOT APPLY。

SQL> SELECT TABLESPACE_NAME，RETENTION FROM DBA_TABLESPACES;

四、Undo相关操作
创建 Undo 表空间
如果在创建数据库期间，将Undo_management参数设置为auto，当实例在将在启动时自动创建一个自动扩展的名称为 UNDOTBS1 的 Undo 表空间；可以创建多个 Undo 表空间，但是在同一时间内只有一个表空间是 Active 状态的

创建 Undo 表空间有两种方式:

-- 使用 CREATE DATABASE 语句的子句
SQL> CREATE DATABASE MyOrclDb
    ……
UNDO TABLESPACE UNDOTBS2 DATAFILE '/usr/oracle/oradata/MyOrclDb/UNDOTBS201.DBF' SIZE 10M REUSE AUTOEXTEND ON;

-- 使用 CREATE UNDO TABLESPACE 语句
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/usr/oracle/oradata/MyOrclDb/UNDOTBS201.DBF' SIZE 10M REUSE AUTOEXTEND ON;

修改 Undo 表空间

-- 增加数据文件
SQL> ALTER TABLESPACE UNDOTBS2 ADD DATAFILE '/usr/oracle/oradata/MyOrclDb/UNDOTBS202.DBF' SIZE 10M;

-- 修改数据文件大小
SQL> ALTER DATABASE DATAFILE '/usr/oracle/oradata/MyOrclDb/UNDOTBS201.DBF' RESIZE 30M;

-- 重命名数据文件
SQL> ALTER TABLESPACE UNDOTBS2 RENAME DATAFILE '/usr/oracle/oradata/MyOrclDb/UNDOTBS201.DBF' TO '/usr/oracle/oradata/MyOrclDb/UNDOTBS204.DBF';

-- 使数据文件ONLINE或者OFFLINE
SQL> ALTER TABLESPACE UNDOTBS2 ONLINE|OFFLINE;

-- 开始或者结束一个联机备份
SQL> ALTER TABLESPACE UNDOTBS2 BEGIN|END BACKUP;

删除Undo 表空间
Undo 表空间只能在当前没有被实例使用的情况下才能被删除。如果Undo表空间正在使用(尚有回滚段处于 Online 状态)，那么drop表空间命令将失败。强制删除数据文件，有引发可能 ora-01555 的错误

SQL> DROP TABLESPACE UNDOTBS2;

切换Undo 表空间
因为 Undo_tablespace 初始化参数是一个动态参数，可以随时指定一个新的 Undo 表空间以实现切换

SQL>  ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

切换 Undo 表空间并不影响用户事务的执行，当切换操作成功后，Switch 操作之后所有事务产生的 Undo 数据将会保存在新的 Undo 表空间
切换表空间操作并不会等待旧的 Undo 空间上所有的事务都提交。如果在旧的 Undo 表空间上还有事务在执行，那么旧的 Undo 表空间状态变为 Pending Offline。在这个模式下，已经存在的事务继续执行，但是产生的 Undo 数据不能保存在这个 Undo 表空间中。
即使切换操作执行完成，旧的 Undo 表空间(状态为 Pending Offline) 不能被其他实例使用也不能删除。最后，当旧的 Undo 表空间上所有的事务都提交后，它的状态从 Pending Offline 变为 Offline。这时，旧的 Undo 表空间可以被其他实例使用(RAC环境)

五、Undo相关数据字典
V$UNDOSTAT
显示最近一段时间内关于 Undo 表空间使用情况的统计信息，可以用这个 View 来优化系统，每隔 10 分钟自动增加一条数据，总共只保存四天的数据，如果要查询超过四天的数据就要使用 DBA_HIST_UNDOSTAT，这个 view 仅对于自动 Undo 管理模式有效

SELECT 
    TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME, UNDOTSN，UNDOBLKS，TXNCOUNT，MAXCONCURRENCY，MAXQUERYLEN，MAXQUERYID，
    UNXPSTEALCNT，UNXPBLKRELCNT，UNXPBLKREUCNT，EXPSTEALCNT，EXPBLKRELCNT，EXPBLKREUCNT，SSOLDERRCNT，NOSPACEERRCNT，TUNED_UNDORETENTION 
FROM 
    V$UNDOSTAT 
ORDER BY 
    END_TIME;

几个重要的字段说明：

           BEGIN_TIME            DATE          -- 时间间隔开始时间
           UNDOTSN               NUMBER        -- 最近活动的 Undo 表空间的 Id  
           UNDOBLKS              NUMBER        -- 当前时间段内消耗的 Undo Blocks 总数 
           TXNCOUNT              NUMBER        -- 当前时间段内执行的事务总数          
           MAXCONCURRENCY        NUMBER        -- 当前时间段内并行执行的最大事务数
           MAXQUERYLEN           NUMBER        -- 当前时间段内执行单个查询花费的最长时间，单位为秒 
           MAXQUERYID            NUMBER        -- 当前时间段内执行单个查询花费的最长时间的 Sql id  
           UNXPSTEALCNT          NUMBER        -- 从其他事务尝试盗用未过期 Undo 回滚段的次数  
           UNXPBLKRELCNT         NUMBER        -- 从其他事务已经盗用未过期 Undo 回滚段的数量 
           UNXPBLKREUCNT         NUMBER        -- 未过期的 Undo 回滚段重用的数量 
           EXPSTEALCNT           NUMBER        -- 尝试从其他 Undo 段盗用已经过期回滚段的次数 
           EXPBLKRELCNT          NUMBER        -- 从其他 Undo 段盗用过期回滚段的数量 
           EXPBLKREUCNT          NUMBER        -- 已经过期回滚段重用的数量 
           SSOLDERRCNT           NUMBER        -- 返回 ora-01555 错误的次数，如果大于 0，说明 UNDO_RETENTION 需要增加大小 
           NOSPACEERRCNT         NUMBER        -- 空间不足申请新的 Undo 表空间的次数 
           TUNED_UNDORETENTION   NUMBER        -- 已提交数据的Undo 数据过期时间，即Retention Period 变化的历史，单位为秒

DBA_HIST_UNDOSTAT
显示所有的 V$UNDOSTAT 的历史记录。

DBA_UNDO_EXTENTS
显示数据库所有 UNDO 表空间的回滚段情况，其中 STATUS 的取值为 EXPIRED / UNEXPIRED / ACTIVE：
ACTIVE: 活动状态，说明当前这个回滚段被某个事务正在使用，不会被其他事务使用；
UNEXPIRED: 没有过期，在UNDO_RETENTION时间内，当空间不足时有可能被其他事务使用；
EXPIRED: 已经过期，随时可以被其他事务使用。

V$ROLLNAME
只有两个字段(USN，NAME)，就是一个回滚段 ID 和 NAME，通常和 V$ROLLSTAT 一起使用。

V$ROLLSTAT
统计回滚段表的使用情况。XACTS 表示当前回滚段上存在的活动事务的数量，STATUS 的取值为 ONLINE / PENDING OFFLINE / OFFLINE / FULL。

SELECT 
    A.USN, A.NAME, B.RSSIZE, B.WRITES, B.XACTS, B.WAITS, B.EXTENDS, B.STATUS
FROM 
    V$ROLLNAME A, V$ROLLSTAT B
WHERE  
    A.USN = B.USN;

V$DBA_ROLLBACK_SEGS
显示所有回滚段(包括 SYS 和 PUBLIC)的空间分配情况及当前状态。其中 STATUS 的取值为 OFFLINE / ONLINE / NEEDS RECOVERY / PARTLY AVAILABLE / UNDEFINED。

SELECT 
    OWNER，SEGMENT_NAME，TABLESPACE_NAME，INITIAL_EXTENT，NEXT_EXTENTS，STATUS 
FROM 
    DBA_ROLLBACK_SEGS;

六、Undo 表空间管理
查看当前 Undo 表空间配置

SQL> show parameter undo

NAME                            TYPE        VALUE
------------------------------- ----------- -----------
_undo_autotune                  boolean     FALSE
undo_management                 string      AUTO
undo_retention                  integer     28800
undo_tablespace                 string      UNDOTBS2

查看当前 Undo 表空间磁盘大小(占用磁盘的大小)

SQL> SELECT SUM(BYTES)/1024/1024/1024 "UNDO_SIZE(G)" FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='UNDOTBS2';

UNDO_SIZE(G)
------------
  91.9999847

查看当前 Undo 表空间使用情况

-- 统计Undo表空间使用的总大小
SQL> SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 "USED_SIZE(G)" FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='UNDOTBS2' GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME                USED_SIZE(G)
------------------------------ ------------
UNDOTBS2                         1.84082031

-- 统计回滚段各状态下的总大小
SQL> SELECT TABLESPACE_NAME，STATUS，SUM(BYTES)/1024/1024/1024 "USED_SIZE(G)" FROM DBA_UNDO_EXTENTS WHERE TABLESPACE_NAME='UNDOTBS2' GROUP BY TABLESPACE_NAME，STATUS;

TABLESPACE_NAME                STATUS    USED_SIZE(G)
------------------------------ --------- ------------
UNDOTBS2                       EXPIRED     1.08789063
UNDOTBS2                       UNEXPIRED   .752929688

查看当前谁占用了 Undo 表空间

SELECT 
    R.NAME "TABLESPACE", S.SID, S.SERIAL# "SERIAL", S.USERNAME "USERNAME",
    RSSIZE / 1024 / 1024 / 1024 "RSSIZE(G)", HWMSIZE / 1024 / 1024 / 1024 "HWMSIZE(G)",
    S.STATUS, S.SQL_HASH_VALUE, S.SQL_ADDRESS, S.MACHINE, S.MODULE,
    SUBSTR(S.PROGRAM,1,15) "PROGRAM", R.USN, T.START_TIME, SHRINKS, XACTS
FROM 
    SYS.V_$SESSION S, SYS.V_$TRANSACTION T, SYS.V_$ROLLNAME R, V$ROLLSTAT RS
WHERE 
    T.ADDR = S.TADDR AND T.XIDUSN = R.USN AND R.USN = RS.USN
ORDER BY 
    S.SID DESC;