block corruption & ora-600 [6122]

最近遇到oracle block corruption的一个case,记录下解决过程

 

alert.log

 

Thread 1 advanced to log sequence 5011 (LGWR switch)
  Current log# 3 seq# 5011 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ZMD\REDO03.LOG
Tue Jun 01 22:15:09 2010
Errors in file d:\oracle\product\10.2.0\admin\zmd\bdump\zmd_m000_2872.trc:
ORA-00600: 内部错误代码, 参数: [6122], [0], [1], [0], [], [], [], []

 

Tue Jun 01 10:11:53 2010
Errors in file d:\oracle\product\10.2.0\admin\zmd\udump\zmd_ora_3888.trc:
ORA-00368: 重做日志块中的校验和错误
ORA-00353: 日志损坏接近块 1139 更改 770852211 时间 06/01/2010 09:17:03
ORA-00334: 归档日志: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ZMD\REDO02.LOG'
ORA-07445: 出现异常错误: 核心转储 [ACCESS_VIOLATION] [_smbget+132] [PC:0x258457C] [ADDR:0x6AEDD0A] [UNABLE_TO_READ] []

 

 

metalink关于ora-600 6122的描述

 

ORA-600 [6122] "Index Block Corrupt" [ID 99300.1]   

--------------------------------------------------------------------------------
 
  Modified 16-APR-2009     Type REFERENCE     Status PUBLISHED   




Note: For additional ORA-600 related information please read Note:146580.1

PURPOSE:            
  This article discusses the internal error "ORA-600 [6122]", what 
  it means and possible actions. The information here is only applicable 
  to the versions listed and is provided only for guidance.

ERROR:              
  ORA-600 [6122] [a] [b] [c]
 
VERSIONS:           
  versions 7.1 to 10.1

DESCRIPTION:        

  Block checking has found a logically corrupt block. 

  In most cases the bad block will have been marked as soft corrupt
  and so will result in ORA-1578 errors after this error has occured.
 
ARGUMENTS:          
  Arg [a] Number of rows deleted in the block
  Arg [b] Number of row locks left
  Arg [c] Number of rows left in the block
 
FUNCTIONALITY:      
  Kernel Data indeX block
 
IMPACT:             
  PROCESS FAILURE
  INDEX BLOCK CORRUPTION

SUGGESTIONS:

  This error would indicate an index block corruption.

  Please refer to the following Metalink article for information:

  Note:28814.1 Handling Oracle Block Corruptions in Oracle7/8/8i

  If the Known Issues section below does not help in terms of identifying       
  a solution, please submit the trace files and alert.log to Oracle             
  Support Services for further analysis.                                        
 
  Known Issues:

  Bug# 2909160   See Note:2909160.8
      OERI[6122] / index corruption for DML against AUTO space managed indexes
      Fixed: 9.2.0.4, 10.1.0.2

于是dbv 检查,最终确定为索引受影响，在于是drop and create解决,

 

select * from v$database_block_corruption;

select * from dba_extents where file_id=3 and 48826 between block_id and block_id+blocks-1;

DROP INDEX SYS.WRH$_SQL_PLAN_PK;

CREATE UNIQUE INDEX SYS.WRH$_SQL_PLAN_PK ON SYS.WRH$_SQL_PLAN
(DBID, SQL_ID, PLAN_HASH_VALUE, ID)
LOGGING
TABLESPACE SYSAUX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

 

 

现在重现下问题

 

 

 

DBVERIFY - Verification complete

Total Pages Examined         : 15360
Total Pages Processed (Data) : 756
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 763
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 10756
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 3083
Total Pages Marked Corrupt   : 2
Total Pages Influx           : 0
Highest block SCN            : 204511 (0.204511)

 

RMAN> backup validate tablespace sysaux;

 

select * from v$database_block_corruption;

 

确定object

 

select * from dba_extents where file_id=3 and 5212 between block_id and block_id+blocks-1

 

确定为local分区索引WRH$_SYS_TIME_MODEL_PK,

 

ALTER INDEX SYS.WRH$_SYS_TIME_MODEL_PK
REBUILD partition WRH$_SYS_TIME_MO_MXDB_MXSN online;

 

再查看发现5212block已经没有object,重启后5212block标记为正常,v$database_block_corruption更新了block

 

对于表的影响和处理

 

建立admin table

 

DBMS_REPAIR.ADMIN_TABLES (
   table_name  IN   VARCHAR2,
   table_type  IN   BINARY_INTEGER,
   action      IN   BINARY_INTEGER,
   tablespace  IN   VARCHAR2  DEFAULT NULL);

 

对object检查

 

declare
    x number;
 
   begin
    dbms_repair.check_object(schema_name => 'SYS',object_name => 'TT',corrupt_count => x);
   dbms_output.put_line(a => to_char(x));
    end;
    /

 

select * from repair_table;

 

skip corruption的block

 

begin

 dbms_repair.skip_corrupt_blocks(schema_name => 'SYS',object_name => 'TT',flags => 1);

end;

/

 

如果坏块过多,alter system set events'10231 trace name context forever,level 12',然后全库导出

 

 

V$DATABASE_BLOCK_CORRUPTION