asm dd 10M导致system文件部分坏块修复

Posted on 2026 年 05 月 22 日 by 惜分飞

有客户的数据库由于不当操作导致asm磁盘头损坏,进行的操作命令类似

dd if=/dev/zero of=/dev/dm-29 bs=1024K count=10

asm磁盘组无法mount,提示ORA-15042

SQL> ALTER DISKGROUP DATA MOUNT  /* asm agent *//* {1:1712:2} */ 
2026-05-19T21:57:16.517284-04:00
NOTE: cache registered group DATA 5/0xAE103296
NOTE: cache began mount (first) of group DATA 5/0xAE103296
NOTE: Assigning number (5,10) to disk (/dev/dm-29)
…………
NOTE: Assigning number (5,18) to disk (/dev/dm-15)
NOTE: Assigning number (5,0) to disk (/dev/dm-7)
NOTE: Assigning number (5,15) to disk (/dev/dm-5)
2026-05-19T21:57:16.650481-04:00
cluster guid (b216d47e2bf86f6aff34a36119b0c161) generated for PST Hbeat for instance 1
2026-05-19T21:57:22.659262-04:00
NOTE: GMON heartbeating for grp 5 (DATA)
GMON querying group 5 at 28 for pid 42, osid 12996
2026-05-19T21:57:22.661447-04:00
NOTE: Assigning number (5,14) to disk ()
2026-05-19T21:57:22.662476-04:00
GMON querying group 5 at 29 for pid 42, osid 12996
2026-05-19T21:57:22.663144-04:00
NOTE: cache dismounting (clean) group 5/0xAE103296 (DATA)
NOTE: messaging CKPT to quiesce pins Unix process pid: 12996, image: oracle@dlycdb1 (TNS V1-V3)
NOTE: dbwr not being msg'd to dismount
NOTE: LGWR not being messaged to dismount
NOTE: cache dismounted group 5/0xAE103296 (DATA)
NOTE: cache ending mount (fail) of group DATA number=5 incarn=0xae103296
NOTE: cache deleting context for group DATA 5/0xae103296
2026-05-19T21:57:22.720673-04:00
GMON dismounting group 5 at 30 for pid 42, osid 12996
2026-05-19T21:57:22.721055-04:00
NOTE: Disk DATA_0000 in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0001 in mode 0x7f marked for de-assignment
…………
NOTE: Disk DATA_0013 in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0015 in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0016 in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0017 in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0018 in mode 0x7f marked for de-assignment
ERROR: diskgroup DATA was not mounted
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "14" is missing from group number "5" 

2026-05-19T21:57:22.745140-04:00
ERROR: ALTER DISKGROUP DATA MOUNT  /* asm agent *//* {1:1712:2} */

由于客户这个是19c版本,直接使用备份au还原,然后mount磁盘组成功

