本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 非归档数据文件头损坏的案例
这是某网友的问题，数据库为非归档，硬件故障导致数据文件头损坏，如下是部分日志：
Tue Sep 01 09:38:29 2015 Errors in file d:\oracle\diag\rdbms\dbhx\dbhx\trace\dbhx_ckpt_2984.trc: ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式 ORA-01122: 数据库文件 19 验证失败 ORA-01110: 数据文件 19: 'H:\ORADATA\HXSC_IDX01.DBF' ORA-01251: 读取了文件号 19 的未知文件标头版本 Errors in file d:\oracle\diag\rdbms\dbhx\dbhx\trace\dbhx_ckpt_2984.trc: ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式 ORA-01122: 数据库文件 19 验证失败 ORA-01110: 数据文件 19: 'H:\ORADATA\HXSC_IDX01.DBF' ORA-01251: 读取了文件号 19 的未知文件标头版本 CKPT (ospid: 2984): terminating the instance due to error 1242 Tue Sep 01 09:38:29 2015 ORA-1092 : opiodr aborting process unknown ospid (1660_3356) Tue Sep 01 09:38:29 2015 ORA-1092 : opitsk aborting process Tue Sep 01 09:38:29 2015 ...... ...... Tue Sep 01 09:38:30 2015 Error occured while spawning process m000; error = 1242 Tue Sep 01 09:38:30 2015 opidrv aborting process S000 ospid (1660_3008) due to error ORA-1242 Tue Sep 01 09:38:31 2015 Errors in file d:\oracle\diag\rdbms\dbhx\dbhx\trace\dbhx_q004_12.trc: ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式 Tue Sep 01 09:38:31 2015 ORA-1092 : opiodr aborting process unknown ospid (1660_3996) Tue Sep 01 09:38:31 2015 ORA-1092 : opitsk aborting process Tue Sep 01 09:38:31 2015 Errors in file d:\oracle\diag\rdbms\dbhx\dbhx\trace\dbhx_q002_592.trc: ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式 Instance terminated by CKPT, pid = 2984 Tue Sep 01 09:45:22 2015
我通过dbv检查发现其实有超过3个文件都存在文件头损坏的情况，对于文件头损坏的情况，客户通过create datafile的方式进行恢复，其实这种情况下是不行的。如果是归档模式，而且自文件创建以来的归档都存在的话，那么这种create的方式是可以的，如下是网友的操作信息：
Tue Sep 01 10:43:52 2015 MMNL started with pid=52, OS id=3040 starting up 1 shared server(s) ... ORACLE_BASE from environment = D:\oracle Tue Sep 01 10:43:52 2015 alter database mount exclusive Setting recovery target incarnation to 3 Successful mount of redo thread 1, with mount id 2444484840 Database mounted in Exclusive Mode Lost write protection disabled Completed: alter database mount exclusive alter database open Errors in file d:\oracle\diag\rdbms\dbhx\dbhx\trace\dbhx_dbw0_4752.trc: ORA-01157: ????/?????? 19 - ??? DBWR ???? ORA-01110: ???? 19: 'H:\ORADATA\HXSC_IDX01.DBF' ORA-27041: ?????? OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件。 ORA-1157 signalled during: alter database open... Tue Sep 01 10:44:02 2015 Checker run found 1 new persistent data failures Tue Sep 01 10:44:38 2015 ALTER DATABASE RECOVER datafile 'H:\ORADATA\HXSC_IDX01.DBF' Media Recovery Start Fast Parallel Media Recovery NOT enabled Tue Sep 01 10:44:39 2015 Errors in file d:\oracle\diag\rdbms\dbhx\dbhx\trace\dbhx_dbw0_4752.trc: ORA-01157: ????/?????? 19 - ??? DBWR ???? ORA-01110: ???? 19: 'H:\ORADATA\HXSC_IDX01.DBF' ORA-27041: ?????? OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件。 Media Recovery failed with error 1110 ORA-283 signalled during: ALTER DATABASE RECOVER datafile 'H:\ORADATA\HXSC_IDX01.DBF' ... Tue Sep 01 10:47:45 2015 alter database create datafile 'h:\oradta\HX_IDX01.DBF' ORA-1516 signalled during: alter database create datafile 'h:\oradta\HX_IDX01.DBF'... Tue Sep 01 10:48:45 2015 ALTER DATABASE RECOVER datafile 'h:\oradta\HX_IDX01.DBF' ORA-1179 signalled during: ALTER DATABASE RECOVER datafile 'h:\oradta\HX_IDX01.DBF' ... Tue Sep 01 10:49:08 2015 alter database create datafile 'h:\oradta\HX_IDX01.DBF' ORA-1516 signalled during: alter database create datafile 'h:\oradta\HX_IDX01.DBF'... Tue Sep 01 10:51:12 2015 alter database open Tue Sep 01 10:51:12 2015 Errors in file d:\oracle\diag\rdbms\dbhx\dbhx\trace\dbhx_dbw0_4752.trc: ORA-01157: ????/?????? 19 - ??? DBWR ???? ORA-01110: ???? 19: 'H:\ORADATA\HXSC_IDX01.DBF' ORA-27041: ?????? OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件。 ORA-1157 signalled during: alter database open... Tue Sep 01 11:20:49 2015 ALTER SYSTEM SET _allow_resetlogs_corruption=TRUE SCOPE=SPFILE; Tue Sep 01 11:21:01 2015 Shutting down instance: further logons disabled Stopping background process MMNL Stopping background process MMON Shutting down instance (immediate)
其实这个问题处理很简单，首先可以尝试修复文件头，对于文件头的修复，博客中也有相关案例。其次损坏的文件从名称上来看都是index，完全可以offline drop，然后把库拉起来，然后重建相关index即可。
对于这样的案例，如果需要专业处理，请联系我们！
Related posts: