Workaround for the Oracle Database runtime RM mistakenly deleted files (2)--Recovery by script

According to the previous article, the entire recovery process can be done using scripting, saving time and increasing the success rate of recovery.

This method is valid for data files that are mistakenly deleted, including online Redo,undo,control file, system, or normal data files.

The whole process is divided into two parts:

The first part is to hold the file descriptor FD through the tail command and then build the soft link of the data file. By completing this step, the database will function properly and the business will not be affected. However, it is necessary to prevent the restart of the server or power outages and other anomalies, it is recommended that if you still need to continue to run the database, the data file can be mistakenly deleted by Rman to make a backup.

The second part is the deleted data file offline or DB after the shutdown, through the file descriptor fd to recover the wrong deletion of files to the original directory of hard disk.

Here is the demo section:

$ ll/home/oracle/bin/file_dump
-rwxrwxr-x 2 kiever oinstall 2143 Nov 5 10:15/home/oracle/bin/file_dump
$ ll
Total 2238932
-rw-r--r--1 Oracle Oinstall (OCT) 12:04 Bbed.par
-rw-r--r--1 Oracle oinstall 18432 Oct 13:48 BIFILE.BBD
-RW-R-----1 Oracle Oinstall 9814016 Nov 5 10:26 Control01.ctl
-RW-R-----1 Oracle Oinstall 362422272 Nov 5 10:21 example01.dbf
-rw-r--r--1 Oracle Oinstall 259 Oct 12:03 filelist.txt
-rw-r--r--1 Oracle Oinstall 1725 Oct 13:48 LOG.BBD
-RW-R-----1 Oracle Oinstall 52429312 Nov 5 10:22 Redo01.log
-RW-R-----1 Oracle Oinstall 52429312 Nov 5 10:22 Redo02.log
-RW-R-----1 Oracle Oinstall 52429312 Nov 5 10:22 Redo03.log
-RW-R-----1 Oracle Oinstall 660611072 Nov 5 10:21 sysaux01.dbf
-RW-R-----1 Oracle Oinstall 943726592 Nov 5 10:21 system01.dbf
-RW-R-----1 Oracle Oinstall 20979712 Nov 4 22:53 temp01.dbf
-RW-R-----1 Oracle Oinstall 57679872 Nov 5 10:21 undotbs01.dbf
-RW-R-----1 Oracle Oinstall 83894272 Nov 5 10:23 users01.dbf
$ sqlplus/as SYSDBA

Sql*plus:release 11.2.0.3.0 Production on Wed Nov 5 10:27:01 2014

Copyright (c) 1982, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options

Sql> Select COUNT (*) from test;

COUNT (*)
----------
619237

Sql> disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options
$ ll
Total 2238932
-rw-r--r--1 Oracle Oinstall (OCT) 12:04 Bbed.par
-rw-r--r--1 Oracle oinstall 18432 Oct 13:48 BIFILE.BBD
-RW-R-----1 Oracle Oinstall 9814016 Nov 5 10:27 Control01.ctl
-RW-R-----1 Oracle Oinstall 362422272 Nov 5 10:27 example01.dbf
-rw-r--r--1 Oracle Oinstall 259 Oct 12:03 filelist.txt
-rw-r--r--1 Oracle Oinstall 1725 Oct 13:48 LOG.BBD
-RW-R-----1 Oracle Oinstall 52429312 Nov 5 10:27 Redo01.log
-RW-R-----1 Oracle Oinstall 52429312 Nov 5 10:27 Redo02.log
-RW-R-----1 Oracle Oinstall 52429312 Nov 5 10:27 Redo03.log
-RW-R-----1 Oracle Oinstall 660611072 Nov 5 10:27 sysaux01.dbf
-RW-R-----1 Oracle Oinstall 943726592 Nov 5 10:27 system01.dbf
-RW-R-----1 Oracle Oinstall 20979712 Nov 5 10:27 temp01.dbf
-RW-R-----1 Oracle Oinstall 57679872 Nov 5 10:27 undotbs01.dbf
-RW-R-----1 Oracle Oinstall 83894272 Nov 5 10:27 users01.dbf
$rm-f control01.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
$file_dump relink
Relink/u01/app/oracle/oradata/kv/control01.ctl successfully
RELINK/U01/APP/ORACLE/ORADATA/KV/SYSTEM01.DBF successfully
RELINK/U01/APP/ORACLE/ORADATA/KV/SYSAUX01.DBF successfully
RELINK/U01/APP/ORACLE/ORADATA/KV/UNDOTBS01.DBF successfully
RELINK/U01/APP/ORACLE/ORADATA/KV/USERS01.DBF successfully
Relink/u01/app/oracle/oradata/kv/redo01.log successfully
Relink/u01/app/oracle/oradata/kv/redo02.log successfully
Relink/u01/app/oracle/oradata/kv/redo03.log successfully

Only after tablespace/datafile offline or DB shutdown,
Then run below command to save the files to disk:

/home/oracle/bin/file_dump Recover

