问题描述
最近帮助用户恢复了一个由于存储故障导致的数据库灾难,整个数据库容量4TB,浪潮的存储设备,控制器损坏导致数据库故障。
从启动信息来看,首先出现故障的是控制文件:
Read from controlfile member 'oradata/ORCL/control01.ctl' has found a fractured block (blk# 25, seq# 1909664) Read from controlfile member 'oradata/ORCL/control02.ctl' has found a fractured block (blk# 25, seq# 1909664) Read from controlfile member 'oradata/ORCL/control03.ctl' has found a fractured block (blk# 25, seq# 1909664)
专家解答
我们可以用dbv来检查一下控制文件的损坏情况,控制文件块大小是16K,检查3个控制文件,都出现了不一致:
dbv file=control01.ctl DBVERIFY: Release 10.2.0.1.0 - Production on Thu Dec 2 21:40:27 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. DBV-00103: Specified BLOCKSIZE (8192) differs from actual (16384) [oracle@oradbserver ORCL]$ dbv file=control01.ctl blocksize=16384 DBVERIFY: Release 10.2.0.1.0 - Production on Thu Dec 2 21:40:44 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = control01.ctl Page 19 is influx - most likely media corrupt Corrupt block relative dba: 0x00000013 (file 0, block 19) Fractured block found during dbv: Data in bad block: type: 21 format: 2 rdba: 0x00000013 last change scn: 0xffff.001d238a seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x23791501 check value in block header: 0x6ad5 computed block checksum: 0xf3 Page 25 is marked corrupt Corrupt block relative dba: 0x00000019 (file 0, block 25) Bad check value found during dbv: Data in bad block: type: 21 format: 2 rdba: 0x00000019 last change scn: 0xffff.001d2388 seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x23881501 check value in block header: 0xbe7a computed block checksum: 0x52d0 Page 27 is influx - most likely media corrupt Corrupt block relative dba: 0x0000001b (file 0, block 27) Fractured block found during dbv: Data in bad block: type: 21 format: 2 rdba: 0x0000001b last change scn: 0xffff.001d238b seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x23501501 check value in block header: 0x9663 computed block checksum: 0x5fc4 Page 316 is influx - most likely media corrupt Corrupt block relative dba: 0x0000013c (file 0, block 316) Fractured block found during dbv: Data in bad block: type: 21 format: 2 rdba: 0x0000013c last change scn: 0xffff.001d2393 seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x23811501 check value in block header: 0xc99d computed block checksum: 0x12 DBVERIFY - Verification complete Total Pages Examined : 662 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 319 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 339 Total Pages Marked Corrupt : 4 Total Pages Influx : 3 Highest block SCN : 1909665 (65535.1909665)
注意控制文件也是有一致性校验的。
但是我仍然可以通过strings命令从损坏的控制文件中提取文件名,重建控制文件。
剩下的是UNDO表空间损坏,这个不太可怕,强制来启动数据库,出现2662错误:
Errors in file /ORCL/udump/orcl_ora_4792.trc: ORA-00600: internal error code, arguments: [2662], [1], [1574405318], [1], [1574541355], [8388617], [], [] Errors in file /ORCL/udump/orcl_ora_4792.trc: ORA-00600: internal error code, arguments: [2662], [1], [1574405318], [1], [1574541355], [8388617], [], []
这里我设置了_minimum_giga_scn参数来推进SCN
_minimum_giga_scn=1047
增进这个参数后,启动数据库可以在ALERT文件中看到:
Advancing SCN to 1124207689728 according to _minimum_giga_scn
Oracle将SCN增进到 1047 * 1024 * 1024 * 1024 = 1124207689728 .
最后启动数据库后,遇到了以下错误:
ORA-00604: error occurred at recursive SQL level 1 ORA-08102: index key not found, obj# 239, file 1, block 1674 (2) ORA-12012: error on auto execute of job 1 ORA-08102: index key not found, obj# 239, file 1, block 1674 (2)
这是索引的一致性问题,找到这个对象:
SQL> select object_name from dba_objects where object_id=239; OBJECT_NAME -------------------------------------------------------------------------------- I_JOB_NEXT
直接DROP掉了重建:
SQL> drop index I_JOB_NEXT; Index dropped. SQL> create index i_job_next on job$ (next_date); Index created.
最后修改时间:2019-05-08 11:05:15
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。