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

来源：互联网发布：淘宝钻展要多少钱编辑：程序博客网时间：2024/05/16 15:31

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

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