archivelog 下不需要熱備也能完全恢復損壞或者被刪除的datafile(原創)
2024-07-21 02:06:36
供稿：網友
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
至此，已經完全恢復了^_^
