oracle壞塊的rowid方式修復
江西行的問題：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;
/
遠端支援解決過程：檢視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;
/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29306197/viewspace-1067623/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle日常問題-壞塊修復Oracle
- RMAN修復壞塊
- 【DATAGUARD】Oracle Dataguard nologging 塊修復Oracle
- 【ROWID】Oracle rowid說明Oracle
- WPS文件損壞如何修復？WPS文件損壞的修復方法
- 驗證ADG的壞塊檢測和自動修復
- 伺服器Oracle資料庫損壞修復伺服器Oracle資料庫
- oracle壞塊(二)Oracle
- Oracle壞塊處理Oracle
- oracle rowid詳解Oracle
- 【LINUX】Oracle資料庫 linux磁碟頭資料損壞修復LinuxOracle資料庫
- 行動硬碟壞道修復硬碟
- linux下修復磁碟損壞Linux
- PostgreSQL 恢復大法 - 恢復部分資料庫、跳過壞塊、修復無法啟動的資料庫SQL資料庫
- 磁頭損壞的修復方法有哪些
- truncate操作消除ORACLE SEG壞塊解析Oracle
- 電腦硬碟分割槽表損壞怎麼修復？電腦硬碟分割槽表損壞的修復方法硬碟
- SQL Anywhere db檔案損壞修復 DB檔案修復 DB資料庫修復SQL資料庫
- 學習這篇Oracle資料庫檔案壞塊損壞的恢復方法,擴充你的知識面Oracle資料庫
- 筆記本硬碟壞了修復方法教程 膝上型電腦硬碟壞了怎麼修復？筆記硬碟
- SQL Server 資料頁損壞修復SQLServer
- Oracle redo解析之-4、rowid的計算Oracle Redo
- Oracle資料庫壞塊典型案例分析Oracle資料庫
- 【BLOCK】Oracle壞塊處理命令參考BloCOracle
- 資料恢復工具Recoverit使用教程:如何修復損壞的影片資料恢復
- InterBase資料庫檔案損壞的修復方法資料庫
- linux RAID10磁碟陣列損壞的修復LinuxAI陣列
- 用 set或map修復if-else的壞味道 - egkatzioura
- 不重灌也能修復損壞的 Ubuntu 系統Ubuntu
- Oracle asm磁碟損壞異常恢復OracleASM
- linux檔案系統損壞如何修復Linux
- system資料檔案頭損壞修復
- 圖片、視訊損壞了，如何修復？
- SQLite資料庫損壞及其修復探究SQLite資料庫
- Oracle資料庫壞塊典型案例擴充Oracle資料庫
- 【資料庫資料恢復】Oracle資料庫檔案出現壞塊報錯的資料恢復案例資料庫資料恢復Oracle
- 【TUNE_ORACLE】ROWID切片SQL參考OracleSQL
- RAID10磁碟陣列損壞修復操作AI陣列
- MySQL資料庫InnoDB壞頁處理修復MySql資料庫