一次datafile 恢复

环境: 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/admin04.dbf                    6     ADMIN_IDX                                                                        AVAILABLE          134                                                                                                                                                                                           RECOVER

/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.

SQL> recover datafile 6;    
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