Linux way of Flashback; how to restore back a dropped datafile

Using the PROC file system available on Unix and Linux, we can retrieve accidentally dropped datafiles. Below is a demonstration of this method on OEL4 and 10gR2 –

SQL> create tablespace my_test datafile '/tmp/my_test_01.dbf' size 200k; 

SQL> alter system check datafiles ;

SQL> alter system checkpoint global ;

SQL> host rm -rf /tmp/my_test_01.dbf

-- since it is removed can not resize
SQL> alter database datafile '/tmp/my_test_01.dbf' resize 250k; 

alter database datafile '/tmp/my_test_01.dbf' resize 250k
ERROR at line 1:
ORA-01565: error in identifying file '/tmp/my_test_01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

-- first take the process id of the dbwr(could be pmon, smon etc.)
SQL> host ps -ef |grep tcell|grep dbw|grep -v grep
oracle    5033     1  0 09:54 ?        00:00:00 ora_dbw0_tcell

-- find open files for this process id
SQL> host lsof -p 5033 |grep /tmp/my_test_01.dbf
oracle  5033 oracle   28uW  REG  253,0    212992  180316 /tmp/my_test_01.dbf (deleted)

-- go to the file descriptors directory and make a copy of file using file descriptor(28uW for this example)
SQL> host cat /proc/5033/fd/28 > /tmp/my_test_01.dbf
SQL> host ls -alt /tmp/my_test_01.dbf
-rw-r--r--  1 oracle oinstall 212992 Sep  4 14:25 /tmp/my_test_01.dbf

SQL> alter database datafile '/tmp/my_test_01.dbf' resize 250k;

This method can also be used for retrieving the deleted current redo logfile, but the limitations are –
a. Database is not restarted,
b. Server is not restarted,
c. The file was not offline before deletion.

Refences Used :
Note:444749.1 – Retrieve deleted files on Unix / Linux using File Descriptors

Previously Released Oracle Support Case Studies

Intense and Random Buffer Busy Wait Performance
Performance / Beginner / December, 2005

Configuring the Kerberos Adapter in a Windows Environment
Networking / Beginner / July, 2006

The Case of the Stubborn Cost Based Optimizer
Performance / Intermediate / December, 2005

Diagnosing Another Buffer Busy Waits Issue
Performance / Intermediate / March, 2006

Using Real-Time Diagnostic Tools to Diagnose Intermittent Database Hangs
Performance / Intermediate / July, 2006

Resolving High CPU Usage on Oracle Servers
Performance / Intermediate / May, 2006

Analyzing 10053 Trace Files
Performance / Expert / December, 2005

The Mysterious Performance Drop
Performance / Expert / May, 2006

Judicious Use of Histograms for Oracle Applications Tuning
Tuning and Performance / Expert / March, 2006

Slow Client Connection in a RAC/Sun Cluster
Real Application Clusters / Expert / December, 2005

Diagnosing Unsuccessful CRS Issues
Real Application Clusters / Expert / March, 2006

Setting up External Stream Generation in Lease Management
Lease Management / Expert / March, 2006

OCFS Drives Disappearing and/or Becoming Inaccessible
Clustered File System / Expert / December, 2005

Refences Used : Metalink Case Study Index