Business Insights Technical Track Pythian Life

How to Recover Deleted Oracle Datafiles with No Downtime

by
November 7, 2007
Posted in: Technical Track
Tags: , ,

So you have accidentally removed a datafile from your production database? First thing, DON’T PANIC! There’s an easy way to recover deleted datafiles, for as long as your database remains up. The procedure below works on linux, however this method conceivably can work for other platforms.

This procedure will even work if your database is in NOARCHIVELOG mode.

You may have reached this posting through Google, and in a rush to get the solution right away, so here it is.

The recovery is done in two phases.

Phase 1: instant recovery to prevent Oracle errors (like ORA-27041 “unable to open file”, ORA-01110, ORA-01116)

  1. Find the PID of DBWRITER for the right database. 
    ps -ef | grep dbw0_SID_OF_YOUR_DB
oracle   12272     1  0 10:55 ?        00:00:00 ora_dbw0_test
oracle   12941 11501  0 12:36 pts/0    00:00:00 grep dbw0_test
  2. List the deleted file handles for that DBWRITER process. 
    ls -l /proc/_PID_OF_DBWRITER/fd | grep deleted

lrwx------  1 oracle oinstall 64 Oct 15 11:24 10 -> /home/oracle/product/10.2.0.2/dbs/lkinsttest (deleted)
lrwx------  1 oracle oinstall 64 Oct 15 11:24 23 -> /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf (deleted)
  3. Create a symbolic link to your datafile with the original name. 
    ln -s /proc/PID_OF_DBWRITER/fd/23 /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf

    That’s all. Now you are no longer going to get errors. However, if your database goes down now, you will lose that datafile for good.

Phase 2: restore the file

ARCHIVELOG database

  1. (Optional.) Issue a checkpoint. This is to reduce the recovery time when bringing the file online, depending on activity for that datafile. Unfortunately, you can’t checkpoint a single file, so the checkpoint may take some time. 
    alter system checkpoint;
  2. Backup the datafile with rman. Why rman? It’s much easier then you think. Total downtime is about one second for inactive datafiles, and more for active ones (with writes). 
    rman target /
report schema;
backup as copy datafile YOUR_DATAFILE_NUMBER format '/location_of_your_database/new_name_for_File.dbf';
sql 'alter database datafile YOUR_DATAFILE_NUMBER offline';
switch datafile YOUR_DATAFILE_NUMBER to copy;
recover datafile YOUR_DATAFILE_NUMBER;
sql 'alter database datafile YOUR_DATAFILE_NUMBER online';
exit;

NOARCHIVELOG database

  1. Make the tablespace with that datafile read only 
    select distinct tablespace_name from dba_data_files where file_name = 'YOUR_DELETED_FILE';
alter tablespace THE_TABLESPACE read only;
  2. Copy the file from the symlink to a new name 
    cp SIM_LINK_DATA_FILE NEW_NAME_FOR_DATAFILE.dbf
  3. WARNING: Ensure your copy is complete! Then, crash the database. 
    /*WAIT FOR COPY!!!*/
shutdown abort;
  4. Remove the now invalid symlink, and rename the datafile to its original name. Be careful not to remove the wrong file now — that would be a disaster: 
    rm -i SIM_LINK_DATA_FILE
mv NEW_NAME_FOR_DATAFILE.dbf SIM_LINK_DATA_FILE
  5. Startup your database normally and make the tablespace read/write. 
    startup
alter tablespace THE_TABLESPACE read write;

I hope this helps you to get out of a nasty situation.

Here’s both cases fully captured from terminal. Note, I am using Oracle-managed files. This doesn’t change the steps.

/ra5a/orabkp/test/TEST/datafile> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:31:55 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> 
SQL> select tablespace_name, file_name from dba_data_files

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------------------------------------
USERS                          /ra5a/orabkp/test/TEST/datafile/o1_mf_users_3k6xgwhb_.dbf
SYSAUX                         /ra5a/orabkp/test/TEST/datafile/o1_mf_sysaux_3k6xgwdf_.dbf
UNDOTBS1                       /ra5a/orabkp/test/TEST/datafile/o1_mf_undotbs1_3k6xgwg9_.dbf
SYSTEM                         /ra5a/orabkp/test/TEST/datafile/o1_mf_system_3k6xgwd4_.dbf
EXAMPLE                        /ra5a/orabkp/test/TEST/datafile/o1_mf_example_3k6xjdjw_.dbf
LOST                           /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf

