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
至此，已经完全恢复了^_^
