印刻数据库

外观

Oracle Undo/Redo 优化 ​

Oracle Undo/Redo 优化概述 ​

Undo 和 Redo 是 Oracle 数据库事务管理的核心组件，对数据库的性能和可靠性至关重要。Redo 日志记录数据库的所有修改操作，用于崩溃恢复和介质恢复；Undo 数据记录数据修改前的状态，用于事务回滚、一致性读取和闪回操作。合理的 Undo 和 Redo 配置可以提高数据库的性能、可靠性和可用性。

Undo/Redo 优化的重要性 ​

  • 提高数据库事务处理性能
  • 确保数据库的一致性和可靠性
  • 支持数据恢复和闪回操作
  • 减少系统资源消耗
  • 提高系统的可扩展性

Undo/Redo 优化的基本原则 ​

  • 合理规划 Undo 表空间：根据事务负载设计 Undo 表空间大小和配置
  • 优化 Redo 日志配置：合理设置 Redo 日志大小、组数和位置
  • 平衡性能和可靠性：在性能和可靠性之间找到平衡点
  • 监控和调整：定期监控 Undo 和 Redo 的使用情况，及时调整配置
  • 考虑版本差异：不同版本的 Oracle 数据库在 Undo/Redo 管理方面有不同的特性

Undo 优化 ​

1. Undo 表空间设计 ​

1.1 Undo 表空间类型 ​

Oracle 数据库支持两种 Undo 管理方式：

  • 自动 Undo 管理（AUM）：Oracle 10g 及以上版本推荐使用，自动管理 Undo 数据
  • 手动 Undo 管理：需要手动管理回滚段，不推荐使用

1.2 Undo 表空间创建 ​

sql
-- 创建自动 Undo 表空间
CREATE UNDO TABLESPACE undo_ts
DATAFILE '/oradata/undo_ts01.dbf' SIZE 20G
AUTOEXTEND ON NEXT 2G MAXSIZE 50G;

-- 设置当前 Undo 表空间
ALTER SYSTEM SET undo_tablespace = undo_ts SCOPE=BOTH;

1.3 Undo 表空间大小规划 ​

Undo 表空间大小的规划需要考虑以下因素：

  • 事务负载：平均事务大小和并发事务数量
  • 最长运行事务：最长运行事务的持续时间
  • Undo 保留时间：需要保留 Undo 数据的时间长度
  • 数据库版本：不同版本的 Oracle 数据库对 Undo 管理有不同的优化

1.4 Undo 表空间大小计算公式 ​

Undo 表空间大小 = ( undo_retention * db_block_size * undo_blocks_per_second ) / ( 1024 * 1024 * 1024 )

其中：

  • undo_retention：Undo 保留时间，单位为秒
  • db_block_size：数据库块大小，单位为字节
  • undo_blocks_per_second：每秒生成的 Undo 块数量

2. Undo 参数优化 ​

2.1 核心 Undo 参数 ​

sql
-- Undo 表空间名称
ALTER SYSTEM SET undo_tablespace = undo_ts SCOPE=BOTH;

-- Undo 保留时间（秒）
ALTER SYSTEM SET undo_retention = 900 SCOPE=BOTH;

-- 自动优化 Undo 保留时间（Oracle 11g 及以上版本）
ALTER SYSTEM SET undo_retention = 900 SCOPE=BOTH;
ALTER SYSTEM SET undo_management = 'AUTO' SCOPE=SPFILE;

-- Undo 表空间自动扩展
ALTER DATABASE DATAFILE '/oradata/undo_ts01.dbf' AUTOEXTEND ON NEXT 2G MAXSIZE 50G;

2.2 高级 Undo 参数 ​

sql
-- 启用 Undo 表空间自动优化（Oracle 19c 及以上版本）
ALTER SYSTEM SET undo_autotune = TRUE SCOPE=SPFILE;

-- 最大 Undo  retention 时间（秒）
ALTER SYSTEM SET _undo_retention_max = 7200 SCOPE=SPFILE;

-- 最小 Undo  retention 时间（秒）
ALTER SYSTEM SET _undo_retention_min = 300 SCOPE=SPFILE;

3. Undo 表空间监控与维护 ​

3.1 监控 Undo 表空间使用情况 ​