6 rows selected.

SQL> select tablespace_name, table_name from dba_tables where owner = 'TESTING';

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
LOST                           LOST_TABLE

SQL> connect testing/testing
SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070

SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

/ra5a/orabkp/test/TEST/datafile> ls -lF
total 1015132
-rw-r-----  1 oracle oinstall 157294592 Oct 15 12:22 o1_mf_example_3k6xjdjw_.dbf
-rw-r-----  1 oracle oinstall 104865792 Oct 15 12:22 o1_mf_lost_3k74mq08_.dbf
-rw-r-----  1 oracle oinstall 241180672 Oct 15 12:32 o1_mf_sysaux_3k6xgwdf_.dbf
-rw-r-----  1 oracle oinstall 503324672 Oct 15 12:32 o1_mf_system_3k6xgwd4_.dbf
-rw-r-----  1 oracle oinstall  20979712 Oct 15 10:17 o1_mf_temp_3k6xj9xn_.tmp
-rw-r-----  1 oracle oinstall  26222592 Oct 15 12:32 o1_mf_undotbs1_3k6xgwg9_.dbf
-rw-r-----  1 oracle oinstall   5251072 Oct 15 12:22 o1_mf_users_3k6xgwhb_.dbf

/ra5a/orabkp/test/TEST/datafile> rm o1_mf_lost_3k74mq08_.dbf
/ra5a/orabkp/test/TEST/datafile> sqlplus testing/testing

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:35:24 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from lost_table;
select count(*) from lost_table
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6:
'/ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> 
SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

/ra5a/orabkp/test/TEST/datafile> ps -ef|grep dbw0_test
oracle   12272     1  0 10:55 ?        00:00:00 ora_dbw0_test
oracle   12941 11501  0 12:36 pts/0    00:00:00 grep dbw0_test
/ra5a/orabkp/test/TEST/datafile> ls -l /proc/12272/fd|grep deleted
lrwx------  1 oracle oinstall 64 Oct 15 11:24 10 -> /home/oracle/product/10.2.0.2/dbs/lkinsttest (deleted)
lrwx------  1 oracle oinstall 64 Oct 15 12:17 26 -> /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf (deleted)

/ra5a/orabkp/test/TEST/datafile> ln -s /proc/12272/fd/26 /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf

/ra5a/orabkp/test/TEST/datafile> ls -lF
total 912620
-rw-r-----  1 oracle oinstall 157294592 Oct 15 12:22 o1_mf_example_3k6xjdjw_.dbf
lrwxrwxrwx  1 oracle oinstall        17 Oct 15 12:37 o1_mf_lost_3k74mq08_.dbf -> /proc/12272/fd/26
-rw-r-----  1 oracle oinstall 241180672 Oct 15 12:32 o1_mf_sysaux_3k6xgwdf_.dbf
-rw-r-----  1 oracle oinstall 503324672 Oct 15 12:32 o1_mf_system_3k6xgwd4_.dbf
-rw-r-----  1 oracle oinstall  20979712 Oct 15 10:17 o1_mf_temp_3k6xj9xn_.tmp
-rw-r-----  1 oracle oinstall  26222592 Oct 15 12:32 o1_mf_undotbs1_3k6xgwg9_.dbf
-rw-r-----  1 oracle oinstall   5251072 Oct 15 12:22 o1_mf_users_3k6xgwhb_.dbf

/ra5a/orabkp/test/TEST/datafile> sqlplus testing/testing

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:38:18 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070

SQL> 
SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

