oracle11g data file deleted by mistake (no backup)

OS:

Oracle Linux Server Release 5.7

Db:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

First create the Yoon database

sql> Create Tablespace Yoon datafile '/u01/oracle/oradata/yoon/yoon01.dbf ' size 50m;

Tablespace created.

Sql> create user Yoon identified by Yoon Default Tablespace yoon;

User created.

Sql> Grant DBA to Yoon;

Grant succeeded.

Sql> CREATE TABLE Yoon.yoon as SELECT * from Scott.emp;

Table created.

Accidental deletion of data files in operation YOON01.DBF

RM-RF yoon01.dbf

Select Ts#,file#,name,bytes,status from V$datafile;

Ps-ef | grep dBW | Grep-v grep

Cd/proc/..../fd

First, online recovery:

[Email protected] yoon]$ RM-RF yoon01.dbf

[Email protected] yoon]$ ps-ef |grep DBW |grep-v grep
Oracle 17417 1 0 00:51? 00:00:00 Ora_dbw0_yoon

[Email protected] yoon]$ CD/PROC/17417/FD

[email protected] fd]$ CP 267/u01/oracle/oradata/yoon/newyoon01.dbf

Sql> ALTER DATABASE datafile 6 offline;

Database altered.

sql> ALTER DATABASE rename file '/u01/oracle/oradata/yoon/yoon01.dbf ' to '/u01/oracle/oradata/yoon/newyoon01.dbf ';

Database altered.

sql> recover datafile 6;
Media recovery complete.

Sql> ALTER DATABASE datafile 6 online;

Database altered.

Second, offline recovery

RM-RF yoon01.dbf

Ps-ef | grep dBW | Grep-v grep

Cp/proc/.../fd/.../u01/oracle/oradata/yoon/yoon01.dbf

Shutdown immediate

Startup

Recover database;

ALTER DATABASE open;

[Email protected] yoon]$ RM-RF yoon01.dbf

[Email protected] yoon]$ Ps-ef | grep dBW | Grep-v grep
Oracle 18743 1 0 01:03? 00:00:00 Ora_dbw0_yoon

[Email protected] yoon]$ CD/PROC/18743/FD

[email protected] fd]$ CP 265/u01/oracle/oradata/yoon/yoon01.dbf

sql> shutdown Immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Sql> Startup
ORACLE instance started.

Total System Global area 3340451840 bytes
Fixed Size 2232960 bytes
Variable Size 2432699776 bytes
Database buffers 889192448 bytes
Redo buffers 16326656 bytes
Database mounted.
Ora-01157:cannot identify/lock data file 6-see DBWR trace file
Ora-01110:data file 6: '/U01/ORACLE/ORADATA/YOON/YOON01.DBF '

sql> Recover database;
Media recovery complete.

sql> ALTER DATABASE open;

Database altered.

Sql> select * from Yoon.yoon;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------ ---------- ------ ---- ----------
7369 SMITH Clerk 7902 17-dec-80 800 20
7499 ALLEN salesman 7698 20-feb-81 1600 300 30
7521 WARD salesman 7698 22-feb-81 1250 500 30
7566 JONES MANAGER 7839 02-apr-81 2975 20
7654 MARTIN salesman 7698 28-sep-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-may-81 2850 30
7782 CLARK MANAGER 7839 09-jun-81 2450 10
7788 SCOTT ANALYST 7566 19-apr-87 3000 20
7839 KING President 17-nov-81 5000 10
7844 TURNER salesman 7698 08-sep-81 1500 0 30
7876 ADAMS Clerk 7788 23-may-87 1100 20
7900 JAMES Clerk 7698 03-dec-81 950 30
7902 FORD ANALYST 7566 03-dec-81 3000 20
7934 MILLER Clerk 7782 23-jan-82 1300 10

Rows selected.

oracle11g data file deleted by mistake (no backup)