适用范围

Oracle Database - Enterprise Edition - Version 11.2.0.4  and later

问题概述

SQL> select count(1) from hsql.test1;
select count(1) from hsql.test1
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 130)
ORA-01110: data file 6: '/oradata/orcl/test02.dbf'

问题原因

### 1. 使用dbv检查所有数据文件
其它数据文件检查都正常无报错，只有data file 6损坏
[oracle@orcldb ~]$ dbv file=/oradata/orcl/test02.dbf
.....略过大量坏块信息
Page 12797 is marked corrupt
Corrupt block relative dba: 0x018031fd (file 6, block 12797)
Completely zero block found during dbv: 

Page 12798 is marked corrupt
Corrupt block relative dba: 0x018031fe (file 6, block 12798)
Completely zero block found during dbv: 

Page 12799 is marked corrupt
Corrupt block relative dba: 0x018031ff (file 6, block 12799)
Completely zero block found during dbv: 
DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 127
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1
Total Pages Marked Corrupt   : 12672  
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 967694 (0.967694)
[oracle@orcldb ~]$

### 2. 检查数据库备份信息
rman target /
crosscheck backupset;
list backupset;
crosscheck archivelog all;

[oracle@orcldb ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 30 20:46:24 2022

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

connected to target database: ORCL (DBID=1649805241)

RMAN> list backupset;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    1.01G      DISK        00:00:03     30-NOV-22      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20221130T203432
        Piece Name: /backup/full_011e5aeo_1_1.bak
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 967028     30-NOV-22 /oradata/orcl/system01.dbf
  2       Full 967028     30-NOV-22 /oradata/orcl/sysaux01.dbf
  3       Full 967028     30-NOV-22 /oradata/orcl/undotbs01.dbf
  4       Full 967028     30-NOV-22 /oradata/orcl/users01.dbf
  5       Full 967028     30-NOV-22 /oradata/orcl/test01.dbf
  6       Full 967028     30-NOV-22 /oradata/orcl/test02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.36M      DISK        00:00:01     30-NOV-22      
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20221130T203432
        Piece Name: /backup/full_021e5aer_1_1.bak
  SPFILE Included: Modification time: 29-NOV-22
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 967029       Ckp time: 30-NOV-22

RMAN>

### 3. 检查归档日志信息，是否包含从备份集开始的所有日志
set linesize 300 pagesize 9999
col name for a100
col f_scn for a20
col n_scn for a20
select SEQUENCE#,name,status,to_char(FIRST_CHANGE#) f_scn,to_char(NEXT_CHANGE#) n_scn from v$archived_log
where status='A' and name is not null;

注意：
如果存在备份集，且从备份集开始后所有的归档日志都在，请使用下面方法恢复。

解决方案

### 1. offline datafile
[oracle@orcldb orcl]$ sqlplus / as sysdba
SQL> alter database datafile 6 offline;
Database altered.
SQL> exit

### 2. move old datafile 
cd /oradata/orcl/
mkdir bak
mv test02.dbf bak/

### 3. 恢复数据文件
[oracle@orcldb orcl]$ rman target /
RMAN> restore datafile 6;
RMAN> recover datafile 6;

### 4. online 数据文件并检查业务恢复
SQL> alter database datafile 6 online;

Database altered.

SQL> select count(1) from hsql.test1;

  COUNT(1)
----------
    300000

SQL>