/ra5a/orabkp/test/TEST/datafile> rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Mon Oct 15 12:39:48 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: TEST (DBID=1934173752)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    480      SYSTEM               ***     /ra5a/orabkp/test/TEST/datafile/o1_mf_system_3k6xgwd4_.dbf
2    25       UNDOTBS1             ***     /ra5a/orabkp/test/TEST/datafile/o1_mf_undotbs1_3k6xgwg9_.dbf
3    230      SYSAUX               ***     /ra5a/orabkp/test/TEST/datafile/o1_mf_sysaux_3k6xgwdf_.dbf
4    5        USERS                ***     /ra5a/orabkp/test/TEST/datafile/o1_mf_users_3k6xgwhb_.dbf
5    150      EXAMPLE              ***     /ra5a/orabkp/test/TEST/datafile/o1_mf_example_3k6xjdjw_.dbf
6    100      LOST                 ***     /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /ra5a/orabkp/test/TEST/datafile/o1_mf_temp_3k6xj9xn_.tmp

RMAN> backup as copy datafile 6 format '/ra5a/orabkp/test/TEST/datafile/lost.dbf';

Starting backup at 2007-10-15 12:40:45
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf
output filename=/ra5a/orabkp/test/TEST/datafile/lost.dbf tag=TAG20071015T124045 recid=13 stamp=636036046
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2007-10-15 12:40:46

RMAN> sql 'alter database datafile 6 offline';

sql statement: alter database datafile 6 offline

RMAN> switch datafile 6 to copy;

datafile 6 switched to datafile copy "/ra5a/orabkp/test/TEST/datafile/lost.dbf"

RMAN> recover datafile 6;

Starting recover at 2007-10-15 12:41:07
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 2007-10-15 12:41:07

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

RMAN> 

Recovery Manager complete.

/ra5a/orabkp/test/TEST/datafile> ls -lF
total 1015132
-rw-r-----  1 oracle oinstall 104865792 Oct 15 12:41 lost.dbf
-rw-r-----  1 oracle oinstall 157294592 Oct 15 12:22 o1_mf_example_3k6xjdjw_.dbf
lrwxrwxrwx  1 oracle oinstall        17 Oct 15 12:37 o1_mf_lost_3k74mq08_.dbf -> /proc/12272/fd/26
-rw-r-----  1 oracle oinstall 241180672 Oct 15 12:32 o1_mf_sysaux_3k6xgwdf_.dbf
-rw-r-----  1 oracle oinstall 503324672 Oct 15 12:38 o1_mf_system_3k6xgwd4_.dbf
-rw-r-----  1 oracle oinstall  20979712 Oct 15 10:17 o1_mf_temp_3k6xj9xn_.tmp
-rw-r-----  1 oracle oinstall  26222592 Oct 15 12:38 o1_mf_undotbs1_3k6xgwg9_.dbf
-rw-r-----  1 oracle oinstall   5251072 Oct 15 12:22 o1_mf_users_3k6xgwhb_.dbf

/ra5a/orabkp/test/TEST/datafile> rm o1_mf_lost_3k74mq08_.dbf
/ra5a/orabkp/test/TEST/datafile> sqlplus testing/testing

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:42:03 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070

SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
email
Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

An Oracle ACE with a deep understanding of databases, application memory, and input/output interactions, Christo is an expert at optimizing the performance of the most complex infrastructures. Methodical and efficiency-oriented, he equates the role of an ATCG Principal Consultant in many ways to that of a data analyst: both require a rigorous sifting-through of information to identify solutions to often large and complex problems. A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

18 Comments. Leave new

Dan Norris
November 7, 2007 11:26 pm

Well done, Christo! I knew there was a way to do this, but haven’t tried to figure it out. Thanks for saving me a lot of work!

Reply
Hazel
November 8, 2007 7:10 am

VERY Clever ! Excellent solutuion.

Reply
Frits Hoogland
November 8, 2007 8:13 am

Mmh, I’ve covered this already: https://frits.homelinux.com/wordpress/?p=20

After that, oracle also made a metalink note about it (ML:444749.1). When asking them about a note with the same information as in my blogpost, they responded it came from an internal note which was around.

I’ve tested this with linux 32 & 64 bit and solaris.

Reply
suva
June 12, 2013 2:44 am

grt..but ur link is not working

