兩隻小虎,一隻豬

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