暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片
首页 / 记一次ORA-00257 实战：归档失败排查修复全流程

记一次ORA-00257 实战：归档失败排查修复全流程

原创 孙莹 2025-12-01
1128

记一次ORA-00257 实战：归档失败排查修复全流程

lateliermars_highly_detailed_3D_miniature_Christmas_village_war_dd3412c9a294493389295c46056a283e.png

一、问题描述

11月28日晚上财务月底结账，前台应用报错：ORA-00257（归档相关错误，数据库会限制会话/需要处理归档问题后才能继续）。初步直觉：归档空间满 / 磁盘空间不足。

ORA-00257 是“归档出问题”的表象，常见两条路：

  1. 空间类问题：FRA/归档目录满、FRA 回收失败
  2. 链路类问题：归档目的地不可写、权限、IO/存储异常、redo 无法读取（本次属于这类）

二、排查过程

2.1 排除：操作系统与目录空间

先在 OS 侧确认磁盘空间、备份盘、数据盘占用都正常

#排除：操作系统与目录空间
[root@cmscqtmdb ~]# df -h
文件系统                         容量  已用  可用 已用% 挂载点
devtmpfs                          59G     0   59G    0% /dev
tmpfs                             59G     0   59G    0% /dev/shm
tmpfs                             59G   18M   59G    1% /run
tmpfs                             59G     0   59G    0% /sys/fs/cgroup
/dev/mapper/centos-root           50G  8.8G   42G   18% /
/dev/sdb1                        1.6T  959G  536G   65% /data
/dev/sda1                       1014M  185M  830M   19% /boot
/dev/mapper/vg_backup-lv_backup  3.5T  1.4T  2.1T   41% /backup
/dev/mapper/centos-home          145G  5.6G  140G    4% /home
tmpfs                             12G     0   12G    0% /run/user/1001
tmpfs                             12G   12K   12G    1% /run/user/42
tmpfs                             12G     0   12G    0% /run/user/0
[root@cmscqtmdb ~]# su - oracle
上一次登录：五 11月 28 18:35:42 CST 2025pts/1 上
[oracle@cmscqtmdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 28 18:36:32 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

#归档模式与 FRA 配置确认
SQL> archive log lost
SP2-0718: illegal ARCHIVE LOG option
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     235878
Next log sequence to archive   235878
Current log sequence           235963
SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /data/app/oracle/fast_recovery
                                                 _area
db_recovery_file_dest_size           big integer 400G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@cmscqtmdb ~]$ cd /data/app/oracle/fast_recovery_area/CQTMDB/archvielog
[oracle@cmscqtmdb archivelog]$ ls
2025_11_22  2025_11_23  2025_11_24  2025_11_25  2025_11_26  2025_11_27  2025_11_28
#没有出现“磁盘满 / FRA 满”的直接证据。
[oracle@cmscqtmdb archivelog]$ du -h .
4.0K    ./2025_11_22
4.0K    ./2025_11_25
4.0K    ./2025_11_23
4.0K    ./2025_11_27
4.0K    ./2025_11_24
4.0K    ./2025_11_26
17G     ./2025_11_28
17G     .
[oracle@cmscqtmdb archivelog]$

2.2 关键证据 1：RMAN 日志直接指向 redo04.log

归档模式开启，每天都有定时任务做Rman备份、同时清理归档。怀疑是Rman备份后没有清理归档日志。结果发现不是“没清理归档”导致满，是 log group 4 的某个序列（235878）无法归档。ORA-00742 lost write → redo 文件读到了异常写入（存储/IO 层面风险）

[oracle@cmscqtmdb archivelog]$ cd /backup/
[oracle@cmscqtmdb backup]$ ll -h *.bak
-rw-r----- 1 oracle oinstall 709G 11月 27 04:07 cmscqtm_full_2280_20251127.bak
-rw-r----- 1 oracle oinstall  54M 11月 27 04:07 cmscqtm_full_2281_20251127.bak
-rw-r----- 1 oracle oinstall 710G 11月 28 04:06 cmscqtm_full_2282_20251128.bak
-rw-r----- 1 oracle oinstall  54M 11月 28 04:07 cmscqtm_full_2283_20251128.bak
[oracle@cmscqtmdb backup]$ vi Rmanbackup20251128.log
#查看 RMAN 备份日志尾部，备份本体完成，但在执行归档切换时失败：
[oracle@cmscqtmdb backup]$ tail -20 Rmanbackup20251128.log
channel c1: starting piece 1 at 2025-11-28 04:07:06
channel c1: finished piece 1 at 2025-11-28 04:07:08
piece handle=/backup/cmscqtm_full_2283_20251128.bak tag=TAG20251128T030016 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
Finished backup at 2025-11-28 04:07:08

