标签归档：ORA-600 3020

通过alert日志回顾其他dba oracle异常恢复故障处理以及后续open数据库操作

发表于 2026 年 2 月 27 日 由 惜分飞

客户有一个数据库故障,是其他工程师进行恢复操作,最后搞不定通过朋友介绍找到我的.我通过分析alert日志,大概追述故障经过
1. 数据库断电之后启动报ORA-01172 ORA-01151错误,直接启动数据库失败,从报错看是由于数据库在open过程中前滚redo日志异常导致

Thu Feb 26 06:48:35 2026
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 23 processes
Started redo scan
Completed redo scan
 read 73194 KB redo, 37226 data blocks need recovery
Thu Feb 26 06:48:49 2026
Started redo application at
 Thread 1: logseq 869366, block 3
Recovery of Online Redo Log: Thread 1 Group 2 Seq 869366 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
Thu Feb 26 06:48:50 2026
RECOVERY OF THREAD 1 STUCK AT BLOCK 16938 OF FILE 3
Slave exiting with ORA-1172 exception
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_p016_4672.trc:
ORA-01172: recovery of thread 1 stuck at block 16938 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Thu Feb 26 06:49:00 2026
Completed redo scan
 read 73194 KB redo, 37226 data blocks need recovery
Started redo application at
 Thread 1: logseq 869366, block 3
Thu Feb 26 06:49:10 2026
Recovery of Online Redo Log: Thread 1 Group 2 Seq 869366 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
RECOVERY OF THREAD 1 STUCK AT BLOCK 16938 OF FILE 3
Aborting crash recovery due to error 1172
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_9260.trc:
ORA-01172: recovery of thread 1 stuck at block 16938 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_9260.trc:
ORA-01172: recovery of thread 1 stuck at block 16938 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: alter database open...

2. 尝试recover database报ORA-600 3020、ORA-600 17147、ORA-600 17114、ORA-600 17182等错误,这个报错比较明确是由于redo的block信息和datafile的block不一致,导致实例recover database失败

Thu Feb 26 06:50:54 2026
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 24 slaves
Thu Feb 26 06:50:57 2026
Recovery of Online Redo Log: Thread 1 Group 2 Seq 869366 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
Thu Feb 26 06:50:59 2026
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_pr0i_9232.trc  (incident=79538):
ORA-00600: internal error code, arguments: [3020], [3], [16934], [12599846], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 16934, file offset is 138723328 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF'
ORA-10560: block type 'KTU UNDO BLOCK'
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_79538\orcl_pr0i_9232_i79538.trc
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_pr0i_9232.trc  (incident=79539):
ORA-00600: internal error code, arguments: [17114], [0x0381C4C60], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [17182], [0x0381C6948], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [17147], [0x0381C4C60], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [3020], [3], [16934], [12599846], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 16934, file offset is 138723328 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF'
ORA-10560: block type 'KTU UNDO BLOCK'

3. 使用隐含参数尝试强制拉库,报ORA-600 2662错误,导致强制拉库没有成功,这个错误相对比较简单,一般修改数据库scn即可

Thu Feb 26 07:04:39 2026
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 16794964253372
Resetting resetlogs activation ID 1548038913 (0x5c453301)
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_8712.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG'
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_8712.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG'
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_8712.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG'
Thu Feb 26 07:04:49 2026
Setting recovery target incarnation to 3
Thu Feb 26 07:04:50 2026
Assigning activation ID 1753992092 (0x688bcb9c)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
Thu Feb 26 07:04:50 2026
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Feb 26 07:04:50 2026
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_8712.trc  (incident=82031):
ORA-00600: internal error code, arguments: [2662], [3910], [1642126020], [3910], [1642126047], [4194432]
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_8712.trc:
ORA-00600: internal error code, arguments: [2662], [3910], [1642126020], [3910], [1642126047], [4194432]
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_8712.trc:
ORA-00600: internal error code, arguments: [2662], [3910], [1642126020], [3910], [1642126047], [4194432]
Error 600 happened during db open, shutting down database
USER (ospid: 8712): terminating the instance due to error 600
Thu Feb 26 07:05:00 2026
Instance terminated by USER, pid = 8712
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (8712) as a result of ORA-1092

