根据上篇文章,整个恢复过程可以使用脚本完成,既节约时间,也增加恢复的成功率。
这个方法对误删的数据文件,包括online redo,undo,control file, system或普通数据文件均有效。
整个过程分两部分:
第一部分是通过tail命令hold住文件描述符FD,然后建立数据文件的soft link。完成此步,数据库可正常运行,业务不受影响。但需要防止重启服务器或断电等异常情况,建议此时如果还需继续运行数据库的话,可以通过rman单独对误删的数据文件做备份。
第二部分是在误删的数据文件offline或DB关闭后,通过文件描述符FD恢复误删数据文件到硬盘原目录。
以下是演示部分:
$ 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 74 Oct 30 12:04 bbed.par
-rw-r--r-- 1 oracle oinstall 18432 Oct 30 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 30 12:03 filelist.txt
-rw-r--r-- 1 oracle oinstall 1725 Oct 30 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, 2011, 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 file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/KV/system01.dbf
2 /u01/app/oracle/oradata/KV/sysaux01.dbf
3 /u01/app/oracle/oradata/KV/undotbs01.dbf
4 /u01/app/oracle/oradata/KV/users01.dbf
5 /u01/app/oracle/oradata/KV/example01.dbf
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 74 Oct 30 12:04 bbed.par
-rw-r--r-- 1 oracle oinstall 18432 Oct 30 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 30 12:03 filelist.txt
-rw-r--r-- 1 oracle oinstall 1725 Oct 30 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 74 Oct 30 12:04 bbed.par
-rw-r--r-- 1 oracle oinstall 18432 Oct 30 13:48 bifile.bbd
lrwxrwxrwx 1 oracle oinstall 18 Nov 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 30 12:03 filelist.txt
-rw-r--r-- 1 oracle oinstall 1725 Oct 30 13:48 log.bbd
lrwxrwxrwx 1 oracle oinstall 18 Nov 5 10:27 redo01.log -> /proc/23467/fd/258
lrwxrwxrwx 1 oracle oinstall 18 Nov 5 10:27 redo02.log -> /proc/23467/fd/259
lrwxrwxrwx 1 oracle oinstall 18 Nov 5 10:27 redo03.log -> /proc/23467/fd/260
lrwxrwxrwx 1 oracle oinstall 18 Nov 5 10:27 sysaux01.dbf -> /proc/23463/fd/259
lrwxrwxrwx 1 oracle oinstall 18 Nov 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 18 Nov 5 10:27 undotbs01.dbf -> /proc/23463/fd/260
lrwxrwxrwx 1 oracle oinstall 18 Nov 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, 2011, 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;
1000 rows created.
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;
1000 rows created.
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 74 Oct 30 12:04 bbed.par
-rw-r--r-- 1 oracle oinstall 18432 Oct 30 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 30 12:03 filelist.txt
-rw-r--r-- 1 oracle oinstall 1725 Oct 30 13:48 log.bbd
-rw-r----- 1 oracle oinstall 52429312 Nov 5 10:30 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Nov 5 10:30 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Nov 5 10:30 redo03.log
-rw-r----- 1 oracle oinstall 660611072 Nov 5 10:30 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 10:30 undotbs01.dbf
-rw-r----- 1 oracle oinstall 83894272 Nov 5 10:30 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, 2011, 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