江西行的問題：8月磁碟空間就滿了，沒人管，這是一個影像的庫，無備份。現在終於發現庫不正常，表無法訪問了。
遠端支援解決過程：檢視alert日誌，搜“computed block checksum”，可以看到非常多的報錯。類似這樣的
Corrupt block relative dba: 0x048cd809 (file 18, block 841737)
Bad header found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0xf5c65dde
last change scn: 0x0000.d0e1728d seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x728d0601
check value in block header: 0xf5d0
computed block checksum: 0x0
Reading datafile '/u01/neap/data/9999/9999/neap_data_99999999_04.dbf' for corruption at rdba: 0x048cd809 (file 18, block 841737)
Reread (file 18, block 841737) found same corrupt data
Sun Apr 28 13:16:17 2013
Corrupt Block Found
TSN = 9, TSNAME = NEAP_DATA_99999999
RFN = 18, BLK = 841737, RDBA = 76339209
OBJN = 116433, OBJD = 116433, OBJECT = SYS_LOB0000070352C00005$$, SUBOBJECT = SYS_LOB_P3142
SEGMENT OWNER = NEAP, SEGMENT TYPE = Lob Partition

以oracle使用者執行以下操作：
1. 確定壞塊範圍：DBVERIFY檢查資料檔案
$ dbv file=/u01/neap/data/9999/9999/neap_data_99999999_04.dbf blocksize=8192
看結果中的這一行 ,壞塊個數
Total Pages Marked Corrupt : 79036
檢視這個資料檔案所在的表空間
Select TableSpace_Name from DBA_DATA_FILES Where FILE_NAME='/u01/neap/data/9999/9999/neap_data_99999999_04.dbf';
檢視這個表空間下的所有資料檔案
Select * from DBA_DATA_FILES Where TableSpace_Name='NEAP_DATA_99999999';
再用dbv檢視其它的資料檔案有沒有壞塊
dbv file=/u01/neap/data/9999/9999/neap_data_99999999_01.dbf blocksize=8192
。。。
Total Pages Marked Corrupt : 0 看到壞塊都為0。
查下這個表空間裡有哪些表
SQL> select owner,table_name from dba_tables where tablespace_name = 'NEAP_DATA_99999999';
OWNER TABLE_NAME
------------------------------------------------------------
NEAP Z_NEAP_BATCH_99999999
NEAP Z_NEAP_BATCH_IMAGE_99999999
NEAP Z_NEAP_IMAGE_99999999
2. Analyze table檢查關鍵表
SQL> analyze table NEAP.Z_NEAP_BATCH_99999999 validate structure cascade online;
Table analyzed.
SQL> analyze table NEAP.Z_NEAP_BATCH_IMAGE_99999999 validate structure cascade online;
Table analyzed.
SQL> analyze table NEAP.Z_NEAP_IMAGE_99999999 validate structure cascade online;
analyze table NEAP.Z_NEAP_IMAGE_99999999 validate structure cascade online
*
ERROR at line 1:
ORA-01502: 索引 'NEAP.IDX_Z_NEAP_IMAGE_99999999' 或這類索引的分割槽處於不可用狀態

------------------
3. 實施資料搶救。思路：先設定event 10231，檢查那2張表看看有沒有問題，再把索引報錯這個Z_NEAP_IMAGE_99999999表挪一挪
用neap使用者登入
ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';
在session 或database級設10231 event，做全表掃描時，可以跳過soft corrupt block壞塊
CREATE TABLE Z_NEAP_BATCH_99999999_new AS SELECT * FROM Z_NEAP_BATCH_99999999;
CREATE TABLE Z_NEAP_BI_99999999_new AS SELECT * FROM Z_NEAP_BATCH_IMAGE_99999999;
都沒有暫停，so這2張表沒有問題。
select table_name,partition_name from user_tab_partitions where table_name='Z_NEAP_IMAGE_99999999'
沒有分割槽！
取表script
set long 1000000 linesize 1000
column ddl format a1000
select dbms_metadata.get_ddl('TABLE','Z_NEAP_IMAGE_99999999') as DDL from dual where rownum=1;
想檢視資料量
select count(*) from Z_NEAP_IMAGE_99999999
*
ERROR at line 1:ORA-01110: 資料檔案 18: '/u01/neap/data/9999/9999/neap_data_99999999_04.dbf'

