eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 诊断案例分享 - ACOUG 11月活动结束 | Blog首页 | Oracle数据库恢复: 存储及系统故障导致文件丢失 »

Oracle数据库恢复 : 存储故障导致的数据损坏
modb.pro

最近帮助用户恢复了一个由于存储故障导致的数据库灾难,整个数据库容量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.
错误消除,参考Metalink:1036858.6



历史上的今天...
    >> 2019-12-03文章:
    >> 2008-12-03文章:
    >> 2007-12-03文章:
    >> 2005-12-03文章:
           2005年的第一场雪

By eygle on 2010-12-03 00:15 | Comments (12) | Backup&Recovery | Case | 2668 |

12 Comments

"但是我仍然可以通过strings命令从损坏的控制文件中提取文件名,重建控制文件",能说的详细点吗?步骤

_minimum_giga_scn=1047
这个1047如何得出?谢谢!

1个4t的数据库scn为1047,打死我也不信。

这样处理之后,数据库是否需要重建?

1047 感觉偏大了 根据这个错误来看,理论上大于4就行了。。。

eygle,这个地方我觉得应该用6:
SQL> select (1*4294967296+1574541355)/(1024*1024*1024) from dual;

(1*4294967296+1574541355)/(102
------------------------------
5.46640590857714

你看一下这篇文章:
http://dbsnake.com/2010/12/-minimum-giga-scn.html

学习了!谢谢博主哦!


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com