SQL> alter diskgroup data mount 
2026-05-19T22:40:59.137657+08:00
NOTE: cache registered group DATA 2/0xB126DA41
NOTE: cache began mount (first) of group DATA 2/0xB126DA41
NOTE: Assigning number (2,14) to disk (/dev/dm-29)
NOTE: Assigning number (2,8) to disk (/dev/dm-28)
…………
NOTE: Assigning number (2,2) to disk (/dev/dm-7)
NOTE: Assigning number (2,0) to disk (/dev/dm-6)
NOTE: Assigning number (2,1) to disk (/dev/dm-3)
2026-05-19T22:40:59.303908+08:00
cluster guid (b216d47e2bf86f6aff34a36119b0c161) generated for PST Hbeat for instance 2
2026-05-19T22:41:05.312792+08:00
NOTE: GMON heartbeating for grp 2 (DATA)
GMON querying group 2 at 80 for pid 35, osid 53051
2026-05-19T22:41:05.314925+08:00
NOTE: cache is mounting group DATA created on 2024/11/28 17:55:45
NOTE: cache opening disk 0 of grp 2: DATA_0000 path:/dev/dm-6
NOTE: 05/20/26 16:41:04 DATA.F1X0 found on disk 0 au 10 fcn 0.0 datfmt 1
NOTE: cache opening disk 1 of grp 2: DATA_0001 path:/dev/dm-3
…………
NOTE: cache opening disk 13 of grp 2: DATA_0013 path:/dev/dm-18
NOTE: cache opening disk 14 of grp 2: DATA_0014 path:/dev/dm-29
NOTE: cache opening disk 15 of grp 2: DATA_0015 path:/dev/dm-16
NOTE: cache opening disk 16 of grp 2: DATA_0016 path:/dev/dm-17
NOTE: cache opening disk 17 of grp 2: DATA_0017 path:/dev/dm-20
NOTE: cache opening disk 18 of grp 2: DATA_0018 path:/dev/dm-21
2026-05-19T22:41:05.317307+08:00
NOTE: cache mounting (first) external redundancy group 2/0xB126DA41 (DATA)
2026-05-19T22:41:05.522191+08:00
NOTE: attached to recovery domain 2
2026-05-19T22:41:05.558136+08:00
validate pdb 2, flags x4, valid 0, pdb flags x204 
* validated domain 2, flags = 0x200
NOTE: cache recovered group 2 to fcn 0.46336611
NOTE: redo buffer size is 512 blocks (2105344 bytes)
2026-05-19T22:41:05.569479+08:00
NOTE: LGWR attempting to mount thread 1 for diskgroup 2 (DATA)
NOTE: LGWR found thread 1 closed at ABA 110.6507 lock domain=0 inc#=0 instnum=1
NOTE: LGWR mounted thread 1 for diskgroup 2 (DATA)
2026-05-19T22:41:05.574499+08:00
mntstmp=2026/05/20 16:41:05.572000
2026-05-19T22:41:05.574854+08:00
NOTE: cache mounting group 2/0xB126DA41 (DATA) succeeded
NOTE: cache ending mount (success) of group DATA number=2 incarn=0xb126da41
2026-05-19T22:41:05.616227+08:00
NOTE: Instance updated compatible.asm to 19.0.0.0.0 for grp 2 (DATA).
2026-05-19T22:41:05.616754+08:00
NOTE: Instance updated compatible.asm to 19.0.0.0.0 for grp 2 (DATA).
2026-05-19T22:41:05.617932+08:00
NOTE: Instance updated compatible.rdbms to 19.0.0.0.0 for grp 2 (DATA).
2026-05-19T22:41:05.618303+08:00
NOTE: Instance updated compatible.rdbms to 19.0.0.0.0 for grp 2 (DATA).
2026-05-19T22:41:05.643844+08:00
SUCCESS: diskgroup DATA was mounted

虽然磁盘组mount成功,但是asm依旧在报错

这个客户胆子也真够大的,这样mount起来的磁盘组,还往里面加入磁盘出发Rebalance操作
1
2026-05-20T09:18:34.292103-04:00
Errors in file /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_arb0_60220.trc:
ORA-15196: invalid ASM block header [kfc.c:30747] [endian_kfbh] [2147483662] [1014] [0 != 1]
ORA-15196: invalid ASM block header [kfc.c:30747] [endian_kfbh] [2147483662] [1014] [0 != 1]
NOTE: cache repaired a corrupt block: group=2(DATA) dsk=14 blk=1014 on disk 14 from disk=14 (DATA_0014) incarn=4042690154 au=11 blk=1014 count=1
2026-05-20T09:18:36.721669-04:00
WARNING: cache read a corrupt block: group=2(DATA) dsk=14 blk=1015 disk=14 (DATA_0014) incarn=4042690154 au=0 blk=1015 count=1
2026-05-20T09:18:36.721982-04:00
Errors in file /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_arb0_60220.trc:
ORA-15196: invalid ASM block header [kfc.c:30747] [endian_kfbh] [2147483662] [1015] [0 != 1]
NOTE: a corrupted block from group DATA was dumped to /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_arb0_60220.trc
WARNING: cache read (retry) a corrupt block: group=2(DATA) dsk=14 blk=1015 disk=14 (DATA_0014) incarn=4042690154 au=0 blk=1015 count=1
2026-05-20T09:18:36.724279-04:00
Errors in file /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_arb0_60220.trc:
ORA-15196: invalid ASM block header [kfc.c:30747] [endian_kfbh] [2147483662] [1015] [0 != 1]
ORA-15196: invalid ASM block header [kfc.c:30747] [endian_kfbh] [2147483662] [1015] [0 != 1]
NOTE: cache repaired a corrupt block: group=2(DATA) dsk=14 blk=1015 on disk 14 from disk=14 (DATA_0014) incarn=4042690154 au=11 blk=1015 count=1
2026-05-20T09:44:20.469792-04:00
NOTE: Starting expel slave for group 2/0xcd67eb4 (DATA)
2026-05-20T09:44:20.473880-04:00
NOTE: GroupBlock outside rolling migration privileged region
NOTE: requesting all-instance membership refresh for group=2
2026-05-20T09:44:20.530101-04:00
NOTE: membership refresh pending for group 2/0xcd67eb4 (DATA)
2026-05-20T09:44:20.534097-04:00
GMON querying group 2 at 178 for pid 27, osid 70130
2026-05-20T09:44:20.557651-04:00
SUCCESS: refreshed membership for 2/0xcd67eb4 (DATA)
2026-05-20T09:44:23.489304-04:00
NOTE: Attempting voting file refresh on diskgroup DATA
NOTE: Refresh completed on diskgroup DATA. No voting file found.
2026-05-20T11:20:49.804413-04:00
NOTE: stopping process ARB0
NOTE: stopping process ARBA
2026-05-20T11:20:51.538777-04:00
SUCCESS: rebalance completed for group 2/0xcd67eb4 (DATA)
2026-05-20T23:20:51.541462+08:00
SUCCESS: ALTER DISKGROUP DATA ADD  DISK '/dev/dm-20' SIZE 4194304M
 REBALANCE WAIT

