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