分类目录归档：Oracle备份恢复

通过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备份恢复 | 标签为 , , , , , , , , , | 留下评论

文件系统损坏导致数据库异常故障处理

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

有客户做了双机rose,由于某种故障导致共享存储在两个主机之间相互频繁挂载(甚至出现了同时挂载的情况),使得该文件系统发生损坏
ntfs

修复双机故障之后,数据库启动ORA-01122 ORA-01110 ORA-01200错误
ora-1200
初步看这个报错，block差距有点大，文件头中记录为419840个block,现在实际有的block数量为384000,使用obet查看文件头记录block number情况

OBET> p kcvfh.kcvfhhdr
File: E:\TEMP\20260219\SYSTEM01.DBF
Size: 8192 bytes
Block: 1
Offset: 20

struct kcvfhhdr, 76 bytes                   @20
   ub4 kccfhswv                            @20      0x00000000
   ub4 kccfhcvn                            @24      0x0B200400
   ub4 kccfhdbi                            @28      0x85D98FAB
   text kccfhdbn[8]                        @32-39   XXXX
   ub4 kccfhcsq                            @40      0x00091079
   ub4 kccfhfsz                            @44      0x00066800 <<--16转换为10进制为419840
   s_blkz kccfhbsz                         @48      0x00
   ub2 kccfhfno                            @52      0x0001
   ub2 kccfhtyp                            @54      0x0003
   ub4 kccfhacid                           @56      0x00000000
   ub4 kccfhcks                            @60      0x00000000
   text kccfhtag[32]                       @64-95

<kcvfh.kcvfhhdr structure printed successfully>

对于这种情况,以前有过很多次处理经验(一般办法2个:1>修改文件头的block数量记录;2>修改现在的文件大小和实际文件有匹配),以前类似的处理记录:
bbed处理ORA-01200故障
记录一次ORA-01200完美恢复
ORA-01122 ORA-01200故障处理
处理完成system文件异常之后,sysaux文件继续异常

SQL> recover datafile 1;
完成介质恢复。
SQL> recover datafile 2;
ORA-00283: 恢复会话因错误而取消
ORA-01110: 数据文件 2: 'Z:\APP\ADMINISTRATOR\ORADATA\XXXX\SYSAUX01.DBF'
ORA-01122: 数据库文件 2 验证失败
ORA-01110: 数据文件 2: 'Z:\APP\ADMINISTRATOR\ORADATA\XXXX\SYSAUX01.DBF'
ORA-01200: 149760 的实际文件大小小于 153600 块的正确大小

类似处理该故障之后,由于文件系统故障导致不少文件出现大量连续坏块（全0或者记录了其他文件内容的坏块）,这种是由于文件系统元数据异常导致,通过文件系统层面恢复继续无法正常处理,对于这样的情况,通过碎片扫描工具按照oracle block级别的文件重组(其实就是基于rdba信息进行重组),获取正确的数据块信息然后重新重组成数据文件
QQ20260220-005159

然后打开数据库,顺利导出数据,实现客户数据最大限度恢复

发表在 非常规恢复 | 标签为 , , | 留下评论

expdp导出xml列报ORA-22924故障处理

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

expdp导出xml列类型表报错

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "SYS"."EXPDP_TBA1":  "/******** AS SYSDBA" tables=XFF.XML_TAB dumpfile=XML_TAB.dmp
    DIRECTORY=expdp_dir logfile=expdp_XML_TAB.log EXCLUDE=STATISTICS job_name=expdp_tba1
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 13.12 GB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-31693: 表数据对象 "XFF"."XML_TAB" 无法加载/卸载并且被跳过, 错误如下:
ORA-29913: 执行 ODCIEXTTABLEPOPULATE 调出时出错
ORA-22924: 快照太旧
已成功加载/卸载了主表 "SYS"."EXPDP_TBA1"
******************************************************************************
SYS.EXPDP_TBA1 的转储文件集为:
  D:\XML_TAB.DMP
作业 "SYS"."EXPDP_TBA1" 已经完成, 但是有 1 个错误 (于 星期六 2月 14 09:01:26 2026 elapsed 0 00:04:15 完成)

一般对于这些问题的解决思路是找出来异常的行的rowid,然后导出跳过异常行或者把对应的xml列置空,关键就是如何找出来该记录,这里自己写了一个plsql来找出来异常rowid

SQL> declare
  2  page number;
  3  len number;
  4  c varchar2(10);
  5  charpp number := 8132/2;
  6  n number;
  7  v_sqlerrm varchar2(100);
  8  begin
  9  n := 0;
 10  for r in (select rowid rid from XFF.XML_TAB order by rowid) loop
 11  begin
 12  select dbms_lob.getlength (xmltype.getclobval(XML_COL)) into c from XFF.XML_TAB     where rowid = r.rid;
 13  v_sqlerrm := SQLERRM;
 14  insert into result values (n, r.rid, 'good', page, v_sqlerrm);
 15  commit;
 16  exception
 17  when others then
 18  dbms_output.put_line ('Error on rowid ' ||r.rid||' page '||page);
 19  dbms_output.put_line (sqlerrm);
 20  v_sqlerrm := SQLERRM;
 21  insert into result values (n, r.rid, 'bad',page,v_sqlerrm);
 22  commit;
 23  end;
 24  end loop;
 25  end;
 26  /
Error on rowid AAASO1AAKAAAH5bAAC page
ORA-22924: 快照太旧
ORA-06512: 在 "SYS.XMLTYPE", line 138
Error on rowid AAASO1AAKAAAIPtAAZ page
ORA-22924: 快照太旧
ORA-06512: 在 "SYS.XMLTYPE", line 138
Error on rowid AAASO1AAKAAAIRfAAU page
ORA-22924: 快照太旧
ORA-06512: 在 "SYS.XMLTYPE", line 138
Error on rowid AAASO1AAKAAAIS9AAW page
ORA-22924: 快照太旧
ORA-06512: 在 "SYS.XMLTYPE", line 138
Error on rowid AAASO1AAKAAAIT7AAW page
ORA-22924: 快照太旧
ORA-06512: 在 "SYS.XMLTYPE", line 138
Error on rowid AAASO1AAKAAAIT/AAG page
ORA-22924: 快照太旧
ORA-06512: 在 "SYS.XMLTYPE", line 138

PL/SQL 过程已成功完成。

然后expdp导出数据跳过这些异常的rowid的par文件

directory=expdp_dir
dumpfile=expdp_XFF.XML_TAB.dmp
logfile=expdp_XFF.XML_TAB.log
tables=XFF.XML_TAB
query="XFF.XML_TAB:WHERE rowid not IN  ('AAASO1AAKAAAH5bAAC','AAASO1AAKAAAIPtAAZ','AAASO1AAKAAAIT7AAW',
'AAASO1AAKAAAIT/AAG', 'AAASO1AAKAAAIRfAAU','AAASO1AAKAAAIS9AAW')"
job_name=exp_1

数据可以正常到导出
expdp

也可以通过update语句直接把异常的xml值直接置空

update XFF.XML_TAB
set XML_COL = XMLType.createXML('')
where rowid in (select checked_rowid from result where status='bad');
发表在 逻辑备份/恢复 | 标签为 | 留下评论