CentOS下恢复被误删除的oracle数据文件

在生产环境，总会发生数据库的数据文件被误删除的事情，如SA不懂得数据库，数据库的在线日志为redo01.log，还是cp备份后删除的，让人哭笑不得，数据文件命名为无dbf后缀等，都容易被误删除
在操作系统层面被误rm的，如果及时发现，还是可以挽救的，下面举例说明下:

1、查询数据库版本和是否归档模式

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.0ProductionTNS forLinux: 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、fly用户创建表fly，fly表的记录数为1128432

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

3、查看fly用户默认表空间的数据文件，用操作系统命令rm删除fly用户默认表空间下的所有数据文件

SQL> conn sys/oracle assysdbaConnected.SQL> select default_tablespace from dba_users where username='FLY';DEFAULT_TABLESPACE------------------------------USERSSQL> col file_name format a80SQL> setlinesize 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、在fly用户下创建表fly007，报错了,注意及时多执行几次创建表的SQL语句,这边都只显示user02.dbf数据文件不存在了，接下来的恢复中，我们肯定要考虑到到底被误删除了几个数据文件

SQL> conn fly/flyConnected.SQL> create table fly007 asselect * from dba_objects;create table fly007 asselect * from dba_objects*ERROR at line 1:ORA-01116: error inopening 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、查看该文件是否仍旧被某些进程打开着

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

6、数据库的dbwr进程会打开所有的数据文件，包括控制文件，查看dbwr进程的PID为22297

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

误删除数据文件恢复原理
当进程打开了某个文件时，只要该进程仍旧保持打开该文件，即使将该文件删除了，该进程仍然可以向打开该文件时提供给它的文件描述符进行读取和写入操作。在/proc 目录下，包含了反映内核和进程树的各种文件。/proc目录挂载的是在内存中所映射的一块区域，所以这些文件和目录并不存在于磁盘中，因此当我们对这些文件进行读取和写入时，实际上是在从内存中获取相关信息。大多数与lsof 相关的信息都存储于以进程的PID 命名的目录中，即/proc/1116 中包含的是PID 为1116的进程的信息。每个进程目录中存在着各种文件，它们可以使得应用程序简单地了解进程的内存空间、文件描述符列表、指向磁盘上的文件的符号链接和其他系统信息。lsof 程序使用该信息和其他关于内核内部状态的信息来产生其输出。所以lsof 可以显示进程的文件描述符和相关的文件名等信息。也就是我们通过访问进程的文件描述符可以找到该文件的相关信息。
当系统中的某个文件被意外的删除了，只要这个时候系统中还有进程正在访问该文件，那么我们就可以通过lsof从/proc目录下恢复该文件的内容

