recover时报错ora-289，有意思

oracle的alter database recover语句实在很弱智，明明目录中有arch文件，却不去找这个文件。
看下面的2个例子：

-- 例子1：
sys@ORALOCAL(10.1.26.26)> alter database datafile 'e:\test02.dbf' offline;

Database altered.

Elapsed: 00:00:02.73
sys@ORALOCAL(10.1.26.26)>
sys@ORALOCAL(10.1.26.26)> alter database recover datafile 'e:\test02.dbf';

Database altered.

Elapsed: 00:01:00.25
sys@ORALOCAL(10.1.26.26)> alter database datafile 'e:\test02.dbf' online;

Database altered.

Elapsed: 00:00:00.43
sys@ORALOCAL(10.1.26.26)>

-- 例子1：

sys@ORALOCAL(10.1.26.26)> alter database datafile 'e:\test02.dbf' offline;

Database altered.

Elapsed: 00:00:02.73

sys@ORALOCAL(10.1.26.26)>

sys@ORALOCAL(10.1.26.26)> alter database recover datafile 'e:\test02.dbf';

Database altered.

Elapsed: 00:01:00.25

sys@ORALOCAL(10.1.26.26)> alter database datafile 'e:\test02.dbf' online;

Database altered.

Elapsed: 00:00:00.43

sys@ORALOCAL(10.1.26.26)>

-- 例子2：
sys@ORALOCAL(10.1.26.26)> alter database datafile 'e:\test02.dbf' offline;

Database altered.

Elapsed: 00:00:00.50
sys@ORALOCAL(10.1.26.26)> alter system switch logfile;

System altered.

Elapsed: 00:00:01.03
sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:05.14
sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.15
sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:05.28
sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.15
sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.21
sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.32
sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.35
sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:05.17
sys@ORALOCAL(10.1.26.26)> alter database recover datafile 'e:\test02.dbf';
alter database recover datafile 'e:\test02.dbf'
*
ERROR at line 1:
ORA-00279: change 801109 generated at 12/08/2008 13:21:12 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_MF_1_55_%U_.ARC
ORA-00280: change 801109 for thread 1 is in sequence #55


Elapsed: 00:00:00.59
sys@ORALOCAL(10.1.26.26)>
sys@ORALOCAL(10.1.26.26)>
sys@ORALOCAL(10.1.26.26)> alter database recover cancel;

Database altered.

Elapsed: 00:00:01.18
sys@ORALOCAL(10.1.26.26)>
sys@ORALOCAL(10.1.26.26)>
sys@ORALOCAL(10.1.26.26)> alter database recover automatic datafile 'e:\test02.dbf';

Database altered.

Elapsed: 00:00:02.29
sys@ORALOCAL(10.1.26.26)> alter database datafile 'e:\test02.dbf' online;

Database altered.

Elapsed: 00:00:00.39
sys@ORALOCAL(10.1.26.26)>

-- 例子2：

sys@ORALOCAL(10.1.26.26)> alter database datafile 'e:\test02.dbf' offline;

Database altered.

Elapsed: 00:00:00.50

sys@ORALOCAL(10.1.26.26)> alter system switch logfile;

System altered.

Elapsed: 00:00:01.03

sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:05.14

sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.15

sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:05.28

sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.15

sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.21

sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.32

sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.35

sys@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:05.17

sys@ORALOCAL(10.1.26.26)> alter database recover datafile 'e:\test02.dbf';

alter database recover datafile 'e:\test02.dbf'

ERROR at line 1:

ORA-00279: change 801109 generated at 12/08/2008 13:21:12 needed for thread 1

ORA-00289: suggestion :

D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_MF_1_55_%U_.ARC

ORA-00280: change 801109 for thread 1 is in sequence #55

Elapsed: 00:00:00.59

sys@ORALOCAL(10.1.26.26)>

sys@ORALOCAL(10.1.26.26)> alter database recover cancel;

Database altered.

Elapsed: 00:00:01.18

sys@ORALOCAL(10.1.26.26)>

sys@ORALOCAL(10.1.26.26)> alter database recover automatic datafile 'e:\test02.dbf';

Database altered.

Elapsed: 00:00:02.29

sys@ORALOCAL(10.1.26.26)> alter database datafile 'e:\test02.dbf' online;

Database altered.

Elapsed: 00:00:00.39

sys@ORALOCAL(10.1.26.26)>

可见，不加automatic只能还原redo中的东西。

不过，如果我们用recover datafile语句，就没这么弱智了，至少还有个交互界面提示你输入auto，还是cancel还是指定某个arch：

sys@ORALOCAL(10.1.26.26)> recover datafile 'e:\test02.dbf';
ORA-00279: change 801230 generated at 12/08/2008 13:25:38 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_MF_1_66_%U_.ARC
ORA-00280: change 801230 for thread 1 is in sequence #66


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
sys@ORALOCAL(10.1.26.26)>