算幸运,由于ORA-15196: invalid ASM block header [kfc.c:30747] [endian_kfbh] 错误导致rebalance没有真正运行起来,从而该磁盘组没有dismount(19c这个方面确实增强不少,如果以前版本大概率会直接dismount掉)

客户在这样mount的磁盘组上尝试启动库,报ORA-01578错误,无法启动成功

2026-05-20T17:46:07.060794+08:00
Error attempting to elevate LMHB's priority: no further priority changes will be attempted for this process
2026-05-20T17:46:07.647114+08:00
Undo initialization recovery: Parallel FPTR complete: start:26091908 end:26096229 diff:4321 ms (4.3 seconds)
Undo initialization recovery: err:0 start: 26091907 end: 26096229 diff: 4322 ms (4.3 seconds)
[50880] Successfully onlined Undo Tablespace 5.
Undo initialization online undo segments: err:0 start: 26096229 end: 26096576 diff: 347 ms (0.3 seconds)
Undo initialization finished serial:0 start:26091907 end:26096591 diff:4684 ms (4.7 seconds)
Database Characterset is AL32UTF8
No Resource Manager plan active
2026-05-20T17:46:08.734113+08:00

Corrupt block relative dba: 0x004030ee (file 1, block 12526)
Completely zero block found during buffer read

Reread (file 1, block 12526) found same corrupt data (no logical check)
2026-05-20T17:46:08.811455+08:00
Corrupt Block Found
         TIME STAMP (GMT) = 05/20/2026 17:46:07
         CONT = 0, TSN = 0, TSNAME = SYSTEM
         RFN = 1, BLK = 12526, RDBA = 4206830
         OBJN = 37, OBJD = 37, OBJECT = I_OBJ2, SUBOBJECT = 
         SEGMENT OWNER = SYS, SEGMENT TYPE = Index Segment
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_50880.trc  (incident=800708):
ORA-01578: ORACLE data block corrupted (file # 1, block # 12526)
ORA-01110: data file 1: '+DATA/xff/DATAFILE/system.257.1186720165'
2026-05-20T05:46:09.047772-04:00
ALTER SYSTEM SET remote_listener=' xff-scan:11521' SCOPE=MEMORY SID='xff2';
2026-05-20T05:46:09.049615-04:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='xff2';
2026-05-20T17:46:09.812271+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************

Corrupt block relative dba: 0x004030de (file 1, block 12510)
Completely zero block found during buffer read

Reread (file 1, block 12510) found same corrupt data (no logical check)
2026-05-20T17:46:10.350573+08:00
Corrupt Block Found
         TIME STAMP (GMT) = 05/20/2026 17:46:09
         CONT = 0, TSN = 0, TSNAME = SYSTEM
         RFN = 1, BLK = 12510, RDBA = 4206814
         OBJN = 83, OBJD = 83, OBJECT = DEPENDENCY$, SUBOBJECT = 
         SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_50880.trc  (incident=800709):
ORA-01578: ORACLE data block corrupted (file # 1, block # 12510)
ORA-01110: data file 1: '+DATA/xff/DATAFILE/system.257.1186720165'
2026-05-20T17:46:10.694365+08:00

Corrupt block relative dba: 0x00403000 (file 1, block 12288)

Completely zero block found during validation
Reread of blocknum=12288, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data
Reread of blocknum=12288, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data
Reread of blocknum=12288, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data
Reread of blocknum=12288, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data
Reread of blocknum=12288, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data

Corrupt block relative dba: 0x00403001 (file 1, block 12289)

Completely zero block found during validation
Reread of blocknum=12289, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data
Reread of blocknum=12289, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data
Reread of blocknum=12289, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data
Reread of blocknum=12289, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data
Reread of blocknum=12289, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data
………………
Corrupt block relative dba: 0x004030ff (file 1, block 12543)

Completely zero block found during validation
Reread of blocknum=12543, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data
Reread of blocknum=12543, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data
Reread of blocknum=12543, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data
Reread of blocknum=12543, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data
Reread of blocknum=12543, file=+DATA/xff/DATAFILE/system.257.1186720165. found same corrupt data

Corrupt block relative dba: 0x0040301d (file 1, block 12317)
Completely zero block found during buffer read

Reread (file 1, block 12317) found same corrupt data (no logical check)
2026-05-20T17:46:12.183545+08:00
Corrupt Block Found
         TIME STAMP (GMT) = 05/20/2026 17:46:11
         CONT = 0, TSN = 0, TSNAME = SYSTEM
         RFN = 1, BLK = 12317, RDBA = 4206621
         OBJN = 37, OBJD = 37, OBJECT = I_OBJ2, SUBOBJECT = 
         SEGMENT OWNER = SYS, SEGMENT TYPE = Index Segment
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_50880.trc  (incident=800710):
ORA-01578: ORACLE data block corrupted (file # 1, block # 12317)
ORA-01110: data file 1: '+DATA/xff/DATAFILE/system.257.1186720165'
Incident details in: /u01/app/oracle/diag/rdbms/xff/xff2/incident/incdir_800710/xff2_ora_50880_i800710.trc
2026-05-20T05:46:12.371040-04:00
ALTER SYSTEM SET remote_listener=' xff-scan:11521' SCOPE=MEMORY SID='xff2';
2026-05-20T05:46:12.372924-04:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='xff2';
2026-05-20T17:46:13.814346+08:00
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_50880.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 12317)
ORA-01110: data file 1: '+DATA/xff/DATAFILE/system.257.1186720165'
2026-05-20T17:46:13.814407+08:00
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_50880.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 12317)
ORA-01110: data file 1: '+DATA/xff/DATAFILE/system.257.1186720165'
2026-05-20T17:46:13.814442+08:00
Error 604 happened during db open, shutting down database
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_50880.trc  (incident=800711):
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 12317)
ORA-01110: data file 1: '+DATA/xff/DATAFILE/system.257.1186720165'

