Category Archives: Oracle备份恢复

记录一次win删除数据文件完美恢复案例

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：记录一次win删除数据文件完美恢复案例

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户在操作系统层面删除了四个数据文件(数据库在关闭情况下直接删除物理文件),然后offline,启动数据库,结果发现被删除的数据文件是业务表空间的,导致大量业务访问报错
ORA-00376

通过数据库层面查询确认这些文件已经不存在
121
对于这样的情况,先尝试从文件系统层面尝试反删除恢复文件
0
由于删除文件之后,启动了数据库并写入了一些日志和数据导致被删除的文件的元数据信息彻底丢失,这种情况下基于文件系统的反删除恢复肯定无法需要的数据,对于这种情况,考虑进行碎片层面恢复,以前有过类似案例:
win系统删除oracle数据文件恢复
Oracle 数据文件大小为0kb或者文件丢失恢复
删除数据库文件并部分覆盖情况下Oracle恢复
通过底层扫描发现需要恢复的文件头部信息丢失,其他block完整(该文件本身不大,只有100M),扫描出来的数据块类似这样的结果
413
414
这里比较幸运,丢失的block为1-3,也就是涉及文件头和一些位图信息,通过以前自研的OraFHR工具(Oracle数据库被勒索加密一键open工具–OraFHR)进行重构文件头，再通过oracle recovery tools工具进行文件头的一些修复工作
orarec
再重建控制文件打开数据库,并完美导出数据,实现数据0丢失恢复
expdp

Oracle典型故障：The controlfile header block returned by the OS has a sequence number that is too old

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：Oracle典型故障：The controlfile header block returned by the OS has a sequence number that is too old

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

这个是一例子客户数据库运行过程中突然报:The controlfile header block returned by the OS has a sequence number that is too old.然后数据库无法正常启动的数据库恢复case
以前处理过一些类似case:Controlfile sequence number in file header is different from the one in memory
故障现象
alert日志中报The controlfile header block returned by the OS has a sequence number that is too old.错误，然后数据库crash


Wed Mar 18 12:00:44 2026
********************* ATTENTION: ******************** 
 The controlfile header block returned by the OS
 has a sequence number that is too old. 
 The controlfile might be corrupted.
 PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE 
 without following the steps below.
 RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE 
 TO THE DATABASE, if the controlfile is truly corrupted.
 In order to re-start the instance safely, 
 please do the following:
 (1) Save all copies of the controlfile for later 
     analysis and contact your OS vendor and Oracle support.
 (2) Mount the instance and issue: 
     ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
 (3) Unmount the instance. 
 (4) Use the script in the trace file to
     RE-CREATE THE CONTROLFILE and open the database. 
*****************************************************
USER (ospid: 15912): terminating the instance

这个错误比较明显是由于控制文件的sequence number比较老导致，出现这种问题，一般是由于io过慢，或者底层不稳定（比如虚拟化平台，文件系统异常，硬件不稳定等）导致（官方参考文档：The controlfile header block returned by the OS has a sequence number that is too old.）

尝试重启数据库报ORA-01207错误

Wed Mar 18 18:51:54 2026
alter database mount exclusive
Successful mount of redo thread 1, with mount id 1534819594
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_2992.trc:
ORA-01122: ????? 18 ????
ORA-01110: ???? 18: 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\XIFENFEI.DBF'
ORA-01207: ????????? - ??????
ORA-1122 signalled during: alter database open...
Wed Mar 18 18:52:01 2026
Checker run found 1 new persistent data failures

该错误的官方解释

[oracle@xifenfei.com ~]$ oerr ora 1207
01207, 00000, "file is more recent than control file - old control file"
// *Cause:  The control file change sequence number in the data file is 
//         greater than the number in the control file. This implies that
//         the wrong control file is being used. Note that repeatedly causing
//         this error can make it stop happening without correcting the real
//         problem. Every attempt to open the database will advance the
//         control file change sequence number until it is great enough.
// *Action: Use the current control file or do backup control file recovery to 
//         make the control file current. Be sure to follow all restrictions 
//         on doing a backup control file recovery.

由于数据文件比控制文件更新，导致该问题，通过查询v$datafile_header发现更多类似异常文件(可以使用Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)收集信息）
111

alert日志文件中也有明显的写错日志信息
222
这些二进制内容和监听日志内容被写入到了alert日志中，证明当时文件系统或者操作系统甚至更底层出现了异常，这个客户是运行在云平台上的，具体运营需要平台厂商才能分析

故障处理
重建控制文件并进行recover

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

Total System Global Area 2.9931E+10 bytes
Fixed Size                  2190296 bytes
Variable Size            1946158120 bytes
Database Buffers         2.7917E+10 bytes
Redo Buffers               64905216 bytes
SQL> @rectl.sql

控制文件已创建。
SQL> recover database;

完成介质恢复。

尝试启动数据库,报ora-600 2662错误

SQL> alter database open;

alter database open
*
第 1 行出现错误:

ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [1], [45773288], [1], [45777527], [301990016]
ORA-00600: internal error code, arguments: [2662], [1], [45773287], [1], [45777527], [301990016]
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [1], [45773285], [1], [45777527], [301990016]
进程 ID: 2708
会话 ID: 148 序列号: 5

数据库启动报ORA-600 2662错误，这个是典型的文件头scn过小的问题,通过自研小工具Patch_SCN可以快速解决，以前类似文章:
Patch SCN一键解决ORA-600 2662故障
Patch SCN工具一键恢复ORA-600 kcbzib_kcrsds_1
ORA-600 kcratr_nab_less_than_odr和ORA-600 2662故障处理
333

修改scn之后，数据库顺利打开

SQL> startup mount pfile='e:/pfile.txt';
ORACLE 例程已经启动。

Total System Global Area 2.9931E+10 bytes
Fixed Size                  2190296 bytes
Variable Size            1946158120 bytes
Database Buffers         2.7917E+10 bytes
Redo Buffers               64905216 bytes
数据库装载完毕。
SQL> alter database open;

alter database open
*
第 1 行出现错误:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
SQL> recover database;

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

数据库已更改。

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

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

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

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

客户有一个数据库故障,是其他工程师进行恢复操作,最后搞不定通过朋友介绍找到我的.我通过分析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

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

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

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

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户做了双机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故障处理

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

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

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

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');