7、进入到dbwr进程的fd（文件描述符）目录下，需要确定被删除的数据文件是不是只有一个user02.dbf，结果发现不是的，10，25，32为fd(文件描述符）

fly007:~ # cd /proc/22297/fdfly007:/proc/22297/fd # ls -l | grep deletelrwx------ 1oracle oinstall 64Dec 621:2610-> /home/oracle/product/11g/db/dbs/lkinstfly (deleted)lrwx------ 1oracle oinstall 64Dec 621:2625-> /home/oracle/oradata/fly/datafile/user03.dbf (deleted)lrwx------ 1oracle oinstall 64Dec 621:2632-> /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、拷贝对应文件描述符为被误删除的数据文件绝对路径，以下使用root拷贝，注意加上-p参数

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 datafile恢复操作，数据没有丢失，完全恢复

fly007:/proc/22297/fd # su - oracleoracle@fly007:~> sqlplus /nologSQL*Plus: Release 11.1.0.7.0- Production on Fri Dec 621:56:452013Copyright (c) 1982, 2008, Oracle. All rights reserved.SQL> conn sys/oracle assysdbaConnected.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 asselect * from dba_objects;Table created.

数据文件被误删除后，如果关闭数据库，则dbw0进程消失，没有了持续打开误删除文件的进程，就不可以通过此方法进行恢复，因此在数据库出现问题的时候，如果不确认情况的复杂程度，千万不要随便关闭数据库。重启数据库往往是没有意义的，甚至是致命的，另外，若控制文件被rm了，通过这种方式是无法恢复的
同时，通过linux的rm删除了数据文件，甚至连归档日志都全部删除了，只要数据库没有崩溃，dbw0进程还在，就可以将数据完全恢复，和是否有归档日志没有关系，但是和是否开启了归档是有区别的，后续继续讨论操作系统层面误删除数据库数据文件的恢复

恢复被误删除的oracle数据文件(一) 分析的是users表空间下所有数据文件被操作系统命令rm误删除的情况，我们拷贝对应的数据文件到原来的位置后，只需要在数据库中对该数据文件先offline,然后进行recover datafile就可以恢复了，该篇博文的链接为:
http://fly1116.blog.51cto.com/8301004/1337681
那么，如果，连system表空间下的数据文件都被误删除了呢，system表空间的数据文件是不可以在线offline的，我们要怎么进行恢复呢
下面我们就来看下所有数据文件和归档日志都被误删除的情况下，如果数据库没有崩溃，要如何恢复，恢复的原理请看上面链接的文章，这边只是讲述恢复的过程
1、数据库的版本为11.1.0.7.0和数据库处于归档模式

123456789101112131415SQL> 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.0ProductionTNS forLinux: 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 20Next log sequence to archive 22Current log sequence 22

2、在fly用户下创建fly表，在sys用户下创建表fly_sys表,fly,,fly_sys表记录分别为4513536和70525

123456789101112131415161718192021222324252627282930SQL> conn fly/flyConnected.SQL> create table fly asselect * from dba_objects;Table created.SQL> insert into fly select * from fly;70524rows created.SQL> /141048rows created.SQL> /282096rows created.SQL> /564192rows created.SQL> /1128384rows created.SQL> /2256768rows created.SQL>commit;Commit complete.SQL> select count(*) from fly;COUNT(*)----------4513536SQL>conn sys/oracle assysdbaConnected.SQL> create table fly_sys asselect * from dba_objects;Table created.SQL> select count(*) from fly_sys;COUNT(*)----------70525

3、删除所有数据文件和归档日志
3.1. fly.sh脚本用来查看数据文件和归档文件以及组装删除这些文件的脚本fly_datafile.sh,注意fly.sh脚本包含fly.sql脚本

123456789101112131415161718192021222324252627282930313233oracle@fly007:~> cat fly.sh#!/bin/bashrman target sys/oracle<<EOF 1>/dev/nullcrosscheck archivelog all;deletenoprompt expired archivelog all;quitEOFif[ -f fly_datafile.sh ];thenrm fly_datafile.shfisqlplus /nolog<<EOFconn sys/oracle assysdba@fly.sqlEOFchmod u+x fly_datafile.shoracle@fly007:~> cat fly.sqlsetecho oncol file_name format a80col name format a100setlinesize 200select file_name from dba_data_files;select name from v$archived_log where name isnot null;setecho offsetheading offsetnewpage nonesetfeedback offsettermout offsettrimspool on --去除重定向（spool）输出每行的拖尾空格，缺省为offspool fly_datafile.shselect 'rm'||' '||file_name from dba_data_files;select 'rm'||' '||name from v$archived_log where name isnot null;spool offquit

3.2. 以oracle执行fly.sh脚本,查看所有数据文件和归档日志，以及生成fly_datafile.sh脚本

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647oracle@fly007:~> ./fly.shSQL*Plus: Release 11.1.0.7.0- Production on Mon Dec 911:29:282013Copyright (c) 1982, 2008, Oracle. All rights reserved.SQL> Connected.SQL> SQL> col file_name format a80SQL> col name format a100SQL> setlinesize 200SQL> select file_name from dba_data_files;FILE_NAME--------------------------------------------------------------------------------/home/oracle/oradata/fly/datafiles/fly01.dbf/home/oracle/oradata/APPLE/datafile/users02.dbf/home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf/home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf/home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf/home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf/home/oracle/oradata/APPLE/datafile/example02.dbf/home/oracle/oradata/APPLE/datafile/example03.dbf/home/oracle/oradata/APPLE/datafile/example04.dbf/home/oracle/oradata/APPLE/datafile/example05.dbf/home/oracle/oradata/APPLE/datafile/system03.dbf/home/oracle/oradata/APPLE/datafile/sysaux03.dbf13rows selected.SQL> select name from v$archived_log where name isnot null;NAME----------------------------------------------------------------------------------------------------/archivelog/1_8_833412001.dbf/archivelog/1_9_833412001.dbf/archivelog/1_10_833412001.dbf/archivelog/1_11_833412001.dbf/archivelog/1_12_833412001.dbf/archivelog/1_13_833412001.dbf/archivelog/1_14_833412001.dbf/archivelog/1_15_833412001.dbf/archivelog/1_16_833412001.dbf/archivelog/1_17_833412001.dbf/archivelog/1_18_833412001.dbf/archivelog/1_19_833412001.dbf/archivelog/1_20_833412001.dbf/archivelog/1_21_833412001.dbf14rows selected.SQL> setecho offDisconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0- 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing optionsoracle@fly007:~>

3.3.查看fly_datafile.sh脚本，并执行fly_datafile.sh脚本删除所有数据文件和归档日志

12345678910111213oracle@fly007:~> cat fly_datafile.shrm /home/oracle/oradata/fly/datafiles/fly01.dbfrm /home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbfrm /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbfrm /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbfrm /home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbfrm /home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbfrm /archivelog/1_513_809364352.dbfrm /archivelog/1_514_809364352.dbfrm /archivelog/1_512_809364352.dbfrm /archivelog/1_511_809364352.dbforacle@fly007:~> ./fly_datafile.shoracle@fly007:~>

4、用户查询数据以及创建表报错

123456789101112131415161718192021SQL> conn fly/flyConnected.SQL> select count(*) from fly;select count(*) from fly*ERROR at line 1:ORA-01116: error inopening database file 7ORA-01110: data file 7: '/home/oracle/oradata/fly/datafiles/fly01.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> create table fly008 asselect * from dba_objects;create table fly008 asselect * from dba_objects*ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-01116: error inopening database file 1ORA-01110: data file 1: '/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3

5、关闭监听,KILL所有通过监听连接过来的连接

1234567oracle@fly007:~> lsnrctl stopLSNRCTL forLinux: Version 11.1.0.7.0- Production on 09-DEC-201312:50:52Copyright (c) 1991, 2008, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.46.200.5)(PORT=1521)))The command completed successfullyoracle@fly007:~> ps aux | grep LOCAL=NO | grep -v grep | awk '{print $2}'| xargs kill -9oracle@fly007:~>

