面试宝典:Oracle数据库log file switch (archiving needed)等待事件处理过程

原创于 2025-08-08 12:36:53 发布 · 656 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述

Oracle 数据库 `log file switch (archiving needed)` 等待事件深度解析

1. 等待事件本质

log file switch (archiving needed)：
当数据库处于归档模式时，LGWR进程尝试切换到下一个重做日志组，但发现目标日志组尚未完成归档而发生的等待。该事件直接反映归档进程无法跟上日志生成速度。
关键特性：
- 仅发生在归档模式(ARCHIVELOG)下
- 导致所有DML操作暂停
- 数据库活动完全停止的严重事件

与相关等待的区别：

等待事件	触发条件	解决方向
log file switch (archiving needed)	下一个日志组未完成归档	加速归档/优化日志配置
log file switch (checkpoint incomplete)	检查点未完成	优化检查点/增大日志
log file switch completion	常规日志切换	优化日志切换过程

2. 产生过程详解

LGWR写入日志：
当前日志组即将写满（通常90%）
尝试日志切换：
LGWR准备切换到下一个日志组
检查归档状态：
验证目标日志组是否已完成归档
未归档触发等待：
若目标组未归档，LGWR挂起并记录等待事件
唤醒归档进程：
LGWR通知ARCH进程加速归档
执行归档操作：
ARCH读取日志文件并写入归档存储
恢复日志切换：
归档完成后LGWR切换到新日志组

3. 高频场景

批量数据加载

-- 10亿行数据导入
INSERT /*+ APPEND */ INTO sales_history 
SELECT * FROM sales_staging;  -- 产生TB级重做

大型事务操作

-- 全表更新
UPDATE terabyte_table SET status='P';  -- 未分批提交

归档存储性能差

-- 归档到慢速NFS
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/nfs/archive';

归档进程不足

-- 高负载下只有2个归档进程
SHOW PARAMETER log_archive_max_processes;  -- 默认值2

日志文件过小

-- 100MB日志文件
SELECT group#, bytes/1024/1024 size_mb FROM v$log;  -- size_mb=100

4. 根本原因分类

归档性能问题

问题类型	典型案例
归档目标慢	归档到7200转HDD而非SSD
网络延迟高	归档到远程NFS，网络延迟>50ms
归档进程不足	log_archive_max_processes=2（高负载需8+）
归档格式低效	未使用压缩：`log_archive_format='arch_%t_%s_%r.arc'`

日志配置问题

日志文件过小：

SELECT group#, bytes/1024/1024 FROM v$log;  -- <500MB

日志组不足：

SELECT COUNT(*) FROM v$log;  -- 只有2组

日志缓冲区过小：
```
SHOW PARAMETER log_buffer;  -- <64MB
```

系统资源瓶颈

CPU资源不足：
ARCH进程无法获得足够CPU时间片
I/O带宽饱和：
存储网络仅1GbE，无法传输归档数据
内存压力：
大量进程竞争内存资源

应用设计问题

超大事务设计：

UPDATE huge_table ...;  -- 更新10亿行后提交

未用批量提交：

FOR i IN 1..1e9 LOOP
  INSERT ...; COMMIT;  -- 高频提交
END LOOP;

Oracle内部问题

ARCH进程僵死：
归档进程异常终止未恢复
Bug导致归档延迟：
- Bug 12902068：11g中归档进程内存泄漏
- Bug 21382587：12c RAC中归档协调失败
- Bug 29936857：19c中并行归档故障

5. 深度排查流程

步骤1：系统级诊断

-- 确认等待强度
SELECT event, total_waits, time_waited_micro
FROM v$system_event 
WHERE event = 'log file switch (archiving needed)';

-- 归档状态检查
SELECT dest_id, status, error 
FROM v$archive_dest 
WHERE status != 'VALID';

严重性阈值：
time_waited_micro > 300秒/小时 + 存在ERROR状态目标

步骤2：归档性能分析

-- 归档进程状态
SELECT process, status, sequence#, delay 
FROM v$managed_standby 
WHERE process LIKE 'ARC%';

-- 归档速度统计
SELECT 
    sequence#,
    first_time start_time,
    next_time end_time,
    ROUND((next_time - first_time)*86400) duration_sec,
    blocks*block_size/1024/1024 size_mb,
    ROUND((blocks*block_size/1024/1024)/NULLIF((next_time - first_time)*86400,0),2) mb_per_sec
FROM v$log_history
ORDER BY sequence# DESC;

性能指标：
mb_per_sec < 存储设备最大吞吐量50%

步骤3：日志配置检查

-- 日志组配置
SELECT 
    group#, 
    bytes/1024/1024 size_mb, 
    members, 
    status, 
    archived
FROM v$log;

-- 日志切换频率
SELECT 
    to_char(first_time, 'YYYY-MM-DD HH24:MI') time,
    COUNT(*) switches_per_min
FROM v$log_history
WHERE first_time > SYSDATE - 1/24
GROUP BY to_char(first_time, 'YYYY-MM-DD HH24:MI')
ORDER BY COUNT(*) DESC;

