二、使用DBMS_REPAIR.CHECK_OBJECT進行檢測
CHECK_OBJECT procedure檢查指定的object，并且將關于損壞和修補的指導信息裝入Repair Table。它將效驗指定object中所有塊的一致性。而在此之前已標識的塊就會被跳過。
SQL> @checkObject
SQL> set serveroutput on
SQL>
SQL> declare
2 rpr_count int;
3 begin
4 rpr_count := 0;
5 dbms_repair.check_object (
6 schema_name => 'SYSTEM',
7 object_name => 'T1',
8 repair_table_name => 'REPAIR_TABLE',
9 corrupt_count => rpr_count);
10 dbms_output.put_line('repair count: ' || to_char(rpr_count));
11 end;
12 /
repair count: 1
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 VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE
我們可以從repair_table中查詢壞塊的情況：
SQL> select object_name, block_id, corrupt_type, marked_corrupt,
2 corrupt_description, repair_description
3 from repair_table;
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
T1 3 1 FALSE
kdbchk: row locked by non-existent transaction
table=0 slot=0
lockid=32 ktbbhitc=1
mark block software corrupt
三、從壞塊中進行數據抽取
從repair_table中可以知道file 6的block 3 壞了，但注意此時這個塊還沒有被標識為壞塊，因此要在這個時候將任何有意義的數據趕快抽取出來。一旦該塊被標識為壞塊，整個塊就會被跳過。
1、 通過ALTER SYSTEM DUMP或trace中來獲取塊中包含的記錄數 (nrows = 3).
2、 查詢損壞的object，盡量抽取盡可能多的信息。
下面的查詢可以用來從壞塊中搶救數據。
建立一個臨時表(temp_t1)以方便數據的插入：
SQL> create table temp_t1 as
2 select * from system.t1
3 where dbms_rowid.rowid_block_number(rowid) = 3
4 and dbms_rowid.rowid_to_absolute_fno (rowid, 'SYSTEM','T1') = 6;
Table created.
SQL> select col1 from temp_t1;
COL1
----------
2
3
四、使用DBMS_REPAIR.FIX_CORRUPT_BLOCKS來標識壞塊
FIX_CORRUPT_BLOCKS procedure用來根據repair table中的信息修正指定objects中的壞塊。當這個塊被標識為壞了以后，做全表掃描將引起ORA-1578錯。
SQL> declare
2 fix_count int;
3 begin
4 fix_count := 0;
5 dbms_repair.fix_corrupt_blocks (
6 schema_name => 'SYSTEM',
7 object_name => 'T1',
8 object_type => dbms_repair.table_object,
9 repair_table_name => 'REPAIR_TABLE',
10 fix_count => fix_count);
11 dbms_output.put_line('fix count: ' || to_char(fix_count));
12 end;
13 /
fix count: 1
PL/SQL procedure successfully completed.
查詢repair_table可以看到block 3已經被標識：
SQL> select object_name, block_id, marked_corrupt
2 from repair_table;
OBJECT_NAME BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
T1 3 TRUE
這時再對table t1做全表掃描，ORA-1578將會出現。
SQL> select * from system.t1;
select * from system.t1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 3)
ORA-01110: data file 6: '/tmp/ts_corrupt.dbf'
文章來源于領測軟件測試網 http://www.kjueaiud.com/