Corrupt block relative dba: 0x00403097 (file 1, block 12439)
Completely zero block found during buffer read

Reread (file 1, block 12439) found same corrupt data (no logical check)
2026-05-20T17:46:14.259044+08:00
Corrupt Block Found
         TIME STAMP (GMT) = 05/20/2026 17:46:13
         CONT = 0, TSN = 0, TSNAME = SYSTEM
         RFN = 1, BLK = 12439, RDBA = 4206743
         OBJN = 18, OBJD = 18, OBJECT = OBJ$, SUBOBJECT = 
         SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_gen0_48604.trc  (incident=798852):
ORA-01578: ORACLE data block corrupted (file # 1, block # 12439)
ORA-01110: data file 1: '+DATA/xff/DATAFILE/system.257.1186720165'

2026-05-20T17:46:13.814346+08:00
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_50880.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 12317)
ORA-01110: data file 1: '+DATA/xff/DATAFILE/system.257.1186720165'
2026-05-20T17:46:13.814407+08:00
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_50880.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 12317)
ORA-01110: data file 1: '+DATA/xff/DATAFILE/system.257.1186720165'
2026-05-20T17:46:13.814442+08:00
Error 604 happened during db open, shutting down database
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_50880.trc  (incident=800711):
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 12317)
ORA-01110: data file 1: '+DATA/xff/DATAFILE/system.257.1186720165'

Corrupt block relative dba: 0x00403097 (file 1, block 12439)
Completely zero block found during buffer read

Reread (file 1, block 12439) found same corrupt data (no logical check)
2026-05-20T17:46:14.259044+08:00
Corrupt Block Found
         TIME STAMP (GMT) = 05/20/2026 17:46:13
         CONT = 0, TSN = 0, TSNAME = SYSTEM
         RFN = 1, BLK = 12439, RDBA = 4206743
         OBJN = 18, OBJD = 18, OBJECT = OBJ$, SUBOBJECT = 
         SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment
