sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle坏块 通过dbv检测排除修复数据库坏块的过程案例

时间:2016-07-08 22:14   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 运维DBA反映生产的Oracle数据库的alert日志出现坏块,通过dbv工具检测排除Oracle坏块

1.alert文件出现Oracle坏块警告

Fri Sep 16 02:58:25 2011
Hex dump of (file 19, block 1444767) in trace file /opt/oracle/admin/ora9i/udump/ora9i_ora_24702.trc
Corrupt block relative dba: 0x04d60b9f (file 19, block 1444767)
Fractured block found during backing up datafile
Data in bad block:
type: 6 format: 2 rdba: 0x04d60b9f
last change scn: 0x0abf.56961827 seq: 0×1 flg: 0×04
spare1: 0×0 spare2: 0×0 spare3: 0×0
consistency value in tail: 0x7c340601
check value in block header: 0xba17
computed block checksum: 0×6413
Reread of blocknum=1444767, file=/opt/oracle/oradata/ora9i/TS_INDX_Base.005.dbf. found valid data

因为这个是我们比较重要的业务服务器,如果出现坏块,不能及时处理,后果将不堪设想,因此我马上检查

先查看是什么对象:(结果是一个分区表的index,担心减少一半,index大不了重建,无大事)

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  FROM DBA_EXTENTS A
 WHERE FILE_ID = &FILE_ID
   AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

2.然后检查是否真的坏块

方法一:dbv

[oracle@DB1 bdump]$ dbv file=/opt/oracle/oradata/ora9i/TS_INDX_Base.005.dbf blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on Fri Sep 16 09:15:11 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /opt/oracle/oradata/ora9i/TS_INDX_Base.005.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 2207744
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 2201581
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 3271
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 2892
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 1454468581 (2751.1454468581)

结论无坏块

方法二:bbed

[oracle@DB1 lib]$ bbed
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Fri Sep 16 10:01:26 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/opt/oracle/oradata/ora9i/TS_INDX_Base.005.dbf'
        FILENAME        /opt/oracle/oradata/ora9i/TS_INDX_Base.005.dbf

BBED> set block 1444767
        BLOCK#          1444767

BBED> VERIFY 
DBVERIFY - Verification starting
FILE = /opt/oracle/oradata/ora9i/TS_INDX_Base.005.dbf
BLOCK = 1444767


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 1
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

再次证明无坏块

方法三:rman
RMAN> backup validate datafile 19;
结果查询V$BACKUP_CORRUPTION也无坏块

方法四:使用index查询
结果也正常,没报错,alert中无错误记录

发现alert报错,但是实际没有错误,查询相关资料发现
从日志中可以看到,提示Corrupt的block对应的dba为0x04d60b9f (file 19, block 1444767),data block的类型为6(6为trans data,所有的data和index blocks都是该类型)。Oracle发现block有可能corrupt后,进行了reread,结果为found valid data,说明数据块未损坏。Fractured block found,表示rman发现这个数据块正在被使用,这时rman会进行重新读取,如果再次失败,才认为是坏块。如果第二次尝试读取时成功,则表示数据完好,不会产生影响。此类信息在IO负载较高的情况下进行rman备份时比较容易出现。
查询系统发现,正好该时间段是系统io负载最高的时候


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle坏块 通过dbv检测排除修复数据库坏块的过程案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/760.html

Oracle研究中心

关键词:

Oracle坏块

dbv检测排除修复数据库坏块的过程案例

Oralce坏块的解决办法