6、查看dbwr进程，进入该进程的fd（文件描述符）目录，查看哪些文件被删除了

123456789101112131415161718oracle@fly007:~> ps aux | grep dbw0 | grep -v greporacle 230380.08.03431484646716? Ss 10:180:03ora_dbw0_appleoracle@fly007:~> cd /proc/23038/fdoracle@fly007:/proc/23038/fd> ls -l | grep deletelrwx------ 1oracle oinstall 642013-12-0911:3610-> /home/oracle/product/11g/db/dbs/lkinstapple (deleted)lrwx------ 1oracle oinstall 642013-12-0911:3622-> /home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0911:3623-> /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0911:3624-> /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0911:3625-> /home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0911:3626-> /home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0911:3627-> /home/oracle/oradata/fly/datafiles/fly01.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0911:3628-> /home/oracle/oradata/APPLE/datafile/example02.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0911:3629-> /home/oracle/oradata/APPLE/datafile/example03.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0911:3630-> /home/oracle/oradata/APPLE/datafile/example04.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0911:3631-> /home/oracle/oradata/APPLE/datafile/example05.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0911:3632-> /home/oracle/oradata/APPLE/datafile/users02.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0911:3633-> /home/oracle/oradata/APPLE/datafile/system03.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0911:3634-> /home/oracle/oradata/APPLE/datafile/sysaux03.dbf (deleted)

