印刻数据库

外观

Oracle Undo表空间管理 ​

Undo表空间的作用 ​

  • 事务回滚：当事务需要回滚时，Oracle使用undo数据恢复到事务开始前的状态
  • 一致性读：为查询提供事务一致性视图，确保读取的数据与查询开始时一致
  • 事务恢复：在实例崩溃后，Oracle使用undo数据进行事务恢复
  • 闪回操作：支持闪回查询、闪回表等闪回功能

Undo表空间的基本概念 ​

  • Undo段：存储undo数据的基本单位
  • Undo块：undo段中的最小存储单位
  • Undo记录：记录数据修改前的值
  • Undo保留时间：undo数据保留的最小时间

Undo表空间配置 ​

初始化参数配置 ​

UNDO_MANAGEMENT ​

sql
-- 设置为自动管理模式
ALTER SYSTEM SET undo_management = 'AUTO' SCOPE=SPFILE;

UNDO_TABLESPACE ​

sql
-- 指定默认的undo表空间
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS1' SCOPE=SPFILE;

UNDO_RETENTION ​

sql
-- 设置undo保留时间（秒）
ALTER SYSTEM SET undo_retention = 900 SCOPE=BOTH;

Undo表空间创建 ​

创建Undo表空间 ​

sql
-- 创建undo表空间
CREATE UNDO TABLESPACE undotbs2
  DATAFILE 'D:\\ORADATA\\ORCL\\undotbs201.dbf' SIZE 100M
  AUTOEXTEND ON NEXT 50M MAXSIZE 1024M;

创建多个数据文件 ​

sql
-- 为undo表空间添加数据文件
ALTER TABLESPACE undotbs2
  ADD DATAFILE 'D:\\ORADATA\\ORCL\\undotbs202.dbf' SIZE 100M
  AUTOEXTEND ON NEXT 50M MAXSIZE 1024M;

Undo表空间监控 ​

空间使用监控 ​

查看Undo表空间使用情况 ​

