oracle中rman坏块,Oracle技术之通过RMAN修复坏块

通过dbv和rman blockrecover对Oracle数据库坏块进行修复。

(1)rman备份时alert.log报如下错误：

Fri Jul  2 12:41:36 2010

Hex dump of (file 12, block 2718618) in trace file /u01/app/oracle/admin/bi/udump/bi_ora_31213.trc

Corrupt block relative dba: 0x03297b9a (file 12, block 2718618)

Fractured block found during backing up datafile

Data in bad block:

type: 6 format: 2 rdba: 0x03297b9a

last change scn: 0x0002.482fc15b seq: 0x1 flg: 0x06

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

consistency value in tail: 0x77b20601

check value in block header: 0x253

computed block checksum: 0xb6e9

Reread of blocknum=2718618, file=/u01/oradata/BI/estaging_user01.712.714072365. found same corrupt data

Reread of blocknum=2718618, file=/u01/oradata/BI/estaging_user01.712.714072365. found same corrupt data

Reread of blocknum=2718618, file=/u01/oradata/BI/estaging_user01.712.714072365. found same corrupt data

Reread of blocknum=2718618, file=/u01/oradata/BI/estaging_user01.712.714072365. found same corrupt data

Reread of blocknum=2718618, file=/u01/oradata/BI/estaging_user01.712.714072365. found same corrupt data

(2)查询数据库，可知含有坏块的对象：

SQL> col SEGMENT_NAME format a20

col PARTITION_NAME format a10

select owner,segment_name,partition_name from dba_extents where file_id = 12 and 2718618 between block_id and block_id + blocks-1;

OWNER                SEGMENT_NAME         PARTITION_

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

ESTAGING             LOG_RECORD_DETAIL_4  P20100630

(3)但全表扫描却没有任何问题：

SQL> select count(*) from ESTAGING.LOG_RECORD_DETAIL_4 partition (P20100630)；

COUNT(*)

----------

449937

SQL> select count(*) from ESTAGING.LOG_RECORD_DETAIL_4;

COUNT(*)

----------

42049608

(4)使用dbv检查发现有一个坏块(耗时较长)：

$ dbv file=/u01/oradata/BI/estaging_user01.712.714072365 BLOCKSIZE=8192

DBVERIFY: Release 10.2.0.4.0 - Production on Fri Jul 2 14:15:49 2010

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

DBVERIFY - Verification starting : FILE = /u01/oradata/BI/estaging_user01.712.714072365

Page 2718618 is influx - most likely media corrupt

Corrupt block relative dba: 0x03297b9a (file 12, block 2718618)

Fractured block found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x03297b9a

last change scn: 0x0002.482fc15b seq: 0x1 flg: 0x06

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

consistency value in tail: 0x77b20601

check value in block header: 0x253

computed block checksum: 0xb6e9

DBVERIFY - Verification complete

Total Pages Examined         : 2748160

Total Pages Processed (Data) : 2462446

Total Pages Failing   (Data) : 0

Total Pages Processed (Index)： 235234

Total Pages Failing   (Index)： 0

Total Pages Processed (Other)： 24969

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 25510

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 1

Highest block SCN            : 1229607770 (2.1229607770)

(5)使用rman检查含有坏块的数据文件(耗时较长)， 期间观察alert.log会发现同样的提示：

RMAN> backup validate datafile 12;

这个时候访问v$database_block_corruption可以看到详细的坏块的信息：

SQL> select * from v$database_block_corruption;

FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

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

12    2718618          1                  0 FRACTURED

(6)使用rman进行块恢复：

RMAN> blockrecover datafile 12 block 2718618 from backupset;

(7)块恢复后，执行BLOCKRECOVER CORRUPTION LIST，会自动按照V$DATABASE_BLOCK_CORRUPTION进行修复(耗时较长)：

RMAN> BLOCKRECOVER   CORRUPTION   LIST;

(8)这个时候再访问v$database_block_corruption就看不到详细的坏块信息了：

SQL> select * from v$database_block_corruption;

no rows selected

(9)再使用dbv检查发现没有坏块了(耗时较长)：

$ dbv file=/u01/oradata/BI/estaging_user01.712.714072365 BLOCKSIZE=8192

DBVERIFY: Release 10.2.0.4.0 - Production on Fri Jul 2 15:38:15 2010

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

DBVERIFY - Verification starting : FILE = /u01/oradata/BI/estaging_user01.712.714072365

DBVERIFY - Verification complete

Total Pages Examined         : 2749440

Total Pages Processed (Data) : 2463763

Total Pages Failing   (Data) : 0

Total Pages Processed (Index)： 235250

Total Pages Failing   (Index)： 0

Total Pages Processed (Other)： 24981

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 25446

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Highest block SCN            : 1230819157 (2.1230819157)

--End--