ORA-01578: ORACLE data block corrupted (file # string, block # string)
今天發現Oracle DB 上出現ORA-01578的錯誤
ORA-01578: ORACLE data block corrupted (file # 83, block # 468517)
ORA-01110: data file 83: '/data/tp01.dbf'
1. Determine the Extent of Corruption Problem
1.1 Segment Type, Owner, Name and Tablespace
先確認data block corrupted segment,
SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 83 and 468517 between block_id AND block_id + blocks - 1 ; TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME ------------------ ------------- ---------- ------------- TP TABLE RD REPAIR
如果沒有傳回row ，有可能corrupted bock 是segment header in Locally Managed Tablespace (LMT)，因此會在alter.log 出現錯誤訊息，改用下面SQL查詢
SELECT owner, segment_name, segment_type, partition_name FROM dba_segments WHERE header_file = 83 and header_block = 468517
上面顯示發生data block corrupted 的segment 是Table，處理上會比較麻煩，可能會造成data lose，如果sgement type 是Index 只要rebuild index 即可
1.2 Index and Constraint and foregin key constraints
當segment type 是table 時，使用下面SQL 查詢相關Index and constraint
SELECT owner, index_name, index_type
FROM dba_indexes
WHERE table_owner='RD'
AND table_name='REPAIR' ;
OWNER INDEX_NAME INDEX_TYPE
---------- ------------------------- ---------------------------
RD REPAIR_PK NORMAL
RD REPAIR_IX2 NORMAL
RD REPAIR_IX1 NORMAL
RD REPAIR_IX3 NORMAL
RD REPAIR_IX4 NORMAL
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE owner='RD' AND table_name='REPAIR'
AND constraint_type='P' ;
OWNER CONSTRAINT_NAME C TABLE_NAME
------ -------------------- - ------------------------------
RD REPAIR_PK P REPAIR
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE r_owner='RD'
AND r_constraint_name='REPAIR' ;
no rows selected
1.3. DBV ( DBVerify)
使用dbv 來檢查Data file 是否有問題，在使用dbv之前必須知道data block size
SELECT block_size
FROM dba_tablespaces
WHERE tablespace_name =
( SELECT tablespace_name
FROM dba_data_files
WHERE file_id=83
);
BLOCK_SIZE
----------
8192
block size 是8192 bytes
$ dbv file=/data/tp01.dbf blocksize=8192 DBVERIFY: Release 10.2.0.4.0 - Production on Tue Aug 30 16:47:45 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /data/tp01.dbf DBV-00200: Block, DBA 348595749, already marked corrupt DBVERIFY - Verification complete Total Pages Examined : 524288 Total Pages Processed (Data) : 506986 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 7394 Total Pages Failing (Index): 0 Total Pages Processed (Other): 9788 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 120 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Highest block SCN : 2204868664 (52.2204868664)
在DB open 的情況下，dbv也可以直接檢查segment，先查出segem
select t.ts#, s.header_file, s.header_block
from v$tablespace t, dba_segments s
where s.segment_name='REPAIR'
and t.name = s.tablespace_name;
TS# HEADER_FILE HEADER_BLOCK
---------- ----------- ------------
5 19 261299
$ dbv userid=system/password SEGMENT_ID=5.19.261299 DBVERIFY: Release 10.2.0.4.0 - Production on Tue Aug 30 14:12:42 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : SEGMENT_ID = 5.19.261299 DBV-00200: Block, DBA 348595749, already marked corrupt DBVERIFY - Verification complete Total Pages Examined : 1843632 Total Pages Processed (Data) : 1840696 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 576 Total Pages Failing (Index): 0 Total Pages Processed (Other): 2359 Total Pages Processed (Seg) : 1 Total Pages Failing (Seg) : 0 Total Pages Empty : 0 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Highest block SCN : 2193378735 (52.2193378735)
2. Recovery data
要救回資料的方法有下面幾種
1. RMAN
2. Rename data file
3. Disable db_block_checksum parameter
4. Event 10231
5. DBMS_REPAIR
由於這個DB 沒有備份，所以沒有辦法從備份倒回來，因此只能使用2,3,4,5 的方式來處理
2.1 Rename data file
忘記是在那篇文章看到的，將DB shutdonw ，把問題的Data file 用OS 指令copy 成另外一個Data file ，再將DB mount後rename 只到新的 data file
不過沒試驗過，可以參考看看
2.2 Disable db_block_checksum parameter
disable db_block_checksum parameter 讓DB不過checksum 的動作後，再將資料用export /import 的方式匯出
SQL> show parameter db_block_checksum; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_checksum string TRUE SQL> alter system set db_block_checksum = false scope = both; System altered. SQL> show parameter db_block_checksum; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_checksum string FALSE
接下來再用export / import 的方式將資料匯出，不過當時執行還是無法順利將資料export
2.3 Event 10231
在使用EVent 10231之前為了避免失敗，建議先完成下面的工作
1.重新開機讓機器保持在最乾淨的情況
2.stop listener
3.startup db on restrict mode
4.disable job (job_queue_processes = 0 )
SQL>ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10'; SQL>select count(*) from RD.REPAIR; COUNT(*) ---------- 72363746
先count 資料筆數，因為data block corruption 是table ，只要有 PK 時select count(*) 就只會使用index，而不會進行table scan
SQL> ALTER SYSTEM SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10'; System altered. SQL> exit; $ exp system/0ra_acct^system tables=RD.REPAIR file=REPAIR.dmp log=REPAIR.log buffer=999999 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and UTF8 NCHAR character set About to export specified tables via Conventional Path ... Current user changed to RD . . exporting table REPAIR 72363719 rows exported Export terminated successfully without warnings.
成功將資料export 後就可以drop old table ，並使用import 將資料到回
$ imp system/0ra_acct^system file=REPAIR.dmp fromuser=RD touser=RD log=imp_REPAIR.log buffer=999999 commit=y feedback=100000
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in UTF8 character set and UTF8 NCHAR character set
. importing RD's objects into RD
. . importing table "REPAIR"
...........................................................................
72363719 rows imported
Import terminated successfully without warnings.
exprot / import 的時間大約是1:3, 這是因為import 除了將資料匯入之外，如果table 有index 還需要重建index ， 可以透過 V$SESSION_LONGOPS來查詢
export 後就可以知道有多少筆資料lose，或者import 結束後再執行一次select count
2.4 DBMS_REPAIR
DBMS_REPARI，沒有使用過，不過先記錄下來方便以後使用
SQL> begin
dbms_repair.admin_tables (
table_name => 'REPAIR_TABLE',
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => 'TP');
end;
/
SQL> set serveroutput on
SQL> declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_object (
schema_name => 'RD',
object_name => 'REPAIR',
repair_table_name =>'REPAIR_TABLE',
corrupt_count => rpr_count);
dbms_output.put_line('repair count:' || to_char(rpr_count));
end;
/
SQL> select object_name, block_id, corrupt_type,
marked_corrupt,corrupt_description,
repair_description from repair_table;
SQL> declare
fix_count int;
begin
fix_count := 0;
dbms_repair.fix_corrupt_blocks (
schema_name =>'RD',
object_name =>'REPAIR',
object_type => dbms_repair.table_object,
repair_table_name => 'REPAIR_TABLE',
fix_count => fix_count);
dbms_output.put_line('fix count: ' || to_char(fix_count));
end;
/
SQL> begin
dbms_repair.skip_corrupt_blocks (
schema_name => 'RD',
object_name => 'REPAIR',
object_type => dbms_repair.table_object,
flags => dbms_repair.skip_flag);
end;
/
參考資料
MetaLink :
[ID : 21205.1] Event 10231 skip corrupted blocks on_table_scan
[ID : 28814.1] Handing Oracle Block Corrupions in Oracle 7/8/8i/9i/10g/11g
[ID : 33405.1] Extracting Data from a corrupt table using DBMS_REPAIR or EVENT 10231
留言列表
學習筆記 (8)