sys@ORALOCAL(10.1.26.26)> recover datafile 'e:\test02.dbf';

ORA-00279: change 801230 generated at 12/08/2008 13:25:38 needed for thread 1

ORA-00289: suggestion :

D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_MF_1_66_%U_.ARC

ORA-00280: change 801230 for thread 1 is in sequence #66

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

Log applied.

Media recovery complete.

sys@ORALOCAL(10.1.26.26)>

另外，除了上述的方法，我们还能用rman来实现，连auto都不用指定了，呵呵：

test@ORALOCAL(10.1.26.26)> alter database recover datafile 'e:\test02.dbf';
alter database recover datafile 'e:\test02.dbf'
*
ERROR at line 1:
ORA-00279: change 804474 generated at 12/08/2008 15:28:19 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_MF_1_100_%U_.ARC
ORA-00280: change 804474 for thread 1 is in sequence #100


Elapsed: 00:00:00.70
test@ORALOCAL(10.1.26.26)>
test@ORALOCAL(10.1.26.26)> alter database recover cancel;

Database altered.

Elapsed: 00:00:01.35
test@ORALOCAL(10.1.26.26)>
test@ORALOCAL(10.1.26.26)> host;
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\hejianmin>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on 星期一 12月 8 15:40:57 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORALOCAL (DBID=3897889420)

RMAN>

RMAN> recover datafile 9;

Starting recover at 08-12月-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK

starting media recovery

archive log thread 1 sequence 100 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_
MF_1_100_4MSM6KJK_.ARC
archive log thread 1 sequence 101 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_
MF_1_101_4MSM6LJK_.ARC
archive log thread 1 sequence 102 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_
MF_1_102_4MSM6P7R_.ARC
archive log thread 1 sequence 103 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_
MF_1_103_4MSM6RYP_.ARC
archive log thread 1 sequence 104 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_
MF_1_104_4MSM6SMZ_.ARC
archive log thread 1 sequence 105 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_
MF_1_105_4MSM6ZWR_.ARC
archive log thread 1 sequence 106 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_
MF_1_106_4MSM72KJ_.ARC
archive log thread 1 sequence 107 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_
MF_1_107_4MSM73OF_.ARC
archive log thread 1 sequence 108 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_
MF_1_108_4MSM74V9_.ARC
archive log thread 1 sequence 109 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_
MF_1_109_4MSM7BWR_.ARC
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_MF_1_100_4MSM6KJK_.ARC thread=1 sequence
=100
media recovery complete, elapsed time: 00:00:03
Finished recover at 08-12月-08

RMAN>

test@ORALOCAL(10.1.26.26)> alter database recover datafile 'e:\test02.dbf';

alter database recover datafile 'e:\test02.dbf'

ERROR at line 1:

ORA-00279: change 804474 generated at 12/08/2008 15:28:19 needed for thread 1

ORA-00289: suggestion :

D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_MF_1_100_%U_.ARC

ORA-00280: change 804474 for thread 1 is in sequence #100

Elapsed: 00:00:00.70

test@ORALOCAL(10.1.26.26)>

test@ORALOCAL(10.1.26.26)> alter database recover cancel;

Database altered.

Elapsed: 00:00:01.35

test@ORALOCAL(10.1.26.26)>

test@ORALOCAL(10.1.26.26)> host;

Microsoft Windows XP [版本 5.1.2600]

C:\Documents and Settings\hejianmin>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on 星期一 12月 8 15:40:57 2008

connected to target database: ORALOCAL (DBID=3897889420)

RMAN>

RMAN> recover datafile 9;

Starting recover at 08-12月-08

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=158 devtype=DISK

starting media recovery

archive log thread 1 sequence 100 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_

MF_1_100_4MSM6KJK_.ARC

archive log thread 1 sequence 101 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_

MF_1_101_4MSM6LJK_.ARC

archive log thread 1 sequence 102 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_

MF_1_102_4MSM6P7R_.ARC

archive log thread 1 sequence 103 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_

MF_1_103_4MSM6RYP_.ARC

archive log thread 1 sequence 104 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_

MF_1_104_4MSM6SMZ_.ARC

archive log thread 1 sequence 105 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_

MF_1_105_4MSM6ZWR_.ARC

archive log thread 1 sequence 106 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_

MF_1_106_4MSM72KJ_.ARC

archive log thread 1 sequence 107 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_

MF_1_107_4MSM73OF_.ARC

archive log thread 1 sequence 108 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_

MF_1_108_4MSM74V9_.ARC

archive log thread 1 sequence 109 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_

MF_1_109_4MSM7BWR_.ARC

archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_MF_1_100_4MSM6KJK_.ARC thread=1 sequence

=100