4. 尝试重新open库,数据库open成功但是报ORA-600 kturbleurec1、ORA-600 kcbgtcr_13错误，数据库运行一会就直接crash，这个错误一般是由于undo异常导致

Thu Feb 26 07:09:10 2026
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 23 processes
Started redo scan
Completed redo scan
 read 0 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 1, block 3, scn 16794964253378
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 1, block 3, scn 16794964273379
 0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Thu Feb 26 07:09:14 2026
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Feb 26 07:09:14 2026
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
Thu Feb 26 07:09:21 2026
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_p002_10096.trc  (incident=84847):
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_84847\orcl_p002_10096_i84847.trc
Thu Feb 26 07:09:21 2026
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4600.trc  (incident=84831):
ORA-00600: internal error code, arguments: [kcbgtcr_13], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_84831\orcl_ora_4600_i84831.trc
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4600.trc  (incident=84832):
ORA-00600: internal error code, arguments: [kcbgtcr_13], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_84832\orcl_ora_4600_i84832.trc

客户那边还做了各种恢复尝试,最终依旧无法正常open库,让我这边进行恢复支持.由于客户库不大,而且可以提供数据进行恢复,我让客户发生我数据之后,在本地电脑上进行恢复,下载文件之后,重命名相关路径然后尝试open库

SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-16433: 必须以读/写模式打开数据库。

重建控制文件

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 2月 27 14:36:57 2026

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

已连接到空闲例程。

SQL> startup nomount pfile='d:/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area 4275781632 bytes
Fixed Size                  2182592 bytes
Variable Size             973079104 bytes
Database Buffers         3288334336 bytes
Redo Buffers               12185600 bytes
SQL> @rectl.sql

控制文件已创建。

SQL> recover database;
完成介质恢复。

尝试open库报ORA-600 2663错误

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2663], [3910], [1642323772],
[3910], [1642327019], [], [], [], [], [], [], []
进程 ID: 27296
会话 ID: 14 序列号: 3

使用Patch_SCN工具修改数据库scn
ora-600-2663-patch_scn

再次尝试open数据库

SQL> recover database;
完成介质恢复。
SQL> alter database open ;

数据库已更改。

alert日志报ORA-600 6856错误

Fri Feb 27 14:40:03 2026
QMNC started with pid=62, OS id=18360 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open 
Fri Feb 27 14:40:03 2026
Errors in file c:\app\xff\diag\rdbms\orcl\orcl\trace\orcl_p001_21540.trc  (incident=15787):
ORA-00600: 内部错误代码, 参数: [6856], [0], [479], [], [], [], [], [], [], [], [], []
Incident details in: c:\app\xff\diag\rdbms\orcl\orcl\incident\incdir_15787\orcl_p001_21540_i15787.trc
Fri Feb 27 14:40:04 2026
Starting background process CJQ0
Fri Feb 27 14:40:04 2026
CJQ0 started with pid=63, OS id=15592 
Doing block recovery for file 8 block 1541549
Resuming block recovery (PMON) for file 8 block 1541549
Block recovery from logseq 3, block 108 to scn 16794965431265
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
  Mem# 0: H:\BAIDUNETDISK\20260227\REDO03.LOG
Block recovery completed at rba 3.16415.16, scn 3910.1643303906
Fri Feb 27 14:40:04 2026
Trace dumping is performing id=[cdmp_20260227144004]
SMON: ignoring slave err,downgrading to serial rollback
Errors in file c:\app\xff\diag\rdbms\orcl\orcl\trace\orcl_smon_21696.trc  (incident=15723):
ORA-00600: 内部错误代码, 参数: [6856], [0], [479], [], [], [], [], [], [], [], [], []
Incident details in: c:\app\xff\diag\rdbms\orcl\orcl\incident\incdir_15723\orcl_smon_21696_i15723.trc
………………
ORACLE Instance orcl (pid = 15) - Error 607 encountered while recovering transaction (3, 10) on object 81310.
Errors in file c:\app\xff\diag\rdbms\orcl\orcl\trace\orcl_smon_21696.trc:
ORA-00607: 当更改数据块时出现内部错误
ORA-00600: 内部错误代码, 参数: [6856], [0], [479], [], [], [], [], [], [], [], [], []
Process debug not enabled via parameter _debug_enable
Trace dumping is performing id=[cdmp_20260227144011]
PMON (ospid: 23760): terminating the instance due to error 474

