oracle dbv验证,关于dbv检测的结果，有些不懂，请各位指点

处理测试环境的一个坏块问题，具体如下：

使用DBMS_REPAIR.SKIP_CORRUPT_BLOCKS处理后，dbv检测仍然报告有坏块；

即使把表drop表，还是报告有坏块，请教各位，要怎么做，dbv才能检测没有坏块呢？

一、坏块信息

测试环境alert日志提示有坏块：

Mon Feb 25 13:08:18 2013

Hex dump of (file 20, block 108406) in trace file /usr/oracle/admin/edu/udump/edu_ora_14291.trc

Corrupt block relative dba: 0x0501a776 (file 20, block 108406)

Bad header found during buffer read

Data in bad block:

type: 100 format: 2 rdba: 0x0501a776

last change scn: 0x0106.4d8954cc seq: 0x2 flg: 0x06

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x54cc0602

check value in block header: 0x964e

computed block checksum: 0x62

Reread of rdba: 0x0501a776 (file 20, block 108406) found same corrupted data

Mon Feb 25 13:08:25 2013

Corrupt Block Found

TSN = 6, TSNAME = EDUCATION

RFN = 20, BLK = 108406, RDBA = 83994486

OBJN = 58838, OBJD = 58838, OBJECT = TEST, SUBOBJECT =

SEGMENT OWNER = EDU, SEGMENT TYPE = Table Segment

从文件号和块号查询对象：

SQL> SELECT tablespace_name, segment_type, owner, segment_name

2  FROM dba_extents

3  WHERE file_id = 20

4  and 108406 between block_id AND block_id + blocks - 1;

TABLESPACE_NAME                SEGMENT_TYPE       OWNER                          SEGMENT_NAME

------------------------------ ------------------ ------------------------------ --------------------

EDUCATION                      TABLE              EDU                            TEST

使用rdba查询文件号和块号：

SQL> select to_number('0501a776','xxxxxxxx') from dual;

TO_NUMBER('0501A776','XXXXXXXX')

--------------------------------

83994486

SQL> select dbms_utility.data_block_address_file(83994486) filenum from dual;

FILENUM

----------

20

SQL> select dbms_utility.data_block_address_block(83994486) blocknum from dual;

BLOCKNUM

----------

108406

二、坏块的检测方法：

1、查询有坏块的表：

SQL> select count(1) from edu.test;

select count(1) from edu.test

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 20, block # 108406)

ORA-01110: data file 20: '/usr/oracle/oradata/edu/edu07.dbf'

2、使用analyze命令检测：

SQL> analyze table edu.test validate structure cascade;

analyze table edu.test validate structure cascade

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 20, block # 108406)

ORA-01110: data file 20: '/usr/oracle/oradata/edu/edu07.dbf'

3、使用exp检测：

[oracle@testdb ~]$ exp file=test.dmp tables=test

Export: Release 10.2.0.4.0 - Production on 星期一 2月 25 15:40:20 2013

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

Username: edu

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                           TEST

EXP-00056: ORACLE error 1578 encountered

