环境: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
库运行在archive log 模式。
现象:
应用报错误:ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/data/admin07.dbf'
检查数据库:
SQL> select * from dba_data_files where online_status IN ('OFFLINE','RECOVER');
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
/data/admin07.dbf 5 ADMIN_IDX AVAILABLE 143 RECOVER
发现有2个datafile 的状态是RECOVER
于是执行:
SQL> recover datafile 5;
ORA-00279: change 74235172681 generated at 11/27/2011 11:38:22 needed for
thread 1
ORA-00289: suggestion : /data/arch/1_1148_758804239.dbf
ORA-00280: change 74235172681 for thread 1 is in sequence #1148
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
ORA-00279: change 74235172681 generated at 11/27/2011 11:38:22 needed for
thread 1
ORA-00289: suggestion : /data/arch/1_1148_758804239.dbf
ORA-00280: change 74235172681 for thread 1 is in sequence #1148
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
再查看2个数据文件:
SQL> select file_name,file_id,online_status from dba_data_files where file_id in (5,6);
FILE_NAME FILE_ID ONLINE_STATUS
----------------------------------------------------------------------- ------------------ ------------------
/data/admin07.dbf 5 OFFLINE
/dataadmin04.dbf 6 OFFLINE
切换到online
SQL> alter database datafile 5 online;
Database altered
SQL> alter database datafile 6 online;
Database altered
再次查看
SQL>select * from dba_data_files where online_status IN ('OFFLINE','RECOVER');
没有返回的条目了。
OK