该错误是undo异常引起,屏蔽掉异常undo之后,正常open,并顺利导出所有数据,完成本次恢复任务
dmp

发表在 Oracle备份恢复 | 标签为 , , , , , , , , , | 评论关闭

ORA-01172 ORA-01151故障处理

发表于 2025 年 11 月 1 日 由 惜分飞

接手客户一个云平台硬件故障恢复之后,数据库无法启动的case,通过分析alert日志,发现数据库在open过程中报ORA-01172: recovery of thread 1 stuck at block 2220167 of file 262,ORA-01151: use media recovery to recover block, restore backup if needed等相关错误(其实也就是在做实例恢复的过程中报了logically corrupt导致无法完成实例恢复)

Sat Nov 01 14:29:10 2025
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 7034 KB redo, 937 data blocks need recovery
Started redo application at
 Thread 1: logseq 296553, block 389408
Recovery of Online Redo Log: Thread 1 Group 3 Seq 296553 Reading mem 0
  Mem# 0: /data/orcl/onlinelog/redo03a.log
Sat Nov 01 14:29:11 2025
Hex dump of (file 262, block 2220584) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p009_2648.trc
Sat Nov 01 14:29:11 2025
Hex dump of (file 262, block 2218886) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p007_2644.trc
Reading datafile '/data/orcl/datafile/xifenfei12.dbf' for corruption at rdba: 0x41a1db86 (file 262, block 2218886)
Reading datafile '/data/orcl/datafile/xifenfei12.dbf' for corruption at rdba: 0x41a1e228 (file 262, block 2220584)
Sat Nov 01 14:29:11 2025
Hex dump of (file 262, block 2219845) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p008_2646.trc
Reading datafile '/data/orcl/datafile/xifenfei12.dbf' for corruption at rdba: 0x41a1df45 (file 262, block 2219845)
Sat Nov 01 14:29:11 2025
Hex dump of (file 262, block 2220167) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p001_2632.trc
Reading datafile '/data/orcl/datafile/xifenfei12.dbf' for corruption at rdba: 0x41a1e087 (file 262, block 2220167)
Reread (file 262, block 2218886) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 2218886 OF FILE 262
Reread (file 262, block 2220584) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 2220584 OF FILE 262
Reread (file 262, block 2219845) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 2219845 OF FILE 262
Reread (file 262, block 2220167) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 2220167 OF FILE 262
Sat Nov 01 14:29:26 2025
Slave exiting with ORA-1172 exception
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p007_2644.trc:
ORA-01172: recovery of thread 1 stuck at block 2218886 of file 262
ORA-01151: use media recovery to recover block, restore backup if needed
Sat Nov 01 14:29:26 2025
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p008_2646.trc:
ORA-10388: parallel query server interrupt (failure)
Sat Nov 01 14:29:26 2025
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p009_2648.trc:
ORA-10388: parallel query server interrupt (failure)
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p008_2646.trc:
ORA-10388: parallel query server interrupt (failure)
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p009_2648.trc:
ORA-10388: parallel query server interrupt (failure)
Sat Nov 01 14:29:26 2025
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p001_2632.trc:
ORA-10388: parallel query server interrupt (failure)
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p001_2632.trc:
ORA-10388: parallel query server interrupt (failure)
Sat Nov 01 14:29:26 2025
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 7034 KB redo, 937 data blocks need recovery
Started redo application at
 Thread 1: logseq 296553, block 389408
Recovery of Online Redo Log: Thread 1 Group 3 Seq 296553 Reading mem 0
  Mem# 0: /data/orcl/onlinelog/redo03a.log
Hex dump of (file 262,block 2220167) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2606.trc
Reading datafile '/data/orcl/datafile/xifenfei12.dbf'for corruption at rdba: 0x41a1e087 (file 262,block 2220167)
Reread (file 262, block 2220167) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 2220167 OF FILE 262
Aborting crash recovery due to error 1172
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2606.trc:
ORA-01172: recovery of thread 1 stuck at block 2220167 of file 262
ORA-01151: use media recovery to recover block, restore backup if needed
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2606.trc:
ORA-01172: recovery of thread 1 stuck at block 2220167 of file 262
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: ALTER DATABASE OPEN...

