1、 问题描述

在使用 duplicate搭建DG时报错

包括 rman copy，rman备份

2、 问题分析

由于数据文件逻辑坏块导致物理备份不能正常进行。

使用 rman检查数据文件47

SELECT tablespace_name, segment_type, owner, segment_name

FROM dba_extents

         WHERE file_id = 47

and 1378432 between block_id AND block_id + blocks - 1;

看到该坏块属于表

3、 问题解决

生成该块的 rowid  

select dbms_rowid.rowid_create(1,30426,47,1378432,level) from dual connect by level<=100;

DBMS_ROWID.ROWID_C

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

AAAHbaAAvAAFQiAAAB

AAAHbaAAvAAFQiAAAC

AAAHbaAAvAAFQiAAAD

AAAHbaAAvAAFQiAABk

。。。。 略 。。。。

根据 rowid查询数据返回为空

select * from KTHIS5.EMR_CASE_HISTORY_DOCUMENT_DS where rowid in ();

判断该块为空 块

Move 该 表

Alter table KTHIS5.EMR_CASE_HISTORY_DOCUMENT_DS   move online;

查询该表的表空间为 KTHIS5 ， 创建测试表

create table test (n number,c varchar2(4000)) nologging tablespace KTHIS5 ;

给该临时表分配空间

alter table test allocate extent (DATAFILE ' +DATA/ORCL/kthis5_3 ' SIZE 8192k);

直到坏块属于该临时表后停止

select segment_name, segment_type, owner

        from dba_extents

       where file_id = 47

         and 1378432 between block_id

             and block_id + blocks -1 ;

创建触发器 ,当该坏块被格式化时给出提示

CREATE OR REPLACE TRIGGER corrupt_trigger

   AFTER INSERT ON test

   REFERENCING OLD AS p_old NEW AS new_p

   FOR EACH ROW

DECLARE

   corrupt EXCEPTION;

BEGIN

   IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)

  and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN

      RAISE corrupt;

   END IF;

EXCEPTION

   WHEN corrupt THEN

      RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted');

END;

/

往临时表插入数据 直到报 'Corrupt block has been formatted'

BEGIN

FOR i IN 1..1000000000 LOOP

INSERT /*+ APPEND */ INTO test select i, lpad('REFORMAT',3092, 'R') from dual;

commit ;

END LOOP;

END;

然后删除该表

Drop table test