64-Oracle Redo Log

小伙伴们，关于数据库的redo log相信大家都操作很多次了,且这是OCM考试必考内容。Oracle Redo Log是一种特殊的日志文件，用于完整地记录数据库中所有数据变更的详细信息。当数据库执行插如、更新或删除等更新操作，这些操作并不会立刻写入数据库的实际数据文件。依赖WAL这个规则（所有关系型数据库几乎都是）变更会首先被记录到Redo Log文件中而后后台进程刷盘落库。

一、 Redo Log 核心功能与原理​

​1.1 核心功能​​

1.2 技术原理​

1.3 日志结构​： 

1.4 核心组件 

​组件	​作用
​Redo Log Buffer​	SGA中的循环缓冲区，临时存储redo条目（约10MB-15MB）。
​LGWR进程​	将Redo Log Buffer写入Redo Log文件。
​Redo Log文件​	物理文件组（通常3组），每组包含多个成员（镜像）。

1.5. 版本演进 

​版本​	​演进特性​
9i​	引入LogMiner，支持Redo Log分析。
​10g	LOG_BUFFER自动计算（Granule机制）， 默认值 ≈ max(512KB, 128KB * CPU_COUNT)
​11g​	引入In-Memory Undo，减少恢复时间。
​12c	多租户下每个PDB有独立Redo线程，支持Far Sync异步redo传输。
​19c​	优化Active Data Guard实时redo应用。

二. 实操脚本与验证​

​2.1 查看Redo配置

-- 查看Redo Log Buffer大小
SELECT * FROM v$sgainfo WHERE name IN ('Fixed SGA Size', 'Redo Buffers');
--
NAME                   BYTES RESIZEABLE       CON_ID
_________________ __________ _____________ _________
Fixed SGA Size       4922232 No                    0
Redo Buffers         4530176 No                    0-- 查看Redo Log文件组
SELECT group#, bytes/1024/1024 size_mb, members, status 
FROM v$log;
--
SYS@CDB$ROOT> alter system switch logfile;
System altered.
SYS@CDB$ROOT> SELECT group#, bytes/1024/1024 size_mb, members, statusFROM v$log;GROUP#    SIZE_MB    MEMBERS STATUS
_________ __________ __________ ___________1        200          1 CURRENT2        200          1 INACTIVE3        200          1 ACTIVE

2.2 Redo Log管理脚本（11g+）

2.2.1 创建与维护

-- 添加ASM存储的日志组(示例)
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 4 ('+DATA/redo04a.log','+FRA/redo04b.log'
) SIZE 2G;-- 验证日志组,所有日志组状态
SELECT group#, member, status FROM v$logfile;GROUP# MEMBER                                 STATUS
_________ ______________________________________ _________3 /opt/oracle/oradata/FREE/redo03.log2 /opt/oracle/oradata/FREE/redo02.log1 /opt/oracle/oradata/FREE/redo01.log
--
SELECT * 
FROM (SELECTl.GROUP# AS "Group ID",l.STATUS AS "Group Status",COUNT(f.MEMBER) AS "Members",ROUND(l.BYTES / POWER(1024, 2), 2) AS "Size (MB)",l.SEQUENCE# AS "Sequence"FROM v$log lJOIN v$logfile f ON l.GROUP# = f.GROUP#GROUP BY l.GROUP#, l.STATUS, l.BYTES, l.SEQUENCE#
)
ORDER BY "Group ID";
--Group ID Group Status       Members    Size (MB)    Sequence
___________ _______________ __________ ____________ ___________1 CURRENT                  1          200          792 INACTIVE                 1          200          773 INACTIVE                 1          200          78-- 在线重定位日志文件-ASM自动处理重平衡
ALTER DATABASE RENAME FILE '+DATA/redo04b.log' TO '+N_DG/redo04b.log';

2.2.2 大小调整 

-- 通过替换组调整大小（官方手册）
ALTER DATABASE ADD LOGFILE GROUP 5 ('+DATA/redo05.log') SIZE 4G;
ALTER SYSTEM SWITCH LOGFILE; -- 执行直到旧组状态为INACTIVE
ALTER DATABASE DROP LOGFILE GROUP 4;