接手故障之后,尝试recover database恢复,结果报ORA-600 4552错误

SQL> recover database;
ORA-10562: Error occurred while applying redo to data block (file# 262, block#
2222153)
ORA-10564: tablespace XIFENFEI
ORA-01110: data file 262: '/data/orcl/datafile/xifenfei12.dbf'
ORA-10560: block type '0'
ORA-00600: internal error code, arguments: [4552], [1], [0], [], [], [], [],
[], [], [], [], []

关于ORA-600 4552对应的alert日志信息

Sat Nov 01 17:49:58 2025
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 16 slaves
Sat Nov 01 17:49:59 2025
Recovery of Online Redo Log: Thread 1 Group 3 Seq 296553 Reading mem 0
  Mem# 0: /data/orcl/onlinelog/redo03a.log
Sat Nov 01 17:49:59 2025
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pr0c_28770.trc  (incident=1018821):
ORA-00600: internal error code, arguments: [4552], [1], [0], [], [], [], [], [], [], [], [], []
Incident details in:/u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_1018821/orcl_pr0c_28770_i1018821.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sat Nov 01 17:50:03 2025
Sweep [inc][1018821]: completed
Sweep [inc2][1018821]: completed
Slave exiting with ORA-10562 exception
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pr0c_28770.trc:
ORA-10562: Error occurred while applying redo to data block (file# 262, block# 2222153)
ORA-10564: tablespace xifenfei
ORA-01110: data file 262: '/data/orcl/datafile/xifenfei12.dbf'
ORA-10560: block type '0'
ORA-00600: internal error code, arguments: [4552], [1], [0], [], [], [], [], [], [], [], [], []
Recovery Slave PR0C previously exited with exception 10562
Media Recovery failed with error 448
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pr00_28732.trc:
ORA-00283: recovery session canceled due to errors
ORA-00448: normal completion of background process
ORA-10562 signalled during: ALTER DATABASE RECOVER  database  ...

无法整个库级别recover,尝试数据文件recover操作

SQL> recover datafile 1;
Media recovery complete.
…………
SQL> recover datafile 22,23,24,26,25,27,28,29,30;
Media recovery complete.
…………
SQL>    recover datafile  251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261;
Media recovery complete.
SQL> recover datafile 262;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [262], [2215808],
[1101123456], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 262, block# 2215808,
file offset is 972029952 bytes)
ORA-10564: tablespace XIFENFEI
ORA-01110: data file 262: '/data/orcl/datafile/xifenfei12.dbf'
ORA-10560: block type '0'

SQL> recover datafile 263;
Media recovery complete.

出file# 262数据文件之外,其他文件全部recover成功,对应的ORA-600 3020错误相关alert日志信息

Sat Nov 01 17:53:37 2025
ALTER DATABASE RECOVER  datafile 262  
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 296553 Reading mem 0
  Mem# 0: /data/orcl/onlinelog/redo03a.log
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28561.trc  (incident=1018717):
ORA-00600: internal error code, arguments: [3020], [262], [2215808], [1101123456], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 262, block# 2215808, file offset is 972029952 bytes)
ORA-10564: tablespace xifenfei
ORA-01110: data file 262: '/data/orcl/datafile/xifenfei12.dbf'
ORA-10560: block type '0'
Incident details in:/u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_1018717/orcl_ora_28561_i1018717.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Media Recovery failed with error 600
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 262  ...

对于这种情况,有两种处理方式:
1)在recover过程中对于报错的block标记为坏块,然后继续恢复,这样正常应用日志成功,再把标记的坏块修复好
2)直接修改该文件头跳过该文件跳过这些block的应用日志,直接骗过数据库
在本case中由于客户急着恢复业务,需要尽快处理,所以采用了第一个方案,这里我使用自研的m_scn(modify_scn)工具快速修改相关数据文件信息

[oracle@host-172-18-50-10 tmp]$ cat 1.txt
1@/data/orcl/datafile/system01.dbf
262@/data/orcl/datafile/xifenfei12.dbf
[oracle@host-172-18-50-10 tmp]$ ./m_scn 1.txt
Please Enter Password: 

