ORA-01113: file 8 needs media recovery【数据文件的OFFLINE操作造成】

     在让一个数据文件上线的时候，突然报错：“ORA-01113: file 8 needs media recovery”

SQL> alter database datafile  'D:\APP\ASUS\ORADATA\WAREHOUSE\TEST03.DBF' online;alter database datafile  'D:\APP\ASUS\ORADATA\WAREHOUSE\TEST03.DBF' online*ERROR at line 1:ORA-01113: file 8 needs media recoveryORA-01110: data file 8: 'D:\APP\ASUS\ORADATA\WAREHOUSE\TEST03.DBF'

 一、修复过程：

第一步：启动RMAN，同时连接恢复目录和目标数据库

SQL> host rmanRecovery Manager: Release 11.2.0.1.0 - Production on Mon Aug 19 21:59:56 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.RMAN> connect catalog rman/123456@catalog;connected to recovery catalog databaseRMAN> connect target sys/123456@warehouse;connected to target database: WAREHOUS (DBID=4011143137)

第二步：分析

RMAN> list failure;starting full resync of recovery catalogfull resync completeList of Database Failures=========================Failure ID Priority Status    Time Detected Summary---------- -------- --------- ------------- -------782        HIGH     OPEN      19-AUG-13     One or more non-system datafiles need media recoveryRMAN> advise failure;List of Database Failures=========================Failure ID Priority Status    Time Detected Summary---------- -------- --------- ------------- -------782        HIGH     OPEN      19-AUG-13     One or more non-system datafiles need media recoveryanalyzing automatic repair options; this may take some timeallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=140 device type=DISKanalyzing automatic repair options completeMandatory Manual Actions========================no manual actions availableOptional Manual Actions=======================1. If you restored the wrong version of data file D:\APP\ASUS\ORADATA\WAREHOUSE\TEST03.DBF, then replace it with the correct oneAutomated Repair Options========================Option Repair Description------ ------------------1      Recover datafile 8  Strategy: The repair includes complete media recovery with no data loss  Repair script: d:\app\asus\diag\rdbms\warehouse\warehouse\hm\reco_32960388.hmstarting full resync of recovery catalogfull resync complete

第三步，修复数据文件

RMAN> run2> {3>    sql 'alter database datafile 8 offline';4>    recover datafile 8;5>    sql 'alter database datafile 8 online';6> }

 

另外，还可以使用SQL语句来进行修复：

SQL>alter database  datafile 8 offline;SQL>alter database recover datafile 8;SQL>alter database datafile 8 online;

二、原因分析

         查了一下oracle的技术文档，造成该数据文件进行物理修复的原因，是之前的offline操作造成的。因为，如果进行OFFLINE操作的时候数据库是处于OPEN状态的话，该操作进行的时候不会发出检查点，也就是说不会把该数据文件在内存中可能存在的脏数据块刷新到物理文件中。所以，下次把该数据文件ONLINE的时候，必须进行物理修复。

     另外，注意到了一个比较特别的情况，就是把该数据文件离线之后，该数据文件的状态不是OFFLINE，而是RECOVER。这个应该是修复的时候，为什么先执行：sql 'alter database datafile 8 offline';的原因吧。

SQL> select a.file#,b.name ,a.status from v$datafile a,v$tablespace b  where a.ts#=b.ts#  and b.name='TEST01';     FILE# NAME                           STATUS---------- ------------------------------ -------         6 TEST01                         ONLINE         8 TEST01                         RECOVER

三、拓展

        另外，alter tablespace ...offline的时候，也有可能出现类似的问题。

        alter tablespace ....offline有三种选项：normal,temporary,immediate

normal:让表空间offline之前会发出检查点，刷新所有属于该表空间但还在内存中的脏数据块到物理文件中。之后的ONLINE操作不需要进行物理恢复。此为，默认设置。

