Oracle basic Tutorial: Repairing Bad blocks through RMAN

Use dbv and rmanblockrecover to repair bad Oracle Database blocks.

Use dbv and rman blockrecover to repair bad Oracle Database blocks.

Use dbv and rman blockrecover to repair bad Oracle Database blocks.

(1) alert. log reports the following error during rman backup:

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 extends upt data

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

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

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

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

(2) query the database. We can see the objects with bad blocks:

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) but the full table scan has no problem:

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) using dbv check to find a bad block (time-consuming ):

$ Dbv file =/u01/oradata/BI/estaging_user01.712.714072365 block size = 8192

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

Copyright (c) 1982,200 7, Oracle. All rights reserved.

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

Page 2718618 is influx-most likely media upt

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 upt: 1

Total Pages Influx: 1

Highest block SCN: 1229607770 (2.1229607770)

(5) Use rman to check data files containing Bad blocks (which takes a long time). During this period, observe alert. log and you will find the same prompt:

RMAN> backup validate datafile 12;

In this case, you can access v $ database_block_corruption to view the detailed bad block information:

SQL> select * from v $ database_block_corruption;

FILE # BLOCK # BLOCKS upload uption_change # upload uptio

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

12 2718618 1 0 FRACTURED

(6) Use rman for block recovery:

RMAN> blockrecover datafile 12 block 2718618 from backupset;

(7) after the block is restored, the BLOCKRECOVER resume uption LIST will be executed, and the repair will be automatically performed according to V $ database_block_resume uption (time-consuming ):

RMAN> BLOCKRECOVER upload uption LIST;

(8) Access v $ database_block_corruption at this time and you will not be able to see the detailed bad block information:

SQL> select * from v $ database_block_corruption;

No rows selected

(9) Use dbv to check whether there are any bad blocks (time-consuming ):

$ Dbv file =/u01/oradata/BI/estaging_user01.712.714072365 block size = 8192

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

Copyright (c) 1982,200 7, 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 success upt: 0

Total Pages Influx: 0

Highest block SCN: 1230819157 (2.1230819157)

-- End --