===== Starting Datafile Header modification program =====
Datafile list file: 1.txt
Operation Mode: Only Modify Datafile Header CheckPoint
Block Size: 8192
Log Path: /tmp/modify_scn
---------------------------------------------------------
Preparing Datafile list file...
Verifying Datafile existence...
Datafile verification passed
Initializing working directory...
Recovery script created: /tmp/modify_scn/backup/recover_datafile.sh
---------------------------------------------------------
Starting Datafile Header processing (total 2 files)...
[1/2] Processing Datafile Header: /data/orcl/datafile/system01.dbf (File number: 1)
  - Skipping file number 1 (control file)
---------------------------------------------------------
[2/2] Processing Datafile Header: /data/orcl/datafile/xifenfei12.dbf (File number: 262)
  - Backing up Datafile header...
  - Executing Datafile Header modification with block size 8192...
  - Datafile Header processing completed
---------------------------------------------------------
Cleaning up temporary files...
================= All operations completed =================

Note: Execute /tmp/modify_scn/backup/recover_datafile.sh operation for rollback

然后查询相关scn信息,确认修改文件信息没有问题并尝试recover 262号文件

[oracle@host-172-18-50-10 tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 1 18:02:18 2025

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

Connected to an idle instance.

SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 4.1823E+10 bytes
Fixed Size                  2262368 bytes
Variable Size            4294970016 bytes
Database Buffers         3.7447E+10 bytes
Redo Buffers               78614528 bytes
Database mounted.
SQL> set pages 10000
SQL> set numw 16
SQL> SELECT status,
  2  checkpoint_change#,
  3  to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,
  4  last_change#,
  5  count(*) ROW_NUM
FROM v$datafile
  6    7  GROUP BY status, checkpoint_change#, checkpoint_time,last_change#
ORDER BY status, checkpoint_change#, checkpoint_time;
  8  

set numw 16
col CHECKPOINT_TIME for a40
set lines 150
set pages 1000
SELECT status,
to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#,
count(*) ROW_NUM
FROM v$datafile_header
GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy
ORDER BY status, checkpoint_change#, checkpoint_time;

SELECT dd.FILE#,
dd.NAME,
dd.STATUS,
dd.checkpoint_change# dfile_chkp_change,
dh.checkpoint_change# dfile_hed_chkp_change,
dh.recover,
dh.fuzzy
FROM v$datafile dd,
v$datafile_header dh
WHERE dd.FILE#=dh.FILE#
AND dd.checkpoint_change#<>dh.checkpoint_change#;


STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME         LAST_CHANGE#          ROW_NUM
------- ------------------ ------------------- ---------------- ----------------
ONLINE      16816934458875 2025-11-01 17:58:28   16816934458875              258
RECOVER     16816934368799 2025-11-01 05:29:39   16816934456943                1
SYSTEM      16816934458875 2025-11-01 17:58:28   16816934458875                4

SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6  
STATUS  CHECKPOINT_TIME                          FUZ CHECKPOINT_CHANGE#          ROW_NUM
------- ---------------------------------------- --- ------------------ ----------------
OFFLINE 2025-11-01 17:58:28                      NO      16816934458875                1
ONLINE  2025-11-01 17:58:28                      NO      16816934458875              262

SQL> SQL>   2    3    4    5    6    7    8    9   10   11  
           FILE#
----------------
NAME
----------------------------------------------------------------------------------
STATUS  DFILE_CHKP_CHANGE DFILE_HED_CHKP_CHANGE REC FUZ
------- ----------------- --------------------- --- ---
             262
/data/orcl/datafile/xifenfei12.dbf
RECOVER    16816934368799        16816934458875 YES NO

SQL> recover datafile 262;
Media recovery complete.

open数据库成功

SQL> alter database open;

Database altered.

Sat Nov 01 18:06:00 2025
ALTER DATABASE OPEN
Thread 1 opened at log sequence 296554
  Current log# 1 seq# 296554 mem# 0: /data/orcl/onlinelog/redo01a.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[33941] Successfully onlined Undo Tablespace 143.
Undo initialization finished serial:0 start:12793234 end:12793304 diff:70 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sat Nov 01 18:06:01 2025
QMNC started with pid=20, OS id=33973 
Completed: ALTER DATABASE OPEN

至此基本上完成本次恢复任务,后续根据alert日志,有个别表可能由于在file# 262中丢失一些数据导致不一致的问题进行单独,其他没有太大问题,最快帮客户恢复了业务

发表在 Oracle备份恢复 | 标签为 , , , , , , | 评论关闭

断电引起redo和数据文件不一致故障恢复

发表于 2024 年 8 月 4 日 由 惜分飞

有些时候故障总是来的让人非常意外,这个在准备停机迁移数据库之前的几分钟由于某种原因直接导致主机掉电,再次开机数据库无法启动

Sat Aug 03 23:10:37 2024
Successful mount of redo thread 1, with mount id 3696805928
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
Sat Aug 03 23:10:43 2024
alter database open
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_6808.trc:
ORA-01113: 文件 21 需要介质恢复
ORA-01110: 数据文件 21: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\XIFENFEI.DBF'
ORA-1113 signalled during: alter database open...

尝试数据库恢复各种报错ORA-600 kdourp_inorder2,ORA-600 3020,ORA-7445 kdxlin等

ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFF\ARCHIVELOG\2024_08_03\O1_MF_1_1159998_MBW605HP_.ARC
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFF\ARCHIVELOG\2024_08_03\O1_MF_1_1159999_MBW63QBY_.ARC
Sat Aug 03 23:22:10 2024
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xC] [PC:0x14306B54A, kdxlin()+4432]
Sat Aug 03 23:22:10 2024
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xC] [PC:0x14306B54A, kdxlin()+4432]
Sat Aug 03 23:22:10 2024
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr25_7740.trc  (incident=132557):
ORA-00600: internal error code, arguments: [kdourp_inorder2], [4], [22], [44], [44], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_132557\xff_pr25_7740_i132557.trc
ERROR: Unable to normalize symbol name for the following short stack (at offset 213):
dbgexProcessError()+200<-dbgeExecuteForError()+65<-dbgePostErrorKGE()+2269<-dbkePostKGE_kgsf()+77<-kgeade()+562
<-kgerelv()+151<-kgerev()+45<-kgerec5()+60<-sss_xcpt_EvalFilterEx()+1862<-sss_xcpt_EvalFilter()+174
<-.1.4_5+59<-00007FFCB5E2C92F<-00007FFCB5E3D82D<-00007FFCB5DE916B<-00007FFCB5E3C9EE<-kdxlin()+4432
<-kco_issue_callback()+196<-kcoapl()+746<-kcbr_apply_change()+6156<-kcbr_mapply_change()+1162
<-kcbrapply()+2297<-kcbr_apply_pending()+2931<-krp_slave_apply()+1155<-krp_slave_main()+4010<-ksvrdp()+2580
<-opirip()+904<-opidrv()+906<-sou2o()+98<-opimai_real()+280<-opimai()+191<-BackgroundThreadStart()+646
<-00007FFCB562168D<-00007FFCB5E14629
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr1w_6472.trc  (incident=132485):
ORA-07445: exception encountered: core dump [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A]
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_132485\xff_pr1w_6472_i132485.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr2o_7472.trc  (incident=132709):
ORA-07445: exception encountered: core dump [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] 
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_132709\xff_pr2o_7472_i132709.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sat Aug 03 23:22:11 2024
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr18_7812.trc  (incident=132301):
ORA-00600: internal error code, arguments: [3020], [62], [517633], [260564481], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 62, block# 517633, file offset is 4240449536 bytes)
ORA-10564: tablespace HSEMR_TAB
ORA-01110: data file 62: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\EMR006.DBF'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Sat Aug 03 23:22:56 2024
Slave exiting with ORA-10562 exception
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr25_7740.trc:
ORA-10562: Error occurred while applying redo to data block (file# 64, block# 508263)
ORA-10564: tablespace HSEMR_TAB
ORA-01110: data file 64: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\HSEMR_TAB008.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 467202
ORA-00600: internal error code, arguments: [kdourp_inorder2], [4], [22], [44], [44], [], [], []
Sat Aug 03 23:22:56 2024
Slave exiting with ORA-10562 exception
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr1w_6472.trc:
ORA-10562: Error occurred while applying redo to data block (file# 65, block# 498512)
ORA-10564: tablespace HSEMR_TAB
ORA-01110: data file 65: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\EMR009.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 467200
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] 
Sat Aug 03 23:22:57 2024
Media Recovery failed with error 448
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr00_6732.trc:
ORA-00283: recovery session canceled due to errors
ORA-00448: normal completion of background process
Sat Aug 03 23:22:57 2024
ORA-600 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER CANCEL 
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...

通过分析确认有部分数据文件和redo信息不匹配,导致无法正常recover成功

SQL> recover datafile 77;
完成介质恢复。
SQL> recover datafile 78;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [78], [473221], [327628933], [], [], [],
[], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 78, block# 473221, file
offset is 3876626432 bytes)
ORA-10564: tablespace HSEMR_TAB
ORA-01110: 数据文件 78: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\HIS23.DBF'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'

