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)
==================================================================
