Use file descriptors in Unix/linux to retrieve deleted files (data file or redo log)
Reference text:
Retrieve deleted files on Unix/linux using File descriptors (Doc ID 444749.1)
Suitable for:
Oracle database-enterprise edition-version 8.1.7.0 to 11.2.0.3 [Release 8.1.7 to 11.2]
Linux x86
Oracle Solaris on SPARC (64-bit)
Linux x86-64
Checked for relevance on 24-nov-2010***
Target: Retrieve deleted datafile and logfile from the operating system in the event that the database is not restarted
Solution:
When the following States are met, we can retrieve (retrieve) the deleted datafile and logfile with the help of the proc file system in Unix/linux
1.) Database is not restarted.
2.) Server is not restarted.
3.) The file is not offline before deletion.
The background process (DBWR, Pmon, Smon etc) accesses all the datafiles opened by this database, so with the help of the lsof command, you can see a list of files that are open by the process.
Any file that is opened by the process has a file descriptor (FD) associated with the file. If the file is mistakenly deleted from the operating system, the file's entry (entry) is not deleted from the proc file system, and with this entry we can reconstruct the deleted file (datafile or logfile)
Here is an example:
Steps to salvage the file
1.) Locate the OS PID for the DBWR process
-$ ps-ef |grep ' <SID> ' | grep ' <name_of_background_process> '
$ ps-ef |grep emr102u6|grep dBW Emrdbms 21943 1 0 10:27:08? 0:00 Ora_dbw0_emr102u6
2.) Use the lsof command to locate the open file for the Ospid
$ lsof-p 21943 |grep/emea/rdbms/64bit/app/oracle/oradata/emr102u6/my_test_01.dbfcommand PID USER FD TYPE DEVICE size/off NODE NAME Oracle 21943 Emrdbms 270uW VREG 304,25 212992
Attention:
If you have the using NAS then the file name in the above command may is displayed properly and hence this procedure should Not being used under these circumstances.
Notice the FD value above--270
3.) to the FD directory
-$ cd/proc/<process_id>/<file_descriptor_directory> '/
$ cd/proc/21943/fd/
4.) Place the table space Read only
Set the tablespace to read only freezes the file header, preventing the file header from being updated. Because when database is open, it is possible to copy the file only if the datafile is in read only state.
Read only allows the user to select the tablespace, but does not allow inserts to the tablespace, Update,delete
5.) Make a copy of the file
$ cat >/EMEA/RDBMS/64BIT/APP/ORACLE/ORADATA/EMR102U6/MY_TEST_01.DBF
6.) To ensure that copy of the file is not used after copy, do the following:
A) take datafile offline alter tablespace my_test offline; Query The view V$datafile to verify the datafile are offline: Select status from V$datafile where File#=<file number >;B) bring datafile back online alter tablespace my_test online;
7.) Place the table space as read write
Alter Tablespace My_test Read write;
Query View Dba_tablespaces to check the status of the tablespace:
Select Tablespace_name,status from dba_tablespaces where tablespace_name= ' my_test ';
8.) The resize operation of the data file is normal.
sql> ALTER DATABASE datafile '/EMEA/RDBMS/64BIT/APP/ORACLE/ORADATA/EMR102U6/MY_TEST_01.DBF ' resize 250k;
Database altered.
Note: This procedure also applies to retrieving the deleted current redo logfile