sql
-- 查看 Undo 表空间使用情况
SELECT tablespace_name, 
       SUM(bytes)/1024/1024/1024 AS total_gb,
       SUM(bytes - free_bytes)/1024/1024/1024 AS used_gb,
       SUM(free_bytes)/1024/1024/1024 AS free_gb,
       ROUND((SUM(bytes - free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
    SELECT tablespace_name, bytes, 0 AS free_bytes
    FROM dba_data_files
    WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace')
    UNION ALL
    SELECT tablespace_name, 0 AS bytes, bytes AS free_bytes
    FROM dba_free_space
    WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace')
) 
GROUP BY tablespace_name;

-- 查看 Undo 统计信息
SELECT * FROM v$undostat ORDER BY begin_time DESC;

-- 查看长时间运行的事务
SELECT s.sid, s.serial#, s.username, t.start_time, t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY t.start_time;

3.2 Undo 表空间维护 ​

  • 扩展 Undo 表空间：当 Undo 表空间使用率过高时，添加数据文件或调整现有数据文件大小
  • 重建 Undo 表空间：当 Undo 表空间存在碎片或性能问题时，考虑重建 Undo 表空间
  • 调整 Undo 保留时间：根据业务需求调整 Undo 保留时间

4. Undo 相关问题与解决方案 ​

4.1 ORA-01555: snapshot too old ​

问题：长时间运行的查询因 Undo 数据被覆盖而失败

解决方案：

  • 增加 undo_retention 参数值
  • 扩大 Undo 表空间大小
  • 优化长时间运行的查询，减少查询执行时间
  • 考虑使用 Read-Only 表空间或 Materialized View 存储历史数据

4.2 Undo 表空间使用率过高 ​

问题：Undo 表空间使用率持续过高

解决方案：

  • 检查是否有长时间运行的事务
  • 增加 Undo 表空间大小
  • 调整 undo_retention 参数值
  • 考虑使用多个 Undo 表空间（Oracle 12c 及以上版本支持 PDB 级别的 Undo 表空间）

Redo 优化 ​

1. Redo 日志设计 ​

1.1 Redo 日志大小规划 ​

合理的 Redo 日志大小可以减少日志切换频率，提高数据库性能。

Redo 日志大小建议 ​

数据库类型Redo 日志大小建议
OLTP 系统1-2GB
OLAP 系统2-4GB
数据仓库4-8GB

1.2 Redo 日志组数规划 ​

Oracle 数据库建议至少有 3 个 Redo 日志组，每个组至少有 2 个成员。

1.3 Redo 日志创建 ​

sql
-- 创建 Redo 日志组
ALTER DATABASE ADD LOGFILE GROUP 4 ('/oradata/redo04a.log', '/oradata/redo04b.log') SIZE 2G;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/oradata/redo05a.log', '/oradata/redo05b.log') SIZE 2G;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/oradata/redo06a.log', '/oradata/redo06b.log') SIZE 2G;

-- 查看现有 Redo 日志组
SELECT group#, status, member FROM v$logfile ORDER BY group#;

2. Redo 日志优化策略 ​

2.1 Redo 日志位置优化 ​

  • 分离 Redo 日志和数据文件：将 Redo 日志文件存储在与数据文件不同的物理磁盘上
  • 使用镜像 Redo 日志：每个 Redo 日志组至少有 2 个成员，存储在不同的物理磁盘上
  • 使用高性能存储：Redo 日志对 I/O 性能要求较高，建议使用 SSD 或其他高性能存储

2.2 Redo 日志参数优化 ​

sql
-- 设置 Redo 日志同步方式
ALTER SYSTEM SET log_sync = 'IMMEDIATE' SCOPE=SPFILE;

-- 设置 Redo 日志缓冲区大小
ALTER SYSTEM SET log_buffer = 16M SCOPE=SPFILE;

-- 设置日志写入进程数量（Oracle 11g 及以上版本）
ALTER SYSTEM SET log_writer_processes = 4 SCOPE=SPFILE;

-- 启用异步 Redo 日志写入（仅适用于特定存储系统）
ALTER SYSTEM SET disk_asynch_io = TRUE SCOPE=SPFILE;

2.3 Redo 日志高级优化 ​

sql
-- 启用日志写入批处理
ALTER SYSTEM SET _log_io_size = 16384 SCOPE=SPFILE;

-- 优化日志缓冲区刷新
ALTER SYSTEM SET _log_buffer_resize = TRUE SCOPE=SPFILE;

-- 启用 Redo 日志压缩（Oracle 19c 及以上版本）
ALTER DATABASE ADD LOGFILE GROUP 7 ('/oradata/redo07a.log') SIZE 2G COMPRESS;

3. Redo 日志监控与维护 ​

3.1 监控 Redo 日志使用情况 ​

sql
-- 查看 Redo 日志状态
SELECT group#, thread#, sequence#, bytes/1024/1024 AS size_mb, status, archived
FROM v$log ORDER BY group#;

-- 查看 Redo 日志文件
SELECT group#, member, status FROM v$logfile ORDER BY group#;

-- 查看日志切换频率
SELECT to_char(first_time, 'YYYY-MM-DD HH24') AS hour, 
       COUNT(*) AS log_switches
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY to_char(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

-- 查看 Redo 生成速率
SELECT name, value FROM v$sysstat WHERE name IN ('redo size', 'redo writes');

3.2 Redo 日志维护 ​

  • 添加 Redo 日志组：当日志切换频率过高时，添加更多的 Redo 日志组
  • 调整 Redo 日志大小：根据日志切换频率调整 Redo 日志大小
  • 删除不需要的 Redo 日志组：删除状态为 INACTIVE 且不再需要的 Redo 日志组
  • 重建 Redo 日志：当 Redo 日志文件损坏时，重建 Redo 日志

4. Redo 相关问题与解决方案 ​

4.1 日志切换频率过高 ​

问题：Redo 日志切换频率过高，导致 I/O 瓶颈

解决方案：

  • 增加 Redo 日志大小
  • 添加更多的 Redo 日志组
  • 优化事务设计，减少大事务
  • 考虑使用批量提交

4.2 Redo 日志损坏 ​

问题：Redo 日志文件损坏导致数据库无法正常启动

解决方案：

  • 如果损坏的是 INACTIVE 日志组，可以删除并重建
  • 如果损坏的是 CURRENT 或 ACTIVE 日志组，需要使用不完全恢复
  • 定期备份数据库，确保可以恢复到最近的时间点

4.3 Redo 生成量过大 ​

问题：数据库生成大量的 Redo 日志，导致存储和 I/O 压力

解决方案：

  • 优化 SQL 语句，减少不必要的数据修改
  • 考虑使用 NOLOGGING 或 UNRECOVERABLE 选项（仅适用于临时数据或可以重新生成的数据）
  • 分区表使用增量更新，减少 Redo 生成量
  • 考虑使用压缩技术减少数据修改量

Oracle 19c 和 21c Undo/Redo 新特性 ​

Oracle 19c 新特性 ​

1. Redo 日志压缩 ​

Oracle 19c 引入了 Redo 日志压缩功能，可以减少 Redo 日志的存储开销和 I/O 带宽需求。

sql
-- 创建压缩的 Redo 日志组
ALTER DATABASE ADD LOGFILE GROUP 8 ('/oradata/redo08a.log') SIZE 2G COMPRESS;

-- 查看 Redo 日志压缩状态
SELECT group#, compression FROM v$log;

2. 自动 Undo 优化 ​

Oracle 19c 增强了自动 Undo 管理功能，可以根据工作量自动调整 Undo 保留时间和 Undo 表空间大小。

sql
-- 启用自动 Undo 优化
ALTER SYSTEM SET undo_autotune = TRUE SCOPE=SPFILE;

Oracle 21c 新特性 ​

1. 增强的 Redo 日志管理 ​

Oracle 21c 引入了更灵活的 Redo 日志管理功能，包括：

  • 支持更大的 Redo 日志文件
  • 增强的 Redo 日志监控功能
  • 更高效的 Redo 日志写入算法

2. 多租户环境下的 Undo 优化 ​

Oracle 21c 增强了多租户环境下的 Undo 管理，支持：

  • PDB 级别的 Undo 表空间（Oracle 12c 及以上版本已支持）
  • 更灵活的 Undo 资源隔离
  • 增强的 Undo 监控和管理功能

Undo/Redo 优化最佳实践 ​

1. Undo 优化最佳实践 ​

  • 使用自动 Undo 管理：Oracle 10g 及以上版本推荐使用自动 Undo 管理
  • 合理设置 Undo 保留时间：根据业务需求和查询模式设置合适的 undo_retention 值
  • 扩大 Undo 表空间：确保 Undo 表空间有足够的空间容纳并发事务产生的 Undo 数据
  • 监控 Undo 使用率：定期监控 Undo 表空间使用率和长时间运行的事务
  • 优化长时间运行的查询：减少查询执行时间，降低 Undo 数据需求

2. Redo 优化最佳实践 ​

  • 合理设置 Redo 日志大小：根据数据库工作量设置合适的 Redo 日志大小，减少日志切换频率
  • 使用多个 Redo 日志组：至少创建 3 个 Redo 日志组，每个组至少有 2 个成员
  • 优化 Redo 日志位置：将 Redo 日志文件存储在高性能存储上，并与数据文件分离
  • 监控日志切换频率：目标是日志切换频率不超过每 10-15 分钟一次
  • 优化事务设计：减少大事务，使用批量提交，避免不必要的数据修改

3. 综合优化建议 ​

  • 平衡 Undo 和 Redo 配置：在性能和可靠性之间找到平衡点
  • 考虑使用 ASM：使用 Oracle ASM 管理 Undo 和 Redo 存储，提高性能和可靠性
  • 定期备份：确保有最新的数据库备份，以便在需要时进行恢复
  • 测试和调整：在测试环境中测试不同的 Undo/Redo 配置，找到最佳配置方案

常见问题（FAQ） ​

Q1: 如何确定 Undo 表空间的合理大小？ ​

A1: Undo 表空间的合理大小取决于多个因素，包括：

  • 并发事务数量
  • 平均事务大小
  • 最长运行事务的持续时间
  • Undo 保留时间设置

可以使用以下公式估算 Undo 表空间大小：

Undo 表空间大小 = (undo_retention * db_block_size * undo_blocks_per_second) / (1024 * 1024 * 1024)

Q2: 如何优化 Redo 日志性能？ ​

A2: 优化 Redo 日志性能可以从以下方面入手：

  • 合理设置 Redo 日志大小和组数
  • 将 Redo 日志文件存储在高性能存储上
  • 将 Redo 日志与数据文件分离
  • 优化事务设计，减少大事务
  • 考虑使用批量提交

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

A3: ORA-01555 是 "snapshot too old" 错误，通常发生在长时间运行的查询中，当查询需要的 Undo 数据被覆盖时。解决方法包括：

  • 增加 undo_retention 参数值
  • 扩大 Undo 表空间大小
  • 优化查询，减少执行时间
  • 使用 Read-Only 表空间或 Materialized View 存储历史数据

Q4: 如何监控 Redo 日志切换频率？ ​

A4: 可以使用以下 SQL 查询监控 Redo 日志切换频率：

sql
SELECT to_char(first_time, 'YYYY-MM-DD HH24') AS hour, 
       COUNT(*) AS log_switches
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY to_char(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

Q5: Oracle 19c 和 21c 在 Undo/Redo 管理方面有什么新特性？ ​

A5: Oracle 19c 和 21c 在 Undo/Redo 管理方面的主要新特性包括：

  • Oracle 19c：Redo 日志压缩、增强的自动 Undo 优化
  • Oracle 21c：增强的 Redo 日志管理、多租户环境下的 Undo 优化

Q6: 如何优化大事务的性能？ ​

A6: 优化大事务的性能可以从以下方面入手：

  • 拆分为多个小事务，使用批量提交
  • 使用 NOLOGGING 或 UNRECOVERABLE 选项（如果数据可以重新生成）
  • 增加 Redo 日志大小，减少日志切换频率
  • 考虑使用并行执行
  • 优化 SQL 语句，减少数据修改量

总结 ​

Undo 和 Redo 是 Oracle 数据库事务管理的核心组件，合理的 Undo 和 Redo 配置对数据库性能和可靠性至关重要。Undo 优化包括合理规划 Undo 表空间、优化 Undo 参数、监控 Undo 使用情况等；Redo 优化包括合理设计 Redo 日志、优化 Redo 参数、监控 Redo 使用情况等。

Oracle 19c 和 21c 引入了许多新特性，如 Redo 日志压缩、增强的自动 Undo 优化等，可以进一步提高 Undo/Redo 管理的效率和性能。DBA 需要根据数据库的工作量和业务需求，合理配置和优化 Undo 和 Redo，以提高数据库的整体性能和可靠性。

通过合理的 Undo/Redo 优化，可以提高 Oracle 数据库的事务处理性能、确保数据的一致性和可靠性、支持数据恢复和闪回操作，为业务提供更好的支持。