oracle 普通表空间数据文件坏块处理详解
模拟损坏
#获取表的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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69980685/viewspace-3002239/ ，如需转载，请注明出处，否则将追究法律责任。