Errors in file /u01/app/oracle/diag/rdbms/xff/xff2/trace/xff2_gen0_48604.trc  (incident=798852):
ORA-01578: ORACLE data block corrupted (file # 1, block # 12439)
ORA-01110: data file 1: '+DATA/xff/DATAFILE/system.257.1186720165'
2026-05-20T05:46:14.436597-04:00
ALTER SYSTEM SET remote_listener=' xff-scan:11521' SCOPE=MEMORY SID='xff2';
2026-05-20T05:46:14.438492-04:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='xff2';
2026-05-20T17:46:15.486758+08:00
opiodr aborting process unknown ospid (50880) as a result of ORA-603
2026-05-20T17:46:15.498707+08:00
ORA-603 : opitsk aborting process
License high water mark = 423
USER(prelim) (ospid: 50880): terminating the instance due to ORA error 604
2026-05-20T17:46:15.536740+08:00
opiodr aborting process unknown ospid (69547) as a result of ORA-1092
2026-05-20T05:46:16.321597-04:00
ORA-1092 : opitsk aborting process

这里基本上可以看出来是由于在数据库启动过程中递归调用一些sql,但是由于遭遇到坏块导致启动失败,通过dbv检查system数据文件发现256个坏块

256个连续的全0坏块,怀疑是2M的数据被dd全空覆盖,这样的情况,也就是怀疑是au=2的后面2M被覆盖(ausize为4M),分析system的数据分布情况

这里可以确认system的第24个au(从0开始)在14号盘au 2 上面,也就是数据块起始损坏为block：12288-12543（24M*4/8K[有block 0 需要考虑])，对于这种彻底损坏而且比较靠前的system中block,通过人工构造出来这些block的方式进行修复,在自研的Oracle Recovery Tools和obet工具都有该功能.运气不错,通过这个修复之后,直接expdp导出数据没有大问题,比较完美的恢复了这个故障.

Oracle故障第一现场被恢复混乱的数据库恢复

Posted on 2026 年 05 月 13 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle故障第一现场被恢复混乱的数据库恢复

有客户数据库断电异常之后,由于第三方进行了一系列的恢复,现场比较混乱,无法判断最初情况,通过Oracle Database Recovery Check收集的结果进行初步判断
1. 有三个文件处于丢失状态，并且数据库在故障之后被人强制resetlogs拉过库

2. 数据文件头scn不一致,而且相差的日志序列还比较大(该数据库为非归档模式)

3. 该库多次重建ctl(alert日志中也有相关记录)

现在恢复这个库需要做的几件事情:
1. 由于没有任何原始故障之后的控制文件,需要从服务器上找出来所有故障之时的数据文件,担心被人重建ctl使用了错误的数据文件
2. 对于三个file missing的进行分析,并确认磁盘上是否存在,是否是好的,如果是好的需要和现在的文件一起作为一个整体进行恢复,并打开库
3. 打开数据库过程可能遇到的错误处理

通过obet中近期增加的get_dbinfo功能来解析所有可能的数据文件头（obet官方说明）,结合文件头的信息判断,发现磁盘上名称dbf结尾的文件号重复

这样的情况下,我们取filesize大,（scn大不一定正确,可能由于被强制resetlogs导致scn比正确的文件大）,同时也结合这个收集的信息,确认三个丢失的文件中两个为undotbs1表空间文件,另外一个为112k的数据文件,这里让我学习到了新知识,oracle的数据文件最小可以多少个block(通过试验测试,最小可以16个block,文件大小即为:16+1(block 0)*block_size)

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期三 5月 13 22:05:52 2026

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


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace tbs datafile 'e:/tbs01.dbf' size 8k;
create tablespace tbs datafile 'e:/tbs01.dbf' size 8k
*
第 1 行出现错误:
ORA-03214: 指定的文件大小小于所需的最小值


SQL> create tablespace tbs datafile 'e:/tbs01.dbf' size 80k;
create tablespace tbs datafile 'e:/tbs01.dbf' size 80k
*
第 1 行出现错误:
ORA-03214: 指定的文件大小小于所需的最小值


SQL> create tablespace tbs datafile 'e:/tbs01.dbf' size 96k;

表空间已创建。

确认好相关信息之后,然后对于三个file missing状态的文件进行dbv检测确认undotbs01.dbf(file 3)基本上全部损坏（大量全0块）,另外两个文件正常

对于正常的文件通过obet修改相关scn信息

然后重建控制文件(丢弃undotbs01.dbf文件),由于确认undo已经异常,直接设置undo为manual管理方式并屏蔽回滚段,然后屏蔽一致性,强制打开数据库,结果报ORA-600 2662错误

使用Patch_SCN工具修改数据库scn(Patch_SCN工具说明)

然后数据库顺利打开,重建新undo,增加temp,删除老undo,导出数据完成本次恢复任务

impdp报ORA-39083 ORA-14102错误处理

Posted on 2026 年 05 月 12 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：impdp报ORA-39083 ORA-14102错误处理

最近两次遇到impdp 报 ORA-39083 ORA-14102错误

[oracle@localhost tmp]$ impdp "'/as sysdba'" directory=expdp_dir  full=y dumpfile=1.dmp 

