oracle的alter database recover语句实在很弱智，明明目录中有arch文件，却不去找这个文件。
看下面的2个例子：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- 例子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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
-- 例子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)> |
可见，不加automatic只能还原redo中的东西。
不过，如果我们用recover datafile语句，就没这么弱智了，至少还有个交互界面提示你输入auto，还是cancel还是指定某个arch：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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都不用指定了，呵呵：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
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> |
或者，在recover datafile之前set一下，也能和rman的效果类似的，不需要在另外的指定auto：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
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的文档中也说了（点击此处）：
|
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条评论
牛！记住了！
是不是9I的?我记得10G的alter database recover datafile 是会自动应用archive log的。
re OoNiceDream:我的数据库是9i的，那个官方文档上的话，也是9i的。
pgnq here