ORA-01578: ORACLE data block corrupted (file # 20, block # 108406)

ORA-01110: data file 20: '/usr/oracle/oradata/edu/edu07.dbf'

Export terminated successfully with warnings.

注意expdp导出不会报错，不能用于检测。(ps：这里因为该表中有效数据是0行，所以导出0行，并不是无法导出数据)

[oracle@testdb ~]$ expdp directory=DUMPDIR dumpfile=test.dmp tables=test

Export: Release 10.2.0.4.0 - 64bit Production on 星期一, 25 2月, 2013 15:41:34

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

Username: edu

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "EDU"."SYS_EXPORT_TABLE_01":  edu/******** directory=DUMPDIR dumpfile=test.dmp tables=test

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "EDU"."TEST"                                5.195 KB       0 rows

Master table "EDU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for EDU.SYS_EXPORT_TABLE_01 is:

/usr/oracle/test.dmp

Job "EDU"."SYS_EXPORT_TABLE_01" successfully completed at 15:41:57

4、使用dbv检测坏块：

[oracle@testdb ~]$ dbv file=/usr/oracle/oradata/edu/edu07.dbf blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on 星期一 2月 25 13:02:43 2013

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

DBVERIFY - Verification starting : FILE = /usr/oracle/oradata/edu/edu07.dbf

Page 108406 is marked corrupt

Corrupt block relative dba: 0x0501a776 (file 20, block 108406)

Bad header found during dbv:

Data in bad block:

type: 100 format: 2 rdba: 0x0501a776

last change scn: 0x0106.4d8954cc seq: 0x2 flg: 0x06

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x54cc0602

check value in block header: 0x964e

computed block checksum: 0x62

DBVERIFY - Verification complete

Total Pages Examined         : 1310720

Total Pages Processed (Data) : 1198465

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 97720

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 10019

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 4515

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Highest block SCN            : 1839489834 (271.1839489834)

5、使用rman检测整个数据库：

RMAN> backup validate database;

Starting backup at 25-2月 -13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=256 devtype=DISK

channel ORA_DISK_1: starting compressed full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/25/2013 14:27:55

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

continuing other job steps, job failed will not be re-run

channel ORA_DISK_1: starting compressed full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/25/2013 14:27:55

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

RMAN> shutdown immediate

RMAN> startup mount

RMAN> backup validate database;

Starting backup at 25-2月 -13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=323 devtype=DISK

channel ORA_DISK_1: starting compressed full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

......

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 25-2月 -13

SQL> select * from v$database_block_corruption;

FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------- ------------------ ---------

20     108406          1                  0 CORRUPT

6、使用dbms_repair检测：

使用dbms_repair.admin_tables创建需要的表

SQL> BEGIN

2  DBMS_REPAIR.ADMIN_TABLES (

3  table_name => 'REPAIR_TABLE',

4  table_type => DBMS_REPAIR.REPAIR_TABLE,

5  action => DBMS_REPAIR.CREATE_ACTION,

6  tablespace => 'EDUCATION');

7  END;

8  /

PL/SQL procedure successfully completed.

SQL> desc repair_table;

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

OBJECT_ID                                 NOT NULL NUMBER

TABLESPACE_ID                             NOT NULL NUMBER

RELATIVE_FILE_ID                          NOT NULL NUMBER

BLOCK_ID                                  NOT NULL NUMBER

CORRUPT_TYPE                              NOT NULL NUMBER

SCHEMA_NAME                               NOT NULL VARCHAR2(30)

OBJECT_NAME                               NOT NULL VARCHAR2(30)

BASEOBJECT_NAME                                    VARCHAR2(30)

PARTITION_NAME                                     VARCHAR2(30)

CORRUPT_DESCRIPTION                                VARCHAR2(2000)

REPAIR_DESCRIPTION                                 VARCHAR2(200)

MARKED_CORRUPT                            NOT NULL VARCHAR2(10)

CHECK_TIMESTAMP                           NOT NULL DATE

FIX_TIMESTAMP                                      DATE

REFORMAT_TIMESTAMP                                 DATE

使用dbms_repair.check_object检测坏块：

SQL> SET SERVEROUTPUT ON

SQL> DECLARE num_corrupt INT;

2  BEGIN

3  num_corrupt := 0;

4  DBMS_REPAIR.CHECK_OBJECT (

5  schema_name => 'EDU',

6  object_name => 'TEST',

7  repair_table_name => 'REPAIR_TABLE',

8  corrupt_count => num_corrupt);

9  dbms_output.put_line(num_corrupt);

10  END;

11  /

1

PL/SQL procedure successfully completed.

查询repair_table表：

SQL> exec edu.print_table('select * from repair_table');

OBJECT_ID                     : 58838

TABLESPACE_ID                 : 6

RELATIVE_FILE_ID              : 20

BLOCK_ID                      : 108406

CORRUPT_TYPE                  : 6148

SCHEMA_NAME                   : EDU

OBJECT_NAME                   : TEST

BASEOBJECT_NAME               :

PARTITION_NAME                :

CORRUPT_DESCRIPTION           :

REPAIR_DESCRIPTION            : mark block software corrupt

MARKED_CORRUPT                : TRUE

CHECK_TIMESTAMP               : 25-2月 -2013 16:26:33

FIX_TIMESTAMP                 :

REFORMAT_TIMESTAMP            :

-----------------

PL/SQL procedure successfully completed.

三、解决方法：

1、删除包含坏块的对象：

如果块块对象是索引，可以删除索引，然后重建。

2、使用rman备份恢复块，即Block media recovery:

RMAN> blockrecover datafile 20 block 108406;

3、使用其他物理备份恢复包含坏块的数据文件(影响较大)：

SQL> recover datafile 20;

4、使用dbms_repair.skip_corrupt_blocks跳过坏块：

SQL> BEGIN

2  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

3  schema_name => 'EDU',

4  object_name => 'TEST',

5  object_type => DBMS_REPAIR.TABLE_OBJECT,

6  flags => DBMS_REPAIR.SKIP_FLAG);

7  END;

8  /

PL/SQL procedure successfully completed.

此时访问表，不再报错：

SQL> select count(1) from edu.test;

COUNT(1)

----------

0

后来测试使用dbv进行检测，仍然报告有坏块。

[oracle@testdb ~]$ dbv file=/usr/oracle/oradata/edu/edu07.dbf blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on 星期一 2月 25 17:35:23 2013

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

DBVERIFY - Verification starting : FILE = /usr/oracle/oradata/edu/edu07.dbf

Page 108406 is marked corrupt

Corrupt block relative dba: 0x0501a776 (file 20, block 108406)

Bad header found during dbv:

Data in bad block:

type: 100 format: 2 rdba: 0x0501a776

last change scn: 0x0106.4d8954cc seq: 0x2 flg: 0x06

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x54cc0602

check value in block header: 0x964e

computed block checksum: 0x62

DBVERIFY - Verification complete

Total Pages Examined         : 1310720

Total Pages Processed (Data) : 1198446

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 97727

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 10031

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 4515

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Highest block SCN            : 1839591990 (271.1839591990)

当然也可以设置回来：

SQL> BEGIN

2  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

3  schema_name => 'EDU',

4  object_name => 'TEST',

5  object_type => DBMS_REPAIR.TABLE_OBJECT,

6  flags => DBMS_REPAIR.NOSKIP_FLAG);

7  END;

8  /

PL/SQL procedure successfully completed.

SQL> select count(1) from edu.test;

select count(1) from edu.test

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 20, block # 108406)

ORA-01110: data file 20: '/usr/oracle/oradata/edu/edu07.dbf'

使用expdp/impdp导出导入：

SQL> insert into edu.test values (1111,'abc');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from edu.test where id=1111;

ERROR:

ORA-01578: ORACLE data block corrupted (file # 20, block # 108406)

ORA-01110: data file 20: '/usr/oracle/oradata/edu/edu07.dbf'

no rows selected

expdp能够导出正常的数据：

[oracle@testdb ~]$ expdp directory=DUMPDIR dumpfile=test.dmp tables=test

Export: Release 10.2.0.4.0 - 64bit Production on 星期一, 25 2月, 2013 17:03:13

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

Username: edu

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "EDU"."SYS_EXPORT_TABLE_01":  edu/******** directory=DUMPDIR dumpfile=test.dmp tables=test

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "EDU"."TEST"                                5.218 KB       1 rows

Master table "EDU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for EDU.SYS_EXPORT_TABLE_01 is:

/usr/oracle/test.dmp

Job "EDU"."SYS_EXPORT_TABLE_01" successfully completed at 17:03:28

使用impdp以truncate方式导入：

[oracle@testdb ~]$ impdp directory=DUMPDIR dumpfile=test.dmp tables=test table_exist_action=truncate

LRM-00101: unknown parameter name 'table_exist_action'

[oracle@testdb oracle]$ impdp directory=DUMPDIR dumpfile=test.dmp tables=test table_exists_action=truncate

Import: Release 10.2.0.4.0 - 64bit Production on 星期一, 25 2月, 2013 17:06:18

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

Username: edu

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "EDU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "EDU"."SYS_IMPORT_TABLE_01":  edu/******** directory=DUMPDIR dumpfile=test.dmp tables=test table_exists_action=truncate

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39153: Table "EDU"."TEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "EDU"."TEST"                                5.218 KB       1 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "EDU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 17:06:30

SQL> select count(1) from edu.test;

COUNT(1)

----------

1

使用dbv再次检测，仍然报告有坏块：

[oracle@testdb ~]$ dbv file=/usr/oracle/oradata/edu/edu07.dbf blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on 星期一 2月 25 17:09:09 2013

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

DBVERIFY - Verification starting : FILE = /usr/oracle/oradata/edu/edu07.dbf

Page 108406 is marked corrupt

Corrupt block relative dba: 0x0501a776 (file 20, block 108406)

Bad header found during dbv:

Data in bad block:

type: 100 format: 2 rdba: 0x0501a776

last change scn: 0x0106.4d8954cc seq: 0x2 flg: 0x06

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x54cc0602

check value in block header: 0x964e

computed block checksum: 0x62

DBVERIFY - Verification complete

Total Pages Examined         : 1310720

Total Pages Processed (Data) : 1198449

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 97728

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 10027

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 4515

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Highest block SCN            : 1839590591 (271.1839590591)

但是访问表不报错：

SQL> select * from edu.test;

ID NAME

---------- ----------

1111 abc

drop表后，进行dbv检测，还是报告有坏块。

SQL> drop table edu.test;

Table dropped.

[oracle@testdb ~]$ dbv file=/usr/oracle/oradata/edu/edu07.dbf blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on 星期一 2月 25 17:14:40 2013

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

DBVERIFY - Verification starting : FILE = /usr/oracle/oradata/edu/edu07.dbf

Page 108406 is marked corrupt

Corrupt block relative dba: 0x0501a776 (file 20, block 108406)

Bad header found during dbv:

Data in bad block:

type: 100 format: 2 rdba: 0x0501a776

last change scn: 0x0106.4d8954cc seq: 0x2 flg: 0x06

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x54cc0602

check value in block header: 0x964e

computed block checksum: 0x62

DBVERIFY - Verification complete

Total Pages Examined         : 1310720

Total Pages Processed (Data) : 1198449

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 97728

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 10027

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 4515

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Highest block SCN            : 1839590643 (271.1839590643)