暂无图片
分享
知识蛀虫
2019-07-15
疑似有数据坏块,但是用dbv检测没有检测出坏块

告警日志出现大量如下信息

Corrupt block relative dba: 0x21800983 (file 134, block 2435)
Bad header found during buffer read (logical check)
Data in bad block:
 type: 6 format: 2 rdba: 0x21800983
 last change scn: 0x0000.002f.57e793d8 seq: 0xc flg: 0x04
 spare3: 0x0
 consistency value in tail: 0x93d8060c
 check value in block header: 0xd053
 computed block checksum: 0x0

Hex dump of (file 131, block 1916) in trace file /u01/app/oracle/diag/rdbms/bieeprod/bieeprod2/trace/bieeprod2_p000_86685.trc

Corrupt block relative dba: 0x20c0077c (file 131, block 1916)
Bad header found during buffer read (logical check)
Data in bad block:
 type: 6 format: 2 rdba: 0x20c0077c
 last change scn: 0x0000.002f.57e79387 seq: 0x3 flg: 0x04
 spare3: 0x0
 consistency value in tail: 0x93870603
 check value in block header: 0x4f1a
 computed block checksum: 0x0

Hex dump of (file 131, block 1916) in trace file /u01/app/oracle/diag/rdbms/bieeprod/bieeprod2/trace/bieeprod2_p000_86685.trc

Corrupt block relative dba: 0x20c0077c (file 131, block 1916)
Bad header found during buffer read (logical check)
Data in bad block:
 type: 6 format: 2 rdba: 0x20c0077c
 last change scn: 0x0000.002f.57e79387 seq: 0x3 flg: 0x04
 spare3: 0x0
 consistency value in tail: 0x93870603
 check value in block header: 0x4f1a
 computed block checksum: 0x0

Hex dump of (file 131, block 1916) in trace file /u01/app/oracle/diag/rdbms/bieeprod/bieeprod2/trace/bieeprod2_p000_86685.trc

Corrupt block relative dba: 0x20c0077c (file 131, block 1916)
Bad header found during buffer read (logical check)
Data in bad block:
 type: 6 format: 2 rdba: 0x20c0077c
 last change scn: 0x0000.002f.57e79387 seq: 0x3 flg: 0x04
 spare3: 0x0
 consistency value in tail: 0x93870603
 check value in block header: 0x4f1a
 computed block checksum: 0x0

Hex dump of (file 134, block 2435) in trace file /u01/app/oracle/diag/rdbms/bieeprod/bieeprod2/trace/bieeprod2_p000_86685.trc


收藏
分享
11条回答
默认
最新
张小帅

先确认下对象是表还是索引:

SELECT segment_name,segment_type,extent_id,block_id, blocks

from dba_extents t

where file_id = &file_id

AND &block_id between block_id and (block_id + blocks - 1) ;


暂无图片 评论
暂无图片 有用 0
知识蛀虫

索引,但是我用dbv检测不出坏块信息

SQL> SELECT segment_name,segment_type,extent_id,block_id, blocks
from dba_extents t  2 
  3  where file_id =134
AND 2435 between block_id and (block_id + blocks - 1) ;  4 

SEGMENT_NAME                        SEGMENT_TYPE        EXTENT_ID   BLOCK_ID     BLOCKS
------------------                                  ----------                             ----------      ----------              ----------
JY_SALES_TARGET_AOWEI_N3  INDEX                                 15           2432                    32


[orarac@bidb1 ~]$ dbv file="+DATA/BIEEPROD/DATAFILE/bidwd.361.1008841929"  blocksize=32768

DBVERIFY: Release 12.2.0.1.0 - Production on Mon Jul 15 09:31:02 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = +DATA/BIEEPROD/DATAFILE/bidwd.361.1008841929


DBVERIFY - Verification complete

Total Pages Examined         : 652736
Total Pages Processed (Data) : 529828
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 119827
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2008
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1073
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

[orarac@bidb1 ~]$ dbv file="+DATA/BIEEPROD/DATAFILE/bidwd.401.1006871733"  blocksize=32768

DBVERIFY: Release 12.2.0.1.0 - Production on Mon Jul 15 09:34:33 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = +DATA/BIEEPROD/DATAFILE/bidwd.401.1006871733


DBVERIFY - Verification complete

