查询数据坏块。

数据块损坏了查询表会报错：

SQL> select * from block_test;
select * from block_test
              *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 9, 块号 13)
ORA-01110: 数据文件 9: '/u01/app/oracle/oradata/orcl/blocktest.dbf'

用DBV查询具体坏块信息 ：

[oracle@TESTDB ~]$ dbv file=/u01/app/oracle/oradata/orcl/blocktest.dbf blocksize=8192

DBVERIFY: Release 11.2.0.1.0 - Production on Thu Jun 6 11:48:48 2013

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

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/blocktest.dbf
Page 13 is marked corrupt
Corrupt block relative dba: 0x0240000d (file 9, block 13)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0240000d
 last change scn: 0x0000.003ae4d6 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xe4d60601
 check value in block header: 0x6989
 computed block checksum: 0x2fca

DBVERIFY - Verification complete
Total Pages Examined         : 128
Total Pages Processed (Data) : 4
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 10
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 113
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3859646 (0.3859646)

注意：

如果dbv报错 DBV-00107: Unknown header format

则说明数据文件头部块有问题或者损坏。

用rman检查坏块：这个也查不出数据文件头部块坏块的情况

RMAN> backup validate datafile 9;

Starting backup at 06-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 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=00009 name=/u01/app/oracle/oradata/orcl/blocktest.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9    FAILED 0              113          128             3859646   
  File Name: /u01/app/oracle/oradata/orcl/blocktest.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       1              5               
  Index      0              0               
  Other      0              10              

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30507.trc for details
Finished backup at 06-JUN-13

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ------------------
         9         13          1                  0 CHECKSUM