7、拼出批量拷贝数据文件到原来位置的脚本

123456789101112131415161718192021222324252627282930oracle@fly007:/proc/23038/fd> ls -l | grep delete| grep dbf | awk '{print $8,$10}'> /tmp/copy_datafile.shoracle@fly007:/proc/23038/fd> cat /tmp/copy_datafile.sh22/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf23/home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf24/home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf25/home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf26/home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf27/home/oracle/oradata/fly/datafiles/fly01.dbf28/home/oracle/oradata/APPLE/datafile/example02.dbf29/home/oracle/oradata/APPLE/datafile/example03.dbf30/home/oracle/oradata/APPLE/datafile/example04.dbf31/home/oracle/oradata/APPLE/datafile/example05.dbf32/home/oracle/oradata/APPLE/datafile/users02.dbf33/home/oracle/oradata/APPLE/datafile/system03.dbf34/home/oracle/oradata/APPLE/datafile/sysaux03.dbforacle@fly007:/proc/23038/fd> sed -i -e "s/^/cp /g"-e "s/$/\ \&/g"/tmp/copy_datafile.shoracle@fly007:/proc/23038/fd> cat /tmp/copy_datafile.shcp 22/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf &cp 23/home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf &cp 24/home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf &cp 25/home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf &cp 26/home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf &cp 27/home/oracle/oradata/fly/datafiles/fly01.dbf &cp 28/home/oracle/oradata/APPLE/datafile/example02.dbf &cp 29/home/oracle/oradata/APPLE/datafile/example03.dbf &cp 30/home/oracle/oradata/APPLE/datafile/example04.dbf &cp 31/home/oracle/oradata/APPLE/datafile/example05.dbf &cp 32/home/oracle/oradata/APPLE/datafile/users02.dbf &cp 33/home/oracle/oradata/APPLE/datafile/system03.dbf &cp 34/home/oracle/oradata/APPLE/datafile/sysaux03.dbf &

8、执行/tmp/copy_datafile.sh脚本，并行的将数据文件拷贝回原位置

123oracle@fly007:/proc/22297/fd>chmod u+x /tmp/copy_datafile.shoracle@fly007:/proc/22297/fd> /tmp/copy_datafile.shoracle@fly007:/proc/22297/fd>

9、查看拷贝进程是否结束

12345678910oracle@fly007:/proc/22297/fd> watch -n 1"ps aux | grep cp | grep -v grep"root 450.00.000? S< Jul24 0:00[kacpid]root 460.00.000? S< Jul24 0:00[kacpi_notify]root 28580.00.02684520? Ss Jul24 0:00/sbin/acpidroot 30520.00.06080700? S Jul24 0:00hald-addon-acpiroot 74440.00.0203562088? S Jul24 0:01/usr/sbin/powersaved -d -f /var/run/acpid.socket -v 3oracle 124491.00.05952712pts/1D 10:300:00cp 22/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbforacle 124501.20.05948712pts/1D 10:300:00cp 23/home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbforacle 124512.20.05952712pts/1D 10:300:00cp 24/home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbforacle 124531.20.05948712pts/1D 10:300:00cp 26/home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf

10、拷贝结束，确认在线日志，控制文件没有被删除后，关闭数据库，进行recover操作，数据完全恢复

