小伙伴們，關于數據庫的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機制通過持續創新實現：