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, name the data file as none

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, name the data file as none

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 customized 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.0ProductionTNS for Linux: version 11.1.0.7.0-ProductionNLSRTL Version 11.1.0.7.0-ProductionSQL> archive log list; Database log modeArchive ModeAutomatic implements destination/archivelogOldest online log sequence1Next log sequence to archive 1 Current log sequence1

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 users/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 32uWREG8, 2 209723392 1410008/home/oracle/oradata/fly/datafile/users02.dbf (deleted) oracle 22301 oracle 42uREG8, 2 209723392 1410008/home/oracle/oradata/fly/datafile/users02.dbf (deleted) oracle 22309 oracle 30uREG8, 2 209723392 1410008/home/oracle/oradata/fly/datafile/users02.dbf (deleted) oracle 22317 oracle 32uREG8, 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 222971 0? 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) Directory of the dbwr process, and check whether the deleted data file has only one user02.dbf. The result shows no, 32 is fd (file descriptor)

Fly007 :~ # Cd/proc/22297/fdfly007: /proc/22297/fd # ls-l | grep deletelrwx ------ 1 oracle oinstall 64 Dec 6 10->/home/oracle/product/11g/db/dbs/lkinstfly (deleted) lrwx ------ 1 oracle oinstall 64 Dec 6 25->/home/oracle/oradata/fly/datafile/user03.dbf (deleted) lrwx ------ 1 oracle oinstall 64 Dec 6 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