12345678910111213141516171819202122232425262728SQL> conn sys/oracle assysdbaConnected.SQL> shutdown immediateORA-01122: database file 1failed verification checkORA-01110: data file 1: '/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf'ORA-01208: data file isan old version - not accessing current versionSQL> shutdown abort //如果不能正常关闭，则只能shutdown abort了ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 3240239104bytesFixed Size 2164048bytesVariable Size 2499807920bytesDatabase Buffers 721420288bytesRedo Buffers 16846848bytesDatabase mounted.SQL> recover database;Media recovery complete.SQL> alter database open;Database altered.SQL> select count(*) from fly_sys;COUNT(*)----------70525SQL> select count(*) from fly.fly;COUNT(*)----------

恢复的原理，请查看关于该恢复主题的第一篇博文：
http://fly1116.blog.51cto.com/8301004/1337681
在数据库非归档状态，没有任何的备份情况下，通过操作系统命令rm，误删除了某一个表空间下所有数据文件，要如何恢复呢
1、数据库版本11.1.0.7.0和数据库处于非归档状态

1234567891011121314SQL> 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.0ProductionTNS forLinux: Version 11.1.0.7.0- ProductionNLSRTL Version 11.1.0.7.0- ProductionSQL> archive log list;Database log mode No Archive ModeAutomatic archival DisabledArchive destination /archivelogOldest online log sequence 31Current log sequence 33

2、在fly用户下创建fly表,在sys用户下创建表fly_sys,表记录分别为：2256800和70525

123456789101112131415161718192021222324252627282930SQL> conn fly/flyConnected.SQL> create table fly asselect * from dba_objects;Table created.SQL> insert into fly select * from fly;70525rows created.SQL> /141050rows created.SQL> /282100rows created.SQL> /564200rows created.SQL> /1128400rows created.SQL> commit;Commit complete.SQL> select count(*) from fly;COUNT(*)----------2256800SQL> conn sys/oracle assysdbaConnected.SQL> drop table fly_sys purge;Table dropped.SQL> create table fly_sys asselect * from dba_objects;Table created.SQL> select count(*) from fly_sys;COUNT(*)----------70525

3、删除fly表空间下的数据文件

12345678910111213SQL> col file_name format a80SQL> select file_name from dba_data_files where tablespace_name='FLY';FILE_NAME--------------------------------------------------------------------------------/home/oracle/oradata/fly/datafiles/fly01.dbfSQL> col file_name format a60SQL> select file_name from dba_data_files where tablespace_name='FLY';FILE_NAME------------------------------------------------------------/home/oracle/oradata/fly/datafiles/fly01.dbfSQL> host rm /home/oracle/oradata/fly/datafiles/fly01.dbfSQL> host ls /home/oracle/oradata/fly/datafiles/fly01.dbfls: /home/oracle/oradata/fly/datafiles/fly01.dbf: No such file or directory

4、fly用户创建表报错

123456789SQL> create table fly008 asselect * from fly;create table fly008 asselect * from fly*ERROR at line 1:ORA-01116: error inopening database file 7ORA-01110: data file 7: '/home/oracle/oradata/fly/datafiles/fly01.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3

5、停止监听，kill通过监听连接过来的进程

1234567oracle@fly007:~> lsnrctl stopLSNRCTL forLinux: Version 11.1.0.7.0- Production on 09-DEC-201317:02:56Copyright (c) 1991, 2008, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.46.200.5)(PORT=1521)))The command completed successfullyoracle@fly007:~> ps aux | grep LOCAL=NO | grep -v grep | awk '{print $2}'| xargs kill -9oracle@fly007:~>

6、查看dbw0进程pid，查看哪些数据文件被删除了,拷贝被删除的数据文件到原来的位置

12345678oracle@fly007:~> ps aux | grep dbw0 | grep -v greporacle 12570.14.33430848352280? Ss 15:360:01ora_dbw0_appleoracle@fly007:~> cd /proc/1257/fdoracle@fly007:/proc/1257/fd> ls -l | grep deletelrwx------ 1oracle oinstall 642013-12-0915:5510-> /home/oracle/product/11g/db/dbs/lkinstapple (deleted)lrwx------ 1oracle oinstall 642013-12-0915:5527-> /home/oracle/oradata/fly/datafiles/fly01.dbf (deleted)oracle@fly007:/proc/1257/fd> cp 27/home/oracle/oradata/fly/datafiles/fly01.dbforacle@fly007:/proc/1257/fd>

