Oracle Data Recovery

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 is still in the open state without crashing.

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 is still in the open state without crashing.

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 alias/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 4 ORA-01110: data file 4: '/app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o _. dbf 'ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3

Similar information can also be seen in the alarm log.

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

3. Check the dbwr process PID

$ Ps-ef | grep dbw0 | grep-v grep oracle 2879 1 0? 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-l total 0 lr-x ------ 1 oracle dba 64 Dec 19 0->/dev/null lr-x ------ 1 oracle dba 64 dec 19 1->/dev/null lr-x ------ 1 oracle dba 64 Dec 19 10->/dev/zero lr-x ------ 1 oracle dba 64 Dec 19 11 ->/dev/zero lr-x ------ 1 oracle dba 64 Dec 19 21:50 12->/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus. msb lrwx ------ 1 oracle dba 64 Dec 19 13->/app/oracle/product/10.2.0/db_1/dbs/hc_orcl.dat lrwx ------ 1 oracle dba 64 Dec 19 14->/ app/oracle/product/10.2.0/db_1/dbs/lkORCL lrwx ------ 1 oracle dba 64 Dec 19 15->/app/oracle/oradata/ORCL/controlfile/ow.mf_555wq3ng _. ctl lrwx ------ 1 oracle dba 64 Dec 19 16->/app/oracle/oradata/ORCL/datafile/o1_mf_system_555wqbnk _. dbf lrwx ------ 1 oracle dba 64 Dec 19 17->/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_555wqxgl _. dbf lrwx ------ 1 oracle dba 64 Dec 19 18->/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_555wr5p6 _. dbf lrwx ------ 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/null lrwx ------ 1 oracle dba 64 Dec 19 21:50 20->/app/oracle/oradata/ORCL/datafile /o1_mf_temp_555wrbnz _. tmp lr-x ------ 1 oracle dba 64 Dec 19 21:50 21->/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus. msb lr-x ------ 1 oracle dba 64 Dec 19 21:50 3->/dev/null lr-x ------ 1 oracle dba 64 Dec 19 21:50 4->/dev/null l-wx ------ 1 oracle dba 64 Dec 19 5->/app/oracle/admin/orcl/udump/orcl_ora_2871.trc l-wx ------ 1 oracle dba 64 Dec 19 6->/app/ oracle/admin/orcl/bdump/alert_orcl.log lrwx ------ 1 oracle dba 64 Dec 19 21:50 7->/app/oracle/product/10.2.0/db_1/dbs/lkinstorcl (deleted)

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 the statement 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.