oracle dataguard坏块,DataGuard 环境rman恢复主库坏块一例

环境:oracle 10g dataguard环境

问题:

ERROR:

ORA-01578: ORACLE data block corrupted (file # 4, block # 558)

ORA-01110: data file 4: '/data/oracle/oradata/primary/users01.dbf'

主库坏块报错

由于是DG环境，考虑从备用库COPY数据文件，利用rman恢复坏块

1.在主库上先查出出现坏块的object

SELECT   segment_name,

file_id,

block_id,

blocks,

block_id + blocks - 1 largest

FROM   dba_extents

WHERE   block_id = (SELECT   MAX (block_id)

FROM   dba_extents

WHERE   block_id < 558 AND file_id = 4)

AND file_id = 4;

SEGMENT_NAME        FILE_ID   BLOCK_ID     BLOCKS    LARGEST

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

TEST                   4        553          8        560

看到是test表上有坏块

2.用dbv检查datafile，查看一共有多少坏块

[oracle@primary bin]$ $ORACLE_HOME/bin/dbv file=/data/oracle/oradata/primary/users01.dbf

DBVERIFY: Release 10.2.0.3.0 - Production on Wed Apr 28 16:22:13 2010

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

DBVERIFY - Verification starting : FILE = /data/oracle/oradata/primary/users01.dbf

Page 558 is marked corrupt

Corrupt block relative dba: 0x0100022e (file 4, block 558)

Bad header found during dbv:

Data in bad block:

type: 4 format: 7 rdba: 0x0100022e

last change scn: 0x0000.0004af86 seq: 0x1 flg: 0x04

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

consistency value in tail: 0xaf860601

check value in block header: 0x84e4

computed block checksum: 0xf502

DBVERIFY - Verification complete

Total Pages Examined         : 1120

Total Pages Processed (Data) : 261

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 72

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 255

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 531

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Highest block SCN            : 309453 (0.309453)

坏块只有1个处理起来还是比较简单

3.先暂时跳过坏块，用户临时可用

sqlplus "/as sysdba"

declare

begin

dbms_repair.skip_corrupt_blocks (

schema_name => 'TEST',

object_name => 'TEST',

object_type => dbms_repair.table_object,

flags => dbms_repair.skip_flag);

end;

/

4.rman的方法恢复

停备库，从备库得到最新的users01.dbf，改名为users01.dbf.bak传到主库

scp  user01.dbf  192.168.6.2:/home/oracle

主库  mv /home/oracle/users01.dbf   /home/oracle/user01.dbf.bak

在主库上作

rman nocatalog

connect target

catalog datafilecopy '/home/oracle/users01.dbf.bak';

list copy of database;

run {

blockrecover datafile 4 block 558;

}

RMAN> catalog datafilecopy '/home/oracle/users01.dbf.bak';

cataloged datafile copy

datafile copy filename=/home/oracle/users01.dbf.bak recid=2 stamp=717525968

RMAN> list copy of database;

List of Datafile Copies

Key     File S Completion Time Ckp SCN    Ckp Time        Name

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

2       4    A 28-APR-10       338972     28-APR-10       /home/oracle/users01.dbf.bak

RMAN> run {

2>           blockrecover datafile 4 block 558;

3>           }

Starting blockrecover at 28-APR-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=142 devtype=DISK

channel ORA_DISK_1: restoring block(s) from datafile copy /home/oracle/users01.dbf.bak

starting media recovery

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

Finished blockrecover at 28-APR-10

RMAN>

5.关闭NOSKIP_FLAG

declare

begin

dbms_repair.skip_corrupt_blocks (

schema_name => 'TEST',

object_name => 'TEST',

object_type => dbms_repair.table_object,

flags => dbms_repair.NOSKIP_FLAG);

end;

/

恢复完成,重新启动备库恢复!!