创建测试表：

SQL> drop table testtest;

Table dropped.

SQL> create table testtest as select * from dba_objects;

Table created.

SQL> create index i_test on testtest(object_id);

Index created.

SQL> col SEGMENT_NAME format a15

SQL>

SQL> select segment_name , header_file , header_block,blocks from dba_segments where segment_name ='TESTTEST';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS

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

TESTTEST 201 1938 1536

select * from dba_extents where segment_name='TESTTEST' and owner='TT';

制造测试数据（坏块）

dd if=/dev/zero of=/u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf bs=8192 conv=notrunc seek=2180 count=1

dd if=/dev/zero of=/u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf bs=8192 conv=notrunc seek=2310 count=1

dd if=/dev/zero of=/u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf bs=8192 conv=notrunc seek=2320 count=1

检查坏块：

[oracle@test ~]$ dbv file=/u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf

DBVERIFY: Release 19.0.0.0.0 - Production on Mon Jun 24 21:08:14 2024

DBVERIFY - Verification starting : FILE = /u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf

Page 2180 is marked corrupt

Corrupt block relative dba: 0x32400884 (file 201, block 2180)

Completely zero block found during dbv:

Page 2310 is marked corrupt

Corrupt block relative dba: 0x32400906 (file 201, block 2310)

Completely zero block found during dbv:

Page 2320 is marked corrupt

Corrupt block relative dba: 0x32400910 (file 201, block 2320)

Completely zero block found during dbv:

DBVERIFY - Verification complete

Total Pages Examined : 2560

Total Pages Processed (Data) : 1873

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 326

Total Pages Failing (Index): 0

Total Pages Processed (Other): 190

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 168

Total Pages Marked Corrupt : 3

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 3492779 (0.3492779)

方法1：简单的 row id的方式

1) 先取得坏块中ROW ID的最小值，执行以下的语句：

通过 dba_objects 和 dba_data_files 获取

SELECT dbms_rowid.rowid_create(1,<OBJ_ID>,<RFN>,<BL>,0) from DUAL;

SELECT dbms_rowid.rowid_create(1,75003,201,2180,0) from DUAL; AAAST7ADJAAAAiEAAA

SELECT dbms_rowid.rowid_create(1,75003,201,2310,0) from DUAL; AAAST7ADJAAAAkGAAA

SELECT dbms_rowid.rowid_create(1,75003,201,2320,0) from DUAL; AAAST7ADJAAAAkQAAA

2）取得坏块中的ROW ID的最大值，执行以下的语句：

SELECT dbms_rowid.rowid_create(1,<OBJ_ID>,<RFN>,<BL>+1,0) from DUAL;

SELECT dbms_rowid.rowid_create(1,75003,201,2181,0) from DUAL; AAAST7ADJAAAAiFAAA

SELECT dbms_rowid.rowid_create(1,75003,201,2311,0) from DUAL; AAAST7ADJAAAAkHAAA

SELECT dbms_rowid.rowid_create(1,75003,201,2321,0) from DUAL; AAAST7ADJAAAAkRAAA

3）建议一个临时表存储那些没有坏块的数据，执行以下的语句：

CREATE TABLE test AS SELECT * FROM testtest Where 1=2;

4）保存那些不存在坏块的数据到临时表中，执行以下的语句：

INSERT INTO test SELECT /*+ ROWID(A) */ * FROM testtest A WHERE rowid < 'AAAST7ADJAAAAiEAAA';

INSERT INTO test SELECT /*+ ROWID(A) */ * FROM testtest A WHERE rowid >= 'AAAST7ADJAAAAiFAAA' and rowid<'AAAST7ADJAAAAkGAAA';

INSERT INTO test SELECT /*+ ROWID(A) */ * FROM testtest A WHERE rowid >= 'AAAST7ADJAAAAkHAAA' and rowid<'AAAST7ADJAAAAkQAAA';

INSERT INTO test SELECT /*+ ROWID(A) */ * FROM testtest A WHERE rowid >= 'AAAST7ADJAAAAkRAAA';

5) 根据临时表中的数据重建表，重建表上的索引，限制。

方法2：

通过设置10231诊断事件可以在导出的时候让Oracle忽略表损坏的块，10231是Oracle的内部诊断事件，设置在全表扫描时跳过坏块的数据块，

只导出包含正确块的数据，之后把表删除，再把导出的表数据导入新表，从而修复该表。

SQL> ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';

Session altered.

SQL> SQL> CREATE TABLE tt AS SELECT * FROM testtest;

Table created.

SQL> select count(*) from tt;

COUNT(*)

----------

73130

方法3：设置系统event事件然后导出（表比较大）

ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10';

exp tt/tt file=/home/oracle/testtest.dmp tables=tt.testtest;

