分类目录归档：数据库

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');
发表在 逻辑备份/恢复 | 标签为 | 评论关闭

obet处理ORA-704 ORA-604 ORA-1578故障

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

有客户数据库启动报ORA-704 ORA-604 ORA-1578错误,导致启动失败
ORA-704-ORA-1578

alert日志报错

Fri Feb 06 23:38:43 2026
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_239325.trc  (incident=1008437):
ORA-01578: ORACLE data block corrupted (file # 1, block # 241)
ORA-01110: data file 1: '+DATADG/orcl/datafile/system.270.1015415183'
Fri Feb 06 23:38:46 2026
Redo thread 2 internally disabled at seq 6 (CKPT)
Archived Log entry 182212 added for thread 2 sequence 5 ID 0xd68b4385 dest 1:
ARC0: Archiving disabled thread 2 sequence 6
Archived Log entry 182213 added for thread 2 sequence 6 ID 0xd68b4385 dest 1:
Thread 1 advanced to log sequence 9 (LGWR switch)
  Current log# 1 seq# 9 mem# 0: +DATADG/orcl/onlinelog/group_1.302.1223816761
  Current log# 1 seq# 9 mem# 1: +ARCHDG/orcl/onlinelog/group_1.257.1223816761
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_239325.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 241)
ORA-01110: data file 1: '+DATADG/orcl/datafile/system.270.1015415183'
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_239325.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 241)
ORA-01110: data file 1: '+DATADG/orcl/datafile/system.270.1015415183'
Error 704 happened during db open, shutting down database
USER (ospid: 239325): terminating the instance due to error 704
Fri Feb 06 23:38:48 2026
Instance terminated by USER, pid = 239325
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (239325) as a result of ORA-1092
Fri Feb 06 23:38:48 2026
ORA-1092 : opitsk aborting process

对system文件进行dbv检测(客户通过asmcmd cp命令拷贝出来system文件)

DBVERIFY - Verification starting : FILE = /asm_backup/orcl/datafile/SYSTEM.270.1015415183

DBV-00200: Block, DBA 4194545, already marked corrupt
csc(0x0006.d75a0ed7) higher than block scn(0x0000.00000000)
Page 241 failed with check code 6054

DBV-00200: Block, DBA 4306619, already marked corrupt
csc(0x0006.d75a14f4) higher than block scn(0x0000.00000000)
Page 112315 failed with check code 6054


DBVERIFY - Verification complete

Total Pages Examined         : 738560
Total Pages Processed (Data) : 424109
Total Pages Failing   (Data) : 1
Total Pages Processed (Index): 287403
Total Pages Failing   (Index): 1
Total Pages Processed (Other): 3408
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 23640
Total Pages Marked Corrupt   : 2
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3613010282 (6.3613010282)

这里可以看到有两个block被标记为坏块原因是由于:csc(0×0006.d75a0ed7) higher than block scn(0×0000.00000000),坏块错误代码为:Page 241 failed with check code 6054.这种问题的原因是由于block的scn小于数据块的csc(一般是由于写丢失导致),对于这个问题,最近在obet中刚刚增加了repair blkscn功能进行修复,具体参考:obet修复csc higher than block scn类型坏块,使用工具修复操作(其他block类似修改)

OBET> set filename /datapool/asm_backup/orcl/datafile/SYSTEM.270.1015415183
filename set to: /datapool/asm_backup/orcl/datafile/SYSTEM.270.1015415183

OBET> set block 241
block set to: 241

OBET> backup
Created backup directory: backup_blk
Successfully backed up current block from current file to 
    /tmp/backup_blk/SYSTEM.270.1015415183_241.20260209122822

OBET> repair blkscn

Repairing SCN for block 241...

Current block SCN information:
  scn_base: 0x00000000 (0)
  scn_wrap: 0x0000 (0)

Block 1 reference information:
  hdr_base: 0xD75FF65E (3613390430)
  hdr_wrap: 0x0006 (6)

Current block CSC information:
  csc_base: 0xD75A0ED7 (3613003479)
  csc_wrap: 0x0006 (6)

Calculated values:
  blkcsc: 29382807255 (0x00000006D75A0ED7)
  blkscn: 0 (0x0000000000000000)

blkscn < blkcsc, SCN repair required.

SCN repair requires edit mode.
Please enable edit mode with 'set mode edit' first.

OBET> set mode edit
mode set to: edit

OBET> repair blkscn

Repairing SCN for block 241...

Current block SCN information:
  scn_base: 0x00000000 (0)
  scn_wrap: 0x0000 (0)

Block 1 reference information:
  hdr_base: 0xD75FF65E (3613390430)
  hdr_wrap: 0x0006 (6)

Current block CSC information:
  csc_base: 0xD75A0ED7 (3613003479)
  csc_wrap: 0x0006 (6)

Calculated values:
  blkcsc: 29382807255 (0x00000006D75A0ED7)
  blkscn: 0 (0x0000000000000000)

blkscn < blkcsc, SCN repair required.

New SCN values to write:
  new_scn_base: 0xD75FF65D (3613390429)
  new_scn_wrap: 0x0006 (6)

Confirm SCN repair operations:
File: /datapool/asm_backup/orcl/datafile/SYSTEM.270.1015415183
Block: 241
Operations:
  1. Write new scn_base (0xD75FF65D)
  2. Write new scn_wrap (0x0006)
  3. Fix seq_kcbh (0xFF -> 0x01)

Confirm? (Y/YES to proceed): y

[Undo information saved - use 'undo' command to restore original values]

Verification after SCN repair:
1. scn_base: 5DF65FD7 (expected: 5DF65FD7)
2. scn_wrap: 0600 (expected: 0600)
3. seq_kcbh: 0x01 OK