警报阈值：
日志切换 > 5次/分钟

步骤4：归档目标分析

-- 归档目标配置
SELECT dest_id, destination, status, error 
FROM v$archive_dest;

-- 目标性能测试（需OS访问）
$ dd if=/dev/zero of=/archive/test.img bs=1G count=10

步骤5：系统资源检查

-- CPU使用率
SELECT 
    metric_name,
    ROUND(value,2) value
FROM v$sysmetric 
WHERE metric_name IN ('CPU Usage Per Sec','I/O Megabytes per Second')
AND group_id=2;

-- 归档进程资源
SELECT 
    sid,
    program,
    sql_id,
    event,
    wait_time_micro
FROM v$session 
WHERE program LIKE '%ARC%';

步骤6：高负载事务分析

-- 重做生成排名
SELECT 
    sql_id,
    sql_text,
    executions,
    ROUND(io_interconnect_bytes/executions/1048576) redo_mb_per_exec
FROM v$sql
WHERE io_interconnect_bytes > 0
ORDER BY io_interconnect_bytes DESC
FETCH FIRST 10 ROWS ONLY;

-- 归档积压情况
SELECT 
    sequence#, 
    applied 
FROM v$archived_log 
WHERE applied='NO'
ORDER BY sequence# DESC;

步骤7：高级诊断

-- 归档进程追踪
ALTER SYSTEM SET events 'immediate trace name processstate level 10' sid = 'ARCH';

-- 重做日志转储
ALTER SYSTEM DUMP LOGFILE '+DATA/redo01.log';

-- 检查Bug
SELECT patch_id, description 
FROM dba_registry_sqlpatch 
WHERE patch_id IN (12902068, 21382587, 29936857);

6. 根治方案

紧急处置

-- 增加归档进程（动态）
ALTER SYSTEM SET log_archive_max_processes=8 SCOPE=MEMORY;

-- 手动切换并强制归档
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG CURRENT;

-- 临时禁用归档（极端情况）
ALTER DATABASE NOARCHIVELOG;  -- 需重启到mount状态

归档系统优化

升级存储设备：
迁移归档目录到NVMe SSD

启用归档压缩：

ALTER SYSTEM SET log_archive_compression='enable';
ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc.gz';

分离归档存储：

ALTER SYSTEM SET log_archive_dest_1='LOCATION=+FAST_ARCHIVE';

日志配置优化

-- 增大日志文件
ALTER DATABASE ADD LOGFILE GROUP 4 '+DATA' SIZE 2G;
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE GROUP 1;

-- 增加日志组
ALTER DATABASE ADD LOGFILE GROUP 5 '+DATA' SIZE 2G;
ALTER DATABASE ADD LOGFILE GROUP 6 '+DATA' SIZE 2G;

-- 优化归档进程
ALTER SYSTEM SET log_archive_max_processes=10 SCOPE=SPFILE;

应用优化

批量提交：

DECLARE
  TYPE id_array IS TABLE OF NUMBER;
  ids id_array := ...;
BEGIN
  FORALL i IN 1..ids.COUNT
    UPDATE accounts SET balance=balance-100 WHERE id=ids(i);
  COMMIT;
END;

NOLOGGING操作：

ALTER TABLE sales_history NOLOGGING;
INSERT /*+ APPEND NOLOGGING */ INTO sales_history ...;

分区操作：

-- 分批更新分区
BEGIN
  FOR p IN (SELECT partition_name FROM dba_tab_partitions WHERE table_name='SALES') 
  LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE sales MOVE PARTITION '||p.partition_name;
    COMMIT;
  END LOOP;
END;

补丁与升级

关键补丁：
- Bug 12902068：应用11.2.0.4.210119+ PSU
- Bug 21382587：安装12.1.0.2.220719补丁
- Bug 29936857：19c中应用Jan 2023 RU

升级建议：

19c引入智能归档压缩

ALTER SYSTEM SET "_arch_compression_algorithm"='zstd';  -- 更高压缩率

21c新增持续归档

ALTER SYSTEM SET log_archive_async=ON;  -- 异步归档模式

架构优化

使用Standby重定向：

ALTER SYSTEM SET log_archive_dest_2='SERVICE=standby LGWR ASYNC';

分离归档网络：
为归档流量配置专用网络链路

云存储集成：

ALTER SYSTEM SET log_archive_dest_1='SERVICE=cloud_archive';

分级归档：

-- 热归档到SSD
ALTER SYSTEM SET log_archive_dest_1='LOCATION=+SSD_ARCHIVE';

-- 冷归档到对象存储
ALTER SYSTEM SET log_archive_dest_2='SERVICE=s3_archive';

根治原则：
解决log file switch (archiving needed) = 加速归档处理（I/O优化） + 增大日志容量（文件大小/组数） + 优化事务模式（批量提交）。
核心在于确保归档速度始终大于日志生成速度，这是维持数据库高可用的关键。

通过此方案可彻底消除归档等待，保障数据库在归档模式下的高性能运行。

欢迎关注我的公众号《IT小Chen》