适用范围
Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
问题概述
SQL> select count(1) from hsql.drop_1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 1282)
ORA-01110: data file 8: '/oradata/orcl/hsql02.dbf'
SQL>
问题原因
### 1. 使用dbv检查所有数据文件
其它数据文件检查都正常无报错，只有data file 6损坏
[oracle@orcldb arch]$ dbv file=/oradata/orcl/hsql02.dbf
.....略过大量坏块信息
Page 12798 is marked corrupt
Corrupt block relative dba: 0x020031fe (file 8, block 12798)
Completely zero block found during dbv:
Page 12799 is marked corrupt
Corrupt block relative dba: 0x020031ff (file 8, block 12799)
Completely zero block found during dbv:
DBVERIFY - Verification complete
Total Pages Examined : 31344
Total Pages Processed (Data) : 8695
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 10879
Total Pages Failing (Index): 0
Total Pages Processed (Other): 230
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 113
Total Pages Marked Corrupt : 11427
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1257613 (0.1257613)
[oracle@orcldb arch]$
与现场沟通没有可用的物理级rman备份，但保留的归档日志较多，包含数据文件创建后的所有归档。
### 2. 检查数据文件创建日期
SQL> set linesize 600 pagesize 2000
SQL> col ERROR for a20
SQL> col NAME for a60
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select file#,name,CREATION_TIME,status from v$datafile;
FILE# NAME CREATION_TIME STATUS
---------- ------------------------------------------------------------ ------------------- -------
1 /oradata/orcl/system01.dbf 2013-08-24 11:37:33 SYSTEM
2 /oradata/orcl/sysaux01.dbf 2013-08-24 11:37:37 ONLINE
3 /oradata/orcl/undotbs01.dbf 2013-08-24 12:07:19 ONLINE
4 /oradata/orcl/users01.dbf 2013-08-24 11:37:49 ONLINE
5 /oradata/orcl/test01.dbf 2022-11-30 20:30:33 ONLINE
6 /oradata/orcl/test02.dbf 2022-11-30 20:30:34 ONLINE
7 /oradata/orcl/hsql01.dbf 2022-12-01 16:24:31 ONLINE
8 /oradata/orcl/hsql02.dbf 2022-12-05 14:32:34 ONLINE
8 rows selected.
SQL> select file#,name,CREATION_TIME,status from v$datafile_header;
FILE# NAME CREATION_TIME STATUS
---------- ------------------------------------------------------------ ------------------- -------
1 /oradata/orcl/system01.dbf 2013-08-24 11:37:33 ONLINE
2 /oradata/orcl/sysaux01.dbf 2013-08-24 11:37:37 ONLINE
3 /oradata/orcl/undotbs01.dbf 2013-08-24 12:07:19 ONLINE
4 /oradata/orcl/users01.dbf 2013-08-24 11:37:49 ONLINE
5 /oradata/orcl/test01.dbf 2022-11-30 20:30:33 ONLINE
6 /oradata/orcl/test02.dbf 2022-11-30 20:30:34 ONLINE
7 /oradata/orcl/hsql01.dbf 2022-12-01 16:24:31 ONLINE
8 /oradata/orcl/hsql02.dbf 2022-12-05 14:32:34 ONLINE
8 rows selected.
SQL>
解决方案
### 1. offline datafile
SQL> alter database datafile 8 offline;
Database altered.
SQL> set linesize 600 pagesize 2000
SQL> col ERROR for a20
SQL> col NAME for a60
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select file#,name,CREATION_TIME,status from v$datafile;
FILE# NAME CREATION_TIME STATUS
---------- ------------------------------------------------------------ ------------------- -------
1 /oradata/orcl/system01.dbf 2013-08-24 11:37:33 SYSTEM
2 /oradata/orcl/sysaux01.dbf 2013-08-24 11:37:37 ONLINE
3 /oradata/orcl/undotbs01.dbf 2013-08-24 12:07:19 ONLINE
4 /oradata/orcl/users01.dbf 2013-08-24 11:37:49 ONLINE
5 /oradata/orcl/test01.dbf 2022-11-30 20:30:33 ONLINE
6 /oradata/orcl/test02.dbf 2022-11-30 20:30:34 ONLINE
7 /oradata/orcl/hsql01.dbf 2022-12-01 16:24:31 ONLINE
8 /oradata/orcl/hsql02.dbf 2022-12-05 14:32:34 RECOVER
8 rows selected.
SQL> select file#,name,CREATION_TIME,status from v$datafile_header;
FILE# NAME CREATION_TIME STATUS
---------- ------------------------------------------------------------ ------------------- -------
1 /oradata/orcl/system01.dbf 2013-08-24 11:37:33 ONLINE
2 /oradata/orcl/sysaux01.dbf 2013-08-24 11:37:37 ONLINE
3 /oradata/orcl/undotbs01.dbf 2013-08-24 12:07:19 ONLINE
4 /oradata/orcl/users01.dbf 2013-08-24 11:37:49 ONLINE
5 /oradata/orcl/test01.dbf 2022-11-30 20:30:33 ONLINE
6 /oradata/orcl/test02.dbf 2022-11-30 20:30:34 ONLINE
7 /oradata/orcl/hsql01.dbf 2022-12-01 16:24:31 ONLINE
8 /oradata/orcl/hsql02.dbf 2022-12-05 14:32:34 OFFLINE
8 rows selected.
SQL>
### 2. move old datafile
mv /oradata/orcl/hsql02.dbf /oradata/orcl/bak/
### 3. 恢复数据文件
[oracle@orcldb orcl]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 5 14:44:50 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1649805241)
RMAN> restore datafile 8;
Starting restore at 05-DEC-22
using channel ORA_DISK_1
creating datafile file number=8 name=/oradata/orcl/hsql02.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 05-DEC-22
RMAN> recover datafile 8;
Starting recover at 05-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=198 device type=DISK
starting media recovery
.....略过信息
archived log file name=/arch/1_251_1122024507.dbf thread=1 sequence=251
archived log file name=/arch/1_252_1122024507.dbf thread=1 sequence=252
archived log file name=/arch/1_253_1122024507.dbf thread=1 sequence=253
archived log file name=/arch/1_254_1122024507.dbf thread=1 sequence=254
archived log file name=/arch/1_255_1122024507.dbf thread=1 sequence=255
archived log file name=/arch/1_256_1122024507.dbf thread=1 sequence=256
media recovery complete, elapsed time: 00:00:32
Finished recover at 05-DEC-22
RMAN> exit
### 4. online 数据文件并检查业务恢复
SQL> alter database datafile 8 online;
Database altered.
SQL> alter system checkpoint;
System altered.
SQL>
SQL> select count(1) from hsql.drop_1;
COUNT(1)
----------
99999
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
