玉面飞龙的BLOG

最近被alter database recover datafile恶搞了一把，记之。

alter database recover datafile当数据文件需要使用已经归档的archived log file进行恢复的时候，会提示警告，但不会继续恢复；但数据库还处于media recovery模式，这种情况下，可以使用alter database recover logfile 继续media recovery。当数据库文件仅仅需要online redo log file就可以完成恢复的时候，alter database recover datafile会自动恢复。

比recover datafile恶搞多了。

该测试库上只有2个online redo log file group,

[coolcode lang=”sql” linenum=”no” ]
SQL> alter database datafile ‘/export/home/oracle/base10202/product/10.2.0.2/haozhu/bdump/viadea.dbf’ offline;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database recover datafile ‘/export/home/oracle/base10202/product/10.2.0.2/haozhu/bdump/viadea.dbf’ ;

Database altered.

SQL> alter database datafile ‘/export/home/oracle/base10202/product/10.2.0.2/haozhu/bdump/viadea.dbf’ online;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database datafile ‘/export/home/oracle/base10202/product/10.2.0.2/haozhu/bdump/viadea.dbf’ offline;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database recover datafile ‘/export/home/oracle/base10202/product/10.2.0.2/haozhu/bdump/viadea.dbf’ ;
alter database recover datafile ‘/export/home/oracle/base10202/product/10.2.0.2/haozhu/bdump/viadea.dbf’
*
ERROR at line 1:
ORA-00279: change 3183156 generated at 11/02/2007 15:31:52 needed for thread 1
ORA-00289: suggestion : /export/home/oracle/base10202/product/10.2.0.2/dbs/arch1_415_631145392.dbf
ORA-00280: change 3183156 for thread 1 is in sequence #415

这时候可以使用alter database recover logfile ‘/export/home/oracle/base10202/product/10.2.0.2/dbs/arch1_415_631145392.dbf’;继续恢复的。不过这个命令以前从来没有见过。

SQL> recover datafile ‘/export/home/oracle/base10202/product/10.2.0.2/haozhu/bdump/viadea.dbf’ ;
ORA-00275: media recovery has already been started

SQL> alter database recover cancel;

Database altered.

SQL> recover datafile ‘/export/home/oracle/base10202/product/10.2.0.2/haozhu/bdump/viadea.dbf’ ;
ORA-00279: change 3183156 generated at 11/02/2007 15:31:52 needed for thread 1
ORA-00289: suggestion : /export/home/oracle/base10202/product/10.2.0.2/dbs/arch1_415_631145392.dbf
ORA-00280: change 3183156 for thread 1 is in sequence #415

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL>

[/coolcode]

之前只记得有”alter database recover datafile”这样的语法，忘记了recover datafile,搞的我花费了好多时间。