ORA-01113: file 8 needs media recovery

原本為了練習壞軌的模擬，先手動備份 sample.dbf --> sample.dbf.bk，再修改 sample.dbf 的 block 資料，

以觀察資料庫的變化，練習完成之後，再將備份  sample.dbf.bk --> sample.dbf，結果無法正常開啟資料庫。

因為確定 sample.dbf 的資料是正確的，所以問題應該是 checkpoint number不一致所導致。

所以我使用下列的方法解決。但是如果資料無法確定是否正確，則建議還原資料庫。

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 21 00:55:29 2012

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected.
SQL>startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/home/oracle/app/oracle/oradata/orcl/sample.dbf'

方法一：

SQL> Alter database datafile '/home/oracle/app/oracle/oradata/orcl/sample.dbf' offline

Database altered.

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> recover datafile 8;

Media recovery complete.

SQL> Alter database datafile '/home/oracle/app/oracle/oradata/orcl/sample.dbf' online;

Database altered.

方法二：

查詢檔案應該要註記的 checkpoint number

SQL> select name,checkpoint_change#,last_change# from v$datafile;

NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
/home/oracle/app/oracle/oradata/orcl/system01.dbf
        9812649     9812649

/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
        9812649     9812649

/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
        9812649     9812649


NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
/home/oracle/app/oracle/oradata/orcl/users01.dbf
        9812649     9812649

/home/oracle/app/oracle/oradata/orcl/example01.dbf
        9812649     9812649

/home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.dbf
        9812649     9812649


NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
/home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.dbf
        9812649     9812649

/home/oracle/app/oracle/oradata/orcl/sample.dbf
        9812649     9812649

/home/oracle/app/oracle/oradata/orcl/RMAN_CATALOG.dbf
        9812649     9812649


9 rows selected.

查詢資料庫的 checkpoint number
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
        9812649

實際查詢目前在檔案內的 checkpoint number，發現 sample.dbf 的號碼不一樣
SQL> select name,checkpoint_change# from v$datafile_header;

NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE#
------------------
/home/oracle/app/oracle/oradata/orcl/system01.dbf
        9812649

/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
        9812649

/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
        9812649


NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE#
------------------
/home/oracle/app/oracle/oradata/orcl/users01.dbf
        9812649

/home/oracle/app/oracle/oradata/orcl/example01.dbf
        9812649

/home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.dbf
        9812649


NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE#
------------------
/home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.dbf
        9812649

/home/oracle/app/oracle/oradata/orcl/sample.dbf
        9806191

/home/oracle/app/oracle/oradata/orcl/RMAN_CATALOG.dbf
        9812649


9 rows selected.

查詢需要恢復的檔案。
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
     8 ONLINE     9806191
    

SQL> shutdown immediate;

SQL> startup mount

恢復資料庫，第一次使用Auto，第二次使用Cancel


SQL> recover database using backup controlfile until auto;


SQL> recover database using backup controlfile until cancel;


SQL> shutdown immediate;

    

修改pifle ，例如：initorcl.ora，加入隱含的參數 _ALLOW_RESETLOGS_CORRUPTION=TRUE

指定 pfile 參數檔啟用
SQL> startup pfile=?/dbs/initorcl.ora
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size              1344840 bytes
Variable Size            377490104 bytes
Database Buffers        71303168 bytes
Redo Buffers              6008832 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

因為還原是"不完全還原"，open database時就一定要用resetlogs來讓database起來

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

no rows selected

SQL> select name,checkpoint_change# from v$datafile_header;

NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE#
------------------
/home/oracle/app/oracle/oradata/orcl/system01.dbf
        9806195

/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
        9806195

/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
        9806195


NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE#
------------------
/home/oracle/app/oracle/oradata/orcl/users01.dbf
        9806195

/home/oracle/app/oracle/oradata/orcl/example01.dbf
        9806195

/home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.dbf
        9806195


NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE#
------------------
/home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.dbf
        9806195

/home/oracle/app/oracle/oradata/orcl/sample.dbf
        9806195

/home/oracle/app/oracle/oradata/orcl/RMAN_CATALOG.dbf
        9806195


9 rows selected.

SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size              1344840 bytes
Variable Size            394267320 bytes
Database Buffers        54525952 bytes
Redo Buffers              6008832 bytes
Database mounted.
Database opened.