archivelog 下不需要热备也能完全恢复损坏或者被删除的datafile(原创)

prerequisite:被删除的表空间创建时开始，数据库一直在archivelog模式下

恢复具体操作如下：

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination F:\oracle\ora92\RDBMS

Oldest online log sequence 23

Next log sequence to archive 25

Current log sequence 25

SQL> alter tablespace backuptest offline;

alter tablespace backuptest offline

*

ERROR at line 1:

ORA-01109: database not open

SQL> alter database datafile 'f:\backup.dbf' offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter tablespace backuptest offline immediate;

Tablespace altered.

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

---------- ------- ------- ----------------------------------------------------------------- ---------- ---------

11 OFFLINE OFFLINE FILE NOT FOUND 0

SQL> alter database create datafile 'f:\backup.dbf' as 'f:\backup.dbf';

Database altered.

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

---------- ------- ------- ----------------------------------------------------------------- ---------- ---------

11 OFFLINE OFFLINE 3074706 03-NOV-04

SQL> recover tablespace backuptest;

Media recovery complete.

SQL> alter tablespace backuptest online;

Tablespace altered.

SQL> select * from backup;

ID

----------

1

2

至此，已经完全恢复了^_^