Accidentally Deleted Oracle Database Datafile on Linux Server

Sysadmin accidentally deleted an Oracle database datafile on Linux server. There are a couple of ways to recover this database datafile, like restoring the deleted datafile from RMAN backup, etc.

In this special situation, sysadmin has just deleted the dbf file, and then realized it immediately. So we can try a different way to recover this datafile from OS file descriptor, which is still open in memory by Oracle database background process.

IDENTIFY which datafile has been wrongly deleted

ORA-63999: data file suffered media failure
ORA-01116: error in opening database file 13
ORA-01110: data file 13: '/u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Do not offline the deleted file in database until recovering datafile, otherwise file descriptor ( FD) of the deleted file will be lost and unavailable for the rest steps.

Check which OS process still have File Descriptor open for the deleted file

[oracle@oemnode1 trace]$ lsof |grep hr.dbf
ora_dbw0_ 9885 oracle 272uW REG 252,0 104865792 235862719 /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf (deleted)

We can see the file descriptor is 272 by Oracle db writer background process 9885.

Recover datafile from file descriptor

[oracle@oemnode1 proc]$ cd /proc/9885/fd

[oracle@oemnode1 fd]$ ls -ltr 272
lrwx------ 1 oracle oinstall 64 Aug 5 22:33 272 -> /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf (deleted)

[oracle@oemnode1 fd]$ cat 272 > /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf

[oracle@oemnode1 fd]$ ls -ltr /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf
-rw-r--r-- 1 oracle oinstall 104865792 Aug 5 22:38 /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf
SQL> alter database datafile 13 offline;

Database altered.

SQL> recover datafile 13;

Media recovery complete.

SQL> alter database datafile 13 online;

Database altered.

Check and confirm datafile is restored and Recovered successfully

[oracle@oemnode1 fd]$ lsof |grep hr.dbf
oracle_46 4606 oracle 269u REG 252,0 104865792 235862728 /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf
ora_dbw0_ 9885 oracle 272uW REG 252,0 104865792 235862728 /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf
SQL>select FILE_NAME, TABLESPACE_NAME, STATUS  from dba_data_files where file_id=13

FILE_NAME                                      TABLESPACE_NAME  STATUS
----------------------------------------------- ---------------- -------
/u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf     HR            AVAILABLE

Please note this method works only when database is still up running after the datafile accidentally being deleted.

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.