• Find us:
    +1-669-900-5138   |   +44-203-372-5553
  • Free Newsletter

    Get Latest Updates

  • Make Training Enquiry


    Company

  • Categories

  • Archive

  • Recover an accidentally deleted file when the database is still open.

    Posted by "" in "backup, database, Disaster Recovery, Unix" on 2008-10-22

    On Unix/Linux, when a file is deleted, but a process still has the file open, the file is still there in the filesystem, and only the inode is removed.
    But the process can continue to use its file handle, and the file can also be accessible under /proc/<pid>/fd .

    In the following example, we use that behavior to recover a lost datafile after is has been dropped from the os (with rm) but the datafile is still open by the background processes.

    First, we create a tablespace, and populate a table in it.

    SQL> REM we create a tablespace:
    SQL> create tablespace TEST_RM datafile ‘/var/tmp/test_rm.dbf’ size 10M;
    Tablespace created.

    SQL> REM we create a table in it:
    SQL> create table FRANCK tablespace test_rm as select * from dba_objects;
    Table created.

    SQL> REM we check that table data is accessible:
    SQL> select count(*) from FRANCK;
    COUNT(*)
    ———-
    12708

    SQL> exit
    Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

    Then, we drop the datafile from unix prompt.

    here is the datafile
    ls -l /var/tmp/test_rm.dbf
    -rw-r—– 1 oracle dba 10493952 Mar 26 14:25 /var/tmp/test_rm.dbf

    we ‘accidently’ drop the datafile
    rm /var/tmp/test_rm.dbf
    ls -l /var/tmp/test_rm.dbf
    ls: /var/tmp/test_rm.dbf: no such file or directory


    Here the datafile is lost.
    Now we connect again.

    sqlplus / as sysdba

    Connected to:
    Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

    SQL> REM and we check if table data is accessible:
    SQL> select count(*) from FRANCK;

    select * from franck
    *
    ERROR at line 1:
    ORA-01116: error in opening database file 5
    ORA-01110: data file 5: ‘/var/tmp/test_rm.dbf’
    ORA-27041: unable to open file
    Linux Error: 2: No such file or directory
    Additional information: 3

    The datafile is lost and data is not accessible.

    However, the datafile should still have an open file descriptor by an oracle background process

    we check the dbwriter pid:
    ps -edf | grep dbw
    oracle 2661 1 0 Mar25 ? 00:00:06 xe_dbw0_XE
    oracle 7044 7037 0 14:40 pts/1 00:00:00 /bin/bash -c ps -edf | grep dbw
    oracle 7046 7044 0 14:40 pts/1 00:00:00 grep dbw

    and we check its opened file descriptors for our file:
    ls -l /proc/2661/fd | grep test_rm
    lrwx—— 1 oracle dba 64 Mar 26 14:02 66 -> /var/tmp/test_rm.dbf (deleted)

    here it is:
    ls -l /proc/2661/fd/66
    lrwx—— 1 oracle dba 64 Mar 26 14:02 /proc/2661/fd/66 -> /var/tmp/test_rm.dbf (deleted)

    In some other unix, lsof may be needed to map the file descriptor with the deleted file name

    first we set a symbolic link so that oracle can see it as it was before the delete:
    ln -s /proc/2661/fd/66 /var/tmp/test_rm.dbf

    here data is accessible, but that will be lost if dbwriter closes it file handle (i.e if the database is closed)

    However we can now set the tablespace read only so that it is checkpointed, and no writes occurs on it.

    SQL> alter tablespace TEST_RM read only;
    Tablespace altered.

    We can now copy the file safely.

    then we drop the symbolic link:
    rm /var/tmp/test_rm.dbf
    ls -l /var/tmp/test_rm.dbf

    ls: /var/tmp/test_rm.dbf: No such file or directory

    and we can now copy the file
    cp -p /proc/2661/fd/66 /var/tmp/test_rm.dbf
    ls -l /var/tmp/test_rm.dbf

    -rw-r—– 1 oracle dba 10493952 Mar 26 14:54 /var/tmp/test_rm.dbf

    And datafile is now available again.

    SQL> REM we have it back, lets put the tablespace back in read/write
    SQL> alter tablespace test_rm read write;
    Tablespace altered.

    SQL> REM and we check data is still there:
    SQL> select count(*) from FRANCK;

    COUNT(*)
    ———-
    12708

    This is not to be used like that in production. This is unsupported and may behave differently on different unix/linux or oracle versions.

    15 Responses to “Recover an accidentally deleted file when the database is still open.”

    1. adnan says:

      Dear Rupesh,

      i am not understanding your point that you written

      We can now copy the file safely.

      then we drop the symbolic link:
      rm /var/tmp/test_rm.dbf
      ls -l /var/tmp/test_rm.dbf

      ls: /var/tmp/test_rm.dbf: No such file or directory

      and we can now copy the file
      cp -p /proc/2661/fd/66 /var/tmp/test_rm.dbf
      ls -l /var/tmp/test_rm.dbf

      -rw-r—– 1 oracle dba 10493952 Mar 26 14:54 /var/tmp/test_rm.dbf

      first you are saying that “copy the file and then remove the link” but actually you are removing the link first and copying the file later. Whats the operation that we have to perform first. For this you can tell me via email.

      thanks and regards
      adnan ali

    2. Noons says:

      Brilliant piece of applied Unix/Linux file system knowledge.
      Well done!

    3. Rupesh says:

      Thanks for the comment.

      Here my enlightenment confused you. What I said is after making tablespace readonly, we can safely copy the file.
      SQL> alter tablespace TEST_RM read only;
      Tablespace altered.

      We can now copy the file safely.

      That is a generic scentence, now bellow are the steps before coying the file.

      we drop the symbolic link:
      rm /var/tmp/test_rm.dbf
      ls -l /var/tmp/test_rm.dbf

      ls: /var/tmp/test_rm.dbf: No such file or directory

      and we can now copy the file
      cp -p /proc/2661/fd/66 /var/tmp/test_rm.dbf
      ls -l /var/tmp/test_rm.dbf

      -rw-r—– 1 oracle dba 10493952 Mar 26 14:54 /var/tmp/test_rm.dbf

      And datafile is now available again.

      Hope you got it .. ?

    4. Adnan Ali says:

      Dear Rupesh

      Thanks. Now i have got it. Really Nice work.

      regards
      adnan ali

    5. javeedkaleem says:

      this is really a good practice

      but is there any way if db crashed while retreiving the datafile

      When d/b is running in noarchive log mode
      and no backup available.

    6. cvaseen says:

      Will the scenarios works if we delete the redo log file accidently or a control file

    7. Rupesh says:

      Yes, but don’t you see Datafile is more important than the other files at this scenario ?

    8. cvaseem says:

      Rupesh iam asking if the scenario works if a control file or a redo log file is deleted accidentally …

      Thanks
      Vaseem.

    9. Rupesh says:

      Hi Vaseem,
      Here we are using one of the unix feature to recover deleted file, so this is applicable to control or redo file, if the process can continue to use its file handle, and the file can also be accessible under /proc//fd. But I haven’t tried it.

      -Rupesh

    10. cvaseem says:

      Really good workaround if a control file is accidentally deleted it happens when we do a refresh —

      Rupesh Good Job…

    11. seshu says:

      Nice article rupesh.

      I tested same thing with system tablespace, but you cannot put system tablespace in read only mode.

      So I have copied system datafile directly from /proc//fd/ to the actual location, without symbolic link work.

      It seems everything fine. what will be the impact, if we do same thing to any tablespace.

    12. TAI says:

      HI Rupesh

      Gud peice of work amazing skills applied.

      Regards
      TAI

    13. Rupesh says:

      Thanks TAI.

      -Rupesh

    14. Preetam says:

      Fanstic job mate.

    15. Cmobile says:

      I’m unable to map the file descriptor with the deleted file name
      ls -l /proc/_PID_OF_DBWRITER/fd | grep deleted
      is there any other way to get the PID of the delete datafile ?

      I did get the PID for dbw
      ps -edf | grep dbw
      then after I’m getting the PID for the deleted file ;there is no result

    Leave a Reply



  • K21 Technologies is among the most experienced Oracle Gold Partner for Identity Access Management service providers. We work with application development companies and in-house technology division to help achieve significant returns on their IT security investment. Our clientele includes some of the globally renowned corporate, which speaks of our expertise in our field.

    We have the most talented and experienced team that can swiftly deploy security solutions even in complex IT ecosystem. Our clients highly appreciate our timely implementation, interactive training, on-demand support and community resources.
  • CONTACTS

    K21 Technologies
    8 Magnolia Place, Harrow,
    London, HA2 6DS

    UK: +44(0)7476444481
    USA: +1-888-414-1821

  • 2014, K21 Technologies. All rights reserved DMCA.com
  • TOP
    TOP