Total Pages Examined         : 929280
Total Pages Processed (Data) : 737327
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 188559
Total Pages Failing   (Index): 0
Total Pages Processed (Lob)  : 205
Total Pages Failing   (Lob)  : 0
Total Pages Processed (Other): 2648
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 541
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)











暂无图片 评论
暂无图片 有用 0
张小帅


dbv file="+DATA/BIEEPROD/DATAFILE/bidwd.401.1006871733"  blocksize=8192

暂无图片 评论
暂无图片 有用 0
张小帅

检测索引真有坏快:

analyze index username.index_name validate structure;


暂无图片 评论
暂无图片 有用 0
知识蛀虫


分析完也没有坏块

SQL> analyze index bidw.JY_SALES_TARGET_AOWEI_N3 validate structure;

Index analyzed.


暂无图片 评论
暂无图片 有用 0
Albert

尝试用RMAN  BACKUP VALIDATE DATAFILE  、BACKUP VALIDATE CHECK LOGICAL DATAFILE关注一下Marked Corrupt

暂无图片 评论
暂无图片 有用 0
知识蛀虫

rman检查也没坏块信息

RMAN> BACKUP VALIDATE datafile 131;

Starting backup at 15-JUL-2019 15:00:52
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1301 instance=bieeprod1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1587 instance=bieeprod1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00131 name=+DATA/BIEEPROD/DATAFILE/bidwd.401.1006871733
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
131  OK     0              218          929280          203356195913
  File Name: +DATA/BIEEPROD/DATAFILE/bidwd.401.1006871733
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              736928         
  Index      0              189267         
  Other      0              2867           

Finished backup at 15-JUL-2019 15:01:40

RMAN>  BACKUP VALIDATE datafile 134;

Starting backup at 15-JUL-2019 15:02:09
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00134 name=+DATA/BIEEPROD/DATAFILE/bidwd.361.1008841929
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
134  OK     0              1009         652736          203356209059
  File Name: +DATA/BIEEPROD/DATAFILE/bidwd.361.1008841929
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              528328         
  Index      0              121393         
  Other      0              2006           

Finished backup at 15-JUL-2019 15:02:45

RMAN> BACKUP VALIDATE datafile 135;

Starting backup at 15-JUL-2019 15:03:44
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00135 name=+DATA/BIEEPROD/DATAFILE/bidwd.373.1008841961
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
135  OK     0              1120         652736          203356301145
  File Name: +DATA/BIEEPROD/DATAFILE/bidwd.373.1008841961
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              526570         
  Index      0              123124         
  Other      0              1922           

Finished backup at 15-JUL-2019 15:04:20


暂无图片 评论
暂无图片 有用 0
知识蛀虫

RMAN> BACKUP VALIDATE CHECK LOGICAL DATAFILE 131;

Starting backup at 15-JUL-2019 15:06:24
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00131 name=+DATA/BIEEPROD/DATAFILE/bidwd.401.1006871733
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
131  OK     0              218          929280          203356301651
  File Name: +DATA/BIEEPROD/DATAFILE/bidwd.401.1006871733
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              737183         
  Index      0              189013         
  Other      0              2866           

Finished backup at 15-JUL-2019 15:07:30

RMAN> BACKUP VALIDATE CHECK LOGICAL DATAFILE 134;

Starting backup at 15-JUL-2019 15:07:44
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00134 name=+DATA/BIEEPROD/DATAFILE/bidwd.361.1008841929
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
134  OK     0              1009         652736          203356301489
  File Name: +DATA/BIEEPROD/DATAFILE/bidwd.361.1008841929
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              528837         
  Index      0              120883         
  Other      0              2007           

Finished backup at 15-JUL-2019 15:08:40


暂无图片 评论
暂无图片 有用 0
Albert

考虑并评估一下在业务低峰期间刷一下buffer_cache,排除一下是否是buffer_cache Block逻辑问题

暂无图片 评论
暂无图片 有用 0
知识蛀虫

ok,谢谢


暂无图片 评论
暂无图片 有用 0
知识蛀虫
问题已关闭:
暂无图片 评论
暂无图片 有用 0
回答交流
提交
问题信息
请登录之后查看
邀请回答
暂无人订阅该标签,敬请期待~~
暂无图片墨值悬赏