SQL> recover datafile 66;
ORA-00279: 更改 6029114092 (在 08/03/2024 19:44:05 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFF\ARCHIVELOG\2024_08_03\O1_MF_1_115999

9_MBW63QBY_.ARC
ORA-00280: 更改 6029114092 (用于线程 1) 在序列 #1159999 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
已应用的日志。
完成介质恢复。
SQL> recover datafile 65;
ORA-00279: 更改 6029114092 (在 08/03/2024 19:44:05 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFF\ARCHIVELOG\2024_08_03\O1_MF_1_115999

9_MBW63QBY_.ARC
ORA-00280: 更改 6029114092 (用于线程 1) 在序列 #1159999 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: 恢复会话因错误而取消
ORA-10562: Error occurred while applying redo to data block (file# 65, block#
498544)
ORA-10564: tablespace HSEMR_TAB
ORA-01110: 数据文件 65: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\EMR009.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 467200
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC]
[PC:0x14306B54A] [UNABLE_TO_READ] []


ORA-01112: 未启动介质恢复

对于最终无法正常recover成功数据文件,使用Oracle数据库恢复利器：Oracle Recovery Tools工具快速调整scn
oracle-recovery-tools

然后重建ctl，recover 数据库并open成功

Sun Aug 04 01:01:51 2024
Successful mount of redo thread 1, with mount id 3696824638
Completed: CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 200
    MAXINSTANCES 8
    MAXLOGXFFTORY 23360
LOGFILE
  GROUP 1 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'D:\APP\ADMINISTRATOR\ORADATA\XFF\SYSTEM01.DBF',
  'D:\APP\ADMINISTRATOR\ORADATA\XFF\SYSAUX01.DBF',
……
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Only allocated 127 recovery slaves (requested 128)
Parallel Media Recovery started with 127 slaves
Sun Aug 04 01:01:56 2024
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1160002 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO02.LOG
Completed: ALTER DATABASE RECOVER  database  
Sun Aug 04 01:02:20 2024
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 1946 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 1160002, block 2, scn 6029119350
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1160002 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO02.LOG
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 1160002, block 3895, scn 6029139793
 0 data blocks read, 0 data blocks written, 1946 redo k-bytes read
Initializing SCN for created control file
Database SCN compatibility initialized to 3
Sun Aug 04 01:02:21 2024
LGWR: STARTING ARCH PROCESSES
Sun Aug 04 01:02:21 2024
ARC0 started with pid=71, OS id=2772 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Sun Aug 04 01:02:22 2024
ARC1 started with pid=72, OS id=7996 
Sun Aug 04 01:02:22 2024
ARC2 started with pid=73, OS id=2900 
Sun Aug 04 01:02:22 2024
ARC3 started with pid=74, OS id=6856 
Archived Log entry 1 added for thread 1 sequence 1160000 ID 0xc4814d77 dest 1:
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 1160003 (thread open)
Thread 1 opened at log sequence 1160003
  Current log# 1 seq# 1160003 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Aug 04 01:02:23 2024
SMON: enabling cache recovery
Archived Log entry 2 added for thread 1 sequence 1160002 ID 0xc4814d77 dest 1:
Archived Log entry 3 added for thread 1 sequence 1160001 ID 0xc4814d77 dest 1:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
[7808] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:7657234 end:7657703 diff:469 (4 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         Txff condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is ZHS16GBK
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused txff:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
   then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sun Aug 04 01:02:27 2024
QMNC started with pid=75, OS id=7884 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open

后续处理异常表,lob,index等数据,客户业务测试都ok,完成本次恢复工作

发表在 Oracle备份恢复 | 标签为 , , , | 评论关闭