Block 241 SCN repair completed successfully.
Execute tailchk apply and sum apply for this block.

OBET> tailchk apply

Confirm applying tailchk:
File: /datapool/asm_backup/orcl/datafile/SYSTEM.270.1015415183
Block: 241
Offset in block: 8188 (file offset: 0x001E3FFC)
Original value: 0xFF060000
New value:      0x01065DF6
Confirm? (Y/YES to proceed): y
Verification successful: Stored tailchk matches calculated value (0x01065DF6).
Tailchk applied successfully.

OBET> sum apply

Confirm applying checksum:
File: /datapool/asm_backup/orcl/datafile/SYSTEM.270.1015415183
Block: 241
Offset in block: 16 (file offset: 0x001E2010)
Original value: 0x7330
New value:      0x2AE7
Confirm? (Y/YES to proceed): y
Verification successful: Stored checksum matches calculated value (0x2AE7).
Checksum applied successfully.

然后dbv检查数据文件

[oracle@db4 tmp]$ dbv file=/datapool/asm_backup/orcl/datafile/SYSTEM.270.1015415183

DBVERIFY: Release 11.2.0.4.0 - Production on Mon Feb 9 20:37:54 2026

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE =
         /datapool/asm_backup/orcl/datafile/SYSTEM.270.1015415183


DBVERIFY - Verification complete

Total Pages Examined         : 738560
Total Pages Processed (Data) : 424109
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 287403
Total Pages Failing   (Index): 1
Total Pages Processed (Other): 3408
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 23640
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3613451063 (6.3613451063)

然后直接顺利打开数据库

SQL> alter database open;

Database altered.

由于该库在我接手之前已经做了大量的强制拉库等各种恢复尝试,因此对该库做逻辑导出,导入新库完成本次恢复任务

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

ORA-600 kcratr_nab_less_than_odr和ORA-600 4193故障处理

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

数据库启动报ORA-600 kcratr_nab_less_than_odr

alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 2930 KB redo, 1357 data blocks need recovery
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_19096.trc  (incident=576634):
ORA-00600: ??????, ??: [kcratr_nab_less_than_odr], [1], [7859], [62180], [63172], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_576634\orcl_ora_19096_i576634.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Aborting crash recovery due to error 600
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_19096.trc:
ORA-00600: ??????, ??: [kcratr_nab_less_than_odr], [1], [7859], [62180], [63172], [], [], [], [], [], [], []
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_19096.trc:
ORA-00600: ??????, ??: [kcratr_nab_less_than_odr], [1], [7859], [62180], [63172], [], [], [], [], [], [], []
ORA-600 signalled during: alter database open...

这个错误一般常见的是11.2.0.1的数据库异常关机了容易遇到
又一例ORA-600 kcratr_nab_less_than_odr
ORA-600 kcratr_nab_less_than_odr故障解决
这个客户是11.2.0.4的库(在这个版本中该错误相对较少,虽然也遇到过几次)
11.2.0.4库中遇到ORA-600 kcratr_nab_less_than_odr报错
分析日志发现是由于之前io比较慢导致写入异常导致

Fri Feb 06 00:32:59 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. 
*****************************************************
CKPT (ospid: 2600): terminating the instance
Fri Feb 06 00:33:00 2026
System state dump requested by (instance=1, osid=2600 (CKPT)), summary=[abnormal instance termination].
System State dumped to trace file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_diag_2488_20260206003300.trc
Fri Feb 06 00:33:01 2026
opiodr aborting process unknown ospid (3420) as a result of ORA-1092

处理这个错误相对比较简单,重建控制文件即可
11

但是后续数据库出现ORA-600 4193错误

Fri Feb 06 02:57:34 2026
ALTER DATABASE OPEN
Thread 1 opened at log sequence 7860
  Current log# 1 seq# 7860 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\orcl\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[4044] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:5166812 end:5167140 diff:328 (3 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)
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_3868.trc  (incident=640589):
ORA-00600: 内部错误代码, 参数: [4193], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_640589\orcl_smon_3868_i640589.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Starting background process QMNC
Fri Feb 06 02:57:36 2026
QMNC started with pid=21, OS id=5008 
Completed: ALTER DATABASE OPEN
Fri Feb 06 02:57:37 2026
db_recovery_file_dest_size of 4182 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Block recovery from logseq 7860, block 132954 to scn 55142160294
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7860 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\orcl\REDO01.LOG
Block recovery completed at rba 7860.132998.16, scn 12.3602552743
Block recovery from logseq 7860, block 132954 to scn 55142160257
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7860 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\orcl\REDO01.LOG
Block recovery completed at rba 7860.132954.16, scn 12.3602552707
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_3868.trc:
ORA-01595: 释放区 (5) 回退段 (21) 时出错
ORA-00600: 内部错误代码, 参数: [4193], [], [], [], [], [], [], [], [], [], [], []
Flush retried for xcb 0x96c82600, pmd 0x8c6a70b8
Block recovery from logseq 7860, block 132954 to scn 55142160294
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7860 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\orcl\REDO01.LOG
Block recovery completed at rba 7860.132998.16, scn 12.3602552743
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_pmon_2728.trc:
ORA-00600: 内部错误代码, 参数: [4193], [], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 2728): terminating the instance due to error 472
System state dump requested by (instance=1, osid=2728 (PMON)), summary=[abnormal instance termination].
Fri Feb 06 02:58:17 2026
Instance terminated by PMON, pid = 2728

这个错误相对比较简单,由于undo回滚段异常,处理掉异常undo之后,数据库正常,完成本次恢复任务

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