media recovery complete, elapsed time: 00:00:03

Finished recover at 08-12月-08

RMAN>

或者，在recover datafile之前set一下，也能和rman的效果类似的，不需要在另外的指定auto：

test@ORALOCAL(10.1.26.26)> alter database datafile 'e:\test02.dbf' offline;

Database altered.

Elapsed: 00:00:00.45
test@ORALOCAL(10.1.26.26)> alter system switch logfile;

System altered.

Elapsed: 00:00:00.17
test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.45
test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:02.70
test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:03.10
test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.23
test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:03.46
test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.35
test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:05.20
test@ORALOCAL(10.1.26.26)> alter database recover datafile 'e:\test02.dbf';
alter database recover datafile 'e:\test02.dbf'
*
ERROR at line 1:
ORA-00279: change 804963 generated at 12/08/2008 15:47:38 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_MF_1_110_%U_.ARC
ORA-00280: change 804963 for thread 1 is in sequence #110


Elapsed: 00:00:01.23
test@ORALOCAL(10.1.26.26)> alter database recover cancel;

Database altered.

Elapsed: 00:00:02.62
test@ORALOCAL(10.1.26.26)> SET AUTORECOVERY ON
test@ORALOCAL(10.1.26.26)> recover datafile 'e:\test02.dbf';
ORA-00279: change 804963 generated at 12/08/2008 15:47:38 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_MF_1_110_%U_.ARC
ORA-00280: change 804963 for thread 1 is in sequence #110


Log applied.
Media recovery complete.
test@ORALOCAL(10.1.26.26)> alter database datafile 9 online;

Database altered.

Elapsed: 00:00:00.53
test@ORALOCAL(10.1.26.26)>

test@ORALOCAL(10.1.26.26)> alter database datafile 'e:\test02.dbf' offline;

Database altered.

Elapsed: 00:00:00.45

test@ORALOCAL(10.1.26.26)> alter system switch logfile;

System altered.

Elapsed: 00:00:00.17

test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.45

test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:02.70

test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:03.10

test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.23

test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:03.46

test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:00.35

test@ORALOCAL(10.1.26.26)> /

System altered.

Elapsed: 00:00:05.20

test@ORALOCAL(10.1.26.26)> alter database recover datafile 'e:\test02.dbf';

alter database recover datafile 'e:\test02.dbf'

ERROR at line 1:

ORA-00279: change 804963 generated at 12/08/2008 15:47:38 needed for thread 1

ORA-00289: suggestion :

D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_MF_1_110_%U_.ARC

ORA-00280: change 804963 for thread 1 is in sequence #110

Elapsed: 00:00:01.23

test@ORALOCAL(10.1.26.26)> alter database recover cancel;

Database altered.

Elapsed: 00:00:02.62

test@ORALOCAL(10.1.26.26)> SET AUTORECOVERY ON

test@ORALOCAL(10.1.26.26)> recover datafile 'e:\test02.dbf';

ORA-00279: change 804963 generated at 12/08/2008 15:47:38 needed for thread 1

ORA-00289: suggestion :

D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORALOCAL\ARCHIVELOG\2008_12_08\O1_MF_1_110_%U_.ARC

ORA-00280: change 804963 for thread 1 is in sequence #110

Log applied.

Media recovery complete.

test@ORALOCAL(10.1.26.26)> alter database datafile 9 online;

Database altered.

Elapsed: 00:00:00.53

test@ORALOCAL(10.1.26.26)>

结论：
恢复脱机的数据文件时候，如果恢复的内容不在online redolog中，我们可以：
1、recover datafile XXX 然后指定auto。
2、用rman recover datafile。
3、set AUTORECOVERY ON之后用recover datafile XXX。
4、尽量避免用alter database recover datafile。如果用，就用alter database recover automatic datafile XXX。
其实在oracle的文档中也说了（点击此处）：

It is also possible to use the SQL statement ALTER DATABASE RECOVER, but it is highly recommended that you use the SQL*Plus RECOVER command instead.

1	It is also possible to use the SQL statement ALTER DATABASE RECOVER, but it is highly recommended that you use the SQL*Plus RECOVER command instead.

4条评论

Rill说道：

2008-12-09 14:57

牛！记住了！

回复
OoNiceDream说道：

2008-12-12 22:32

是不是9I的?我记得10G的alter database recover datafile 是会自动应用archive log的。

回复
小荷说道：

2008-12-15 01:06

re OoNiceDream:我的数据库是9i的，那个官方文档上的话，也是9i的。

回复
JosephJal说道：

2019-03-22 22:47

pgnq here

回复

回复 JosephJal 取消回复

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据。

recover时报错ora-289，有意思

相关文章

RMAN active duplicate hanging on restore control file

提交SR的一些小技巧

用crs实现single instance的HA

4条评论

回复 JosephJal 取消回复