一次datafile 恢复

来源：互联网发布：c语言新建工程步骤编辑：程序博客网时间：2024/05/17 05:04

环境: 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');

没有返回的条目了。