利用dbms_repair来标记和跳过坏块

SQL> select file_id, block_id, blocks from dba_extents where owner = 'LILC' and segment_name = 'TEST';
，，，，，
 610624    1024
 611648    1024
83 rows selected.

破坏之前的数据：

SQL> select count(*) from test;

  COUNT(*)

----------

    783018

RMAN> recover datafile 6 block 11620 clear;

RMAN> recover datafile 6 block 4467 clear;

RMAN> backup check logical validate datafile 6;

Starting backup at 23-SEP-15

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=00006 name=+DATA/phub/datafile/llc01.dbf

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

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

6    FAILED 0              20           12800           1991935   

  File Name: +DATA/phub/datafile/llc01.dbf

  Block Type Blocks Failing Blocks Processed

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

  Data       2              12140           

  Index      0              329             

  Other      1              311             

validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/phub/PHUB/trace/PHUB_ora_29666.trc for details

Finished backup at 23-SEP-15

数据查询肯定报错：

SQL> select count(*) from test;

select count(*) from test

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 4467)

ORA-01110: data file 6: '+DATA/phub/datafile/llc01.dbf'

skip_corrupt_blocks来跳过坏块：

SQL> exec dbms_repair.skip_corrupt_blocks(schema_name => 'LILC',object_name => 'TEST',flags => 1);

PL/SQL procedure successfully completed.

SQL> conn lilc/lilc;

Connected.

SQL> select count(*) from test;

  COUNT(*)

----------

    782884

这里少了34条数据

修复坏块；

RMAN> recover datafile 6 block 11620

2> ;

Starting recover at 23-SEP-15

using channel ORA_DISK_1

using channel ORA_DISK_2

finished standby search, restored 1 blocks

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 23-SEP-15

RMAN> recover datafile 6 block 4467

Starting recover at 23-SEP-15

using channel ORA_DISK_1

using channel ORA_DISK_2

finished standby search, restored 1 blocks

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 23-SEP-15

数据正常：

SQL> select count(*) from test;

  COUNT(*)

----------

    783018