ORA-00756 ORA-10567故障数据0丢失恢复---惜分飞

客户虚拟化故障修复之后,数据库启动报ORA-600 kcratr_scan_lastbwr错误

---

这个是一个比较常见的错误,一般recover 下就ok了,但是有些时候会出现ORA-600 3020或者类似ORA-00756 ORA-10567的错误,比如这次不幸就遇到了该错误

 

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-00756: recovery detected a lost write of a data block

ORA-10567: Redo is inconsistent with data block (file# 10, block# 4005760, file

offset is 2750414848 bytes)

ORA-10564: tablespace PACS55

ORA-01110: data file 10: '/u02/oradata/pacsdb/pacs55.4.dbf'

ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 76649

然后尝试单个文件recover恢复

SQL> recover datafile 10;

ORA-00283: recovery session canceled due to errors

ORA-00756: recovery detected a lost write of a data block

ORA-10567: Redo is inconsistent with data block (file# 10, block# 4005760, file

offset is 2750414848 bytes)

ORA-10564: tablespace PACS55

ORA-01110: data file 10: '/u02/oradata/pacsdb/pacs55.4.dbf'

ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 76649

 

SQL> recover datafile 9;

ORA-00283: recovery session canceled due to errors

ORA-00756: recovery detected a lost write of a data block

ORA-10567: Redo is inconsistent with data block (file# 9, block# 4158754, file

offset is 4003741696 bytes)

ORA-10564: tablespace PACS55

ORA-01110: data file 9: '/u02/oradata/pacsdb/pacs55.3.dbf'

ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 76660

通过dbv检查这两个异常文件

[oracle@oradb ~]$ dbv file=/u02/oradata/pacsdb/pacs55.3.dbf

 

DBVERIFY: Release 19.0.0.0.0 - Production on Sat Jun 28 23:02:15 2025

 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

 

DBVERIFY - Verification starting : FILE = /u02/oradata/pacsdb/pacs55.3.dbf

 

 

DBVERIFY - Verification complete

 

Total Pages Examined         : 4194302

Total Pages Processed (Data) : 2482487

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 1655515

Total Pages Failing   (Index): 0

Total Pages Processed (Lob)  : 25017

Total Pages Failing   (Lob)  : 0

Total Pages Processed (Other): 15919

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 15364

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 311133131196 (72.1895485884)

[oracle@oradb ~]$ dbv file=/u02/oradata/pacsdb/pacs55.4.dbf

 

DBVERIFY: Release 19.0.0.0.0 - Production on Sat Jun 28 23:04:59 2025

 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

 

DBVERIFY - Verification starting : FILE = /u02/oradata/pacsdb/pacs55.4.dbf

 

 

DBVERIFY - Verification complete

 

Total Pages Examined         : 4194302

Total Pages Processed (Data) : 2466409

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 1683244

Total Pages Failing   (Index): 0

Total Pages Processed (Lob)  : 16977

Total Pages Failing   (Lob)  : 0

Total Pages Processed (Other): 15909

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 11763

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 311133133727 (72.1895488415)

确定数据文件本身没有坏块,只是redo写丢失或者某种bug导致少量block应用redo的时候异常,而且报错是index,直接通过底层处理报错的block,让其这些报错的block直接不应用日志,然后完成recover操作,其他数据块数据不会丢失(最大限度减少损失,而不是直接修改文件头scn,或者强制拉库的方式来处理)

SQL> select file#,fuzzy from v$datafile_header;

 

     FILE# FUZ

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

     1 NO

     2 NO

     3 NO

     4 NO

     5 NO

     7 NO

     8 NO

     9 YES

    10 YES

    11 NO

    12 NO

 

     FILE# FUZ

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

    13 NO

    14 NO

    15 NO

    16 NO

    17 NO

    18 NO

    19 NO

 

18 rows selected.

 

SQL> recover  datafile 9 ;

Media recovery complete.

SQL> recover  datafile 10 ;

ORA-00283: recovery session canceled due to errors

ORA-00756: recovery detected a lost write of a data block

ORA-10567: Redo is inconsistent with data block (file# 10, block# 3822912, file

offset is 1252524032 bytes)

ORA-10564: tablespace PACS55

ORA-01110: data file 10: '/u02/oradata/pacsdb/pacs55.4.dbf'

ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 76649

  

SQL> recover  datafile 10;

Media recovery complete.

正常open数据库成功,并rebuild 异常的对象

SQL> alter database open;

 

Database altered.

 

SQL> select owner,object_name,object_type from dba_objects where data_object_id in(76649,76660);

 

OWNER

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

OBJECT_NAME

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

OBJECT_TYPE

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

PACS55

STUDYINFO_DIAGRPTID

INDEX

 

PACS55

PACS_STUDYINFO_PK

INDEX

 

OWNER

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

OBJECT_NAME

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

OBJECT_TYPE

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

 

 

SQL> alter index PACS55.STUDYINFO_DIAGRPTID rebuild online parallel 4;

 

Index altered.

 

SQL> alter index PACS55.PACS_STUDYINFO_PK rebuild online parallel 4;

 

Index altered.

 

SQL>

SQL>

SQL>

SQL> alter index PACS55.STUDYINFO_DIAGRPTID noparallel;

alter index PACS55.PACS_STUDYINFO_PK noparallel;

Index altered.

 

SQL>

 

Index altered.

至此该库完美恢复业务可以直接使用,业务数据0丢失.这次运气比较好,如果是表数据异常,可能会麻烦一点,但是也可以最大限度恢复(肯定比强制拉库,或者修改文件头的方式效果好)