误删除数据文件的物理文件，如何恢复（归档开启）

在操作系统层面将物理文件误删除，此时全备中并不包括该物理文件，能否进行恢复，答案是可以的！

实验：

删除test表空间

SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
创建实验表空间
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 10M;
Tablespace created.
SQL> select file_name,file_id from dba_data_files;
FILE_NAME                                                                 FILE_ID
---------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/orcl/example01.dbf                                     5
/u01/app/oracle/oradata/orcl/users01.dbf                                       4
/u01/app/oracle/oradata/orcl/sysaux01.dbf                                      3
/u01/app/oracle/oradata/orcl/undotbs01.dbf                                     2
/u01/app/oracle/oradata/orcl/system01.dbf                                      1
/u01/app/oracle/oradata/orcl/test01.dbf                                        6
查询file#为6的数据文件对应的创建SCN
SQL> col CREATION_CHANGE# for 99999999999999999
SQL> select creation_change#,name from v$datafile where file#=6;


  CREATION_CHANGE# NAME
------------------ ------------------------------------------------------------
     1103446957315 /u01/app/oracle/oradata/orcl/test01.dbf
SQL> alter system switch logfile;


System altered.
检查datafile 6创建过程记录在了哪一个日志中，且确保以后的日志全部存在且可用
SQL> select name from v$archived_log where 1103446957315 between first_change# and next_change#;
NAME
-----------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/arch/1_5_859096334.arch
在数据文件上创建数据，主要目的是测试
SQL> create table scott.test01 tablespace test as select * from dba_objects;
Table created.
SQL> select count(*) from scott.test01;
  COUNT(*)
----------
     50613
删除物理文件
SQL> !rm /u01/app/oracle/oradata/orcl/test01.dbf  
再次查询，发现还可以查询到，是缓存的影响，清掉缓存
SQL> select count(*) from scott.test01;
  COUNT(*)
----------
     50613
SQL>  alter system flush buffer_cache;
System altered.
再次查询，报错
SQL> select count(*) from scott.test01;
select count(*) from scott.test01
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/test01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
重建datafile 6
SQL> alter database create datafile 6;
alter database create datafile 6
*
ERROR at line 1:
ORA-01182: cannot create database file 6 - file is in use or recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/test01.dbf'
报:文件仍在被使用
需要先offline
SQL> alter database datafile 6 offline;
Database altered.
SQL> alter database create datafile 6;
Database altered.
SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery if it was restored from backup, or END
BACKUP if it was not
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/test01.dbf'
需要recovery
SQL> recover datafile 6
ORA-00279: change 1103446957315 generated at 10/28/2014 15:18:14 needed for
thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_5_859096334.arch
ORA-00280: change 1103446957315 for thread 1 is in sequence #5




Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1103446957420 generated at 10/28/2014 15:21:54 needed for
thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_6_859096334.arch
ORA-00280: change 1103446957420 for thread 1 is in sequence #6
ORA-00278: log file '/u01/app/oracle/arch/1_5_859096334.arch' no longer needed
for this recovery


ORA-00279: change 1103446957494 generated at 10/28/2014 15:23:28 needed for
thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_7_859096334.arch
ORA-00280: change 1103446957494 for thread 1 is in sequence #7
ORA-00278: log file '/u01/app/oracle/arch/1_6_859096334.arch' no longer needed
for this recovery


ORA-00279: change 1103446957500 generated at 10/28/2014 15:23:40 needed for
thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_8_859096334.arch
ORA-00280: change 1103446957500 for thread 1 is in sequence #8
ORA-00278: log file '/u01/app/oracle/arch/1_7_859096334.arch' no longer needed
for this recovery


Log applied.
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.
SQL> select count(*) from scott.test01;
  COUNT(*)
----------
     50613
恢复结束。