Oracle 数据库突然卡顿？警惕“Checkpoint not complete”导致的性能雪崩！

Oracle 数据库采用预写日志（Write-Ahead Logging）策略来平衡性能与数据安全性。

其 I/O 子系统核心依赖于两个关键后台进程的异步协作：

负责将 Redo Log Buffer 中的重做条目（Redo Entries）顺序写入联机重做日志文件。

由于仅记录变更向量且为顺序 I/O，其写入效率极高，通常是数据库吞吐量的决定性因素。

负责将 Buffer Cache 中的脏数据块（Dirty Blocks）离散写入数据文件（Datafiles）。

为了优化物理 I/O，DBWR 采用延迟写入（Lazy Write）策略，尽可能合并多次数据变更。

联机重做日志采用循环（Round-Robin）方式使用。

假设系统配置了 3 个日志组（Group 1, 2, 3）：

当 LGWR 写满 Group 1 后，切换至 Group 2。

此时会触发增量检查点（Incremental Checkpoint），将 Group 1 覆盖范围内的脏块标记为“必须写入”。

当 LGWR 写满 Group 3 并试图切回 Group 1 时，内核必须验证 Group 1 所保护的脏块是否已全部完成落盘。

当系统处于高频写入状态时，若日志生成的速率（LGWR）显著高于数据块写入磁盘的速率（DBWR），会导致 LGWR 完成一轮循环回到 Group 1 时，DBWR 尚未完成 Group 1 对应脏块的清洗。

此时，为了防止未持久化的数据被覆盖（导致介质恢复失败），Oracle 内核将强制挂起 LGWR。

数据库暂停处理所有涉及 Redo 生成的 DML 操作，直至 DBWR 完成必要的检查点队列写入。

此时 Alert Log 记录 Checkpoint not complete。

为了在受控环境中验证该机制，我们将通过 Shell 脚本模拟“受限的日志配置”与“高并发 I/O 负载”场景。

Oracle Database 23ai (CDB/PDB 架构)

Sysdba (配置变更) Application User (负载生成)

为了直观看到故障现场，我们将使用 Shell 脚本在测试环境中人为制造“瓶颈”。

Oracle Database 23ai (Free Enterprise)

CDB/PDB 架构

apps/apps@db1:1521/pdb2 (用于模拟业务负载)

sys (用于管理 Redo Log 配置)

首先，我们需要在 CDB 层面通过 SYS 用户创建极小的 Redo Log（10MB）。

这是制造故障的关键——让日志迅速被写满。

脚本文件：01_setup_tiny_logs.sh

#!/bin/bash
# -----------------------------------------------------------------------------
# Script Name: 01_setup_tiny_logs_fixed.sh
# Purpose:     Fix ORA-02236 by auto-detecting log path and specifying filenames
# -----------------------------------------------------------------------------

# 这里使用 SYS 用户连接 CDB
CDB_CONN="sys/oracle@db1:1521/FREE as sysdba"

echo "[Phase 1] 配置微型 Redo Log (10MB)..."

sqlplus -S "$CDB_CONN" <<EOF
SET FEEDBACK OFF VERIFY OFF TERMOUT OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- [Step 1] 自动获取当前日志文件的目录路径
COLUMN log_dir NEW_VALUE v_log_dir
SELECT SUBSTR(member, 1, INSTR(member, '/', -1)) AS log_dir 
FROM v\$logfile 
WHERE rownum = 1;

-- 开启输出，显示检测到的路径
SET TERMOUT ON
PROMPT Detected Log Directory: &v_log_dir

