坏块的几种修复方式

来源：这里教程网时间：2026-03-03 20:08:33 作者：

创建测试表： 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,<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 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)