oracle 普通表空间数据文件坏块处理详解

来源：这里教程网时间：2026-03-03 19:13:35 作者：

模拟损坏

#获取表的ROWID_BLOCK_NUMBER和rowid_relative_fno SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from def.d; DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) ------------------------------------ ------------------------------------ 131 5 131 5 131 5 131 5 131 5 #通过dba_segments 查询表的块信息 SQL> select owner,segment_name,header_file,header_block,blocks from dba_segments where owner='DEF' and segment_name='D'; OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS ------------------------------ -------------------- ----------- ------------ ---------- DEF D 5 130 8 RMAN> recover datafile 5 block 131 clear; Starting recover at 06-MAY-22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1154 device type=DISK Finished recover at 06-MAY-22 刷新oracle缓存 SQL> alter system flush buffer_cache; System altered. 测试 SQL> select * from def.d; select * from def.d * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 5, block # 131) ORA-01110: data file 5: '/tmp/soft/app/oracle/oradata/WLH/datafile/bb01.dbf'

方法一（blockrecover）（启用归档的情况）（坏块修复）

1.查找坏块

找坏块方法一、 SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 5 131 1 1003772 CHECKSUM 根据file_id和block_id查询坏块数据那一张段（表）(根据file_id和block_id)(我个人没查出来，发现表损坏后查不出来) SQL> select tablespace_name,segment_type,owner,segment_name,BLOCKS,BLOCK_ID from dba_extents where file_id=5 and BLOCK_ID=131; 找坏块方法二、 时间重组的情况先查看全库，时间不足针对性查看数据文件或者表空间 backup validate check logical database;（全部） backup validate check logical datafile 1;（文件号） backup validate check logical tablespace TT;（表空间） 找坏块方法三、 dbv file=/tmp/soft/app/oracle/oradata/WLH/datafile/bb01.dbf userid=system/oracle

2.blockrecover修复

RMAN> blockrecover datafile 5 block 131; Starting recover at 06-MAY-22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=23 device type=DISK channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00005 channel ORA_DISK_1: reading from backup piece /oracle/bak/aaa_WLH_1_1_1103974269_20220506 channel ORA_DISK_1: piece handle=/oracle/bak/aaa_WLH_1_1_1103974269_20220506 tag=TAG20220506T113109 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 06-MAY-22 例子： RMAN> blockrecover datafile 6 block 133; -- 单块修复 RMAN> blockrecover corruption list; -- 对视图中所有坏块修复

3.测试，表可用

SQL> select * from def.d; ID NAME ---------- -------------------- 2 abc 2 abc 2 abc 2 abc 2 abc

方法二（未启用归档）

检查坏块所在对象（用ROWID_BLOCK_NUMBER和rowid_relative_fno） SELECT tablespace_name, segment_type, owner, segment_name, partition_name FROM dba_extents WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks - 1; TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME ------------------------------ ------------------ -------------------- -------------------- ------------------------------ BB TABLE DEF D 如果坏块所在对象为索引，直接进行删除重建可以解决；如果坏块所在对象为表，检查能否通过日常导出备份或业务恢复。

如果是索引

1.rebuild索引 （方法一） alter index IDX_YHQT6_ID rebuild; 不行的话再analyze分析 analyze table YHQTEST_6 validate structure cascade online; 2.删除再建（方法二） 获取ddl（索引） 'TABLE','表名','用户名' SET SERVEROUTPUT ON SET LINESIZE 1000 SET FEEDBACK OFF set long 99999 set pagesize 4000 SELECT DBMS_METADATA.GET_DDL('INDEX','IDX_YHQT6_ID','SYS') FROM DUAL; 删索引，重建索引 drop index IDX_YHQT6_ID; CREATE INDEX "SYS"."IDX_YHQT6_ID" ON "SYS"."YHQTEST_6" ("OBJECT_ID")

如果为表

1) 新建repair table BEGIN DBMS_REPAIR.ADMIN_TABLES ( TABLE_NAME => 'REPAIR_TABLE', TABLE_TYPE => dbms_repair.repair_table, ACTION => dbms_repair.create_action, TABLESPACE => '&TABLESPACE_NAME'); END; 2) 检查坏块并记录到repair table（不是分区表的话，那行删掉） set serveroutput on DECLARE num_corrupt INT; BEGIN num_corrupt := 0; DBMS_REPAIR.CHECK_OBJECT ( SCHEMA_NAME => '&OWNER', OBJECT_NAME => '&TABLE', partition_name => '&PARTITION_NAME', REPAIR_TABLE_NAME => 'REPAIR_TABLE', corrupt_count => num_corrupt); DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt)); END; SQL> Select * from REPAIR_TABLE; OBJECT_ID TABLESPACE_ID RELATIVE_FILE_ID BLOCK_ID CORRUPT_TYPE SCHEM OBJECT_NAM BASEOBJECT PARTITION_ CORRUPT_DE REPAIR_DESCRIPTION MARKED_COR CHECK_TIM FIX_TIMES REFORMAT_ ---------- ------------- ---------------- ---------- ------------ ----- ---------- ---------- ---------- ---------- ------------------------------ ---------- --------- --------- --------- 87355 6 5 131 6148 DEF D mark block software corrupt TRUE 06-MAY-22 3) 标记坏块并设置为查询跳过（不是分区表的话，那行删掉） DECLARE num_fix INT; BEGIN num_fix := 0; DBMS_REPAIR.FIX_CORRUPT_BLOCKS ( SCHEMA_NAME => '&OWNER', OBJECT_NAME=> '&TABLE', partition_name => '&PARTITION_NAME', OBJECT_TYPE => dbms_repair.table_object, REPAIR_TABLE_NAME => 'REPAIR_TABLE', FIX_COUNT=> num_fix); DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix)); END; BEGIN DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ( SCHEMA_NAME => '&OWNER', OBJECT_NAME => '&TABLE', OBJECT_TYPE => dbms_repair.table_object, FLAGS => dbms_repair.skip_flag); END; / 4) 新建其他表，改名（CTAS） create table new_table_name as select * from old_table_name