sql
-- 查看undo表空间使用情况
SELECT tablespace_name, 
       SUM(bytes)/1024/1024 AS total_mb, 
       SUM(bytes - free_bytes)/1024/1024 AS used_mb, 
       SUM(free_bytes)/1024/1024 AS free_mb,
       ROUND((SUM(bytes - free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (SELECT tablespace_name, 
             bytes, 
             CASE WHEN autoextensible = 'YES' THEN 
                  maxbytes - bytes 
             ELSE 
                  0 
             END AS free_bytes
      FROM dba_data_files
      WHERE tablespace_name IN (SELECT tablespace_name 
                                FROM dba_tablespaces 
                                WHERE contents = 'UNDO'))
GROUP BY tablespace_name;

查看当前Undo段状态 ​

sql
-- 查看当前undo段状态
SELECT segment_name, status, tablespace_name
FROM dba_rollback_segs
WHERE tablespace_name IN (SELECT tablespace_name 
                          FROM dba_tablespaces 
                          WHERE contents = 'UNDO');

事务和回滚监控 ​

查看长时间运行的事务 ​

sql
-- 查看长时间运行的事务
SELECT s.sid, s.serial#, s.username, s.osuser, 
       t.start_time, ROUND((SYSDATE - t.start_time)*24*60, 2) AS minutes, 
       t.used_ublk, t.used_urec, r.name AS rollback_segment
FROM v$transaction t,
     v$session s,
     v$rollname r
WHERE t.ses_addr = s.saddr
AND t.xidusn = r.usn
ORDER BY minutes DESC;

监控Undo使用趋势 ​

sql
-- 查看undo使用趋势
SELECT begin_time, 
       end_time, 
       undoblks, 
       txncount, 
       maxquerylen, 
       maxconcurrency
FROM v$undostat
ORDER BY begin_time DESC
FETCH FIRST 24 ROWS ONLY;

Undo表空间优化 ​

空间优化 ​

调整Undo表空间大小 ​

sql
-- 扩展undo表空间
ALTER DATABASE DATAFILE 'D:\\ORADATA\\ORCL\\undotbs101.dbf' RESIZE 200M;

-- 启用自动扩展
ALTER DATABASE DATAFILE 'D:\\ORADATA\\ORCL\\undotbs101.dbf' 
  AUTOEXTEND ON NEXT 50M MAXSIZE 2048M;

切换Undo表空间 ​

sql
-- 创建新的undo表空间
CREATE UNDO TABLESPACE undotbs_new
  DATAFILE 'D:\\ORADATA\\ORCL\\undotbs_new01.dbf' SIZE 200M
  AUTOEXTEND ON NEXT 50M MAXSIZE 2048M;

-- 切换到新的undo表空间
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS_NEW' SCOPE=BOTH;

-- 检查旧表空间是否可以删除
SELECT * FROM v$rollname WHERE ts# = (SELECT ts# FROM v$tablespace WHERE name = 'UNDOTBS1');

-- 删除旧的undo表空间
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

性能优化 ​

调整UNDO_RETENTION参数 ​

sql
-- 基于闪回需求调整undo保留时间
ALTER SYSTEM SET undo_retention = 1800 SCOPE=BOTH;

-- 启用GUARANTEE RETENTION
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

-- 禁用GUARANTEE RETENTION
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

监控和调整Undo相关指标 ​

sql
-- 查看undo相关的等待事件
SELECT event, count(*) AS wait_count
FROM v$session_wait
WHERE event LIKE '%undo%'
GROUP BY event
ORDER BY wait_count DESC;

-- 查看undo adviser信息
SELECT * FROM v$undostat
WHERE end_time = (SELECT MAX(end_time) FROM v$undostat);

Undo表空间维护 ​

日常维护任务 ​

定期监控Undo表空间使用情况 ​

  • 每周检查undo表空间使用情况
  • 监控长时间运行的事务
  • 检查undo保留时间是否足够

定期重建Undo表空间 ​

sql
-- 重建undo表空间的步骤

-- 1. 创建新的undo表空间
CREATE UNDO TABLESPACE undotbs_new
  DATAFILE 'D:\\ORADATA\\ORCL\\undotbs_new01.dbf' SIZE 200M
  AUTOEXTEND ON NEXT 50M MAXSIZE 2048M;

-- 2. 切换到新的undo表空间
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS_NEW' SCOPE=BOTH;

-- 3. 等待旧表空间中的事务完成
-- 监控旧表空间的使用情况
SELECT tablespace_name, status, contents
FROM dba_tablespaces
WHERE tablespace_name = 'UNDOTBS1';

-- 4. 删除旧的undo表空间
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

-- 5. 重命名新表空间（可选）
-- 注意：Oracle不支持直接重命名表空间，需要通过重建实现

备份和恢复 ​

Undo表空间备份 ​

  • Undo表空间会被包含在完整的数据库备份中
  • 对于RMAN备份，undo表空间会自动包含在备份集中
  • 对于冷备份，确保包含所有undo表空间的数据文件

Undo表空间恢复 ​

  • 在完整数据库恢复时，undo表空间会自动恢复
  • 在表空间级恢复时，需要注意undo表空间的一致性
  • 避免单独恢复undo表空间，可能导致数据库不一致

常见问题（FAQ） ​

Q1: Undo表空间不足怎么办？ ​

A1: 解决方法包括：

  • 扩展现有数据文件：ALTER DATABASE DATAFILE '路径' RESIZE 大小;
  • 添加新的数据文件：ALTER TABLESPACE undotbs1 ADD DATAFILE '路径' SIZE 大小;
  • 启用数据文件自动扩展：ALTER DATABASE DATAFILE '路径' AUTOEXTEND ON;
  • 调整UNDO_RETENTION参数，减少保留时间
  • 检查并终止长时间运行的事务

Q2: 如何确定合适的Undo表空间大小？ ​

A2: 可以通过以下方法估算：

  • 监控v$undostat视图，查看峰值使用情况
  • 使用Oracle的Undo Advisor：EXECUTE dbms_workload_repository.create_undo_advisor_task;
  • 经验公式：Undo表空间大小 = (最大并发事务数 × 每个事务的平均大小 × 保留时间) + 20% 安全余量

Q3: 什么是Ora-01555错误？如何解决？ ​

A3: Ora-01555错误是"快照过旧"错误，通常由以下原因引起：

  • Undo表空间不足
  • UNDO_RETENTION设置过小
  • 长时间运行的查询

解决方法：

  • 增加Undo表空间大小
  • 增大UNDO_RETENTION参数值
  • 启用RETENTION GUARANTEE
  • 优化长时间运行的查询，减少查询时间

Q4: 如何监控Undo表空间的使用情况？ ​

A4: 可以使用以下SQL语句监控：

  • 查看表空间使用情况：SELECT * FROM dba_free_space WHERE tablespace_name = 'UNDOTBS1';
  • 查看undo使用统计：SELECT * FROM v$undostat ORDER BY begin_time DESC;
  • 查看长时间运行的事务：SELECT * FROM v$transaction;

Q5: 能否创建多个Undo表空间？ ​

A5: 可以创建多个Undo表空间，但同一时间只有一个是活跃的。创建多个Undo表空间的好处：

  • 用于表空间维护和迁移
  • 不同的数据库实例可以使用不同的Undo表空间
  • 提供故障转移能力

Q6: 如何处理Undo表空间损坏？ ​

A6: Undo表空间损坏的处理方法：

  • 如果是单实例数据库，需要从备份恢复整个数据库
  • 如果是RAC环境，可以关闭受影响的实例，其他实例继续运行
  • 严重损坏时可能需要重建数据库

Q7: RETENTION GUARANTEE有什么影响？ ​

A7: 启用RETENTION GUARANTEE的影响：

  • 确保undo数据至少保留UNDO_RETENTION指定的时间
  • 可能导致Undo表空间不足，需要更大的表空间
  • 防止Ora-01555错误，但可能影响其他事务

建议：根据实际需求和可用空间决定是否启用。

Q8: 如何在不同版本的Oracle中管理Undo表空间？ ​

A8: 不同版本的管理差异：

  • Oracle 9i及以上：支持自动Undo管理（AUM）
  • Oracle 10g及以上：支持闪回操作，需要适当增大UNDO_RETENTION
  • Oracle 11g及以上：引入了自动调优的undo retention
  • Oracle 12c及以上：支持多租户环境的undo表空间管理

无论版本如何，核心管理原则保持一致：监控空间使用、调整大小、优化参数设置。