count(*)都不行，那就直接rowid方式，
4.重建壞塊物件ROWID儲存資料
估算每個資料塊中的行數，一般可透過dba_tables.avg_row_len進行估算
若估算值比實際值低，會造成資料丟失；若估算值遠大於實際值，會造成執行效率低。一般採用估算值的兩倍作為ROWSPERBLOCK的取值
select table_name,avg_row_len,tablespace_name from user_tables where table_name='Z_NEAP_IMAGE_99999999';
查出為空。那麼就估算為300。
建一個臨時表
create table T_Z_NEAP_IMAGE_99999999 tablespace neap_data_99999999 as select * from Z_NEAP_IMAGE_99999999 where 1=2;
查下原表的大小。
select sum(bytes/1024/1024) MB from dba_segments where SEGMENT_NAME='Z_NEAP_IMAGE_99999999';
2240M
這個錶帶lob欄位，所以還要查lob大小
select SEGMENT_NAME from dba_lobs where table_NAME='Z_NEAP_IMAGE_99999999';
SYS_LOB0000118278C00005$$
select sum(bytes/1024/1024/1024) GB from dba_segments where SEGMENT_NAME='SYS_LOB0000118278C00005$$';
170 G 好大，好慢
把那個臨時表建上索引和約束
CREATE UNIQUE INDEX IDX_T_Z_NEAP_IMAGE_99999999 ON
T_Z_NEAP_IMAGE_99999999(IMAGE_ID, CREATE_DATE) TABLESPACE neap_data_99999999;
ALTER TABLE T_Z_NEAP_IMAGE_99999999 ADD CONSTRAINT PK_T_Z_NEAP_IMAGE_99999999 PRIMARY KEY (IMAGE_ID, CREATE_DATE)
USING INDEX IDX_T_Z_NEAP_IMAGE_99999999;

sqlplus '/as sysdba'
@recreaterowid.sql
。。。
。。。
------------------20131227 挺慢的，現在還在等著，週一看吧
------------------20131230 看到空間爆了。。哎，而且進度非常緩慢，可能是跑時間長了作業系統把它優先順序降低了？
ORA-01578: ORACLE 資料塊損壞 (檔案號 18, 塊號 840704)
掐斷了，
select relative_fno, block_id, block_id+blocks-1 totblocks
from dba_extents
where owner = 'NEAP'
and segment_name = 'Z_NEAP_IMAGE_99999999'
order by extent_id;
結果中relative_fno值分佈為15-22。按這個修改指令碼為8個併發。明天再看
----------------20140102 今天跑完了。後續操作：
檢查新表確認無壞塊：
analyze table neap.TT_Z_NEAP_IMAGE_99999999 validate structure cascade online;
select * from neap.TT_Z_NEAP_IMAGE_99999999;
儲存原表的索引及約束定義，將原表名改為一個臨時表名：
alter table Z_NEAP_IMAGE_99999999 rename to C_Z_NEAP_IMAGE_99999999;
刪除原表的索引和約束定義：
Alter table C_Z_NEAP_IMAGE_99999999 drop constraint PK_Z_NEAP_IMAGE_99999999;
Drop index IDX_Z_NEAP_IMAGE_99999999;
將新建表改名為原表名：
alter table TT_Z_NEAP_IMAGE_99999999 rename to Z_NEAP_IMAGE_99999999;
重建新建表的索引和約束定義：
Alter table Z_NEAP_IMAGE_99999999 drop constraint PK_TT_Z_NEAP_IMAGE_99999999;
Drop index IDX_TT_Z_NEAP_IMAGE_99999999;
CREATE UNIQUE INDEX IDX_Z_NEAP_IMAGE_99999999 ON
Z_NEAP_IMAGE_99999999(IMAGE_ID, CREATE_DATE) TABLESPACE neap_data_99999999;
ALTER TABLE Z_NEAP_IMAGE_99999999 ADD CONSTRAINT PK_Z_NEAP_IMAGE_99999999 PRIMARY KEY (IMAGE_ID, CREATE_DATE)   USING INDEX IDX_Z_NEAP_IMAGE_99999999;
業務驗證

------------------------------我是最關鍵的rowid指令碼--------------------------------------------------
recreaterowid.sql內容：
set serveroutput on
set concat off
DECLARE nrows number;
rid rowid;
dobj number;
ROWSPERBLOCK number; BEGIN
ROWSPERBLOCK:=300;
nrows:=0;
select data_object_id into dobj
from dba_objects
where owner = 'NEAP'
and object_name = 'Z_NEAP_IMAGE_99999999'
;

for i in (select relative_fno, block_id, block_id+blocks-1 totblocks
from dba_extents
where owner = 'NEAP'
and segment_name = 'Z_NEAP_IMAGE_99999999' and relative_fno=15  ---15~22分成了8個指令碼
          order by extent_id)
loop for br in i.block_id..i.totblocks loop
for j in 1..ROWSPERBLOCK loop
begin
rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1);
insert into NEAP.T_Z_NEAP_IMAGE_99999999
select /*+ ROWID(A) */ *
from NEAP.Z_NEAP_IMAGE_99999999 A
where rowid = rid;
if sql%rowcount = 1 then nrows:=nrows+1; end if;
if (mod(nrows,1000)=0) then commit; end if;
exception when others then null;
end;
end loop;
end loop;
end loop;
COMMIT;
dbms_output.put_line('Total rows: '||to_char(nrows));
END;
/

oracle壞塊的rowid方式修復

相關文章