ORACLE 邏輯壞塊真實案例ORA-01578 ORA-01110

2015年04月15日 某系統由於記憶體故障導致RAC的B節點發生當機，在當機其間tjs1b庫由於當機出現3個壞塊，經分析3個壞塊為邏輯壞塊，共涉及到兩個索引的兩個分割槽。對索引分割槽進行重建完成修復；
現象：
ORACLE Instance ojs1a (pid = 26) - Error 1578 encountered while recovering transaction (173, 11) on object 4054036.
Errors in file /oracle/app/oracle/diag/rdbms/orajs1/ojs1a/trace/ojs1a_smon_176306.trc:
ORA-01578: ORACLE data block corrupted (file # 1004, block # 488211)
ORA-01110: data file 1004: '/dev/rvg06lv015'
Sat Apr 18 13:33:54 2015
Thread 1 advanced to log sequence 1547143 (LGWR switch)
  Current log# 10 seq# 1547143 mem# 0: /dev/rd8_redolv1_1
ORACLE Instance ojs1a (pid = 26) - Error 1578 encountered while recovering transaction (176, 12) on object 4043711.
Errors in file /oracle/app/oracle/diag/rdbms/orajs1/ojs1a/trace/ojs1a_smon_176306.trc:
ORA-01578: ORACLE data block corrupted (file # 1133, block # 865723)
ORA-01110: data file 1133: '/dev/rvg06lv073'
Sat Apr 18 13:33:57 2015
Archived Log entry 2782056 added for thread 1 sequence 1547142 ID 0x41a4b0c0 dest 1:
ORACLE Instance ojs1a (pid = 26) - Error 1578 encountered while recovering transaction (86, 20) on object 4043711.
Errors in file /oracle/app/oracle/diag/rdbms/orajs1/ojs1a/trace/ojs1a_smon_176306.trc:
ORA-01578: ORACLE data block corrupted (file # 1133, block # 865723)
ORA-01110: data file 1133: '/dev/rvg06lv073'
ORACLE Instance ojs1a (pid = 26) - Error 1578 encountered while recovering transaction (89, 32) on object 4054036.
Errors in file /oracle/app/oracle/diag/rdbms/orajs1/ojs1a/trace/ojs1a_smon_176306.trc:
ORA-01578: ORACLE data block corrupted (file # 1087, block # 498755)
ORA-01110: data file 1087: '/dev/rvg06lv049'
Sat Apr 18 13:34:05 2015
ORACLE Instance ojs1a (pid = 26) - Error 1578 encountered while recovering transaction (102, 12) on object 4054036.
Errors in file /oracle/app/oracle/diag/rdbms/orajs1/ojs1a/trace/ojs1a_smon_176306.trc:
ORA-01578: ORACLE data block corrupted (file # 1087, block # 498755)
ORA-01110: data file 1087: '/dev/rvg06lv049'
ORACLE Instance ojs1a (pid = 26) - Error 1578 encountered while recovering transaction (105, 12) on object 4054036.
Errors in file /oracle/app/oracle/diag/rdbms/orajs1/ojs1a/trace/ojs1a_smon_176306.trc:
ORA-01578: ORACLE data block corrupted (file # 1087, block # 498755)
問題處理步聚：
1.檢視操盤系統日誌未發現作業系統報錯因此可以說明該壞塊是邏輯壞塊；
2.用DBV或RMAN檢查是否存在壞塊（BDV可以檢查物理和邏輯壞塊）；
－－dbv校驗
[oracle@tjs1a] /oracle> dbv file=/dev/rvg06lv049 blocksize=16384
DBVERIFY: Release 11.2.0.2.0 - Production on Sat Apr 18 13:44:09 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /dev/rvg06lv049
DBV-00200: Block, DBA 268934211, already marked corrupt
csc(0x0bd1.2eb80a06) higher than block scn(0x0000.00000000)
Page 498755 failed with check code 6054
DBVERIFY - Verification complete
Total Pages Examined         : 1048512
Total Pages Processed (Data) : 823753
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 221725
Total Pages Failing   (Index): 1
Total Pages Processed (Other): 2974
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 60
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 849573888 (3025.849573888)
[oracle@tjs1a] /oracle> 
You have mail in /usr/spool/mail/oracle

－－RMAN 校驗
rman target /
backup check logical validate database;
透過下面這條命令，並不是做一個備份 ，只是對檔案做一次校驗，當然也可以發現壞塊
RMAN> backup validate datafile 1004;
RMAN> exit
Recovery Manager complete.

檢查完檢視
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         4        131          1         2.7119E+14 FRACTURED

3.檢視壞塊對應的物件
－－透過object_id檢視
select owner,object_name,object_type from dba_objects where object_id='4043711';
SQL> select owner,index_name,table_name from dba_indexes where index_name in ('UN_PK_STLGPRSCNGKEY','UN_PK_STLGPRSLTECNGKEY');


OWNER                          INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------ ------------------------------
SDSETTLE                       UN_PK_STLGPRSCNGKEY            STLGPRSCNGKEY
SDSETTLE                       UN_PK_STLGPRSLTECNGKEY         STLGPRSLTECNGKEY
---透過file_id 和block來查詢（此方案相對慢一些）
Select owner,segment_name,segment_type from dba_extents where file_id=1087 and 498755 between block_id and block_id+blocks-1;
4.如果是索引可以進行重建；
5.如果是表且沒有備份的情況下可以透過dbms_repair.skip_corrupt_blocks跳過壞塊，然後重建（注：使用skip_corrupt_blocks只能使oracle跳過Oracle能夠讀出的塊，而如果在作業系統層read呼叫就失敗的，則不能跳過該過。甚至於該會話也可能會被中斷。遇到這樣的情況，使用dd命令或作業系統的copy(cp)命令都不能複製該檔案，rman也不能備份該檔案）
例：SQL> analyze table test.t1  VALIDATE STRUCTURE CASCADE;  

analyze table test.t1 validate structure
*
ERROR 位於第 1 行:
ORA-01578: ORACLE 資料塊損壞（檔案號10，塊號1447）
ORA-01110: 資料檔案 10: ‘D:\ORACLE\ORADATA\XJ\TEST01.DBF’

SQL>> begin
  2    dbms_repair.admin_tables (
  3      table_name => ‘REPAIR_TABLE’,
  4      table_type => dbms_repair.repair_table,
  5      action => dbms_repair.create_action,
  6      tablespace => ‘SYSTEM’);
  7  end;
  8  /

PL/SQL 過程已成功完成。
SQL> set serveroutput on
SQL> declare
  2    rpr_count int;
  3  begin
  4    rpr_count := 0;
  5    dbms_repair.check_object (
  6      schema_name => ‘TEST’,
  7      object_name => ‘T1′,
  8      repair_table_name => ‘REPAIR_TABLE’,
  9      corrupt_count => rpr_count);
 10    dbms_output.put_line(‘repair count: ‘ || to_char(rpr_count));
 11  end;
 12  /
repair count: 1

PL/SQL 過程已成功完成。
SQL> select object_name, block_id, corrupt_type, marked_corrupt,corrupt_description,
2 repair_description from repair_table;

OBJECT_NAME     BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIPTION  REPAIR_DESCRIPTION
————- ———- ———— ———- ——————– ——————–
T1                  1447         6148 TRUE                            mark block software
                                                                      corrupt

T1                  1447         6148 TRUE                            mark block software
                                                                      corrupt
SQL> declare
  2    fix_count int;
  3    begin
  4      fix_count := 0;
  5      dbms_repair.fix_corrupt_blocks (
  6      schema_name => ‘TEST’,
  7      object_name => ‘T1′,
  8      object_type => dbms_repair.table_object,
  9      repair_table_name => ‘REPAIR_TABLE’,
 10      fix_count => fix_count);
 11    dbms_output.put_line(‘fix count: ‘ || to_char(fix_count));
 12  end;
 13  /
fix count: 0

PL/SQL 過程已成功完成。
SQL> begin
  2    dbms_repair.skip_corrupt_blocks (
  3      schema_name => ‘TEST’,
  4      object_name => ‘T1′,
  5      object_type => dbms_repair.table_object,
  6      flags => dbms_repair.skip_flag);
  7  end;
  8  /

PL/SQL 過程已成功完成。

SQL> select table_name, skip_corrupt from dba_tables where table_name = ‘T1′ and owner=’TEST’;

TABLE_NAME                     SKIP_COR
—————————— ——–
T1                             ENABLED

SQL> select count(*) from test.t1;

  COUNT(*)
———-
     28762

6.如果是表且有備份，可以透過不完全恢復來解決；
7.如果是表且自從出現壞塊還沒有進行備份的話，可以透過rman直接對壞塊進行恢復
  blockrecover datafile 1087 block 498755;

本案例是分析索引出現壞塊，且是邏輯壞塊，因此只需要重建分割槽索引即可：
解決方法
alter index SDSETTLE.UN_PK_STLGPRSCNGKEY rebuild partition KEY20150418_0 online parallel 16;
alter index SDSETTLE.UN_PK_STLGPRSLTECNGKEY rebuild partition KEY20150418_0 online parallel 16;