生产环境Oracle undo表空间管理的优秀实践

这些记录用于：

• 回滚事务 - 发出 ROLLBACK 语句时，撤销记录用于撤消未提交事务对数据库所做的更改。
• 恢复数据库 - 在数据库恢复期间，撤销记录用于撤消从重做日志应用到数据文件的任何未提交的更改。
• 提供读取一致性 - 撤消记录通过为正在访问数据同时另一个用户正在更改数据的用户维护数据的前映像来提供读取一致性。
• 使用Flashback Query分析较早时间点的数据。
• 使用闪回功能从逻辑损坏中恢复。

在Oracle 8i之前，Oracle使用回滚段来管理撤销数据。Oracle9i引入了自动撤消管理，它允许 dba 对撤消信息的保留时间施加更多控制，简化撤消空间管理并消除管理回滚段的复杂性。Oracle 强烈建议您使用 undo 表空间来管理 undo 而不是回滚段。

undo段的空间是动态分配、使用、释放和重用的--所有这些都在Oracle数据库的控制下，而不是由DBA控制。

从Oracle 9i开始，回滚段方法称为“手动撤销管理模式”，新的撤销表空间的方法称为“自动撤销管理模式”。

说明：

• 尽管支持回滚段和撤消表空间，但这两种模式不能在同一个数据库实例中使用，尽管出于迁移目的，可以在使用回滚段的数据库中创建撤消表空间，或删除回滚段在使用撤消表空间的数据库中。但是，您必须退回数据库才能切换到另一种管理撤销的方法。
• 两种模式都存在系统回滚段。
• 在自动撤消管理模式下运行时，任何手动撤消管理 SQL 语句和初始化参数都将被忽略，并且不会发出错误消息，例如 ALTER ROLLBACK SEGMENT 语句将被忽略。

二、管理undo表空间

2.1 创建undo表空间

有两种创建撤消表空间的方法：

第一种方法：在发出 CREATE DATABASE 语句时创建撤消表空间。这发生在您创建新数据库并且实例以自动撤销管理模式 (UNDO_MANAGEMENT = AUTO) 启动时。

第二种方法：用于现有数据库。它使用 CREATE UNDO TABLESPACE 语句。

您不能在撤销表空间中创建数据库对象。它保留用于系统管理的撤销数据。Oracle 数据库使您能够创建单文件撤销表空间。

以下语句说明了在 CREATE DATABASE 语句中使用 UNDO TABLESPACE 子句。undo 表空间名为 undotbs_01，并为其分配了一个数据文件

SQL> CREATE DATABASE ... 
UNDO TABLESPACE undotbs_01 DATAFILE '/path/undo01.dbf' RETENTION GUARANTEE; 

 如果在 CREATE DATABASE 期间无法成功创建 undo 表空间，则整个操作将失败。CREATE UNDO TABLESPACE 语句与 CREATE TABLESPACE 语句相同，但指定了 UNDO 关键字。数据库决定了撤销表空间的大部分属性，但您可以指定 DATAFILE 子句。

此示例创建 undotbs_02 撤销表空间：

SQL> CREATE UNDO TABLESPACE undotbs_02 DATAFILE '/path/undo02.dbf' SIZE 2M REUSE AUTOEXTEND ON RETENTION NOGUARANTEE ; 

2.2 调整 Undo 表空间大小

方法一：缩小撤消表空间大小

分配后的撤销空间将可供重用，但不会重新分配给操作系统。缩小 Undo 表空间的最佳方法是切换到新的 Undo 表空间并删除旧的 Undo 表空间。步骤是：

根据您的数据库要求创建一个相同大小(更大或更小)的新撤消表空间。

SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\UNDOTBS02.DBF' size 5000M; 

切换到新的 Undo 表空间：

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH; 

查看undo segment的状态，判断old undo tablespace中的segment是否都处于offline状态。

sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status; 

如果表空间中有状态不是OFFLINE的Undo段要被删除，我们需要等到它们变为OFFLINE。您可能必须等待tuned_undoretention 的持续时间(来自v$undostat)以确保所有撤消段都已变为OFFLINE。

sql> select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name=[undo tablespace to be dropped]; 

例如：

sql> select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name='UNDOTBS1'; 

如果旧Undo表空间中的所有Undo段都处于OFFLINE状态，则删除该表空间。

sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status; 

验证然后丢弃：

sql> drop tablespace [tablespace_name] including contents and datafiles; 

例如：

sql> drop tablespace UNDOTBS1 including contents and datafiles; 

方法二：向撤消表空间添加空间

对于增加/调整撤消表空间的大小，有两个选项：

• 调整现有撤销数据文件的大小
• 将新的撤销数据文件添加到表空间。

调整现有撤销数据文件的大小：

col T_NAME for a23 
col FILE_NAME for a65 
select tablespace_name T_NAME,file_name, bytes/1024/1024 MB from dba_data_files where tablespace_name =(SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace') order by file_name; 
 
alter database datafile '[COMPLETE_PATH_OF_UNDO_DBF_FILE]' resize [SIZE]M; 

 例如：

sql> alter database datafile 'D:\ORACLE_DB\TESTDB\TESTDB\UNDOTBS01.DBF' resize 1500M; 

添加新数据文件的步骤：

sql> alter tablespace [UNDO tbs name] ADD DATAFILE '[COMPLETE_PATH_OF_UNDO_DBF_FILE]' size 20M; 

例如：

sql> alter tablespace UNDOTBS1 ADD DATAFILE 'D:\ORACLE_DB\TESTDB\TESTDB\UNDOTBS02.DBF' size 20M; 

三、Oracle 中撤消表空间/撤消管理的最佳实践

以下建议列表将帮助您管理撤销空间以发挥最大优势。

• 除非您的系统具有闪回或 LOB 保留要求，否则您无需为 UNDO_RETENTION 参数设置值。
• 在撤销表空间中留出 10% 到 20% 的额外空间，以应对工作负载的一些波动。
• 正确设置撤消表空间警报的警告和严重警报阈值。
• 要调整 SQL 查询或检查失控查询，请使用长查询或 V$UNDOSTAT 或 WRH$_UNDOSTAT 视图中提供的 SQLID 列的值从 V$SQL 视图检索 SQL 文本和有关 SQL 的其他详细信息。