sql statement: alter system archive log current
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 11/28/2025 04:07:13
RMAN-11003: failure during parse/execution of SQL statement: alter system archive log current
ORA-16038: log 4 sequence# 235878 cannot be archived
ORA-00742: Log read detects lost write in thread %d sequence %d block %d
ORA-00312: online log 4 thread 1: '/data/app/oracle/oradata/cqtmdb/redo04.log'

RMAN>

Recovery Manager complete.
[oracle@cmscqtmdb backup]$

2.3 关键证据 2：alert 日志显示 11/25 起持续归档失败

检查alter告警日志文件，找到11月25日下午14:38就开始报错无法归档。

在 alert_cqtmdb.log 中可见从 11 月 25 日 14:38 起反复出现：

  • All Archive destinations made inactive due to error 742
  • Archival stopped, error occurred. Will continue retrying
  • 同样的错误栈：ORA-16038 + ORA-00742 + ORA-00312 redo04.log

结论：归档从 11/25 就卡住，只是到 11/28 前台才集中爆出 ORA-00257。

[oracle@cmscqtmdb backup]$ vi /data/app/oracle/diag/rdbms/cqtmdb/cqtmdb/trace/alert_cqtmdb.log
Tue Nov 25 13:53:49 2025
Archived Log entry 124910 added for thread 1 sequence 235877 ID 0x70ed81a5 dest 1:
Tue Nov 25 14:38:54 2025
Thread 1 advanced to log sequence 235879 (LGWR switch)
  Current log# 6 seq# 235879 mem# 0: /data/app/oracle/oradata/cqtmdb/redo06.log
Tue Nov 25 14:38:55 2025
ARC2: All Archive destinations made inactive due to error 742
ARC2: Closing local archive destination LOG_ARCHIVE_DEST_1: '/data/app/oracle/fast_recovery_area/CQTMDB/archivelog/2025_11_25/o1_mf_1_235878_nlbmqy3q_.arc' (error 742) (cqtmdb)
Committing creation of archivelog '/data/app/oracle/fast_recovery_area/CQTMDB/archivelog/2025_11_25/o1_mf_1_235878_nlbmqy3q_.arc' (error 742)
Deleted Oracle managed file /data/app/oracle/fast_recovery_area/CQTMDB/archivelog/2025_11_25/o1_mf_1_235878_nlbmqy3q_.arc
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance cqtmdb - Archival Error
ORA-16038: log 4 sequence# 235878 cannot be archived
ORA-00742: Log read detects lost write in thread %d sequence %d block %d
ORA-00312: online log 4 thread 1: '/data/app/oracle/oradata/cqtmdb/redo04.log'
Tue Nov 25 14:38:56 2025
ARC3: All Archive destinations made inactive due to error 742
ARC3: Closing local archive destination LOG_ARCHIVE_DEST_1: '/data/app/oracle/fast_recovery_area/CQTMDB/archivelog/2025_11_25/o1_mf_1_235878_nlbmr0dq_.arc' (error 742) (cqtmdb)
Committing creation of archivelog '/data/app/oracle/fast_recovery_area/CQTMDB/archivelog/2025_11_25/o1_mf_1_235878_nlbmr0dq_.arc' (error 742)
Deleted Oracle managed file /data/app/oracle/fast_recovery_area/CQTMDB/archivelog/2025_11_25/o1_mf_1_235878_nlbmr0dq_.arc
Tue Nov 25 14:38:58 2025
ARC0: All Archive destinations made inactive due to error 742
ARC0: Closing local archive destination LOG_ARCHIVE_DEST_1: '/data/app/oracle/fast_recovery_area/CQTMDB/archivelog/2025_11_25/o1_mf_1_235878_nlbmr1ql_.arc' (error 742) (cqtmdb)
Committing creation of archivelog '/data/app/oracle/fast_recovery_area/CQTMDB/archivelog/2025_11_25/o1_mf_1_235878_nlbmr1ql_.arc' (error 742)
Deleted Oracle managed file /data/app/oracle/fast_recovery_area/CQTMDB/archivelog/2025_11_25/o1_mf_1_235878_nlbmr1ql_.arc
ARC2: All Archive destinations made inactive due to error 742
ARC2: Closing local archive destination LOG_ARCHIVE_DEST_1: '/data/app/oracle/fast_recovery_area/CQTMDB/archivelog/2025_11_25/o1_mf_1_235878_nlbmr3ns_.arc' (error 742) (cqtmdb)
Committing creation of archivelog '/data/app/oracle/fast_recovery_area/CQTMDB/archivelog/2025_11_25/o1_mf_1_235878_nlbmr3ns_.arc' (error 742)
Deleted Oracle managed file /data/app/oracle/fast_recovery_area/CQTMDB/archivelog/2025_11_25/o1_mf_1_235878_nlbmr3ns_.arc
ARC3: All Archive destinations made inactive due to error 742

至此，问题已经清晰如下：

根因：online redo log 4（/data/…/redo04.log）损坏/出现 lost write，导致 sequence 235878 无法归档。
→ ARCn 持续失败并将归档目的地置 inactive
→ redo 无法顺利归档/切换
→ 最终前台报 ORA-00257

三、修复方案与操作步骤

3.1 风险命令提示

alter database clear unarchived logfile group 4;

这条命令的含义是：强制清除并重建该日志组，即便它未归档。
风险与前提：

  • ✅ 前提：日志组必须是 INACTIVE（已查询确认）
  • ⚠️ 风险：会导致该序列对应的 redo 无法再通过归档用于介质恢复；如果有 Data Guard/备库，更要谨慎评估（否则备库可能断链）

由于你在操作前确认 group 4 状态为 INACTIVE，且系统已无法归档该组，不是Data Guard环境，这是典型可用场景。

3.2 操作步骤

3.2.1 确认日志组状态

确认 group 4 状态为 INACTIVE，且 archived=NO。确认在线重做日志大小

[oracle@cmscqtmdb backup]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 28 18:53:09 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select group#,sequence#,archived,status from v$log;

    GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
         4     235878 NO  INACTIVE
         5     235966 NO  CURRENT
         6     235965 YES INACTIVE

SQL> select group#, member from v$logfile;

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         4
/data/app/oracle/oradata/cqtmdb/redo04.log

         5
/data/app/oracle/oradata/cqtmdb/redo05.log

         6
/data/app/oracle/oradata/cqtmdb/redo06.log


SQL> select group#, bytes, status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         4 1073741824 INACTIVE
         5 1073741824 CURRENT
         6 1073741824 INACTIVE

SQL>

3.2.2 先新增日志组（避免日志组数量不足导致更难切换）

先扩了两组：

SQL> alter database add logfile group 7 '/data/app/oracle/oradata/cqtmdb/redo07.log' size 1g;

Database altered.

SQL> alter database add logfile group 8 '/data/app/oracle/oradata/cqtmdb/redo08.log' size 1g;

Database altered.

SQL> select group#, bytes, status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         4 1073741824 INACTIVE
         5 1073741824 CURRENT
         6 1073741824 INACTIVE
         7 1073741824 UNUSED
         8 1073741824 UNUSED

SQL>

3.2.3 尝试 drop 失败

这一步失败是正常的：Oracle 不允许直接 drop “需要归档”的日志组。

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00350: log 4 of instance cqtmdb (thread 1) needs to be archived
ORA-00312: online log 4 thread 1: '/data/app/oracle/oradata/cqtmdb/redo04.log'


SQL>

3.2.4 清除未归档日志组（核心修复）

清理后我们可以查询group 4 状态为UNUSED。

SQL> alter database clear unarchived logfile group 4;

Database altered.

SQL> select group#, bytes, status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         4 1073741824 UNUSED
         5 1073741824 INACTIVE
         6 1073741824 INACTIVE
         7 1073741824 CURRENT
         8 1073741824 UNUSED

SQL>

3.2.5 drop 损坏日志组

SQL> alter database drop logfile group 4;

Database altered.

SQL> select group#, bytes, status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         5 1073741824 INACTIVE
         6 1073741824 INACTIVE
         7 1073741824 CURRENT
         8 1073741824 UNUSED

SQL>

3.2.6 切换与 checkpoint 验证恢复正常

  1. alter system switch logfile; 能成功
  2. select * from v$log; 日志可轮转，未再卡在某个序列
  3. alert 日志不再出现：
    • All Archive destinations made inactive due to error 742
    • ORA-16038/ORA-00742/ORA-00312
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select group#, bytes, status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         5 1073741824 INACTIVE
         6 1073741824 CURRENT
         7 1073741824 INACTIVE
         8 1073741824 INACTIVE

SQL> select group#,sequence#,archived,status from v$log;

    GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
         5     236026 NO  CURRENT
         6     236025 YES INACTIVE
         7     236023 YES INACTIVE
         8     236024 YES INACTIVE
         
SQL>

四、复盘与预防

4.1 预防建议

  • redo 多成员 multiplexing：同一组 redo 配置多个 member，放不同磁盘/存储路径（降低单文件损坏风险）
  • 监控归档失败：对 ORA-16038/ORA-00257、v$archive_dest_status 的 error 状态做告警
  • 存储侧排查：lost write 往往指向 IO/存储/文件系统层问题，建议同步存储团队做底层健康检查

4.2 本次经验

ORA-00257 不要只盯“空间”，一定要沿着 RMAN/alert 的错误栈 把归档链路追到 具体日志组/具体文件。

五、总结

本次 ORA-00257 的根因并非归档空间不足，而是 redo04.log 出现 ORA-00742 lost write 导致归档无法推进；通过新增日志组、clear unarchived logfile 清除损坏组并 drop，最终恢复日志切换和归档。该操作有效但存在“丢失未归档 redo”风险，操作前需确认日志组状态、备份与（如有）备库影响。

墨力计划oracleoracle11g
最后修改时间：2025-12-02 10:33:04
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论