Recover an accidentally deleted file when the database is Up & Running

This post describe you how to recover datafile which is deleted at OS level by accidentally using “rm- -rf” command while database is Up & Running

1. I have 12c (12.1.0.2 relase ) database, first lets login to database and then source to required PDB to create tablespace, user and table to hold some data.

1a. Lets Logged into PDB called PSRS

1b. lets create tablespace called test_tbs under /home/oracle/ file name test_tbs01.dbf.

1c. Let’s create a user called test_user

1d. granted required privileges to connect DB

1e. Lets create table and insert some data for testing purpose.

So now we have a schema test_user with a simple table that was created in the test_tbs datafile (this was default tablespace of test_user).

2. Now we are going to remove this test_tbs01.dbf file at OS level from /home/oracle directory.

2a. check file exist or not at os level with ls -ltr test*
2b. then remove file using rm -rf test_tbs01.dbf
2c. and then check again with ls -ltr test* (you will get no such file or directory)

Since table already cached into buffer cache, you can still retrieve data from cache, but as soon as you are forced to attempt a read from disk you will get error.

2d.You can see below, still get result after deleting related datafile.

2e. lets flush buffer cache alter system flush buffer_cache

2f. now connect user and try to retrieve data

2g. check dba_data_files as well

3. Data file recovery process, first find out PID for the Database writer, so that we can open File Descriptor for the file we just removed

3a. To find out PID, “ps -ef|grep dbw” so we got PID as 8432

3b. Now try to do lsof on this PID to confirm this is right PID, “/usr/sbin/lsof -p 8432” you can see on the last line /home/oracle/test_tbs01.dbf and we see its marked as deleted and we can also see that it has a file descriptor 281.

3c. you can also see this information from this directory “/proc/8432/fd/ (8432 where we have used for PID), again we can see this file marked as deleted and its FD 281.

3d. got to directory “/proc/8432/fd/ and ran a CAT command on this FD number and sent it to another filename or actual data file name in our case “test_tbs01.dbf”.