7、对数据文件进行offline,然后recover datafile

123456789101112SQL> conn sys/oracle assysdbaConnected.SQL> alter database datafile '/home/oracle/oradata/fly/datafiles/fly01.dbf'offline; //由于未开归档，不能进行offline的操作alter database datafile '/home/oracle/oradata/fly/datafiles/fly01.dbf'offline*ERROR at line 1:ORA-01145: offline immediate disallowed unless media recovery enabledSQL> alter database datafile '/home/oracle/oradata/fly/datafiles/fly01.dbf'offline drop; //需要使用offline dropDatabase altered.SQL> recover datafile '/home/oracle/oradata/fly/datafiles/fly01.dbf';Media recovery complete.SQL> alter database datafile '/home/oracle/oradata/fly/datafiles/fly01.dbf'online;

8、查看表的数据，完全恢复

12345678910111213SQL> conn fly/flyConnected.SQL> select count(*) from fly;COUNT(*)----------2256800SQL> conn sys/oracle assysdbaConnected.SQL> select count(*) from fly_sys;COUNT(*)----------70525SQL>

恢复的原理，请查看关于该恢复主题的第一篇博文：
http://fly1116.blog.51cto.com/8301004/1337681
恢复过程使用的fly.sh脚本，及其他脚本的简要介绍，请看关于该恢复主题的第二篇博文：
http://fly1116.blog.51cto.com/8301004/1338316
在数据库非归档状态，没有任何的备份情况下，通过操作系统命令rm，误删除了所有数据文件，要如何恢复呢
1、数据库版本11.1.0.7.0和数据库处于非归档状态

123456789101112131415SQL> 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.0ProductionTNS forLinux: Version 11.1.0.7.0- ProductionNLSRTL Version 11.1.0.7.0- ProductionSQL> archive log list;Database log mode No Archive ModeAutomatic archival DisabledArchive destination /archivelogOldest online log sequence 45Current log sequence 47SQL>

2、在fly用户下创建fly表,表记录为：2256800

1234567891011121314151617181920SQL> conn fly/flyConnected.SQL> create table fly asselect * from dba_objects;Table created.SQL> insert into fly select * from fly;70525rows created.SQL> /141050rows created.SQL> /282100rows created.SQL> /564200rows created.SQL> /1128400rows created.SQL> commit;Commit complete.SQL> select count(*) from fly;COUNT(*)----------2256800

3、查看所有数据文件，以及删除所有数据文件

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879oracle@fly007:~> cat fly.sh#!/bin/bashrman target sys/oracle<<EOF 1>/dev/nullcrosscheck archivelog all;deletenoprompt expired archivelog all;quitEOFif[ -f fly_datafile.sh ];thenrm fly_datafile.shfisqlplus /nolog<<EOFconn sys/oracle assysdba@fly.sqlEOFchmod u+x fly_datafile.shoracle@fly007:~> cat fly.sqlsetecho oncol file_name format a80col name format a100setlinesize 200select file_name from dba_data_files;select name from v$archived_log where name isnot null;setecho offsetheading offsetnewpage nonesetfeedback offsettermout offsettrimspool onspool fly_datafile.shselect 'rm'||' '||file_name from dba_data_files;select 'rm'||' '||name from v$archived_log where name isnot null;spool offquitoracle@fly007:~> ./fly.shSQL*Plus: Release 11.1.0.7.0- Production on Mon Dec 921:11:292013Copyright (c) 1982, 2008, Oracle. All rights reserved.SQL> Connected.SQL> SQL> col file_name format a80SQL> col name format a100SQL> setlinesize 200SQL> select file_name from dba_data_files;FILE_NAME--------------------------------------------------------------------------------/home/oracle/oradata/fly/datafiles/fly01.dbf/home/oracle/oradata/APPLE/datafile/users02.dbf/home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf/home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf/home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf/home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf/home/oracle/oradata/APPLE/datafile/example02.dbf/home/oracle/oradata/APPLE/datafile/example03.dbf/home/oracle/oradata/APPLE/datafile/example04.dbf/home/oracle/oradata/APPLE/datafile/example05.dbf/home/oracle/oradata/APPLE/datafile/system03.dbf/home/oracle/oradata/APPLE/datafile/sysaux03.dbf13rows selected.SQL> select name from v$archived_log where name isnot null;no rows selectedSQL> setecho offDisconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0- 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing optionsoracle@fly007:~> cat fly_datafile.shrm /home/oracle/oradata/fly/datafiles/fly01.dbfrm /home/oracle/oradata/APPLE/datafile/users02.dbfrm /home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbfrm /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbfrm /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbfrm /home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbfrm /home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbfrm /home/oracle/oradata/APPLE/datafile/example02.dbfrm /home/oracle/oradata/APPLE/datafile/example03.dbfrm /home/oracle/oradata/APPLE/datafile/example04.dbfrm /home/oracle/oradata/APPLE/datafile/example05.dbfrm /home/oracle/oradata/APPLE/datafile/system03.dbfrm /home/oracle/oradata/APPLE/datafile/sysaux03.dbforacle@fly007:~> ./fly_datafile.shoracle@fly007:~>

