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丢失.这次运气比较好,如果是表数据异常,可能会麻烦一点,但是也可以最大限度恢复(肯定比强制拉库,或者修改文件头的方式效果好)