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

原创

ora_0000 2011-04-12 13:57:29 博主文章分类：Oracle ©著作权

文章标签 环境 DataGuard 休闲 rman 恢复主库坏块 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者ora_0000的原创作品，请联系作者获取转载授权，否则将追究法律责任

环境: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

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;
/

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