Oracle数据文件物理删除后的恢复

做系统管理的都是这样，难免会误删文件，某天要是把某个Oracle数据文件删除，那该如何恢复呢？（这里数据库是OPEN的，并且未关闭）

建立测试表空间
创建测试用户
插入测试数据
删除数据文件
恢复数据库文件

建立测试表空间SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/member/system01.dbf
/opt/oracle/oradata/member/sysaux01.dbf
/opt/oracle/oradata/member/undotbs01.dbf
/opt/oracle/oradata/member/users01.dbf
SQL> create tablespace test datafile "/opt/oracle/oradata/member/test01.dbf" size 10m;
Tablespace created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/member/system01.dbf
/opt/oracle/oradata/member/sysaux01.dbf
/opt/oracle/oradata/member/undotbs01.dbf
/opt/oracle/oradata/member/users01.dbf
/opt/oracle/oradata/member/test01.dbf2.创建测试账户
SQL> create user test identified by test default tablespace test;
SQL> grant connect,resource to test;3.插入测试数据
SQL> conn test/test
SQL> create table t1（id int）;
SQL> insert into t1 values（1）;
SQL> select * from t1;

ID
----------
14.删除数据文件
[oracle@db2 ~]$ rm -f /opt/oracle/oradata/member/test01.dbf
[oracle@db2 ~]$ sqlplus test/test
SQL> create table t2 as select * from t1;
create table t2 as select * from t1
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: "/opt/oracle/oradata/member/test01.dbf"
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T1 TEST数据文件被删除了，这怎么办呢？这个时候千万别重启数据库，否则数据就丢失了5.数据文件恢复[oracle@db2 ~]$ ps -ef |grep dbw0
oracle 3309 1 0 12:07 ？ 00:00:00 ora_dbw0_member
oracle 6217 5105 0 15:29 pts/0 00:00:00 grep dbw0
#找到ora_dbw0_SID的进程号3309
[oracle@db2 ~]$ cd /proc/3309/fd
#以上3309就是进程号，然后执行ls -al查看文件的链接可以看到文件27就是被删除的文件
[oracle@db2 fd]$ cp 27 /opt/oracle/oradata/member/test01.dbf查看test表空间状态
SQL> select name,status from v$datafile;
NAME STATUS
---------------------------------------- -------
/opt/oracle/oradata/member/system01.dbf SYSTEM
/opt/oracle/oradata/member/sysaux01.dbf ONLINE
/opt/oracle/oradata/member/undotbs01.dbf ONLINE
/opt/oracle/oradata/member/users01.dbf ONLINE
/opt/oracle/oradata/member/test01.dbf ONLINE下线test01表文件
SQL> alter database datafile "/opt/oracle/oradata/member/test01.dbf" offline;
SQL> recover datafile "/opt/oracle/oradata/member/test01.dbf";
Media recovery complete.
SQL> alter database datafile "/opt/oracle/oradata/member/test01.dbf" online;
Database altered.#以上就成功恢复了，若是出现
SQL> recover datafile "/opt/oracle/oradata/member/test01.dbf";
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: "/opt/oracle/oradata/member/test01.dbf"
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: "/opt/oracle/oradata/member/test01.dbf"则有可能是/opt/oracle/oradata/member/test01.dbf文件的权限问题引起，在root用户下
[root@db2 ~]# chown -R oracle.oinstall /opt/oracle/oradata/member/test01.dbf
再recover datafile "/opt/oracle/oradata/member/test01.dbf"实验完成后，删除测试用户及测试表空间
SQL> drop user test cascade;
SQL> drop tablespace test INCLUDING CONTENTS AND DATAFILES;更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址