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”.


          3e. now we can see that have original data file back in same location.


    4. Now Verify data base and check data will we able to retrieve or not.

                 4a. Checking dba_data_files.
             

               4b. Connecting test_user and querying for data.
             
           

#########That’s the end###########


No comments:

Post a Comment