Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

September 4, 2007

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

Filed under: Oracle How To — H.Tonguç Yılmaz @ 11:42 am

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

2 Comments »

  1. […] Linux way of Flashback; how to restore back a dropped datafile » This Summary is from an article posted at H.Tonguç YILMAZ Oracle Blog on Tuesday, September 04, […]

    Pingback by University Update - Linux - Linux way of Flashback; how to restore back a dropped datafile — September 4, 2007 @ 12:53 pm | Reply

  2. That’s odd…this looks very much like something I’ve covered in a blog a while ago: http://frits.homelinux.com/wordpress/?p=20

    Comment by fritshoogland — September 5, 2007 @ 1:22 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: