Oracle Redo 日志配置与管理

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

Redo 日志记录数据库变更[1]：

作用：

恢复
Data Guard
Streams

2. 查看 Redo 日志

2.1 v$log

SELECT 
  group#, 
  thread#, 
  sequence#, 
  members, 
  bytes / 1024 / 1024 AS mb, 
  status, 
  archived
FROM v$log;

2.2 v$logfile

SELECT 
  group#, 
  member, 
  status, 
  type
FROM v$logfile;

3. 配置

3.1 组数

至少 3 组
RAC 每节点至少 2 组

3.2 大小

15-20 分钟切换一次
通常 500M - 4G

3.3 多成员

每组 2-3 成员
不同磁盘

4. 添加 Redo 日志

4.1 添加组

ALTER DATABASE ADD LOGFILE GROUP 4 
  ('/u01/oradata/redo04a.log', '/u02/oradata/redo04b.log') 
  SIZE 2G;

4.2 添加成员

ALTER DATABASE ADD LOGFILE MEMBER 
  '/u03/oradata/redo01c.log' TO GROUP 1,
  '/u03/oradata/redo02c.log' TO GROUP 2;

5. 删除 Redo 日志

5.1 删除成员

ALTER DATABASE DROP LOGFILE MEMBER '/u03/oradata/redo01c.log';

5.2 删除组

-- 必须非活动
ALTER DATABASE DROP LOGFILE GROUP 4;

6. 切换日志

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM CHECKPOINT GLOBAL;

7. 清空 Redo 日志

ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

8. 性能监控

8.1 切换频率

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

8.2 切换间隔

SELECT 
  sequence#,
  first_time,
  next_time,
  (first_time - LAG(first_time) OVER (ORDER BY sequence#)) * 24 * 60 AS minutes
FROM v$log_history
WHERE first_time > SYSDATE - 1
ORDER BY sequence# DESC;

8.3 等待事件

SELECT event, total_waits, time_waited 
FROM v$system_event 
WHERE event IN ('log file sync', 'log file parallel write', 'log buffer space');

9. Standby Redo Log

9.1 创建

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 
  ('/u01/oradata/stdby_redo11.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 
  ('/u01/oradata/stdby_redo12.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 
  ('/u01/oradata/stdby_redo13.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 
  ('/u01/oradata/stdby_redo14.log') SIZE 200M;

9.2 查看

SELECT group#, thread#, sequence#, bytes, archived, status 
FROM v$standby_log;

详细见：Oracle Data Guard 部署。

10. 调整大小

10.1 添加大组

ALTER DATABASE ADD LOGFILE GROUP 5 
  ('/u01/oradata/redo05.log') SIZE 4G;
ALTER DATABASE ADD LOGFILE GROUP 6 
  ('/u01/oradata/redo06.log') SIZE 4G;

10.2 删除旧组

-- 切换使旧组 INACTIVE
ALTER SYSTEM SWITCH LOGFILE;
-- 重复直到旧组 INACTIVE

-- 删除
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;

11. RAC 配置

11.1 每节点多组

-- Thread 1
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 (...) SIZE 2G;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 (...) SIZE 2G;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 3 (...) SIZE 2G;

-- Thread 2
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 (...) SIZE 2G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5 (...) SIZE 2G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6 (...) SIZE 2G;

12. 常见坑与排错

12.1 切换频繁

-- 1. 增大 Redo Log
-- 2. 检查长事务
-- 3. 减少 DML

12.2 ORA-00312

-- 文件无法访问
-- 1. 检查路径
-- 2. 权限
-- 3. 恢复

12.3 ORA-00313

-- 日志组丢失
-- 1. CLEAR
ALTER DATABASE CLEAR LOGFILE GROUP 1;
-- 2. 重建

12.4 log file sync 高

-- 1. Redo SSD
-- 2. 减少提交
-- 3. LGWR 优化

详细见：Oracle Undo 与 Redo 调优。

13. 最佳实践

至少 3 组：避免等待
多成员：冗余
不同磁盘：可用性
大小合理：15-20 分钟切换
SSD：性能
Standby Redo Log：Data Guard
监控切换频率：调整
监控 log file sync：性能
归档模式：保护
文档化：配置

14. 参考资料

[1] Oracle Database Administrator’s Guide 19c, “Redo Log Files” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/redo-log-files.html