Restore accidentally deleted oracle data files (1)

In the production environment, data files in the database will always be deleted by mistake. For example, SA does not know the database, and the online database log is redo01.log, or deleted after cp backup, data files are easy to be deleted by mistake when they are named as non-dbf suffixes.
Rm is mistaken at the operating system level. If it is found in time, it can still be saved. The following is an example:

1. query the database version and whether the archive mode is used.

SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit ProductionPL/SQL Release 11.1.0.7.0 - ProductionCORE    11.1.0.7.0      ProductionTNS for Linux: Version 11.1.0.7.0 - ProductionNLSRTL Version 11.1.0.7.0 - ProductionSQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            /archivelogOldest online log sequence     1Next log sequence to archive   1Current log sequence           1

2. The fly table is created by the fly user, and the number of records in the fly table is 1128432.

SQL> conn fly/flyConnected.SQL> create table fly as select * from dba_objects;Table created.SQL> insert into fly select * from fly;70527 rows created.SQL> /141054 rows created.SQL> /282108 rows created.SQL> /564216 rows created.SQL> commit;Commit complete.SQL> select count(*) from fly;COUNT(*)----------1128432

3. View data files in the default tablespace of the fly user, and use the operating system command rm to delete all data files in the default tablespace of the fly user.

SQL> conn sys/oracle as sysdbaConnected.SQL> select default_tablespace from dba_users where username='FLY';DEFAULT_TABLESPACE------------------------------USERSSQL> col file_name format a80SQL> set linesize 200SQL> select file_name from dba_data_files where tablespace_name='USERS';FILE_NAME--------------------------------------------------------------------------------/home/oracle/oradata/fly/datafile/users02.dbf/home/oracle/oradata/fly/datafile/user03.dbfSQL> host rm /home/oracle/oradata/fly/datafile/users02.dbfSQL> host rm /home/oracle/oradata/fly/datafile/user03.dbf

4. An error is returned when the fly007 table is created under the fly user. Note that the SQL statement for creating the table is executed several times in a timely manner. Here, only the user02.dbf data file does not exist and the subsequent restoration is in progress, we must take into account the accidental deletion of several data files.

SQL> conn fly/flyConnected.SQL> create table fly007 as select * from dba_objects;create table fly007 as select * from dba_objects*ERROR at line 1:ORA-01116: error in opening database file 20ORA-01110: data file 20: '/home/oracle/oradata/fly/datafile/users02.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3

5. Check whether the file is still opened by some processes.

fly007:~ # lsof | grep /home/oracle/oradata/fly/datafile/users02.dbforacle    22297     oracle   32uW     REG                8,2  209723392    1410008 /home/oracle/oradata/fly/datafile/users02.dbf (deleted)oracle    22301     oracle   42u      REG                8,2  209723392    1410008 /home/oracle/oradata/fly/datafile/users02.dbf (deleted)oracle    22309     oracle   30u      REG                8,2  209723392    1410008 /home/oracle/oradata/fly/datafile/users02.dbf (deleted)oracle    22317     oracle   32u      REG                8,2  209723392    1410008 /home/oracle/oradata/fly/datafile/users02.dbf (deleted)

6. The dbwr process of the database opens all data files, including the control file. Check that the pid of the dbwr process is 22297.

fly007:~ # ps -ef | grep dbw0 | grep -v greporacle   22297     1  0 21:21 ?        00:00:00 ora_dbw0_fly

 

Principle of restoring accidentally deleted data files
When a process opens a file, as long as the process continues to open the file, even if the file is deleted, the process can still read and write the file descriptor provided to the file when the file is opened. The/proc directory contains various files that reflect the kernel and process tree. The/proc directory is mounted to a region mapped in the memory. Therefore, these files and directories are not stored in the disk. Therefore, when we read and write these files, in fact, it is getting the relevant information from the memory. Most lsof-related information is stored in the directory named after the PID of the process, that is, the/proc/1116 contains information about the process whose PID is 1116. Each process directory contains various files, which allow applications to easily understand the memory space, file descriptor list, symbolic links to files on the disk, and other system information of the process. The lsof program uses this information and other information about the internal status of the kernel to generate its output. Therefore, lsof can display the file descriptor of a process and related file names. That is, we can find information about the file by accessing the file descriptor of the process.
When a file in the system is accidentally deleted, as long as there are other processes in the system accessing the file at this time, we can use lsof to restore the file content from the/proc directory.

 

7. Go to the fd file descriptor of the dbwr process. Check whether the deleted data file has only one user02.dbf. If no, the values and 32 are fd (file descriptor)

fly007:~ # cd /proc/22297/fdfly007:/proc/22297/fd # ls -l | grep deletelrwx------ 1 oracle oinstall 64 Dec  6 21:26 10 -> /home/oracle/product/11g/db/dbs/lkinstfly (deleted)lrwx------ 1 oracle oinstall 64 Dec  6 21:26 25 -> /home/oracle/oradata/fly/datafile/user03.dbf (deleted)lrwx------ 1 oracle oinstall 64 Dec  6 21:26 32 -> /home/oracle/oradata/fly/datafile/users02.dbf (deleted)fly007:/proc/22297/fd # ls -l /home/oracle/oradata/fly/datafile/user03.dbf/bin/ls: /home/oracle/oradata/fly/datafile/user03.dbf: No such file or directory

8. Copy the corresponding file descriptor to the absolute path of the accidentally deleted data file. The following uses the root COPY method. Note that the-p parameter is added.

fly007:/proc/22297/fd # cp -p 32 /home/oracle/oradata/fly/datafile/users02.dbffly007:/proc/22297/fd # cp -p 25 /home/oracle/oradata/fly/datafile/user03.dbf

9. recover the recover datafile in the database. The data is not lost and is completely restored.

fly007:/proc/22297/fd # su - oracleoracle@fly007:~> sqlplus /nologSQL*Plus: Release 11.1.0.7.0 - Production on Fri Dec 6 21:56:45 2013Copyright (c) 1982, 2008, Oracle.  All rights reserved.SQL> conn sys/oracle as sysdbaConnected.SQL> alter database datafile '/home/oracle/oradata/fly/datafile/users02.dbf' offline;Database altered.SQL> alter database datafile '/home/oracle/oradata/fly/datafile/user03.dbf' offline;Database altered.SQL> recover datafile '/home/oracle/oradata/fly/datafile/users02.dbf';Media recovery complete.SQL> recover datafile '/home/oracle/oradata/fly/datafile/user03.dbf';Media recovery complete.SQL> alter database datafile '/home/oracle/oradata/fly/datafile/users02.dbf' online;Database altered.SQL> alter database datafile '/home/oracle/oradata/fly/datafile/user03.dbf' online;Database altered.SQL> conn fly/flyConnected.SQL> select count(*) from fly;COUNT(*)----------1128432SQL> create table fly007 as select * from dba_objects;Table created.

After the data file is accidentally deleted, if the database is closed, the dbw0 process disappears and the process that continuously opens the file to be deleted cannot be restored. Therefore, when a database problem occurs, do not close the database unless you confirm the complexity of the situation. Restarting the database is often meaningless or even fatal. In addition, if the control file is rm, it cannot be recovered.
At the same time, data files are deleted through the linux rm, and even archive logs are all deleted. As long as the database does not crash and the dbw0 process is still in progress, the data can be completely restored, it has nothing to do with archiving logs, but it is different from whether archiving is enabled. We will continue to discuss restoration of database data files deleted by mistake at the operating system level in the future.

 

 

This article from the "Yang youyoucuncaoxin" blog, please be sure to keep this source http://fly1116.blog.51cto.com/8301004/1337681