Import: Release 11.2.0.1.0 - Production on Mon May 11 20:57:00 2026

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=expdp_dir full=y dumpfile=1.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"AAA"."XXXXX" failed to create with error:
ORA-14102: only one LOGGING or NOLOGGING clause may be specified
Failing sql is:
CREATE TABLE "AAA"."XXXX" ("OBJECT_ID" NUMBER(9,0) NOT NULL ENABLE, "OBJECT_TYPE" VARCHAR2(8 BYTE) NOT NULL ENABLE
……………………

这个错误比较明显由于AAA.XXXX表的创建语句中有多于一个LOGGING or NOLOGGING,从而导致该创建语句无法正常创建表.

[oracle@iZbp11c0qyuuo1gr7j98upZ ~]$ oerr ora 14102
14102, 00000, "only one LOGGING or NOLOGGING clause may be specified"
// *Cause: LOGGING was specified more than once, NOLOGGING was specified
//         more than once, or both LOGGING and NOLOGGING were specified.
// *Action: Remove all but one of the LOGGING or NOLOGGING clauses and
//          reissue the statement.

查看该表对应的expdp导出日志

[oracle@xff expdmp]$ expdp "'/as sysdba'"  tables="AAA"."XXXX" dumpfile=1.dmp compression=all EXCLUDE=STATISTICS,AUDIT  

Export: Release 11.2.0.4.0 - Production on Mon May 11 21:01:06 2026

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  tables=AAA.XXXX dumpfile=1.dmp compression=all EXCLUDE=STATISTICS,AUDIT 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.207 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "AAA"."XXXX":"F_GTG"            5.430 MB  968776 rows
. . exported "AAA"."XXXX":"F_CONS"           4.734 MB  920007 rows
…………

比较明显AAA.XXXX表是一个分区表,而且是从11.2.0.4中导出,然后准备导入到11.2.0.1版本数据库中.通过DBMS_METADATA.get_ddl来获取该表的ddl语句确实有多个LOGGING/NOLOGGING(主要是每个分区都有NOLOGGING)

SET ECHO OFF
SET PAGESIZE 0
SET LINES 3000
SET LONG 200000
SET FEEDBACK OFF
SET HEADING OFF
SET SERVEROUTPUT ON SIZE 1000000
COLUMN TXT FORMAT A3000 WORD_WRAPPED
SQL> SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','XXXX','AAA') TXT FROM DUAL;

  CREATE TABLE "AAA"."XXXX"
   (	"OBJECT_ID" NUMBER(9,0) NOT NULL ENABLE,
	"OBJECT_TYPE" VARCHAR2(8) NOT NULL ENABLE,
	"DL_TYPE" VARCHAR2(8) NOT NULL ENABLE,
        ………………
 	CONSTRAINT "PK_OBJECT_DL_DEF" PRIMARY KEY ("OBJECT_ID", "OBJECT_TYPE", "DL_TYPE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY LIST ("OBJECT_TYPE")
 (PARTITION "F_SUBS"  VALUES ('1') SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ,
  ……………………

尝试在11.2.0.1环境中人工执行该sql创建表,直接报ORA-14020: 不能指定表分区的此物理属性错误

基于此基本上可以确认是由于11.2.0.1默认情况下不支持这样的语法,解决该问题的办法：
1. 重新导出来expdp dmp,加上version=11.2.0.1参数
2. impdp加上impdp TRANSFORM参数TRANSFORM=segment_attributes:n进行导入(impdp TRANSFORM参数)

一次断电引起的Oracle故障恢复-ora-600 2662故障

Posted on 2026 年 05 月 11 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：一次断电引起的Oracle故障恢复-ora-600 2662故障

接手一个oracle恢复case,由于断电导致oracle数据库异常,现场人员进行了一系列的恢复成功,但是没有成功open库,我接手故障之后尝试做recover 报ORA-16433错误

[oracle@xifenfei.com ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 10 09:27:51 2026

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

根据经验这种错误一般是由于强制打开库失败导致,回溯oracle alert日志发现类似操作

Fri May 08 18:47:59 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 138986145572
Errors in file /data/oracle/diag/rdbms/xff/xff/trace/xff_ora_37813.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/data/oracle/oradata/xff/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 1 /data/oracle/oradata/xff/redo01.log
Clearing online log 1 of thread 1 sequence number 2389
Errors in file /data/oracle/diag/rdbms/xff/xff/trace/xff_ora_37813.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/data/oracle/oradata/xff/redo01.log'
………………
Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 677485461 (0x28619b95)
Online log /data/oracle/oradata/xff/redo01.log: Thread 1 Group 1 was previously cleared
Online log /data/oracle/oradata/xff/redo02.log: Thread 1 Group 2 was previously cleared
Online log /data/oracle/oradata/xff/redo03.log: Thread 1 Group 3 was previously cleared
Fri May 08 18:48:17 2026
Setting recovery target incarnation to 4
Fri May 08 18:48:17 2026
Assigning activation ID 677634815 (0x2863e2ff)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /data/oracle/oradata/xff/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri May 08 18:48:18 2026
SMON: enabling cache recovery
Errors in file /data/oracle/diag/rdbms/xff/xff/trace/xff_ora_37813.trc  (incident=170321):
ORA-00600: internal error code, arguments: [2662], [32], [1547192107], [32], [1547212241], [12583040], []
Fri May 08 18:48:20 2026
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 /data/oracle/diag/rdbms/xff/xff/trace/xff_ora_37813.trc:
ORA-00600: internal error code, arguments: [2662], [32], [1547192107], [32], [1547212241], [12583040]
Errors in file /data/oracle/diag/rdbms/xff/xff/trace/xff_ora_37813.trc:
ORA-00600: internal error code, arguments: [2662], [32], [1547192107], [32], [1547212241], [12583040]
Error 600 happened during db open, shutting down database
USER (ospid: 37813): terminating the instance due to error 600
Instance terminated by USER, pid = 37813
ORA-1092 signalled during: ALTER DATABASE OPEN RESETLOGS...

对于这种情况,先重建控制文件

SQL> @/tmp/rectl.sql

Control file created.

尝试recover database恢复

ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 96 slaves
Sun May 10 09:33:03 2026
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: /data/oracle/oradata/xff/redo02.log
Errors in file /data/oracle/diag/rdbms/xff/xff/trace/xff_pr00_250074.trc  (incident=254397):
ORA-00353: log corruption near block 3 change 138986165586 time 05/08/2026 18:55:37
ORA-00312: online log 2 thread 1: '/data/oracle/oradata/xff/redo02.log'
Incident details in: /data/oracle/diag/rdbms/xff/xff/incident/incdir_254397/xff_pr00_250074_i254397.trc
Sun May 10 09:33:04 2026
Media Recovery failed with error 399
Errors in file /data/oracle/diag/rdbms/xff/xff/trace/xff_pr00_250074.trc:
ORA-00283: recovery session canceled due to errors
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 3 change 138986165586 time 05/08/2026 18:55:37
ORA-00312: online log 2 thread 1: '/data/oracle/oradata/xff/redo02.log'
ORA-10877 signalled during: ALTER DATABASE RECOVER  database  ...
Sun May 10 09:33:04 2026
Sweep [inc][254397]: completed
Errors in file /data/oracle/diag/rdbms/xff/xff/trace/xff_m000_250348.trc  (incident=255173):
ORA-00353: log corruption near block 3 change 138986165586 time 05/08/2026 18:55:37
ORA-00312: online log 2 thread 1: '/data/oracle/oradata/xff/redo02.log'
Errors in file /data/oracle/diag/rdbms/xff/xff/incident/incdir_254397/xff_m000_250348_i254397_a.trc:
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 3 change 138986165586 time 05/08/2026 18:55:37
ORA-00312: online log 2 thread 1: '/data/oracle/oradata/xff/redo02.log'
Errors in file /data/oracle/diag/rdbms/xff/xff/incident/incdir_254397/xff_m000_250348_i254397_a.trc:
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 3 change 138986165586 time 05/08/2026 18:55:37
ORA-00312: online log 2 thread 1: '/data/oracle/oradata/xff/redo02.log'
Sun May 10 09:34:05 2026

由于无法正常recover 操作，数据库需要强制打开,考虑到之前该库open的过程有ORA-600 2662错误,这次在打开之前使用Patch_SCN调整SCN(关于Patch_SCN文章:Patch_SCN for Linux 功能完善

[oracle@xifenfei.com tmp]$ ./Patch_SCN 249756 0x205D38954E
Successfully obtained address automatically: 0x6001ae70
Original Oracle SCN at Address 0x6001ae70: 0x0
Are you sure you want to modify Oracle SCN? (yes/no): yes
New SCN at Address 0x6001ae70: 0x205d38954e
Oracle SCN successfully modified.

然后数据库顺利打开

在expdp导出数据过程中遇到了硬件错误

为了安全性采用库只读情况下exp进行导出,运气不错所有数据顺利导出,完成本次数据恢复任务

oracleasm createdisk破坏的acfs文件系统恢复

Posted on 2026 年 04 月 24 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：oracleasm createdisk破坏的acfs文件系统恢复

接到一个朋友请求,客户12.2.0.1的asm被执行了oracleasm createdisk,把之前的asmdisk给重建了

根据以前恢复经验,这个故障会把前面1M的数据全部重置
删除asmlib磁盘导致磁盘组故障恢复
分享oracleasm createdisk重新创建asm disk后数据0丢失恢复案例
这个客户有点特殊,他的asm 磁盘组中跑的不是oracle 数据库而是直接跑acfs,然后再里面跑mysql数据库,也就是利用grid实现mysql的底层高可用,acfs实现共享挂载(我的理解一次也只能启动一个节点的mysql),现在asm disk的头被oracleasm createdisk重置之后,导致asm磁盘组无法mount,从而acfs也无法mount.对于这个故障,让现场提供被破坏磁盘使用dd前面100M 发给我进行分析
使用kfed读取asm disk磁盘头信息

H:\TEMP\0423\0423>kfed read data3_100m
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                  1096040823 ; 0x00c: 0x41544177
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
005B78600 00000000 00000000 00000000 41544177  [............wATA]
005B78610 00000000 00000000 00000000 00000000  [................]
005B78620 4C43524F 4B534944 41544144 00000033  [ORCLDISKDATA3...]
005B78630 00000000 00000000 00000000 00000000  [................]
  Repeat 252 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

这里可以发现比较明显的asmlib的标记信息ORCLDISKDATA3,证明该磁盘被oracleasm createdisk重建之后,没有再进行kfed修复或者重建新磁盘组

SUCCESS: CREATE DISKGROUP DATA EXTERNAL REDUNDANCY  DISK '/dev/oracleasm/disks/DATA1' SIZE 1430507M
 DISK '/dev/oracleasm/disks/DATA2' SIZE 1430507M
 DISK '/dev/oracleasm/disks/DATA3' SIZE 1430507M
 ATTRIBUTE 'compatible.asm'='12.2.0.1','compatible.advm'='12.2.0.1','au_size'='4M'

SUCCESS: CREATE DISKGROUP CRS EXTERNAL REDUNDANCY  DISK '/dev/oracleasm/disks/CRS' SIZE 190732M
 ATTRIBUTE 'compatible.asm'='12.2.0.1','compatible.advm'='12.2.0.1','au_size'='4M' /* ASMCA */

通过alert日志中磁盘组的创建语句,确认该磁组是ausize为4M,这样的情况下,asm的磁盘头备份备份和au备份应该都是好的,直接通过winhex来确认

再次通过kfed来确认相关磁盘头信息

H:\TEMP\0423\0423>kfed read data3_100m aus=4096k blkn=1022 aun=1|grep name
kfdhdb.dskname:               DATA_0002 ; 0x028: length=9
kfdhdb.grpname:                    DATA ; 0x048: length=4
kfdhdb.fgname:                DATA_0002 ; 0x068: length=9
kfdhdb.capname:                         ; 0x088: length=0

H:\TEMP\0423\0423>kfed read data3_100m aus=4096k blkn=0 aun=11|grep name
kfdhdb.dskname:               DATA_0002 ; 0x028: length=9
kfdhdb.grpname:                    DATA ; 0x048: length=4
kfdhdb.fgname:                DATA_0002 ; 0x068: length=9
kfdhdb.capname:                         ; 0x088: length=0

基于上述情况,证明磁盘头和au的备份都还在,而且au的备份是4M,完全包含了被createdisk破坏的1M数据,直接吧这个au给还原回去理论上就可以了,但是很不幸,这样处理之后,crs启动过程依旧无法找到表决盘

通过分析crs盘的情况,发现它的偏移量是错误的

通过分析是由于磁盘分区问题导致

磁盘 /dev/sdc：200.0 GB, 199996997632 字节，390619136 个扇区
Units = 扇区 of 1 * 512 = 512 bytes
扇区大小(逻辑/物理)：512 字节 / 512 字节
I/O 大小(最小/最佳)：512 字节 / 1048576 字节
磁盘标签类型：dos
磁盘标识符：0x00000000

   设备 Boot      Start         End      Blocks   Id  System
/dev/sdc1               1   390619135   195309567+  ee  GPT

磁盘 /dev/sdd：1500.0 GB, 1499996356608 字节，2929680384 个扇区
Units = 扇区 of 1 * 512 = 512 bytes
扇区大小(逻辑/物理)：512 字节 / 512 字节
I/O 大小(最小/最佳)：512 字节 / 1048576 字节
磁盘标签类型：gpt
Disk identifier: 99F1679E-DC32-4F6A-B85D-D91C87B09775


#         Start          End    Size  Type            Name
 1         2048   2929678335    1.4T  Linux LVM

处理好分区问题之后,重启crs,一切自动恢复成功