Recovery ____oracle for Oracle data files being deleted

How do you recover a data file in Oraclek after it has been mistakenly removed from the OS system? Do not panic at this time restart the database or the operating system, you can retrieve data files by DBWN process-related handles.

Test:

Step 1: Log in to the database as a DBA and view the path to the table space State and data file

[Oracle@localhost ~]$ Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.4.0 Production on Sun Sep 18 18:53:44 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition release 11.2.0.4.0-64bit Production
With the partitioning, OLAP, Data Mining and real application testing options

Sql> select * from V$tablespace;
ts# NAME INC Big FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM Yes NO Yes
1 Sysaux Yes NO Yes
2 UNDOTBS1 Yes NO Yes
4 USERS Yes NO Yes
3 TEMP No no YES
6 Wutong Yes NO Yes
6 rows selected.

Sql> select Tablespace_name,status from Dba_tablespaces;
Tablespace_name STATUS
------------------------------ ---------
SYSTEM ONLINE
Sysaux ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
Wutong ONLINE
6 rows selected.

Sql> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
/u03/oracle/oradata/wutong/datafile/o1_mf_system_cx3xt90z_.dbf
/u03/oracle/oradata/wutong/datafile/o1_mf_sysaux_cx3xt930_.dbf
/u03/oracle/oradata/wutong/datafile/o1_mf_undotbs1_cx3xt93b_.dbf
/u03/oracle/oradata/wutong/datafile/o1_mf_users_cx3xt940_.dbf
/u03/oracle/oradata/wutong/datafile/o1_mf_wutong_cx415lcj_.dbf

Step 2: Simulate deleting data files, prompting for failure when creating tables
Sql>!rm/u03/oracle/oradata/wutong/datafile/o1_mf_wutong_cx415lcj_.dbf

Sql>!ls-l/u03/oracle/oradata/wutong/datafile/o1_mf_wutong_cx415lcj_.dbf
Ls:cannot access/u03/oracle/oradata/wutong/datafile/o1_mf_wutong_cx415lcj_.dbf:no such file or directory


Sql> CREATE TABLE Wutong.test as SELECT * from Scott.emp;
CREATE TABLE Wutong.test as SELECT * from Scott.emp
*
ERROR at line 1:
Ora-01116:error in opening database file 5
Ora-01110:data File 5:
'/U03/ORACLE/ORADATA/WUTONG/DATAFILE/O1_MF_WUTONG_CX415LCJ_.DBF '
Ora-27041:unable to open File
linux-x86_64 error:2: No such file or directory
Additional Information:3

Step 3: View the DBW process under the OS system, locate the process SPID, and locate the deleted data file under the/PROC/27722/FD path
Sql> exit
Disconnected from Oracle Database 11g Enterprise Edition release 11.2.0.4.0-64bit Production
With the partitioning, OLAP, Data Mining and real application testing options
[Oracle@localhost ~]$ ps-ef|grep dbw|grep-v grep
Oracle 27722 1 0 Sep14? 00:02:29 Ora_dbw0_wutong
[Oracle@localhost ~]$ LL/PROC/27722/FD
Total 0
Lr-x------. 1 Oracle oinstall 18:59 0->/dev/null
L-WX------. 1 Oracle Oinstall 18:59 1->/dev/null
LRWX------. 1 Oracle oinstall 18:59 SEP->/u01/app/oracle/product/11.2.0.4/dbs/lkwutong
Lr-x------. 1 Oracle oinstall 18:59 SEP->/U01/APP/ORACLE/PRODUCT/11.2.0.4/RDBMS/MESG/ORAUS.MSB
L-WX------. 1 Oracle Oinstall 18:59 2->/dev/null
LRWX------. 1 Oracle Oinstall (SEP) 18:59 256->/u03/oracle/oradata/wutong/controlfile/o1_mf_cx3xw15p_.ctl
LRWX------. 1 Oracle Oinstall (SEP) 18:59 257->/u01/app/oracle/fast_recovery_area/wutong/controlfile/o1_mf_cx3xw181_.ctl
LRWX------. 1 Oracle Oinstall (SEP) 18:59 258->/u03/oracle/oradata/wutong/datafile/o1_mf_system_cx3xt90z_.dbf
LRWX------. 1 Oracle Oinstall (SEP) 18:59 259->/u03/oracle/oradata/wutong/datafile/o1_mf_sysaux_cx3xt930_.dbf
LRWX------. 1 Oracle Oinstall (SEP) 18:59->/u03/oracle/oradata/wutong/datafile/o1_mf_undotbs1_cx3xt93b_.dbf
LRWX------. 1 Oracle Oinstall (SEP) 18:59 261->/u03/oracle/oradata/wutong/datafile/o1_mf_users_cx3xt940_.dbf
LRWX------. 1 Oracle oinstall SEP 18:59 262->/u03/oracle/oradata/wutong/datafile/o1_mf_wutong_cx415lcj_.dbf (deleted)
LRWX------. 1 Oracle Oinstall (SEP) 18:59 263->/u03/oracle/oradata/wutong/datafile/o1_mf_temp_cx3xw7ty_.tmp
Lr-x------. 1 Oracle Oinstall 18:59 3->/dev/null
Lr-x------. 1 Oracle Oinstall 18:59 4->/dev/null
Lr-x------. 1 Oracle Oinstall 18:59 5->/dev/null
Lr-x------. 1 Oracle Oinstall 18:59 6->/U01/APP/ORACLE/PRODUCT/11.2.0.4/RDBMS/MESG/ORAUS.MSB
Lr-x------. 1 Oracle Oinstall 18:59 7->/proc/27722/fd
Lr-x------. 1 Oracle Oinstall 18:59 8->/dev/zero
LRWX------. 1 Oracle Oinstall 18:59 9->/u01/app/oracle/product/11.2.0.4/dbs/hc_wutong.dat

Step 4: Remove the deleted data file CP to the original data file path:
[Oracle@localhost ~]$ cp/proc/27722/fd/262/u03/oracle/oradata/wutong/datafile/o1_mf_wutong_cx415lcj_.dbf
[Oracle@localhost ~]$ ll/u03/oracle/oradata/wutong/datafile/o1_mf_wutong_cx415lcj_.dbf
-rw-r-----. 1 Oracle oinstall 3774881792 SEP 19:02/u03/oracle/oradata/wutong/datafile/o1_mf_wutong_cx415lcj_.dbf

Step 5: Offline the data file and then online after the media is restored

[Oracle@localhost ~]$ Sqlplus/as SYSDBA
Sql*plus:release 11.2.0.4.0 Production on Sun Sep 18 19:03:33 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition release 11.2.0.4.0-64bit Production
With the partitioning, OLAP, Data Mining and real application testing options

sql> ALTER DATABASE datafile 5 offline;
Database altered.
sql> recover datafile 5;
Media recovery complete.
sql> ALTER DATABASE datafile 5 online;
Database altered.
Sql> CREATE TABLE Wutong.test as SELECT * from Scott.emp;
Table created.