当前位置：首页 -> AI编程基础 -> 数据库编程

11203RAC(asm)恢复一例(二)

2014-11-24 00:37:42 · 作者: · 浏览: 42
标签: 11203RAC asm 恢复
-- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) ----- Successfully dispatched ----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) ----- Executing ASYNC actions ----- END DDE Actions Dump (total 0 csec) ----- WAIT #4573319128: nam='control file sequential read' ela= 258 file#=0 block#=1 blocks=1 obj#=0 tim=237352138057 WAIT #4573319128: nam='control file sequential read' ela= 205 file#=1 block#=1 blocks=1 obj#=0 tim=237352138319 WAIT #4573319128: nam='control file sequential read' ela= 190 file#=0 block#=40 blocks=1 obj#=0 tim=237352138539 WAIT #4573319128: nam='control file sequential read' ela= 251 file#=0 block#=42 blocks=1 obj#=0 tim=237352138818 WAIT #4573319128: nam='control file sequential read' ela= 192 file#=0 block#=48 blocks=1 obj#=0 tim=237352139044 WAIT #4573319128: nam='control file sequential read' ela= 255 file#=0 block#=113 blocks=1 obj#=0 tim=237352139328 WAIT #4573319128: nam='KSV master wait' ela= 1 p1=0 p2=0 p3=0 obj#=0 tim=237352139400 Byte offset to file# 3 block# 176 is unknown Incident 115456 created, dump file: /oracle/db/diag/rdbms/hiatmpdb/hiatmpdb1/incident/incdir_115456/hiatmpdb1_ora_12583082_i115456.trc ORA-01578: ORACLE data block corrupted (file # 3, block # 176) ORA-01110: data file 3: '+DATA/hiatmpdb/datafile/undotbs1.264.830644315' ORA-01578: ORACLE data block corrupted (file # 3, block # 176) ORA-01110: data file 3: '+DATA/hiatmpdb/datafile/undotbs1.264.830644315' ORA-01578: ORACLE data block corrupted (file # 3, block # 176) ORA-01110: data file 3: '+DATA/hiatmpdb/datafile/undotbs1.264.830644315' 我们可以看到，在访问回滚段4的时候报错了，但是无法获得回滚段的时间戳。 Oracle 11g中的回滚段名称的格式如下： _SYSSMUx_时间戳. 实际上，回滚段的信息都存在undo$基表中，我们只需要获得该基表的数据即可。11g中该基表的数据在file 1 block 225block中。因此只需要dd该block，然后strings+grep就行了。最后利用隐含参数_offline_rollback_segments=(_SYSSMUx$) 和_corrupted_rollback_segments=(_SYSSMUx$) 来屏蔽，讲
数据库open。 open之后发现想drop问题回滚段居然报错，既然能open也就能够查询dba_rollback_segs试图了，最后发现还有部分回滚段状态也是异常的，因此通过类似这一点 方法来drop 回滚段：
1 2 3 4 5 alter session set "_smu_debug_mode" = 4; alter rollback segment "_SYSSMU3_83481414$" offline; drop rollback segment "_SYSSMU3_83481414$" ; alter rollback segment "_SYSSMU4_2115859630$" offline; drop rollback segment "_SYSSMU4_2115859630$" ;
注意，这里如果不这样做的话，无法清理回滚段，你想切换undo 表空间也会报错的。这个问题搞完后，最后发现一个数据文件的坏块，这个坏块折腾了我很长时间，非常奇怪：
1 2 3 4 5 6 7 8 9 10 11 continued from file: /oracle/db/diag/rdbms/hiatmpdb/hiatmpdb2/trace/hiatmpdb2_ora_13959382.trc ORA-01578: ORACLE 数据块损坏 (文件号 97, 块号 373505) ORA-01110: 数据文件 97: '+DATA/hiatmpdb/datafile/hiatmpts_in06.dbf' ========= Dump for incident 197185 (ORA 1578) ======== *** 2014-05-11 16:38:55.665 dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0) ----- Current SQL Statement for this session (sql_id=47bt6vfv19g6z) ----- select t.nid ,t.cpic1path,t.cpic2path from HIATMP.DETECT_SPEED_DATA_EHL_EXTEND t where t.cdevicecode like '%44900100000001%' and row num < 200
可以看到，97号文件存在一个坏块，我dump了一下该block，发现比较怪：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 ** 2014-05-11 18:51:21.074 Start dump data blocks tsn: 6 file#:97 minblk 373505 maxblk 373505 Block dump from cache: Dump of buffer cache at level 4 for tsn=6 rdba=407220993 BH (0x70000012ef08528) file#: 97 rdba: 0x1845b301 (97/373505) class: 8 ba: 0x70000012e5fe000 set: 33 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 1575,18 dbwrid: 0 obj: 90762 objn: 90762