[Reprinted] ORACLE data recovery-recovery of files accidentally deleted by Linux/Unix

Author: eygle | English version [indicate the source and author information and this statement in hyperlink form during reprinting]
Link: http://www.eygle.com/archives/2011/12/oracle_linux_rm_rf.html

A mistaken deletion case mentioned a few days ago:

Http://www.eygle.com/archives/2011/12/oracle_lsof_recovery_database.html

Kamus wrote a detailed step, which is reprinted for reference:

Http://www.dbform.com/html/2011/1761.html

Today, a customer's database accidentally deleted the data files in the entire directory and deleted them at the operating system level. Fortunately, the database did not crash and was still in the open state, the customer found the problem and asked us for help. Finally, all the data files were completely restored.

In Linux, the recovery process is roughly re-demonstrated. The recovery steps are not closely related to the database version, but different from the operating system.

1. When the database is open, the data files in the users tablespace are deleted directly.

SQL> select name from v$datafile; NAME--------------------------------------------------------------------------------/app/oracle/oradata/ORCL/datafile/o1_mf_system_555wqbnk_.dbf/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_555wqxgl_.dbf/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_555wr5p6_.dbf/app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o_.dbf SQL> host rm /app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o_.dbf

2. Try to create a table in the users tablespace and start to report an error.

SQL> create table t tablespace users as select * from dual;create table t tablespace users as select * from dual                                                 *ERROR at line 1:ORA-01116: error in opening database file 4ORA-01110: data file 4:'/app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o_.dbf'ORA-27041: unable to open fileLinux Error: 2: No such file or directoryAdditional information: 3

Similar information can also be seen in the alarm log.

Mon Dec 19 21:48:17 CST 2011Errors in file /app/oracle/admin/orcl/bdump/orcl_m000_3897.trc:ORA-01116: error in opening database file 4ORA-01110: data file 4: '/app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o_.dbf'ORA-27041: unable to open fileLinux Error: 2: No such file or directoryAdditional information: 3

3. Check the dbwr process PID

$ ps -ef|grep dbw0|grep -v greporacle    2879     1  0 21:38 ?        00:00:00 ora_dbw0_orcl

4. dbwr opens the handles of all data files. In the proc directory, you can find that the directory name is the process PID, and FD indicates the file descriptor.

$ cd /proc/2879/fd$ ls -ltotal 0lr-x------ 1 oracle dba 64 Dec 19 21:50 0 -> /dev/nulllr-x------ 1 oracle dba 64 Dec 19 21:50 1 -> /dev/nulllr-x------ 1 oracle dba 64 Dec 19 21:50 10 -> /dev/zerolr-x------ 1 oracle dba 64 Dec 19 21:50 11 -> /dev/zerolr-x------ 1 oracle dba 64 Dec 19 21:50 12 -> /app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msblrwx------ 1 oracle dba 64 Dec 19 21:50 13 -> /app/oracle/product/10.2.0/db_1/dbs/hc_orcl.datlrwx------ 1 oracle dba 64 Dec 19 21:50 14 -> /app/oracle/product/10.2.0/db_1/dbs/lkORCLlrwx------ 1 oracle dba 64 Dec 19 21:50 15 -> /app/oracle/oradata/ORCL/controlfile/o1_mf_555wq3ng_.ctllrwx------ 1 oracle dba 64 Dec 19 21:50 16 -> /app/oracle/oradata/ORCL/datafile/o1_mf_system_555wqbnk_.dbflrwx------ 1 oracle dba 64 Dec 19 21:50 17 -> /app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_555wqxgl_.dbflrwx------ 1 oracle dba 64 Dec 19 21:50 18 -> /app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_555wr5p6_.dbflrwx------ 1 oracle dba 64 Dec 19 21:50 19 -> /app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o_.dbf (deleted)lr-x------ 1 oracle dba 64 Dec 19 21:50 2 -> /dev/nulllrwx------ 1 oracle dba 64 Dec 19 21:50 20 -> /app/oracle/oradata/ORCL/datafile/o1_mf_temp_555wrbnz_.tmplr-x------ 1 oracle dba 64 Dec 19 21:50 21 -> /app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msblr-x------ 1 oracle dba 64 Dec 19 21:50 3 -> /dev/nulllr-x------ 1 oracle dba 64 Dec 19 21:50 4 -> /dev/nulll-wx------ 1 oracle dba 64 Dec 19 21:50 5 -> /app/oracle/admin/orcl/udump/orcl_ora_2871.trcl-wx------ 1 oracle dba 64 Dec 19 21:50 6 -> /app/oracle/admin/orcl/bdump/alert_orcl.loglrwx------ 1 oracle dba 64 Dec 19 21:50 7 -> /app/oracle/product/10.2.0/db_1/dbs/lkinstorcl (deleted)l-wx------ 1 oracle dba 64 Dec 19 21:50 8 -> /app/oracle/admin/orcl/bdump/alert_orcl.loglrwx------ 1 oracle dba 64 Dec 19 21:50 9 -> /app/oracle/product/10.2.0/db_1/dbs/hc_orcl.dat

Note that "/APP/Oracle/oradata/orcl/datafile/o1_mf_users_555wrj4o _. DBF (Deleted) "indicates that the file has been deleted. If it is a Solaris operating system, the LS command will not be so clearly displayed, to determine which file corresponds to a handle in the Solaris system, you need to use the lsof program.

5. Directly cp the handle file name back to the original location.

cp 19 /app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o_.dbf

6. recover the data file

SQL> alter database datafile 4 offline; Database altered. SQL> recover datafile 4;Media recovery complete.SQL> alter database datafile 4 online; Database altered.

Restore the data file.

The principle of recovery is that in a Linux operating system, if the file is deleted from the operating system level by RM, the process that opened the file still holds the corresponding file handle, the object to which the object points can still be read and written, and the file descriptor of the object can be obtained from the/proc directory. However, if you close the database at this time, the handle will disappear, so there is no other method except scanning the disk for file recovery. Therefore, when the database encounters a problem, do not close the database unless you confirm the complexity of the situation. Restarting a database is often meaningless or even fatal.

Of course, the customer's operating system is Solaris, and the files deleted by the customer also include the current online redo log. Therefore, there are other more complex operations, which are not described here.