http://blog.chinaunix.net/u1/50863/showart_400578.html
問題：用戶查詢一個表時，報數(shù)據(jù)文件有壞塊
目標：用戶可以接受丟失這些壞塊的數(shù)據(jù)，但該數(shù)據(jù)文件其它的好塊應該可以查詢數(shù)據(jù)。
下面是具體的步驟：
1.詢問用戶徐工出錯的表名，收集出錯信息
出錯表名：
fsgazhjf.fsgazhjf_tac_20061018
trace文件中的出錯信息：
***
Corrupt block relative dba: 0xb8428b33 (file 737, block 166707)
Fractured block found during user buffer read
Data in bad block -
type: 6 format: 2 rdba: 0xb8428b33
last change scn: 0x0000.0a66398d seq: 0x1 flg: 0x00
consistency value in tail: 0xbddc0601
check value in block header: 0x0, block checksum disabled
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
2.根據(jù)出錯塊id，查詢出該塊對應的物理表，跟第一步收集的比對
select * from dba_extents
where file_id=737 and block_id <= 166707 and (block_id + blocks - 1) >= 166707;
FSGAZHJF FSGAZHJF_TAC_20061018 TABLE FSGAZHJF_GSM_10 1201 737 166665 1048576 128 737
結(jié)果：的確是該表:FSGAZHJF_TAC_20061018，用戶FSGAZHJF，表空間FSGAZHJF_GSM_10
3.查詢該表，看報錯信息是否和第一步一致
select count(1) from fsgazhjf.fsgazhjf_tac_20061018;
結(jié)果：果然報錯
4.收集該表的所有索引
select * from dba_indexes where owner='FSGAZHJF' and lower(table_name)='fsgazhjf_tac_20061018';
no rows
結(jié)果：無索引
5.用dbv工具來check bad block
SQL> select file_id||' '||file_name from dba_data_files where file_id=737;
FILE_ID||''||FILE_NAME
------------------------------------------------------------------------------
--------------------
737 K:ORADATAORA8FSGAZHJF_GSM_10_50.DBF
C:>dbv file='K:ORADATAORA8FSGAZHJF_GSM_10_50.DBF' blocksize=8192 logfile='h:dbv.log'
DBVERIFY: Release 8.1.7.4.1 - Production on 星期四 11月 9 10:57:13 2006
(c) Copyright 2000 Oracle Corporation. All rights reserved.
DBVERIFY: Release 8.1.7.4.1 - Production on 星期四 11月 9 10:57:13 2006
(c) Copyright 2000 Oracle Corporation. All rights reserved.
DBVERIFY - 檢驗開始：FILE = K:ORADATAORA8FSGAZHJF_GSM_10_50.DBF
標記為損壞的頁面166708
***
Corrupt block relative dba: 0xb8428b34 (file 0, block 166708)
Bad header found during dbv:
Data in bad block -
type: 6 format: 2 rdba: 0xcf012b08
last change scn: 0x0000.0a91bf69 seq: 0x1 flg: 0x00
consistency value in tail: 0x0ccc0601
check value in block header: 0x0, block checksum disabled
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
標記為損壞的頁面166709
***
Corrupt block relative dba: 0xb8428b35 (file 0, block 166709)
Bad header found during dbv:
Data in bad block -
type: 6 format: 2 rdba: 0xce00e7e9
last change scn: 0x0000.0a910ce8 seq: 0x1 flg: 0x00
consistency value in tail: 0x0ce80601
check value in block header: 0x0, block checksum disabled
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
標記為損壞的頁面166710
***
Corrupt block relative dba: 0xb8428b36 (file 0, block 166710)
Bad header found during dbv:
Data in bad block -
type: 6 format: 2 rdba: 0xce00e7ea
last change scn: 0x0000.0a910ce8 seq: 0x1 flg: 0x00
consistency value in tail: 0x0ce80601
check value in block header: 0x0, block checksum disabled
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
標記為損壞的頁面166711
***
Corrupt block relative dba: 0xb8428b37 (file 0, block 166711)
Bad header found during dbv:
Data in bad block -
type: 6 format: 2 rdba: 0xce00e7eb
last change scn: 0x0000.0a910ce8 seq: 0x1 flg: 0x00
consistency value in tail: 0x39910601
check value in block header: 0x0, block checksum disabled
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
DBVERIFY - 完成檢驗
檢查的頁面總數(shù) ：262144
處理的頁面總數(shù)（數(shù)據(jù)）：262010
失敗的頁面總數(shù)（數(shù)據(jù)）：0
處理的頁面總數(shù)（索引）：0
失敗的頁面總數(shù)（索引）：0
處理的頁面總數(shù)（其它）：9
空的頁面總數(shù) ：120
標記損壞的頁面總數(shù)：4
匯集的頁面總數(shù) ：0
檢查結(jié)果：4個壞塊，塊號是166708 ~ 166711 ，經(jīng)查詢，發(fā)現(xiàn)都在一個extent里，屬于同一張表
6.開始打標記
具體過程：
C:>sqlplus sys/change_on_install
SQL*Plus: Release 8.1.7.0.0 - Production on 星期四 11月 9 12:18:08 2006
(c) Copyright 2000 Oracle Corporation. All rights reserved.
連接到:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production
SQL>exec dbms_repair.admin_tables('REPAIR_TABLE',1,1,'USERS');
PL/SQL 過程已成功完成。
SQL>exec dbms_repair.admin_tables('ORPHAN_TABLE',2,1,'USERS');
PL/SQL 過程已成功完成。
SQL> declare
2 cc number;
3 begin
4 dbms_repair.check_object(schema_name => 'FSGAZHJF',object_name => 'FSGAZHJF_TAC_20061018',corrupt_count => cc);
5 dbms_output.put_line(a => to_char(cc));
6 end;
7 /
PL/SQL 過程已成功完成。
SQL>
SQL> select count(1) from repair_table;
COUNT(1)
----------
5
***這里發(fā)現(xiàn)5個壞快，dbv發(fā)現(xiàn)的是4個
SQL>
***具體信息參考repair_table.xls
發(fā)現(xiàn)marked_corrupt列 已經(jīng)為true,可能不需執(zhí)行
exec dbms_repair.skip_corrupt_blocks(schema_name => 'FSGAZHJF',object_name => 'FSGAZHJF_TAC_20061018',flags => 1);
了，通過下面的查詢，確認不需要執(zhí)行了
SQL> select count(1) from FSGAZHJF.FSGAZHJF_TAC_20061018;
COUNT(1)
----------
18804767
7.為該表建立兩個索引
建立成功
證明可以對該表進行全表掃描了，問題解決，但丟失5個塊的數(shù)據(jù)