temporary:对所有的online数据文件发出检查点，注意不包括OFFLINE的数据文件，因此，OFFLINE的数据文件在之后表空间被ONLINE或自身ONLINE之前，必须先进行物理修复。这也是数据文件OFFLINE操作的时候没有发出检查点的原因。

immediate：不发出检查点，之后online的时候必须进行物理修复。

        需要注意的是，如果表空间已经数据文件offline，则就不能使用normal方式让表空间离线了，只能使用temporary或immediate.否则会报错：

SQL> alter tablespace test01 offline normal;alter tablespace test01 offline normal*ERROR at line 1:ORA-01191: file 8 is already offline - cannot do a normal offlineORA-01110: data file 8: 'D:\APP\ASUS\ORADATA\WAREHOUSE\TEST03.DBF'

       下面，以immediate介绍其恢复过程：

SQL> alter tablespace test01 offline immediate;Tablespace altered.SQL> alter tablespace test01 online;alter tablespace test01 online*ERROR at line 1:ORA-01113: file 6 needs media recoveryORA-01110: data file 6: 'D:\APP\ASUS\ORADATA\WAREHOUSE\TEST01.DBF'SQL> select a.file#,b.name ,a.status from v$datafile a,v$tablespace b  where a.ts#=b.ts#  and b.name='TEST01';     FILE# NAME                           STATUS---------- ------------------------------ -------         6 TEST01                         RECOVER         8 TEST01                         RECOVERSQL> select tablespace_name ,status from dba_tablespaces where tablespace_name='TEST01';TABLESPACE_NAME                STATUS------------------------------ ---------TEST01                         OFFLINESQL> host rmanRecovery Manager: Release 11.2.0.1.0 - Production on Mon Aug 19 23:31:39 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.RMAN> connect catalog rman/123456@catalog;connected to recovery catalog databaseRMAN> connect target sys/123456@warehouse;connected to target database: WAREHOUS (DBID=4011143137)RMAN> list failure;starting full resync of recovery catalogfull resync completeList of Database Failures=========================Failure ID Priority Status    Time Detected Summary---------- -------- --------- ------------- -------782        HIGH     OPEN      19-AUG-13     One or more non-system datafiles need media recovery939        HIGH     OPEN      19-AUG-13     Tablespace 7: 'TEST01' is offlineRMAN> advise failure;List of Database Failures=========================Failure ID Priority Status    Time Detected Summary---------- -------- --------- ------------- -------782        HIGH     OPEN      19-AUG-13     One or more non-system datafiles need media recovery939        HIGH     OPEN      19-AUG-13     Tablespace 7: 'TEST01' is offlineanalyzing automatic repair options; this may take some timeallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=148 device type=DISKanalyzing automatic repair options completeNot all specified failures can currently be repaired.The following failures must be repaired before advise for others can be given.Failure ID Priority Status    Time Detected Summary---------- -------- --------- ------------- -------782        HIGH     OPEN      19-AUG-13     One or more non-system datafiles need media recoveryMandatory Manual Actions========================no manual actions availableOptional Manual Actions=======================1. If you restored the wrong version of data file D:\APP\ASUS\ORADATA\WAREHOUSE\TEST03.DBF, then replace it with the correct one2. If you restored the wrong version of data file D:\APP\ASUS\ORADATA\WAREHOUSE\TEST01.DBF, then replace it with the correct oneAutomated Repair Options========================Option Repair Description------ ------------------1      Recover datafile 8; Recover datafile 6  Strategy: The repair includes complete media recovery with no data loss  Repair script: d:\app\asus\diag\rdbms\warehouse\warehouse\hm\reco_3771496276.hmSQL> alter database recover tablespace test01;Database altered.SQL> select tablespace_name ,status from dba_tablespaces where tablespace_name='TEST01';TABLESPACE_NAME                STATUS------------------------------ ---------TEST01                         OFFLINESQL> alter tablespace test01 online;Tablespace altered.