当前位置：首页 > article >正文

一次ORACLE 10G数据库REDO LOG损坏报错的解决办法ORA-00354: corrupt redo log block header

article 2025/3/15 14:34:27

近期一个客户联系反馈业务系统卡主，无法使用；远程连接查看，发现报错是显示归档空间满了，以为磁盘空间不足。

检查后发现归档空间充足，查看数据库ALERT日志，发现有大量报错：ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 9443 change 60986471209 time 02/13/2025 16:49:35

对应日志：

Completed: alter database mount exclusive
Thu Feb 13 18:39:42 2025
alter database open
Thu Feb 13 18:39:43 2025
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=13, OS id=5336
Thu Feb 13 18:39:43 2025
ARC0: Archival started
Thu Feb 13 18:39:43 2025
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Thu Feb 13 18:39:43 2025
Thread 1 advanced to log sequence 1480292 (thread open)
Thread 1 opened at log sequence 1480292
  Current log# 3 seq# 1480292 mem# 0: O:\DATABASE\FOOD\LOGFILES\LOG3AFOOD.ORA
  Current log# 3 seq# 1480292 mem# 1: O:\DATABASE\FOOD\LOGFILES\LOG3BFOOD.ORA
Successful open of redo thread 1
Thu Feb 13 18:39:43 2025
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Thu Feb 13 18:39:43 2025
SMON: enabling cache recovery
Thu Feb 13 18:39:43 2025
ARC0: Log corruption near block 9443 change 60986471209 time ?
Thu Feb 13 18:39:43 2025
Errors in file o:\database\food\trace\food_arc0_5336.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 9443 change 60986471209 time 02/13/2025 16:49:35
ORA-00312: online log 1 thread 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1BFOOD.ORA'
ORA-00312: online log 1 thread 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1AFOOD.ORA'

ARC0: All Archive destinations made inactive due to error 354
Committing creation of archivelog 'E:\DATABASE\FOOD\ARCHIVE\FOOD_T001_S1480288_R688744254.ARC' (error 354)
Thu Feb 13 18:39:43 2025
SMON: enabling tx recovery
Thu Feb 13 18:39:43 2025
ARCH: Archival stopped, error occurred. Will continue retrying
Thu Feb 13 18:39:43 2025
Database Characterset is ZHS16GBK
Thu Feb 13 18:39:43 2025
Errors in file o:\database\food\trace\food_arc0_5336.trc:
ORA-16038: log 1 sequence# 1480288 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 1 thread 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1AFOOD.ORA'
ORA-00312: online log 1 thread 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1BFOOD.ORA'

replication_dependency_tracking turned off (no async multimaster replication found)
WARNING: AQ_TM_PROCESSES is set to 0. System operation might be adversely affected.
ARC1 started with pid=14, OS id=10676
Thu Feb 13 18:39:44 2025
ARC1: Becoming the heartbeat ARCH
Thu Feb 13 18:39:44 2025
Completed: alter database open
Thu Feb 13 18:39:44 2025
ARC1: Log corruption near block 9443 change 60986471209 time ?
Thu Feb 13 18:39:44 2025
Errors in file o:\database\food\trace\food_arc1_10676.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 9443 change 60986471209 time 02/13/2025 16:49:35
ORA-00312: online log 1 thread 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1BFOOD.ORA'
ORA-00312: online log 1 thread 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1AFOOD.ORA'

ARC1: All Archive destinations made inactive due to error 354
Committing creation of archivelog 'E:\DATABASE\FOOD\ARCHIVE\FOOD_T001_S1480288_R688744254.ARC' (error 354)
Thu Feb 13 18:40:21 2025
Thread 1 cannot allocate new log, sequence 1480293
All online logs needed archiving
  Current log# 3 seq# 1480292 mem# 0: O:\DATABASE\FOOD\LOGFILES\LOG3AFOOD.ORA
  Current log# 3 seq# 1480292 mem# 1: O:\DATABASE\FOOD\LOGFILES\LOG3BFOOD.ORA
Thu Feb 13 18:40:44 2025
ARC0: Log corruption near block 9443 change 60986471209 time ?
Thu Feb 13 18:40:44 2025
Errors in file o:\database\food\trace\food_arc0_5336.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 9443 change 60986471209 time 02/13/2025 16:49:35
ORA-00312: online log 1 thread 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1BFOOD.ORA'
ORA-00312: online log 1 thread 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1AFOOD.ORA'

