熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

數據庫恢復實例

2013-11-13 15:29:33  來源: Oracle 

  Errors in file /data/oracle/spftprd/admin/bdump/spftprd_ckpt_trc:
ORA: data file : /data/oracle/spftprd/index/spftprd_CALC_MEDIUM_INDEX_dbf
ORA: IO error reading block from file (block # )
ORA: skgfospo: number of bytes read/written is incorrect
SVR Error: : I/O error
Additional information:
Additional information:
Sun Nov ::
CKPT: terminating instance due to error
Instance terminated by CKPT pid =

  數據庫版本

  從alertlog裡面看到很多I/O錯誤均發生在datafile的第一個block(block # )應該是數據文件頭(datafile header)

  詳細錯誤如下

  SQL> select * from v$recover_file;

  FILE# ONLINE ERROR CHANGE# TIME

ONLINE E+ ::
OFFLINE E+ ::
ONLINE E+ ::
ONLINE CANNOT READ HEADER
ONLINE CANNOT READ HEADER
ONLINE E+ ::
ONLINE E+ ::
ONLINE CANNOT READ HEADER

   rows selected

  ALERT LOG

  Sun Nov ::
Errors in file /data/oracle/spftprd/admin/bdump/spftprd_ckpt_trc:
ORA: data file : /data/oracle/spftprd/index/spftprd_CALC_MEDIUM_INDEX_dbf
ORA: IO error reading block from file (block # )
ORA: skgfospo: number of bytes read/written is incorrect
SVR Error: : I/O error
Additional information:
Additional information:
Sun Nov ::
Errors in file /data/oracle/spftprd/admin/bdump/spftprd_ckpt_trc:
ORA: data file : /data/oracle/spftprd/index/spftprd_CALC_MEDIUM_INDEX_dbf
ORA: IO error reading block from file (block # )
ORA: skgfospo: number of bytes read/written is incorrect
SVR Error: : I/O error
Additional information:
Additional information:
Sun Nov ::
Errors in file /data/oracle/spftprd/admin/bdump/spftprd_ckpt_trc:
ORA: datafile going offline due to error advancing checkpoint
ORA: database file failed verification check
ORA: data file : /data/oracle/spftprd/index/spftprd_CALC_MEDIUM_INDEX_dbf
ORA: data file is an old version not accessing current version
Sun Nov ::
Errors in file /data/oracle/spftprd/admin/bdump/spftprd_ckpt_trc:
ORA: data file : /data/oracle/spftprd/index/spftprd_CALC_MEDIUM_INDEX_dbf
ORA: IO error reading block from file (block # )
ORA: skgfospo: number of bytes read/written is incorrect
SVR Error: : I/O error
Additional information:
Additional information:
Sun Nov ::
Errors in file /data/oracle/spftprd/admin/bdump/spftprd_ckpt_trc:
ORA: data file : /data/oracle/spftprd/index/spftprd_CALC_MEDIUM_INDEX_dbf
ORA: IO error reading block from file (block # )
ORA: skgfospo: number of bytes read/written is incorrect
SVR Error: : I/O error
Additional information:
Additional information:
Sun Nov ::
Errors in file /data/oracle/spftprd/admin/bdump/spftprd_ckpt_trc:
ORA: data file : /data/oracle/spftprd/index/spftprd_CALC_MEDIUM_INDEX_dbf
ORA: IO error reading block from file (block # )
ORA: skgfospo: number of bytes read/written is incorrect
SVR Error: : I/O error
Additional information:
Additional information:
Sun Nov ::
Errors in file /data/oracle/spftprd/admin/bdump/spftprd_ckpt_trc:
ORA: data file : /data/oracle/spftprd/index/spftprd_CALC_MEDIUM_INDEX_dbf
ORA: IO error reading block from file (block # )
ORA: skgfospo: number of bytes read/written is incorrect
SVR Error: : I/O error
Additional information:
Additional information:
Sun Nov ::
Errors in file /data/oracle/spftprd/admin/bdump/spftprd_ckpt_trc:
ORA: data file : /data/oracle/spftprd/index/spftprd_CALC_MEDIUM_INDEX_dbf
ORA: IO error reading block from file (block # )
ORA: skgfospo: number of bytes read/written is incorrect
SVR Error: : I/O error
Additional information:
Additional information:
Sun Nov ::
CKPT: terminating instance due to error
Instance terminated by CKPT pid =

  有個數據文件需要恢復但使用dbv沒有查到有corrupted block類似

  DBVERIFY: Release Production on Mon Nov ::
(c) Copyright Oracle Corporation All rights reserved
DBVERIFY Verification starting : FILE = /data/oracle/spftprd/index/spftprd_build_index_dbf

  DBVERIFY Verification complete

  Total Pages Examined :
Total Pages Processed (Data) :
Total Pages Failing (Data) :
Total Pages Processed (Index):
Total Pages Failing (Index):
Total Pages Processed (Other):
Total Pages Empty :
Total Pages Marked Corrupt :
Total Pages Influx :

  由於沒有合適的備份(數據庫archive log 模式天前剛resetlog過還沒有備份)上一班的DBA挺boring的認為是數據文件有壞塊連嘗試recover database都沒有作

  剛開始我也誤如歧途還想著offline這些數據文件(經過確認全部是索引表空間沒有存儲數據段)再重建這T左右的索引數據

  後來想確認還有哪些datafile有問題便一個tablespace一個tablespace的恢復最後把數據庫給起來了真好笑手生了

  SQL> alter database open;
alter database open
*
ERROR at line :
ORA: file needs media recovery
ORA: data file :
/data/oracle/spftprd/index/spftprd_build_index_dbf
SQL> recover tablespace build_index;
Media recovery complete
SQL> alter database open;
alter database open
*
ERROR at line :
ORA: file needs media recovery
ORA: data file :
/data/oracle/spftprd/index/spftprd_CALC_MEDIUM_INDEX_dbf
SQL> recover tablespace CALC_MEDIUM_INDEX;
Media recovery complete
SQL> alter database open;
alter database open
*
ERROR at line :
ORA: file needs media recovery
ORA: data file :
/data/oracle/spftprd/index/spftprd_CAVJ_LARGE_INDEX_dbf
SQL> recover tablespace CAVJ_LARGE_INDEX;
Media recovery complete
SQL> alter database open;
alter database open
*
ERROR at line :
ORA: file needs media recovery
ORA: data file :
/data/oracle/spftprd/index/spftprd_CALC_LARGE_INDEX_dbf
SQL> recover tablespace CALC_LARGE_INDEX;
Media recovery complete
SQL> alter database open;
alter database open
*
ERROR at line :
ORA: file needs media recovery
ORA: data file :
/data/oracle/spftprd/index/spftprd_BOXV_MEDIUM_INDEX_dbf
SQL> recover tablespace BOXV_MEDIUM_INDEX;
Media recovery complete
SQL> alter database open;
alter database open
*
ERROR at line :
ORA: file needs media recovery
ORA: data file :
/data/oracle/spftprd/index/spftprd_atds_large_index_dbf
SQL> recover tablespace atds_large_index;
Media recovery complete

  SQL> alter database open;

  Database altered

  SQL> alter system checkpoint;
System altered

  早知如此就recover database了

  猜測問題是磁盤子系統的異步I/O出了internal的問題不然怎麼會壞在database header

  同時Oracle support提供了一個bug還是我們的版本太低了CKPT不夠穩定

  This problem is due to the next bug:
==================================================================
BugTag: Support notes on Bug DDR info BugDesc
Affects: RDBMS (A)
NB: FIXED
Abstract: CKPT may crash the instance if datafile cannot be accessed
FixedReleases: A
Tags: CRASH
Details:
If a datafile from a nonsystem tablespace is inaccessible
the CKPT process may bring down the instance rather than taking
the datafile offline
==================================================================

  SOLUTION / ACTION PLAN
======================

  ) Please apply the Oracle Server Patchset (bit) for Sun Sparc Solaris

  ) Then please apply the next oneoff patch:
==================================================================
Patch:
Description: DIAG MERGE LABEL REQUEST ON TOP OF FOR BUG# AND MORE
Product: Oracle Database Family
Release: Oracle
Platform or Language: Solaris Operating System (SPARC bit)
Last Updated: OCT
Size: M ( bytes)
==================================================================


From:http://tw.wingwit.com/Article/program/Oracle/201311/16804.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.