Data recovery: How to recover accidentally deleted Oracle and MySQL databases in Linux

Today, a client's database was accidentally deleted from the entire directory of data files, operating system-level deletion, but fortunately the database did not crash, still in open state, the customer found the problem, help us, and finally completely recovered all the data files.

The recovery process is roughly re-demonstrated under Linux, and the steps to recover are not much related to the database version, and differ from the operating system.

1. When the database is open, delete the data files in the users table space directly.

2. Try to create a table in the Users table space and start an error.

Similar information can also be seen in the alarm log.

3. Check the process PID of the DBWR

4. DBWR will open a handle to all data files. In the proc directory, you can find that the directory name is the process PID,FD representing the file descriptor.

Note that the word "/app/oracle/oradata/orcl/datafile/o1_mf_users_555wrj4o_.dbf (deleted)" indicates that the file has been deleted, and if it is the Solaris operating system, the LS The command is not shown so clearly that you need to use the Lsof program in order to confirm which handle corresponds to which file in the Solaris system.

5. Direct CP The handle file name is back to its original location.

6. Recover the data file

Complete data File recovery.

The principle of recovery is that, in the Linux operating system, if the file is removed from the operating system level by RM, the process that opened the file still holds the corresponding file handle, the file pointed to can still be read and written, and the file descriptor of the file can be obtained from the/proc directory. Note, however, that if you shut down the database at this point, the handle disappears, so there is no other way than to scan the disk for file recovery, so do not shut down the database if you are not sure of the complexity of the database when there is a problem. Restarting a database is often meaningless and even deadly.

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

Data recovery: How to recover accidentally deleted Oracle and MySQL databases in Linux