一次oracle数据库数据块损坏的处理过程 - 数据库编程

4 Total Pages Failing (Index): 0 Total Pages Processed (Other): 92578 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 46754 Total Pages Marked Corrupt : 2 Total Pages Influx : 2 Total Pages Encrypted : 0 Highest block SCN : 791817065 (2139.791817065)

13，此时，全库逻辑导出，保护好现有的数据

expdp userid=\"/ as sysdba\"full=y dumpfile=epm20140206_3.dmp directory=dpdata1 LOGFILE=epm20140206_3.log PARALLEL=3

14，通过rman命令,共检验出22个数据库坏块

SQL> select * fromv$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------------------------- ---------

        4     439296          1                  0 FRACTURED

        4     439168          1                  0 FRACTURED

        8    2610431          1                  0 FRACTURED

        8    2547178          1                  0 FRACTURED

        8    2547114          1                  0 FRACTURED

        8    2547050          1                  0 FRACTURED

        8    2546986          1                  0 FRACTURED

        8    2546922          1                  0 FRACTURED

        8    2546890          1                  0 FRACTURED

        8    2546858          1                  0 FRACTURED

        8    2546826          1                  0 FRACTURED

      FILE#    BLOCK#     BLOCKSCORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------------------------- ---------

        8    2546794          1                  0 FRACTURED

        8    2546762          1                  0 FRACTURED

        8    2546730          1                  0 FRACTURED

        8    2546698          1                  0 FRACTURED

        8    2459433          1                  0 FRACTURED

        8    2459305          1                  0 FRACTURED

        8    1596687          1                  0 FRACTURED

        9     876808          1                  0 FRACTURED

        9     662038          1                  0 FRACTURED

        9     345491          1                  0 FRACTURED

        9     281617          1                  0 FRACTURED

15，进一步定位到数据库对象

查看坏块对应的对象

Selecttablespace_name,segment_type,owner,segment_name From dba_extents Wherefile_id=2 and 31061 between block_id and block_id+blocks-1;

根据坏块生产查看对象的sql

select 'Select tablespace_name,segment_type,owner,segment_nameFrom dba_extents Where file_id=' || FILE# || ' and ' || BLOCK# ||  ' between block_id and block_id+blocks-1;'from v$database_block_corruption;

16，对于索引坏块，处理比较简单，直接rebuild即可,如果索引坏块较多，可以选择重建所有索引

重建索引

SQL> alter INDEX EPM.IDX_QRTZ_T_NEXT_FIRE_TIMErebuild online;

对于表比较复杂

1)，可以通过备份恢复数据块，本例由于备份有问题，未成功

RMAN> blockrecover datafile 2 block31061 from backupset;
 
Starting recover at 06-FEB-14

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) torestore from backup set

restoring blocks of datafile 00002

channel ORA_DISK_1: reading from backuppiece /app/backup/rman_bak/rman.c0_20140122_1629_1_EPM.bak

channel ORA_DISK_1: ORA-19870: error whilerestoring backup piece /app/backup/rman_bak/rman.c0_20140122_1629_1_EPM.bak

ORA-19501: read error on file"/app/backup/rman_bak/rman.c0_20140122_1629_1_EPM.bak", block number154496 (block size=8192)

ORA-27061: waiting for async I/Os failed

Linux-x86_64 Error: 5: Input/output error

Additional information: -1

Additional information: 1048576

2)，如果没有备份，则可以通过如下方法跳过坏块，当然会丢失部分数据

SQL> ALTER SESSION SET EVENTS

 2  '10231 TRACE NAME CONTEXTFOREVER, LEVEL 10';

 Session altered.

 SQL> create tableEPM.REQ_RESPSB_SUPPLIER_BAK as select * from EPM.REQ_RESPSB_SUPPLIER;

 Table created.

 SQL> select count(*) fromEPM.REQ_RESPSB_SUPPLIER_BAK;

  COUNT(*)

----------

   188786

17，至此，数据库启动成功，业务也基本恢复正常了

参考文档

metalink:ORA-600 [kcratr_nab_less_than_odr]during Instance Recovery after Database Crash (Doc ID 1299564.1)

metalink:Step by step to resolve ORA-6004194 4193 4197 on database crash (Doc ID 1428786.1)

http://www.eygle.com/archives/2010/05/ora-00600_kcratr1_lostwrt.html

一次oracle数据库数据块损坏的处理过程(五)