-- [Step 2] 清理可能存在的旧日志组
BEGIN
    FOR r IN (SELECT group# FROM v\$log WHERE group# IN (10,11,12)) LOOP
        EXECUTE IMMEDIATE 'ALTER DATABASE DROP LOGFILE GROUP ' || r.group#;
    END LOOP;
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

-- [Step 3] 创建新日志组 (显式指定文件名 + REUSE 参数以覆盖旧文件)
PROMPT Creating Group 10, 11, 12 in &v_log_dir ...

ALTER DATABASE ADD LOGFILE GROUP 10'&v_log_dir.redo10_sim.log' SIZE 10M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 11'&v_log_dir.redo11_sim.log' SIZE 10M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 12'&v_log_dir.redo12_sim.log' SIZE 10M REUSE;

-- [Step 4] 强制切换
PROMPT Switching logs...
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;

-- [Step 5] 验证结果
SET LINESIZE 200
COL member FORMAT a60
SELECT l.group#, l.bytes/1024/1024 MB, l.status, f.member
FROM v\$log l, v\$logfile f
WHERE l.group# = f.group# AND l.group# IN (10,11,12);

EXIT;
EOF

if [ $? -eq 0 ]; then
    echo "✅ 微型日志组配置成功！"
else
    echo "❌ 配置失败，请检查上方报错。"
fi

第二步：数据准备 (PDB 层面)

接着，连接到具体的 PDB (pdb2)，创建一张测试表并填充数据。

脚本文件：02_prepare_data.sh

#!/bin/bash
# 说明：连接 PDB，准备测试数据
PDB_CONN="apps/apps@db1:1521/pdb2"

echo "[Phase 2] 初始化 PDB 测试数据..."

sqlplus -S "$PDB_CONN" <<EOF
SET FEEDBACK OFF
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE t_ckpt_sim PURGE';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

-- 创建宽表 (填充 CHAR 字段增加数据块大小)
CREATE TABLE t_ckpt_sim (
    id NUMBER, 
    payload CHAR(2000) DEFAULT 'X'
);

-- 插入 20,000 行 (约 40MB+ 数据)
BEGIN
    FOR i IN 1..20000LOOP
        INSERT INTO t_ckpt_sim VALUES (i, 'INIT');
    END LOOP;
    COMMIT;
END;
/
EXIT;
EOF

第三步：并发攻击 (PDB 层面)

这是最核心的部分。

我们启动多个后台进程并发执行 UPDATE。

•  为什么用 UPDATE？ 为了把 Buffer Cache 中的块标记为“脏块”（Dirty），迫使 DBWR 写盘。

• 为什么分区更新？ 通过 BETWEEN start_id AND end_id 避免行锁冲突，确保压力全部集中在 I/O 和 Checkpoint 机制上。

脚本文件：03_attack_load.sh

#!/bin/bash
# 说明：启动并发 Update 压测
PDB_CONN="apps/apps@db1:1521/pdb2"
THREADS=4           # 并发线程数
LOOP_COUNT=10000    # 每个线程循环次数
ROWS_PER_THREAD=5000

echo "[Phase 3] 启动 $THREADS 个并发线程进行高频写入..."

for ((t=1; t<=THREADS; t++)); do
    start_id=$(( (t-1) * ROWS_PER_THREAD + 1 ))
    end_id=$(( t * ROWS_PER_THREAD ))

    # 后台运行 SQLPlus
    sqlplus -S "$PDB_CONN" > dev/null2>&1 <<EOF &
    BEGIN
        FOR i IN 1..$LOOP_COUNT LOOP
            -- 更新数据产生脏块
            UPDATE t_ckpt_sim 
            SET payload = 'Thread_${t}_Iter_'||i 
            WHERE id BETWEEN $start_id AND $end_id;

            -- 极其频繁的提交，迫使 LGWR 疯狂写日志
            COMMIT;
        END LOOP;
    END;
    
    EXIT;
EOF
    echo "  -> 线程 #$t 已启动 (Row ID: $start_id - $end_id)"
done

echo "压测运行中... 请执行监控脚本查看效果。"

第四步：实时监控与验证

运行攻击脚本后，立即运行监控脚本。

如果配置正确，10-20秒内即可看到故障。

脚本文件：04_monitor.sh

#!/bin/bash
PDB_CONN="apps/apps@db1:1521/pdb2"

echo "[Phase 4] 监控等待事件 (按 Ctrl+C 停止)..."

whiletrue; do
    # 查询当前正在等待 Checkpoint 的会话数
    count=$(sqlplus -S "$PDB_CONN" <<EOF
    SET PAGES 0FEEDBACK OFF
    SELECT count(*) FROM v\$session WHERE event LIKE 'log file switch (checkpoint incomplete)%';
    EXIT;
EOF
    )

    # 清洗输出
    count=$(echo $count | tr -d '[:space:]')

    timestamp=$(date '+%H:%M:%S')
    if [ "$count" -gt "0" ]; then
        echo -e "\033[1;41m[$timestamp] CRITICAL: 发现 $count 个会话阻塞于 Checkpoint Not Complete! \033[0m"
    else
        echo "[$timestamp] 系统运行中 (LGWR 未追尾)..."
    fi
    sleep 2
done

当 Checkpoint not complete 发生时，仅仅重启数据库或扩容日志只是治标。

要治本，必须找到那个在短时间内生成了海量 Redo 的“罪魁祸首”。

我们将从 实时诊断（正在发生） 和 事后分析（已经发生） 两个维度进行定位。

如果数据库此刻正处于卡顿状态，Alert Log 正在疯狂刷屏，请立即执行以下 SQL。

我们需要监控 redo size 统计指标。以下 SQL 会按 Redo 生成量降序排列，直接揪出最终目标。

-- 脚本名称: find_redo_hog.sql
SET LINESIZE 200 PAGESIZE 100
COL username FORMAT a15
COL program FORMAT a30
COL machine FORMAT a20
COL redo_mb FORMAT 999,999.99

SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       s.machine,
       s.sql_id,
       ROUND(st.value  1024 / 1024, 2) AS redo_mb,
       s.status,
       s.event
FROM   v$session s,
       v$sesstat st,
       v$statname sn
WHERE  s.sid = st.sid
AND    st.statistic# = sn.statistic#
AND    sn.name = 'redo size'
AND    st.value > 10 * 1024 * 1024  -- 过滤掉小于 10MB 的会话
ORDER  BY st.value DESC;

输出示例分析：

       SID    SERIAL# USERNAME        PROGRAM                        MACHINE              SQL_ID            REDO_MB STATUS   EVENT
---------- ---------- --------------- ------------------------------ -------------------- ------------- ----------- -------- ----------------------------------------------------------------
       214      13714 APPS            sqlplus@db1 (TNS V1-V3)        db1                  1v01ymthgbtfc    3,726.94ACTIVE   log file switch(checkpoint incomplete)
        20      10331 APPS            sqlplus@db1(TNS V1-V3)        db1                  5fu18cgsgq90c    3,262.12 ACTIVE   log file switch(checkpoint incomplete)
        34      49440 APPS            sqlplus@db1(TNS V1-V3)        db1                  g6qjwhyzwhcxc    3,139.75 ACTIVE   buffer busy waits
       206      62693 APPS            sqlplus@db1(TNS V1-V3)        db1                  7r2bgdgpry7cx    2,563.29 ACTIVE   log file switch(checkpoint incomplete)
       208      45825 APPS            sqlplus@db1(TNS V1-V3)        db1                  g6qjwhyzwhcxc      113.25 ACTIVE   log file switch(checkpoint incomplete)
        50      11759 APPS            sqlplus@db1(TNS V1-V3)        db1                  7r2bgdgpry7cx       88.43 ACTIVE   log file switch(checkpoint incomplete)

拿到 SQL_ID (例如 6dk2k3...) 后，查看具体在执行什么：

-- 脚本名称: get_sql_text.sql
SET LONG 9999999
SELECT sql_fulltext 
FROM v$sqlarea 
WHERE sql_id = '&input_sql_id';

语句如下：

SQL_FULLTEXT
--------------------------------------------------------------------------------
UPDATE T_CKPT_SIM SET PAYLOAD = 'Thread_1_Iter_'||:B1 WHERE ID BETWEEN 1 AND 5000

如果报警发生在凌晨 3 点，现在已经是早上 9 点，故障现场已消失。

此时我们需要借助 ASH (Active Session History) 和 AWR 来回溯。

ASH 记录了每秒的活动会话采样。

我们可以查找在故障时间点（Checkpoint not complete 爆发时）谁产生了最多的 PGA 变化或处于 ON CPU 状态（通常 Redo 生成伴随着高 CPU 和内存操作）。

-- 脚本名称: ash_history_redo.sql
-- 查找过去 1 小时内，与日志切换等待相关的 Top SQL
SELECT 
    sql_id,
    COUNT(*) as wait_count,
    TRUNC(RATIO_TO_REPORT(COUNT(*)) OVER () * 100, 2) AS pct_impact,
    MAX(sample_time) as last_seen
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
  AND event LIKE 'log file switch%'
GROUP BY sql_id
ORDER BY wait_count DESC;

我们可以通过关联 DBA_HIST_SQLSTAT 查找历史周期内物理写入量最大的 SQL：

-- 查找 AWR 中记录的“重做日志生成大户”
-- 需指定 snap_id 范围（通过 dba_hist_snapshot 获取）
SELECT s.sql_id,
       t.sql_text,
       SUM(s.executions_delta) as execs,
       ROUND(SUM(s.rows_processed_delta), 0) as rows_proc,
       -- 注意：AWR 不直接记录 SQL 级的 Redo Size，
       -- 但可以通过 "buffer_gets" 和 "rows_processed" 间接推断
       ROUND(SUM(s.buffer_gets_delta)  1024 / 1024, 2) as logical_io_gb
FROM   dba_hist_sqlstat s,
       dba_hist_sqltext t
WHERE  s.sql_id = t.sql_id
AND    s.snap_id BETWEEN &begin_snap AND &end_snap
AND    s.rows_processed_delta > 10000 -- 过滤小事务
GROUP  BY s.sql_id, t.sql_text
ORDER  BY rows_proc DESC;

如果通过 SQL 统计信息无法确定，或者因为 NOLOGGING 操作导致统计失真，可以使用 Log Miner 挖掘归档日志。

这是最精准的证据，但操作较繁琐。

简要步骤：

1）确定故障时间段的归档日志文件。

2）使用 DBMS_LOGMNR.ADD_LOGFILE 添加归档日志。

3）使用 DBMS_LOGMNR.START_LOGMNR 启动挖掘。

4）查询 V$LOGMNR_CONTENTS：

SELECT seg_owner, seg_name, operation, count(*) 
FROM v$logmnr_contents 
GROUP BY seg_owner, seg_name, operation 
ORDER BY count(*) DESC;

这将精确告诉你：是哪个表（SEG_NAME）在发生海量的 INSERT/UPDATE/DELETE。

找到肇事会话（例如 SID 145, Serial# 1203）后，如果该操作是误操作或非关键业务，DBA 的第一反应应该是终止会话，释放日志资源

-- 杀死会话（生成 kill 语句）
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' 
FROM v$session 
WHERE sid = 145;

-- 执行
ALTER SYSTEM KILL SESSION '145,1203' IMMEDIATE;

针对 Checkpoint Not Complete，优化的核心目标是打破 LGWR（生产者）与 DBWR（消费者）之间的速度不对称。

我们建议采用“基础设施层兜底”与“应用层源头治理”相结合的双向策略。

这是解决此类故障最直接、见效最快的手段。

核心逻辑是通过增大日志容器，降低日志切换频率，从而延长 DBWR 完成检查点的时间窗口。

通过查询 V$LOG_HISTORY 评估业务高峰期的 Redo 生成速率。

-- 查看高峰期每小时产生的日志量 (GB)
SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') "Time",
       ROUND(SUM(blocks * block_size) / 1024  1024  1024, 2) "Redo_GB_Per_Hour"
FROM v$archived_log
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY 1;

Oracle 官方建议日志切换频率控制在 15-20 分钟/次。

若高峰期每小时产生 12GB 日志，则单个日志组建议大小为 12GB 3次 = 4GB。

实施示例： 假设当前日志组 (1, 2, 3) 仅为 500MB，需扩容至 4GB。

-- 1. 添加新的大日志组 (Group 4, 5, 6)
ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/oradata/redo04.log') SIZE 4G;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/oradata/redo05.log') SIZE 4G;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/u01/oradata/redo06.log') SIZE 4G;