[oracle@test ~]$ exp tt/tt file=/home/oracle/testtest.dmp tables=tt.testtest;

Export: Release 19.0.0.0.0 - Production on Mon Jun 24 21:29:47 2024

Version 19.22.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.22.0.0.0

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table TESTTEST 73130 rows exported

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

删除表然后再导入。

完事后关闭

alter system set events='10231 trace name context off';

方法4：坏块较多，可以尝试如下过程（硬解析会比较高）：

set serveroutput on

DECLARE

TYPE extent_rec IS record (data_object_id number,relative_fno number,BLOCK_ID number,blocks number);

t_extent extent_rec;

bad_rows number := 0 ;

error_code number;

v_block_id number;

v_rowid rowid;

v_start_rowid rowid;

v_end_rowid rowid;

v_error_rowid rowid;

v_sql1 varchar(1000):='';

v_sql2 varchar(1000):='';

---坏块所在的用户表名

v_table VARCHAR(30):='TESTTEST';

v_owner VARCHAR(30):='TT';

---获取段里面每个extent里面的block_id以及block数量,extent_id=0包含段头ID，要从段头ID之后开始

CURSOR c_extent IS select b.data_object_id,a.relative_fno,c.header_block+1 start_block_id,a.blocks-(c.header_block-a.block_id+1)

from dba_extents a,dba_objects b,dba_segments c

where a.segment_name=b.object_name and a.segment_name=c.segment_name and a.owner=c.owner

and a.owner=b.owner

and b.object_name=v_table

and b.owner=v_owner

and a.extent_id=0

union all

select b.data_object_id,a.relative_fno,a.block_id start_block_id,blocks

from dba_extents a,dba_objects b

where a.segment_name=b.object_name

and a.owner=b.owner

and b.object_name=v_table

and b.owner=v_owner

and a.extent_id>0;

BEGIN

OPEN c_extent;

LOOP

FETCH c_extent INTO t_extent ;

exit when c_extent%notfound;

begin

---构造extent的最小开始rowid以及可能的最大rowid,660行是一个块里面最多存放的行数量

v_start_rowid:=dbms_rowid.rowid_create(1,t_extent.data_object_id,t_extent.relative_fno,t_extent.block_id,0);

v_end_rowid:=dbms_rowid.rowid_create(1,t_extent.data_object_id,t_extent.relative_fno,t_extent.block_id+t_extent.blocks-1,660);

---按extent进行rowid数据扫描,将正常的数据插入test_tab_normal

v_sql1:='insert into test_tab_normal select /*+ ROWID(A) */ * from '||v_owner||'.'||v_table||' A where rowid between '''||v_start_rowid||''' and '''||v_end_rowid||'''';

execute immediate v_sql1;

commit;

---如果按extent遇到报错或者查询数据失败则捕获异常转成按extent进行逐行的rowid扫描

exception

when others then

---按rowid逐行进行检索，插入 test_tab_normal

for a in 0..t_extent.blocks-1 loop

v_block_id:=t_extent.block_id+a;

for b in 0..660 loop

begin

v_rowid:=dbms_rowid.rowid_create(1,t_extent.data_object_id,t_extent.relative_fno,v_block_id,b);

v_sql2:='insert into test_tab_normal select /*+ ROWID(A) */ * from '||v_owner||'.'||v_table||' A where rowid ='''||v_rowid||'''';

execute immediate v_sql2;

---遇到坏块报错，插入bad_rows,由于是构造的rowid，可能会话插入很多的空行数据

exception

when others then

error_code:=sqlcode;

if error_code in (-1410, -8103, -1578) then

v_error_rowid:= v_rowid;

bad_rows := bad_rows + 1;

insert into bad_rows values(v_error_rowid, error_code||' block_id:'||v_block_id);

commit;

else

raise;

end if;

end;

end loop;

commit;

end loop;

commit;

END;

END LOOP;

commit;

CLOSE c_extent;

dbms_output.put_line('Total Bad Rows: '||bad_rows);

END;

/

SQL> select count(*) from tt.test_tab_normal;

COUNT(*)

----------

73130

SQL> select count(*) from tt.bad_rows;

COUNT(*)

----------

4627

方法5：通过rman方式进行恢复

少量坏块，可以直接恢复指定的坏块。

blockrecover datafile 201 block 2180;

大量坏块：

backup validate datafile 201;

blockrecover corruption list;

https://blog.csdn.net/sinat_36757755/article/details/128985053

Extracting Data from a Corrupt Table using ROWID Range Scans (Doc ID 61685.1)

Handling Oracle Block Corruptions (Doc ID 28814.1)

ITPub博客

坏块的几种修复方式

最新文章