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.

利用Plink替代Putty來建立Script