本文共 6318 字，大约阅读时间需要 21 分钟。
有某客户由于硬件故障，导致数据库无法启动，让我们介入处理
数据库启动报错 Mon Feb 26 17:28:24 2018 ALTER DATABASE OPEN Beginning crash recovery of 1 threads parallel recovery started with 3 processes Started redo scan Completed redo scan read 2054 KB redo, 509 data blocks need recovery Started redo application at Thread 1: logseq 41341, block 54 Recovery of Online Redo Log: Thread 1 Group 1 Seq 41341 Reading mem 0 Mem # 0: E:\ORADATA\ORCL\REDO01.LOG Completed redo application of 1.77MB KCF: read , write or open error, block=0x16439 online=1 file =1 'E:\ORADATA\ORCL\SYSTEM01.DBF' error=27070 txt: 'OSD-04016: 异步 I /O 请求排队时出错。 O /S-Error : (OS 1) 函数不正确。' Errors in file d:\oracle\diag\rdbms\orcl\orcl\trace\orcl_dbw0_4928.trc: ORA-01243: system tablespace file suffered media failure ORA-01114: IO error writing block to file 1 (block # 91193) ORA-01110: data file 1: 'E:\ORADATA\ORCL\SYSTEM01.DBF' ORA-27070: async read /write failed OSD-04016: 异步 I /O 请求排队时出错。 O /S-Error : (OS 1) 函数不正确。 DBW0 (ospid: 4928): terminating the instance due to error 1243 Mon Feb 26 17:28:29 2018 Instance terminated by DBW0, pid = 4928 |
DBVERIFY: Release 11.2.0.1.0 - Production on 星期二 2月 27 17:20:14 2018 Copyright (c) 1982, 2009, Oracle and /or its affiliates. All rights reserved. DBVERIFY - 开始验证: FILE = D:\OK\SYSTEM01.DBF 页 91156 标记为损坏 Corrupt block relative dba: 0x00416414 ( file 1, block 91156) Bad header found during dbv: Data in bad block: type : 229 format : 6 rdba: 0xe1d9e3e7 last change scn: 0xd682.ffc8c7cb seq : 0x8c flg: 0x8c spare1: 0xc0 spare2: 0xf6 spare3: 0x70b3 consistency value in tail : 0x71f50602 check value in block header: 0x8195 computed block checksum: 0x8689 DBVERIFY - 验证完成 检查的页总数: 215040 处理的页总数 (数据): 178112 失败的页总数 (数据): 0 处理的页总数 (索引): 19070 失败的页总数 (索引): 0 处理的页总数 (其他): 3118 处理的总页数 (段) : 1 失败的总页数 (段) : 0 空的页总数: 14739 标记为损坏的总页数: 1 流入的页总数: 0 加密的总页数 : 0 最高块 SCN : 1638554501 (0.1638554501) |
这里比较明显重构出来的system文件只有block 91156坏块,这里注意和没有处理之前的坏块不一样
通过dump分析坏块所属对象 Start dump data block from file D:\OK\SYSTEM01.DBF minblk 91156 maxblk 91156 V10 STYLE FILE HEADER: Compatibility Vsn = 186646528=0xb200000 Db ID=1383974140=0x527dc4fc, Db Name= 'ORCL' Activation ID=0=0x0 Control Seq=806694=0xc4f26, File size=215040=0x34800 File Number=1, Blksiz=8192, File Type=3 DATA Dump all the blocks in range: buffer tsn: 0 rdba: 0xe1d9e3e7 (903 /1696743 ) scn: 0xd682.ffc8c7cb seq : 0x8c flg: 0x8c tail : 0x71f50602 frmt: 0x06 chkval: 0x8195 type : 0xe5=unknown Hex dump of corrupt header 4 = CORRUPT Start dump data block from file D:\OK\SYSTEM01.DBF minblk 91155 maxblk 91155 V10 STYLE FILE HEADER: Compatibility Vsn = 186646528=0xb200000 Db ID=1383974140=0x527dc4fc, Db Name= 'ORCL' Activation ID=0=0x0 Control Seq=806694=0xc4f26, File size=215040=0x34800 File Number=1, Blksiz=8192, File Type=3 DATA Dump all the blocks in range: buffer tsn: 0 rdba: 0x00416413 (1 /91155 ) scn: 0x0000.613ad8d6 seq : 0x01 flg: 0x06 tail : 0xd8d60601 frmt: 0x02 chkval: 0xcc5f type : 0x06=trans data Hex dump of block: st=0, typ_found=1 Block header dump: 0x00416413 Object id on Block? Y seg /obj : 0x25 csc: 0x00.613ad8ce itc: 2 flg: - typ: 2 - INDEX fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn /Fsc 0x01 0x0009.000.0016fda0 0x00c0130e.6f38.01 CB-- 0 scn 0x0000.3e6ed294 0x02 0x0009.008.0023e862 0x00c002cf.a217.13 --U- 1 fsc 0x0000.613ad8d6 Start dump data block from file D:\OK\SYSTEM01.DBF minblk 91157 maxblk 91157 V10 STYLE FILE HEADER: Compatibility Vsn = 186646528=0xb200000 Db ID=1383974140=0x527dc4fc, Db Name= 'ORCL' Activation ID=0=0x0 Control Seq=806694=0xc4f26, File size=215040=0x34800 File Number=1, Blksiz=8192, File Type=3 DATA Dump all the blocks in range: buffer tsn: 0 rdba: 0x00416415 (1 /91157 ) scn: 0x0000.6193dc0c seq : 0x01 flg: 0x06 tail : 0xdc0c0601 frmt: 0x02 chkval: 0x8c21 type : 0x06=trans data Hex dump of block: st=0, typ_found=1 Block header dump: 0x00416415 Object id on Block? Y seg /obj : 0x25 csc: 0x00.6193dc04 itc: 2 flg: - typ: 2 - INDEX fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn /Fsc 0x01 0x0006.00c.001ee103 0x00c008a5.8dbd.02 C--- 0 scn 0x0000.57303e03 0x02 0x000b.00f.00053100 0x00c008af.1563.09 --U- 1 fsc 0x0000.6193dc0c |
这里比较明显，可以确定坏块为index，object_id=0×25=37,通过查询其他库，确定为i_obj2(obj$的index)
使用该文件启动数据库 SQL> alter database rename file 'E:\ORADATA\ORCL\SYSTEM01.DBF' to 'd:\orcl\SYSTEM01.DBF' ; 数据库已更改。 SQL> recover database ; 完成介质恢复。 SQL> alter database open ; 数据库已更改。 |
检查alert日志
发现smon进程由于坏块的存储，出现大量报错,需要处理,不然数据库一段时间后就会crash. Tue Feb 27 20:31:23 2018 QMNC started with pid=27, OS id =4652 Completed: alter database open Tue Feb 27 20:31:25 2018 Starting background process CJQ0 Tue Feb 27 20:31:25 2018 CJQ0 started with pid=30, OS id =2172 Tue Feb 27 20:31:25 2018 db_recovery_file_dest_size of 3912 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. Hex dump of ( file 1, block 91156) in trace file d:\oracle\diag\rdbms\orcl\orcl\trace\orcl_cjq0_2172.trc Corrupt block relative dba: 0x00416414 ( file 1, block 91156) Bad header found during multiblock buffer read Data in bad block: type : 229 format : 6 rdba: 0xe1d9e3e7 last change scn: 0xd682.ffc8c7cb seq : 0x8c flg: 0x8c spare1: 0xc0 spare2: 0xf6 spare3: 0x70b3 consistency value in tail : 0x71f50602 check value in block header: 0x8195 computed block checksum: 0x8689 Reading datafile 'D:\ORCL\SYSTEM01.DBF' for corruption at rdba: 0x00416414 ( file 1, block 91156) Reread ( file 1, block 91156) found same corrupt data Errors in file d:\oracle\diag\rdbms\orcl\orcl\trace\orcl_smon_3992.trc (incident=77085): ORA-01578: ORACLE data block corrupted ( file # 1, block # 91156) ORA-01110: data file 1: 'D:\ORCL\SYSTEM01.DBF' Incident details in : d:\oracle\diag\rdbms\orcl\orcl\incident\incdir_77085\orcl_smon_3992_i77085.trc Errors in file d:\oracle\diag\rdbms\orcl\orcl\trace\orcl_cjq0_2172.trc (incident=77221): ORA-01578: ORACLE data block corrupted ( file # 1, block # 91156) ORA-01110: data file 1: 'D:\ORCL\SYSTEM01.DBF' Incident details in : d:\oracle\diag\rdbms\orcl\orcl\incident\incdir_77221\orcl_cjq0_2172_i77221.trc |
重建i_obj2 index，参考：
转载：