-- 2. 强制切换，启用新日志组
ALTER SYSTEM SWITCH LOGFILE; -- 执行多次，直到 CURRENT 指向 Group 4/5/6

-- 3. 删除旧的小日志组 (需等待状态变为 INACTIVE)
-- 注意：如果状态是 ACTIVE，需执行 ALTER SYSTEM CHECKPOINT
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;

若受限于存储空间无法大幅增加单文件大小（例如文件系统剩余空间零散），增加日志组数量是有效的替代方案。

增加日志组相当于延长了 LGWR 的“环形赛道”。

从 3 组增加到 10 组，意味着 LGWR 即使跑得很快，也需要绕过更多的组才能追尾 Group 1，这为 DBWR 争取了宝贵的缓冲时间。

-- 在现有基础上追加更多日志组
ALTER DATABASE ADD LOGFILE GROUP 7 ('/u01/oradata/redo07.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE GROUP 8 ('/u01/oradata/redo08.log') SIZE 500M;
-- ... 重复直至 Group 10

如果日志已经很大但检查点依然未完成，说明磁盘写入速度（IOPS/吞吐量）是瓶颈。

某周五 14:00（业务高峰期） 

• 原则

  Redo Log 是顺序写（Sequential Write），Datafile 是随机写（Random Write）。

  混合存放会导致磁头频繁跳跃（对于机械硬盘）。

• 操作

  将 Redo Log 迁移至独立的物理磁盘或高性能 SSD 卷。

• 启用异步 I/O (AIO)

  检查：确认操作系统和数据库层面已启用 AIO，允许 DBWR 提交写请求后立即处理下一个，而不是阻塞等待。

  配置示例：

-- 1. 数据库层面启用
ALTER SYSTEM SET disk_asynch_io = TRUE SCOPE=SPFILE;

-- 2. 文件系统层面 (Linux)
-- 确保数据文件所在的文件系统支持 AIO (如 xfs, ext4, asm)
-- 检查参数 filesystemio_options
ALTER SYSTEM SET filesystemio_options = 'SETALL' SCOPE=SPFILE;

需重启实例生效。

检查 FAST_START_MTTR_TARGET 参数，该参数定义了实例崩溃后的最大恢复时间（秒）。

若设置过小（如 10-30 秒），Oracle 为了保证能在 30 秒内恢复实例，会极其频繁地触发增量检查点，导致 DBWR 始终处于高压状态。

-- 查看当前设置
SHOW PARAMETER FAST_START_MTTR_TARGET;

-- 优化方案 A：禁用自动调整 (让 Oracle 尽可能延后写入，性能优先)
ALTER SYSTEM SET FAST_START_MTTR_TARGET = 0;

-- 优化方案 B：放宽限制 (如设置为 5 分钟，平衡恢复时间与性能)
ALTER SYSTEM SET FAST_START_MTTR_TARGET = 300;

这是解决问题的治本之策。 

基础设施的扩容只能缓解症状，而优化的 SQL 能直接降低 Redo 的生成速率（Redo Generation Rate）。

• 反模式 (Anti-Pattern)

  循环内逐行提交。

  这将导致“Log Force Write”风暴。

-- [错误示范] 产生大量 Log File Sync 等待，加速日志切换
FOR i IN 1..1000000 LOOP
    UPDATE t_orders SET status = 1 WHERE id = i;
    COMMIT; -- 极度频繁的提交
END LOOP;

• 最佳实践

  每处理 N 行提交一次。

-- [优化示范]
DECLARE
    v_batch_size CONSTANT NUMBER := 5000;
BEGIN
    FOR i IN 1..1000000 LOOP
        UPDATE t_orders SET status = 1 WHERE id = i;

        -- 取模运算，每 5000 行提交一次
        IF MOD(i, v_batch_size) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
    COMMIT; -- 提交剩余的尾数
END;

对于 ETL 或数据迁移，常规 INSERT 是效率低下的根源。

• 原理

  直接路径加载绕过 Buffer Cache，且在 NOLOGGING 模式下仅记录数据字典变更，Redo 生成量可降低 90% 以上。

• 代码对比

-- [常规模式] 产生全量 Redo，容易触发 Checkpoint 不完整
INSERT INTO target_table SELECT * FROM source_table;

-- [优化模式] 几乎不产生 Redo
-- 1. 临时设置表为 NOLOGGING (对 Data Guard 有影响，需注意)
ALTER TABLE target_table NOLOGGING;

-- 2. 使用 APPEND 提示
INSERT /*+ APPEND */ INTO target_table SELECT * FROM source_table;

-- 3. 提交并恢复 LOGGING
COMMIT;
ALTER TABLE target_table LOGGING;

• 反模式

  盲目更新全字段，即使数据值未变。

  Oracle 仍需记录 Undo/Redo 镜像。

-- [错误示范] 即使状态已经是 1，也会产生 Redo
UPDATE t_users SET status = 1 WHERE id = 1001; 

• 最佳实践

  增加“变更检测”逻辑。

-- [优化示范]
UPDATE t_users 
SET status = 1 
WHERE id = 1001 
AND status != 1; -- 仅在状态确实不同时才执行写入

#Oracle Checkpoint not complete  #log file switch (checkpoint incomplete)  #Oracle 19c/23ai  性能优化  #LGWR 与 DBWR 工作原理 

#Redo Log 扩容步骤  #数据库卡顿故障排查  #Oracle 检查点机  #FAST_START_MTTR_TARGET 参数 

动动你的手指

给【安呀智数据坊】加个星标吧~

这样你就不会丢下我啦~