昨天和朋友聊天谈到误删除了数据文件,没有冷备份,只有归档日志文件。这个从理论上来说是可行的,下面的试验证明了这一点。
SQL> create tablespace droptest
2 datafile ‘/u01/app/oradata/randy10g/droptest.dbf’ size 5m;Tablespace created.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> alter system switch logfile;System altered.
SQL> create table droptest (id number) tablespace droptest;
Table created.
SQL> begin
2 for i in 1..1000 loop
3 insert into droptest values(i);
4 end loop;
5 end;
6 /PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select count(*) from droptest;
COUNT(*)
———-
1000SQL> alter system switch logfile;
System altered.
SQL> begin
2 for i in 2001..3000 loop
3 insert into droptest values(i);
4 end loop;
5 end;
6 /PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select count(*) from droptest;
COUNT(*)
———-
2000SQL> alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
Session altered.
SQL> select sysdate from dual;
SYSDATE
——————-
2009-03-05 22:42:02SQL> alter system switch logfile;
System altered.
SQL> !
[oracle@rhserver randy10g]$ mv droptest.dbf droptest.dbf.bak
[oracle@rhserver randy10g]$ pwd
/u01/app/oradata/randy10g
[oracle@rhserver randy10g]$ exit
exit
SQL> insert into droptest values(1001);
1 row created.
SQL> commit;
Commit complete.
SQL> startup force
ORACLE instance started.Total System Global Area 205520896 bytes
Fixed Size 1218532 bytes
Variable Size 62916636 bytes
Database Buffers 138412032 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: ‘/u01/app/oradata/randy10g/droptest.dbf’SQL> alter database create datafile 8 as ‘/u01/app/oradata/randy10g/droptest_tmp.dbf’;
Database altered.
SQL> recover datafile 8;
ORA-00279: change 2501675 generated at 03/05/2009 22:38:18 needed for thread 1
ORA-00289: suggestion : /u01/app/flash_recovery_area/RANDY10G/archivelog/2009_03_05/o1_mf_1_1_%u_.arc
ORA-00280: change 2501675 for thread 1 is in sequence #1Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database open;Database altered.
SQL> select count(*) from droptest;
COUNT(*)
———-
2001