$ ll
Total 368876
-rw-r--r--1 Oracle Oinstall (OCT) 12:04 Bbed.par
-rw-r--r--1 Oracle oinstall 18432 Oct 13:48 BIFILE.BBD
lrwxrwxrwx 1 Oracle Oinstall 5 10:27 control01.ctl/proc/23463/fd/256
-RW-R-----1 Oracle Oinstall 362422272 Nov 5 10:27 example01.dbf
-rw-r--r--1 Oracle Oinstall 259 Oct 12:03 filelist.txt
-rw-r--r--1 Oracle Oinstall 1725 Oct 13:48 LOG.BBD
lrwxrwxrwx 1 Oracle Oinstall 5 10:27 redo01.log/proc/23467/fd/258
lrwxrwxrwx 1 Oracle Oinstall 5 10:27 redo02.log/proc/23467/fd/259
lrwxrwxrwx 1 Oracle Oinstall 5 10:27 redo03.log/proc/23467/fd/260
lrwxrwxrwx 1 Oracle Oinstall 5 10:27 sysaux01.dbf/proc/23463/fd/259
lrwxrwxrwx 1 Oracle Oinstall 5 10:27 system01.dbf/proc/23463/fd/258
-RW-R-----1 Oracle Oinstall 20979712 Nov 5 10:27 temp01.dbf
lrwxrwxrwx 1 Oracle Oinstall 5 10:27 undotbs01.dbf/proc/23463/fd/260
lrwxrwxrwx 1 Oracle Oinstall 5 10:27 users01.dbf/proc/23463/fd/261
$!sqlplus
Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.3.0 Production on Wed Nov 5 10:28:09 2014

Copyright (c) 1982, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options

sql> INSERT INTO Test select * from Test where rownum < 1001;

Created rows.

Sql> commit;

Commit complete.

Sql> Select COUNT (*) from test;

COUNT (*)
----------
620237

Sql> alter system checkpoint;

System altered.

sql> alter system switch logfile;

System altered.

sql> INSERT INTO Test select * from Test where rownum < 1001;

Created rows.

Sql> commit;

Commit complete.

Sql> Select COUNT (*) from test;

COUNT (*)
----------
621237

sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Sql> disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options
$file_dump Restore
Restore files in progress, waiting ...
/u01/app/oracle/oradata/kv/control01.ctl restored successfully
/U01/APP/ORACLE/ORADATA/KV/SYSTEM01.DBF restored successfully
/U01/APP/ORACLE/ORADATA/KV/SYSAUX01.DBF restored successfully
/U01/APP/ORACLE/ORADATA/KV/UNDOTBS01.DBF restored successfully
/U01/APP/ORACLE/ORADATA/KV/USERS01.DBF restored successfully
/u01/app/oracle/oradata/kv/redo01.log restored successfully
/u01/app/oracle/oradata/kv/redo02.log restored successfully
/u01/app/oracle/oradata/kv/redo03.log restored successfully
$ ll
Total 2238932
-rw-r--r--1 Oracle Oinstall (OCT) 12:04 Bbed.par
-rw-r--r--1 Oracle oinstall 18432 Oct 13:48 BIFILE.BBD
-RW-R-----1 Oracle Oinstall 9814016 Nov 5 10:29 Control01.ctl
-RW-R-----1 Oracle Oinstall 362422272 Nov 5 10:29 example01.dbf
-rw-r--r--1 Oracle Oinstall 259 Oct 12:03 filelist.txt
-rw-r--r--1 Oracle Oinstall 1725 Oct 13:48 LOG.BBD
-RW-R-----1 Oracle Oinstall 52429312 Nov 5 Redo01.log
-RW-R-----1 Oracle Oinstall 52429312 Nov 5 Redo02.log
-RW-R-----1 Oracle Oinstall 52429312 Nov 5 Redo03.log
-RW-R-----1 Oracle Oinstall 660611072 Nov 5 sysaux01.dbf
-RW-R-----1 Oracle Oinstall 943726592 Nov 5 10:29 system01.dbf
-RW-R-----1 Oracle Oinstall 20979712 Nov 5 10:27 temp01.dbf
-RW-R-----1 Oracle Oinstall 57679872 Nov 5 undotbs01.dbf
-RW-R-----1 Oracle Oinstall 83894272 Nov 5 users01.dbf
$!sqlplus
Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.3.0 Production on Wed Nov 5 10:30:23 2014

Copyright (c) 1982, Oracle. All rights reserved.

Connected to an idle instance.

Sql> Startup
ORACLE instance started.

Total System Global area 1870647296 bytes
Fixed Size 2229424 bytes
Variable Size 452987728 bytes
Database buffers 1409286144 bytes
Redo buffers 6144000 bytes
Database mounted.
Database opened.
Sql> Select COUNT (*) from test;

COUNT (*)
----------
621237

This article from "Wandering in 1521" blog, declined reprint!

Workaround for the Oracle Database runtime RM mistakenly deleted files (2)--Recovery by script