使用日志恢复误删除的数据文件

昨天和朋友聊天谈到误删除了数据文件，没有冷备份，只有归档日志文件。这个从理论上来说是可行的，下面的试验证明了这一点。

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(*)
———-
1000

SQL> 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(*)
———-
2000

SQL> 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:02

SQL> 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 #1

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select count(*) from droptest;

COUNT(*)
———-
2001

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14284241/viewspace-563056/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14284241/viewspace-563056/