4、fly用户创建表报错

12345678910111213141516oracle@fly007:~> sqlplus /nologSQL*Plus: Release 11.1.0.7.0- Production on Mon Dec 921:11:522013Copyright (c) 1982, 2008, Oracle. All rights reserved.SQL> conn sys/oracle assysdbaConnected.SQL> conn fly/flyConnected.SQL> create table fly008 asselect * from dba_objects;create table fly008 asselect * from dba_objects*ERROR at line 1:ORA-01116: error inopening database file 7ORA-01110: data file 7: '/home/oracle/oradata/fly/datafiles/fly01.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3

5、停止监听，kill通过监听连接过来的进程

1234567oracle@fly007:~> lsnrctl stopLSNRCTL forLinux: Version 11.1.0.7.0- Production on 09-DEC-201321:29:29Copyright (c) 1991, 2008, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.46.200.5)(PORT=1521)))The command completed successfullyoracle@fly007:~> ps aux | grep LOCAL=NO | grep -v grep | awk '{print $2}'| xargs kill -9oracle@fly007:~>

6、查看dbw0进程pid，查看哪些数据文件被删除了,拷贝被删除的数据文件到原来的位置

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162oracle@fly007:~> ps aux | grep dbw0 | grep -v greporacle 12570.04.53431904366532? Ss 15:360:01ora_dbw0_appleoracle@fly007:~> cd /proc/1257/fdoracle@fly007:/proc/1257/fd> ls -l | grep deletelrwx------ 1oracle oinstall 642013-12-0915:5510-> /home/oracle/product/11g/db/dbs/lkinstapple (deleted)lrwx------ 1oracle oinstall 642013-12-0915:5522-> /home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0915:5523-> /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0915:5524-> /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0915:5525-> /home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0915:5526-> /home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0915:5528-> /home/oracle/oradata/APPLE/datafile/example02.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0915:5529-> /home/oracle/oradata/APPLE/datafile/example03.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0915:5530-> /home/oracle/oradata/APPLE/datafile/example04.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0915:5531-> /home/oracle/oradata/APPLE/datafile/example05.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0915:5532-> /home/oracle/oradata/APPLE/datafile/users02.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0915:5533-> /home/oracle/oradata/APPLE/datafile/system03.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0915:5534-> /home/oracle/oradata/APPLE/datafile/sysaux03.dbf (deleted)lrwx------ 1oracle oinstall 642013-12-0921:1537-> /home/oracle/oradata/fly/datafiles/fly01.dbf (deleted)oracle@fly007:/proc/1257/fd> ls -l | grep delete| grep dbf | awk '{print $8,$10}'> /tmp/copy_datafile.shoracle@fly007:/proc/1257/fd> cat /tmp/copy_datafile.sh22/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf23/home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf24/home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf25/home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf26/home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf28/home/oracle/oradata/APPLE/datafile/example02.dbf29/home/oracle/oradata/APPLE/datafile/example03.dbf30/home/oracle/oradata/APPLE/datafile/example04.dbf31/home/oracle/oradata/APPLE/datafile/example05.dbf32/home/oracle/oradata/APPLE/datafile/users02.dbf33/home/oracle/oradata/APPLE/datafile/system03.dbf34/home/oracle/oradata/APPLE/datafile/sysaux03.dbf37/home/oracle/oradata/fly/datafiles/fly01.dbforacle@fly007:/proc/1257/fd> sed -i -e "s/^/cp /g"-e "s/$/\ \&/g"/tmp/copy_datafile.shoracle@fly007:/proc/1257/fd> cat /tmp/copy_datafile.shcp 22/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf &cp 23/home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf &cp 24/home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf &cp 25/home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf &cp 26/home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf &cp 28/home/oracle/oradata/APPLE/datafile/example02.dbf &cp 29/home/oracle/oradata/APPLE/datafile/example03.dbf &cp 30/home/oracle/oradata/APPLE/datafile/example04.dbf &cp 31/home/oracle/oradata/APPLE/datafile/example05.dbf &cp 32/home/oracle/oradata/APPLE/datafile/users02.dbf &cp 33/home/oracle/oradata/APPLE/datafile/system03.dbf &cp 34/home/oracle/oradata/APPLE/datafile/sysaux03.dbf &cp 37/home/oracle/oradata/fly/datafiles/fly01.dbf &oracle@fly007:/proc/1257/fd> chmod u+x /tmp/copy_datafile.shoracle@fly007:/proc/1257/fd> /tmp/copy_datafile.shoracle@fly007:/proc/1257/fd> watch -n 1"ps aux | grep cp"Every 1

