创建测试表：
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>
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
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
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,,,,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,,,+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= ‘AAAST7ADJAAAAkHAAA’ and 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
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
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)
