二使用DBMS_REPAIRCHECK_OBJECT进行检测
CHECK_OBJECT procedure检查指定的object并且将关于损坏和修补的指导信息装入Repair Table它将效验指定object中所有块的一致性而在此之前已标识的块就会被跳过
SQL> @checkObject
SQL> set serveroutput on
SQL>
SQL> declare
rpr_count int;
begin
rpr_count := ;
dbms_repaircheck_object (
schema_name => SYSTEM
object_name => T
repair_table_name => REPAIR_TABLE
corrupt_count => rpr_count);
dbms_outputput_line(repair count: || to_char(rpr_count));
end;
/
repair count:
PL/SQL procedure successfully completed
repair_table的结构如下
SQL> desc repair_table
Name Null? Type
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR()
OBJECT_NAME NOT NULL VARCHAR()
BASEOBJECT_NAME VARCHAR()
PARTITION_NAME VARCHAR()
CORRUPT_DESCRIPTION VARCHAR()
REPAIR_DESCRIPTION VARCHAR()
MARKED_CORRUPT NOT NULL VARCHAR()
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE
我们可以从repair_table中查询坏块的情况
SQL> select object_name block_id corrupt_type marked_corrupt
corrupt_description repair_description
from repair_table;
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
CORRUPT_DESCRIPTION
REPAIR_DESCRIPTION
T FALSE
kdbchk: row locked by nonexistent transaction
table= slot=
lockid= ktbbhitc=
mark block software corrupt
三从坏块中进行数据抽取
从repair_table中可以知道file 的block 坏了但注意此时这个块还没有被标识为坏块因此要在这个时候将任何有意义的数据赶快抽取出来一旦该块被标识为坏块整个块就会被跳过
通过ALTER SYSTEM DUMP或trace中来获取块中包含的记录数 (nrows = )
查询损坏的object尽量抽取尽可能多的信息
下面的查询可以用来从坏块中抢救数据
建立一个临时表(temp_t)以方便数据的插入
SQL> create table temp_t as
select * from systemt
where dbms_rowidrowid_block_number(rowid) =
and dbms_rowidrowid_to_absolute_fno (rowid SYSTEMT) = ;
Table created
SQL> select col from temp_t;
COL
四使用DBMS_REPAIRFIX_CORRUPT_BLOCKS来标识坏块
FIX_CORRUPT_BLOCKS procedure用来根据repair table中的信息修正指定objects中的坏块当这个块被标识为坏了以后做全表扫描将引起ORA错
SQL> declare
fix_count int;
begin
fix_count := ;
dbms_repairfix_corrupt_blocks (
schema_name => SYSTEM
object_name => T
object_type => dbms_repairtable_object
repair_table_name => REPAIR_TABLE
fix_count => fix_count);
dbms_outputput_line(fix count: || to_char(fix_count));
end;
/
fix count:
PL/SQL procedure successfully completed
查询repair_table可以看到block 已经被标识
SQL> select object_name block_id marked_corrupt
from repair_table;
OBJECT_NAME BLOCK_ID MARKED_COR
T TRUE
这时再对table t做全表扫描ORA将会出现
SQL> select * from systemt;
select * from systemt
*
ERROR at line :
ORA: ORACLE data block corrupted (file # block # )
ORA: data file : /tmp/ts_corruptdbf
[] [] []