Reply
Christo Kutrovsky
November 8, 2007 11:52 am

Frits,

Great article! I wish I had read it 4 months ago :)

The metalink note however I find disturbing. It think it will corrupt your database. I will check it out.

Reply
Mark Brinsmead
November 9, 2007 10:03 pm

Slick trick! Others might have reported this before, but its the first time I’ve seen it.

It seems to be rather dependent on the implementation of /proc. I have seen lots of *nix variants with little or nothing implemented there, but this ought to work on a pretty wide variety of platforms. Not that I’m volunteering to try.

Reply
Rob
November 13, 2007 6:53 am

Hi

Nice trick. I wonder whether you saw an Oracle-l posting

https://www.freelists.org/archives/oracle-l/06-2007/msg00699.html

Unfortunately i do not have access to a linux server to try the method, but as the post suggest it could elminate the extra step of having to rman copy and then switch to the file

Reply
Data Recovery Advisor (DRA) and Health Monitor by example | IT World
July 26, 2011 3:08 am

[…] How to Recover Deleted Oracle Datafiles with No Downtime Posted in Oracle – Tagged Data Recovery Advisor, DRA, Health Monitor « RMAN and TDPO performance testing No Comments Yet […]

Reply
Harish
December 28, 2011 2:25 pm

Hi,
If datafile is dropped is os level and no backup is available means u can follow this steps.
1.make offline the dropped datafile.
2.ALTER DATABASE CREATE DATAFILE ‘/oradata/app/users.dbf’ path’ as ‘/oradata/app/datafiles/users.dbf’;
3.recover datafile ‘/new path’
4.make online the datafile.
Note: All archive logs should be there.

Regards Harish

Reply
Christo Kutrovsky
December 28, 2011 7:54 pm

Harish,

I think you missed the point. The point is that the file can be recovered without the presence of backups.

Your approach would only work if your database is in archivelog mode and all archived logs since the creation of the file in question are available.
This could potentially be years of archived logs.

Reply
vsn
May 8, 2015 1:48 am

This doesnot work on ASM

Reply
Harish
December 28, 2011 2:29 pm

In this manner u can recover ur dropped datafiles.This ll work for sure. All can try this.

Regards
Harish(poojari)
Kolar

Reply
LAO
April 4, 2012 5:22 am

Hi;

Wonderfull ! Thanks a lot for sharing this.

LAO.

Reply
sanjay
July 5, 2012 4:54 am

hi,

ls -l /proc/_PID_OF_DBWRITER/fd | grep deleted

i would like to know what is /proc and /fd ?

if this is a mount point we don’t have it. is it a os specific. we are using hp-ux

thanks
Sanjay

Reply
Christo Kutrovsky
July 5, 2012 8:54 am

Sanjay,

The technique should be usable on HP-UX as well, however the specific may be different.

On linux the /proc filesystem is a virtual export of some kernel structures. I am leverageing that fact and the use of softlinks to achieve the goal.

At this point I don’t know how exactly to do this on HP-UX.

Reply
Pankaj
April 26, 2015 8:00 am

Hi,
I have same condition my database is in noarchivelog mode and I had lost 4 user datafile and 4 undo datafiles on OS level.
MY OS is IBM AIX
but i am not getting output of

[email protected]:/home/oracle$ps -ef|grep pmon
oracle 12585202 1 0 Apr 01 – 8:38 ora_pmon_SASAML
oracle 9767204 17106500 0 18:29:33 pts/0 0:00 grep pmon
[email protected]:/home/oracle$ls -l /proc/12585202/fd|grep deleted
[email protected]:/home/oracle$
[email protected]:/home/oracle$

How can i recover ??

Reply
Tushar
June 5, 2015 12:59 am

Hi,

My Question will it applicable for system tablespace????

If i lost system01.dbf will it applicable.it has been deleted from os level and no backup aviable & database is in noarchive mode.

How can i recover.

Regards,

Tushar

Reply
Sumit
February 6, 2019 5:08 am

Thanks it is working . It saves my efforts

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

  • DBA Cloud Services
  • View Upcoming Pythian Events

    • Contact Us