一次简单的Oracle恢复Case实战记录
发现问题
某网友的数据库由于坏盘了，并且存储掉电，导致数据库无法open了。单看其数据库alertlog的错误来看，是非常之简单的，如下:
FriOct2610:33:532018 RecoveryofOnlineRedoLog:Thread1Group3Seq39Readingmem0 Mem#0:/fs/fs/oradata/orcl/redo03.log BlockrecoverystoppedatEOTrba39.77.16 Blockrecoverycompletedatrba39.77.16,scn0.1002048587 ORACLEInstanceorcl(pid=8)-Error600encounteredwhilerecoveringtransaction(9,30)onobject9149. FriOct2610:33:532018 Errorsinfile/fs/fs/oradata/admin/orcl/bdump/orcl_smon_192644.trc: ORA-00600:internalerrorcode,arguments:[6856],[0],[43],[],[],[],[],[] FriOct2610:33:562018 Errorsinfile/fs/fs/oradata/admin/orcl/bdump/orcl_smon_192644.trc: ORA-00600:internalerrorcode,arguments:[4194],[33],[36],[],[],[],[],[] Doingblockrecoveryforfile2block713 Blockrecoveryfromlogseq39,block82toscn1002048595
对于这种错误，很明显，屏蔽回滚段即可，屏蔽之后可顺利打开数据库，不过后面很快又会crash掉，因此重建undo也就绕过这个问题了。
打开数据库之后，再去观察数据库，会发现alertlog有不少的错误，如下所示：
FriOct2611:01:462018 Errorsinfile/fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc: ORA-00600:internalerrorcode,arguments:[17147],[0x110549070],[],[],[],[],[],[] FriOct2611:01:462018 Errorsinfile/fs/fs/oradata/admin/orcl/bdump/orcl_m001_373218.trc: ORA-00600:internalerrorcode,arguments:[kdddgb5],[196650],[0],[],[],[],[],[] ORA-600encounteredwhengeneratingserveralertSMG-4120 FriOct2611:01:472018 Errorsinfile/fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc: ORA-00600:internalerrorcode,arguments:[KGHALO4],[0x11047F6F0],[],[],[],[],[],[] ORA-600encounteredwhengeneratingserveralertSMG-4121 FriOct2611:01:482018 Errorsinfile/fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc: ORA-00600:internalerrorcode,arguments:[KGHALO4],[0x11047F6F0],[],[],[],[],[],[] ORA-600encounteredwhengeneratingserveralertSMG-4121 FriOct2611:01:502018 Errorsinfile/fs/fs/oradata/admin/orcl/bdump/orcl_m001_373218.trc: ORA-00600:internalerrorcode,arguments:[kdddgb5],[196650],[0],[],[],[],[],[] FriOct2611:02:222018 Errorsinfile/fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc: ORA-00600:internalerrorcode,arguments:[17114],[0x110549070],[],[],[],[],[],[] FriOct2611:02:232018 Errorsinfile/fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc: ORA-00600:internalerrorcode,arguments:[kebm_mmon_main_1],[39],[],[],[],[],[],[] ORA-00039:errorduringperiodicaction ORA-00600:internalerrorcode,arguments:[17114],[0x110549070],[],[],[],[],[],[] FriOct2611:03:302018 RestartingdeadbackgroundprocessMMON
除此之外，由于之外alertlog有坏块报错，因此对system进行了dbv检查，发现确实存在少量坏块，如下：
DBVERIFY:Release10.2.0.4.0-ProductiononFriOct2610:37:202018 Copyright(c)1982,2007,Oracle.Allrightsreserved. DBVERIFY-Verificationstarting:FILE=system01.dbf DBV-00200:Block,DBA4255202,alreadymarkedcorrupt BlockChecking:DBA=4258751,BlockType=KTB-manageddatablock dataheaderat0x11022a05c kdbchk:fsbo(596)wrong,(hsz4178) Page64447failedwithcheckcode6129 BlockChecking:DBA=4259386,BlockType=KTB-manageddatablock ****kdxcofbo=208!=24 ----endindexblockvalidation Page65082failedwithcheckcode6401 BlockChecking:DBA=4269609,BlockType=Unlimiteddatasegmentheader Incorrectextentcountintheextentmap:16777317 BlockChecking:DBA=4269612,BlockType=KTB-manageddatablock ****kdxcofbo=224!=216 ----endindexblockvalidation Page75308failedwithcheckcode6401 BlockChecking:DBA=4269615,BlockType=KTB-manageddatablock ****actualrowslockedbyitl2=1!=#intrans.header=0 ----endindexblockvalidation Page75311failedwithcheckcode6401 Page85271isinflux-mostlikelymediacorrupt Corruptblockrelativedba:0x00414d17(file1,block85271) Fracturedblockfoundduringdbv: Datainbadblock: type:6format:2rdba:0x00414d17 lastchangescn:0x0000.3afaf495seq:0x1flg:0x04 spare1:0x0spare2:0x0spare3:0x0 consistencyvalueintail:0xfe830601 checkvalueinblockheader:0x96c6 computedblockchecksum:0x3c6b Page85383isinflux-mostlikelymediacorrupt Corruptblockrelativedba:0x00414d87(file1,block85383) Fracturedblockfoundduringdbv: Datainbadblock: type:6format:2rdba:0x00414d87 lastchangescn:0x0000.3b6b9d19seq:0x1flg:0x06 spare1:0x0spare2:0x0spare3:0x0 consistencyvalueintail:0x970f0601 checkvalueinblockheader:0xe825 computedblockchecksum:0x3c6b DBVERIFY-Verificationcomplete TotalPagesExamined:640000 TotalPagesProcessed(Data):116312 TotalPagesFailing(Data):1 TotalPagesProcessed(Index):65914 TotalPagesFailing(Index):3 TotalPagesProcessed(Other):64634 TotalPagesProcessed(Seg):0 TotalPagesFailing(Seg):0 TotalPagesEmpty:393138 TotalPagesMarkedCorrupt:3 TotalPagesInflux:2 HighestblockSCN:1002028510(0.1002028510)
这部分错误，其实处理起来也不困难，部分是业务表的index，但是其他的几乎都是AWR相关基表，有2个坏块跟是system相关的基表和索引，分别是I_H_OBJ#_COL#和COM$，HISTGRM$。
对于业务索引，很简单，直接drop重建即可，对于这个sys的index，可以通过设置38003event进行drop重建。
对于基表COM$,HISTGRM$，由于是非bootstrap$核心对象，其实也可以处理掉的。
处理方法
不过考虑到这种毕竟是存储掉电，undo异常的情况，还是重建库更稳妥一些。最后补充一点，这个库稍微有点奇葩的地方是全库1.2TB，其中有个表的LOB自动980GB，重建数据库是相对较慢的。对于大表，且有LOB自动，通常建议基于分片，否则会报ORA-01555错误的，如下是常用的一个基于rowid的分片脚本，供大家参考：
setverifyoff
undefinerowid_ranges
undefinesegment_name
undefineowner
setheadoff
setpages0
settrimspoolon
select'whererowidbetween'''||
sys.dbms_rowid.rowid_create(1,d.oid,c.fid1,c.bid1,0)||
'''and'''||
sys.dbms_rowid.rowid_create(1,d.oid,c.fid2,c.bid2,9999)||''''||';'
from(selectdistinctb.rn,
first_value(a.fid)over(partitionbyb.rnorderbya.fid,a.bidrowsbetweenunboundedprecedingandunboundedfollowing)fid1,
last_value(a.fid)over(partitionbyb.rnorderbya.fid,a.bidrowsbetweenunboundedprecedingandunboundedfollowing)fid2,
first_value(decode(sign(range2-range1),
1,
a.bid+
((b.rn-a.range1)*a.chunks1),
a.bid))over(partitionbyb.rnorderbya.fid,a.bidrowsbetweenunboundedprecedingandunboundedfollowing)bid1,
last_value(decode(sign(range2-range1),
1,
a.bid+
((b.rn-a.range1+1)*a.chunks1)-1,
(a.bid+a.blocks-1)))over(partitionbyb.rnorderbya.fid,a.bidrowsbetweenunboundedprecedingandunboundedfollowing)bid2
from(selectfid,
bid,
blocks,
chunks1,
trunc((sum2-blocks+1-0.1)/chunks1)range1,
trunc((sum2-0.1)/chunks1)range2
from(select/*+rule*/
relative_fnofid,
block_idbid,
blocks,
sum(blocks)over()sum1,
trunc((sum(blocks)over())/&&rowid_ranges)chunks1,
sum(blocks)over(orderbyrelative_fno,block_id)sum2
fromdba_extents
wheresegment_name=upper('&&segment_name')
andowner=upper('&&owner'))
wheresum1>&&rowid_ranges)a,
(selectrownum-1rn
fromdual
connectbylevel<=&&rowid_ranges)b
whereb.rnbetweena.range1anda.range2)c,
(selectmax(data_object_id)oid
fromdba_objects
whereobject_name=upper('&&segment_name')
andowner=upper('&&owner')
anddata_object_idisnotnull)d
/
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。