ARC0: All Archive destinations made inactive due to error 354
Committing creation of archivelog 'E:\DATABASE\FOOD\ARCHIVE\FOOD_T001_S1480288_R688744254.ARC' (error 354)
Thu Feb 13 18:41:44 2025
ARC1: Log corruption near block 9443 change 60986471209 time ?
Thu Feb 13 18:41:44 2025
Errors in file o:\database\food\trace\food_arc1_10676.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 9443 change 60986471209 time 02/13/2025 16:49:35
ORA-00312: online log 1 thread 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1BFOOD.ORA'
ORA-00312: online log 1 thread 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1AFOOD.ORA'

ARC1: All Archive destinations made inactive due to error 354
Committing creation of archivelog 'E:\DATABASE\FOOD\ARCHIVE\FOOD_T001_S1480288_R688744254.ARC' (error 354)
Thu Feb 13 18:42:44 2025
ARC0: Log corruption near block 9443 change 60986471209 time ?
Thu Feb 13 18:42:44 2025
Errors in file o:\database\food\trace\food_arc0_5336.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 9443 change 60986471209 time 02/13/2025 16:49:35
ORA-00312: online log 1 thread 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1BFOOD.ORA'
ORA-00312: online log 1 thread 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1AFOOD.ORA'

心里蓦然一惊，以为REDO损坏，那可是大麻烦了，这个小系统是个很古老的ORACLE 10G，没有有效备份的；

仔细一想，REDO损坏但是数据库系统没宕机，仔细分析日志，发现CURRENT状态的日志正常，需要切换日志，而此时发现需要重用的REDOLOG损坏（也未归档）所以报错，相当于没有可用的REDO LOG了；

这样解决办法就多了，比如CLEAR命令重置REDO LOG；或者再增加一组（删除损坏的一组）等。由于其他人处理已经重启数据库，停止在MOUNT状态了，这里使用了CLEAR方法临时解决了。

后续此系统还出现过类似问题，数据库运行在虚拟机环境的，综合考虑建议新建服务器做了迁移，目前运行稳定，算是解决了~~

解决步骤与命令：

D:\oracle\ora10\BIN>sqlplus SYS/ORACLE as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on 星期四 2月 13 19:01:32 2025

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

已连接到空闲例程。

SQL> STARTUP MOUNT;
ORACLE 例程已经启动。

Total System Global Area 1509949440 bytes
Fixed Size                  1299032 bytes
Variable Size             998247848 bytes
Database Buffers          494927872 bytes
Redo Buffers               15474688 bytes
数据库装载完毕。
SQL> set linesize 160 pagesize 100
SQL> col member for a60
SQL> select a.status,a.group#,b.member,a.BYTES/1024/1024 mb,a.SEQUENCE#,a.thread# from v$log a,v$logfile b where a.group
#=b.group# order by thread#,group#;

STATUS               GROUP# MEMBER                                                               MB  SEQUENCE#    THREAD
#
---------------- ---------- ------------------------------------------------------------ ---------- ---------- ---------
-
INACTIVE                  1 O:\DATABASE\FOOD\LOGFILES\LOG1AFOOD.ORA                               5    1480288
1
INACTIVE                  1 O:\DATABASE\FOOD\LOGFILES\LOG1BFOOD.ORA                               5    1480288
1
INACTIVE                  2 O:\DATABASE\FOOD\LOGFILES\LOG2AFOOD.ORA                               5    1480291
1
INACTIVE                  2 O:\DATABASE\FOOD\LOGFILES\LOG2BFOOD.ORA                               5    1480291
1
CURRENT                   3 O:\DATABASE\FOOD\LOGFILES\LOG3AFOOD.ORA                               5    1480292
1
CURRENT                   3 O:\DATABASE\FOOD\LOGFILES\LOG3BFOOD.ORA                               5    1480292
1

已选择6行。

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
第 1 行出现错误:
ORA-00350: 日志 1 (实例 food 的日志, 线程 1) 需要归档
ORA-00312: 联机日志 1 线程 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1AFOOD.ORA'
ORA-00312: 联机日志 1 线程 1: 'O:\DATABASE\FOOD\LOGFILES\LOG1BFOOD.ORA'


SQL> alter database clear unarchived logfile group 1;

数据库已更改。

SQL> ALTER DATABASE OPEN;

数据库已更改。

SQL> ALTER SYSTEM CHECKPOINT;

系统已更改。

查看全文

http://www.kler.cn/a/585646.html