.0s: ps aux | grep cp                                                                                                         Mon Dec

921:18:302013root 450.00.000? S< Jul24 0:00[kacpid]root 460.00.000? S< Jul24 0:00[kacpi_notify]root 28580.00.02684520? Ss Jul24 0:00/sbin/acpidroot 30520.00.06080700? S Jul24 0:00hald-addon-acpiroot 74440.00.0203562088? S Jul24 0:01/usr/sbin/powersaved -d -f /var/run/acpid.socket -v 3oracle 101232.30.05948712pts/0D 21:160:02cp 23/home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbforacle 101242.50.05948712pts/0D 21:160:02cp 24/home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbforacle 102020.00.064001356pts/0S+ 21:180:00watch -n 1ps aux | grep cporacle 102060.00.091681532pts/0S+ 21:180:00sh -c ps aux | grep cporacle 102080.00.03976804pts/0S+ 21:180:00grep cp

7、确认在线日志和控制文件没有丢失后，关闭数据库，进行recover database的操作

1234567891011121314151617181920212223SQL> conn sys/oracle assysdbaConnected.SQL> shutdown immediateORA-01122: database file 1failed verification checkORA-01110: data file 1: '/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf'ORA-01208: data file isan old version - not accessing current versionSQL> startup mountORA-01081: cannot start already-running ORACLE - shut it down firstSQL> shutdown abortORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 3240239104bytesFixed Size 2164048bytesVariable Size 2499807920bytesDatabase Buffers 721420288bytesRedo Buffers 16846848bytesDatabase mounted.SQL> recover database;Media recovery complete.SQL> alter database open;Database altered.SQL>

8、查看表的数据，完全恢复

123456SQL> conn fly/flyConnected.SQL> select count(*) from fly;COUNT(*)----------2256800

本文出自 “斜阳悠悠寸草心” 博客，请务必保留此出处http://fly1116.blog.51cto.com/8301004/1337681