2.3. 观察redo（11g+） 

-- 查找所有非正常状态的日志成员
SELECT *
FROM (SELECTl.GROUP# AS group_id,f.MEMBER AS file_path,f.STATUS AS member_status,l.STATUS AS group_statusFROM v$log lJOIN v$logfile f ON l.GROUP# = f.GROUP#
)
WHERE member_status IS NOT NULL OR group_status NOT IN ('INACTIVE', 'CURRENT');
--no rows selected-- 日志文件状态验证
SELECT group#, status, archived, sequence#, 
bytes/1024/1024 size_mb
FROM v$log ORDER BY group#;
--GROUP# STATUS      ARCHIVED       SEQUENCE#    SIZE_MB
_________ ___________ ___________ ____________ __________1 CURRENT     NO                    79        2002 INACTIVE    YES                   77        2003 INACTIVE    YES                   78        200

2.4. 分析 Redo 性能的推荐方法（不使用事件跟踪） 

-- LGWR瓶颈检测
SELECT event, total_waits, wait_class
FROM v$system_event 
WHERE event IN ('log file parallel write', 'log file sync');
--
EVENT                         TOTAL_WAITS WAIT_CLASS
__________________________ ______________ _____________
log file parallel write              9192 System I/O
log file sync                          62 Commit-- 查看 Redo 生成统计
SELECT stat.name AS metric,sess.value
FROM v$sesstat sess
JOIN v$statname stat ON sess.statistic# = stat.statistic#
WHERE sess.sid = (SELECT sid FROM v$mystat WHERE rownum = 1)
AND stat.name IN ('redo size', 'redo entries', 'redo writes','redo synch time','redo wastage'
);
--
METRIC                VALUE
__________________ ________
redo entries             20
redo size              7448
redo wastage              0
redo writes               0
redo synch time           0-- 查看日志切换频率
SELECT thread#,sequence#,(next_time - first_time) * 86400 AS duration_sec,blocks * block_size / 1024 / 1024 AS size_mb
FROM v$archived_log 
ORDER BY sequence# DESC 
FETCH FIRST 10 ROWS ONLY;
--THREAD#    SEQUENCE#                                  DURATION_SEC           SIZE_MB
__________ ____________ _____________________________________________ _________________1           78     4302.000000000000000000000000000000000003    29.217285156251           77                                             0     0.003417968751           76                                             5       42.839843751           75                                             0     0.004394531251           74     1699.000000000000000000000000000000000004          18.031251           73                                             0      0.00292968751           72                                             3    42.750488281251           71    0.9999999999999999999999999999999999999996     0.007324218751           70                                           176     0.131347656251           69                                             7     0.0102539062510 rows selected.

 2.5. 监控 LGWR 行为的标准方法

-- 查看 LGWR 活动
SELECT event,total_waits,time_waited_micro,average_wait
FROM v$system_event
WHERE event LIKE 'log file%';
--
EVENT                          TOTAL_WAITS    TIME_WAITED_MICRO    AVERAGE_WAIT
___________________________ ______________ ____________________ _______________
log file sequential read               140                54353            0.04
log file single write                   14                 8084            0.06
log file parallel write              10035              9100663            0.09
log file sync                           65                42242            0.06-- 查看日志缓冲区使用率
SELECT (SELECT value FROM v$sysstat WHERE name = 'redo buffer allocation retries') / (SELECT value FROM v$sysstat WHERE name = 'redo entries') * 100 AS retry_pct
FROM dual;
--RETRY_PCT
____________0

四. 最佳实践（源于MOS文档）

4.1 配置规范

​环境​	​日志大小​	​组数	​存储​	​冗余策略​
OLTP生产	2-4GB	4+	NVMe SSD	MULTIPLEX 3路
RAC集群	2GB	每实例3组	ASM HIGH冗余	FAILGROUP隔离
云环境（ExaCC）	2GB	4	持久内存+ASM	跨可用域部署

4.2 优化设计

五、使用体验

Oracle在保障ACID的前提下，Redo Log使Oracle数据库实现：

• RPO=0（零数据丢失），RTO<60秒（自动化恢复），支持>1百万TPS的交易负载

Oracle Redo Log机制通过持续创新实现：