無歸檔,無備份，rm誤刪除所有資料檔案恢複(四）

     恢複的原理，請查看關於該恢複主題的第一篇博文：
http://fly1116.blog.51cto.com/8301004/1337681
      恢複過程使用的fly.sh指令碼，及其他指令碼的簡要介紹，請看關於該恢複主題的第二篇博文：
http://fly1116.blog.51cto.com/8301004/1338316
      在資料庫非歸檔狀態，沒有任何的備份情況下，通過作業系統命令rm，誤刪除了所有資料檔案，要如何恢複呢
      1、資料庫版本11.1.0.7.0和資料庫處於非歸檔狀態

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              No Archive ModeAutomatic archival             DisabledArchive destination            /archivelogOldest online log sequence     45Current log sequence           47SQL>

     2、在fly使用者下建立fly表,表記錄為：2256800

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

   3、查看所有資料檔案，以及刪除所有資料檔案

oracle@fly007:~> cat fly.sh#!/bin/bashrman target sys/oracle<<EOF 1>/dev/nullcrosscheck archivelog all;delete noprompt expired archivelog all;quitEOFif [ -f fly_datafile.sh ];thenrm fly_datafile.shfisqlplus /nolog<<EOFconn sys/oracle as sysdba@fly.sqlEOFchmod u+x fly_datafile.shoracle@fly007:~> cat fly.sqlset echo oncol file_name format a80col name format a100set linesize 200select file_name from dba_data_files;select name from v$archived_log where name is not null;set echo offset heading offset newpage noneset feedback offset termout offset trimspool onspool fly_datafile.shselect 'rm'||' '||file_name from dba_data_files;select 'rm'||' '||name from v$archived_log where name is not null;spool offquitoracle@fly007:~> ./fly.shSQL*Plus: Release 11.1.0.7.0 - Production on Mon Dec 9 21:11:29 2013Copyright (c) 1982, 2008, Oracle.  All rights reserved.SQL> Connected.SQL> SQL> col file_name format a80SQL> col name format a100SQL> set linesize 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.dbf13 rows selected.SQL> select name from v$archived_log where name is not null;no rows selectedSQL> set echo 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使用者建立表報錯

oracle@fly007:~> sqlplus /nologSQL*Plus: Release 11.1.0.7.0 - Production on Mon Dec 9 21:11:52 2013Copyright (c) 1982, 2008, Oracle.  All rights reserved.SQL> conn sys/oracle as sysdbaConnected.SQL> conn fly/flyConnected.SQL> create table fly008 as select * from dba_objects;create table fly008 as select * from dba_objects*ERROR at line 1:ORA-01116: error in opening 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通過監聽串連過來的進程

oracle@fly007:~> lsnrctl stopLSNRCTL for Linux: Version 11.1.0.7.0 - Production on 09-DEC-2013 21: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，查看哪些資料檔案被刪除了,拷貝被刪除的資料檔案到原來的位置

oracle@fly007:~> ps aux | grep dbw0 | grep -v greporacle    1257  0.0  4.5 3431904 366532 ?      Ss   15:36   0:01 ora_dbw0_appleoracle@fly007:~> cd /proc/1257/fdoracle@fly007:/proc/1257/fd> ls -l | grep deletelrwx------ 1 oracle oinstall 64 2013-12-09 15:55 10 -> /home/oracle/product/11g/db/dbs/lkinstapple (deleted)lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 22 -> /home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf (deleted)lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 23 -> /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf (deleted)lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 24 -> /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf (deleted)lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 25 -> /home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf (deleted)lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 26 -> /home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf (deleted)lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 28 -> /home/oracle/oradata/APPLE/datafile/example02.dbf (deleted)lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 29 -> /home/oracle/oradata/APPLE/datafile/example03.dbf (deleted)lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 30 -> /home/oracle/oradata/APPLE/datafile/example04.dbf (deleted)lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 31 -> /home/oracle/oradata/APPLE/datafile/example05.dbf (deleted)lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 32 -> /home/oracle/oradata/APPLE/datafile/users02.dbf (deleted)lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 33 -> /home/oracle/oradata/APPLE/datafile/system03.dbf (deleted)lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 34 -> /home/oracle/oradata/APPLE/datafile/sysaux03.dbf (deleted)lrwx------ 1 oracle oinstall 64 2013-12-09 21:15 37 -> /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  9 21:18:30 2013root        45  0.0  0.0      0     0 ?        S<   Jul24   0:00 [kacpid]root        46  0.0  0.0      0     0 ?        S<   Jul24   0:00 [kacpi_notify]root      2858  0.0  0.0   2684   520 ?        Ss   Jul24   0:00 /sbin/acpidroot      3052  0.0  0.0   6080   700 ?        S    Jul24   0:00 hald-addon-acpiroot      7444  0.0  0.0  20356  2088 ?        S    Jul24   0:01 /usr/sbin/powersaved -d -f /var/run/acpid.socket -v 3oracle   10123  2.3  0.0   5948   712 pts/0    D    21:16   0:02 cp 23 /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbforacle   10124  2.5  0.0   5948   712 pts/0    D    21:16   0:02 cp 24 /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbforacle   10202  0.0  0.0   6400  1356 pts/0    S+   21:18   0:00 watch -n 1 ps aux | grep cporacle   10206  0.0  0.0   9168  1532 pts/0    S+   21:18   0:00 sh -c ps aux | grep cporacle   10208  0.0  0.0   3976   804 pts/0    S+   21:18   0:00 grep cp

     7、確認線上日誌和控制檔案沒有丟失後，關閉資料庫，進行recover database的操作

SQL> conn sys/oracle as sysdbaConnected.SQL> shutdown immediateORA-01122: database file 1 failed verification checkORA-01110: data file 1: '/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf'ORA-01208: data file is an 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 3240239104 bytesFixed Size                  2164048 bytesVariable Size            2499807920 bytesDatabase Buffers          721420288 bytesRedo Buffers               16846848 bytesDatabase mounted.SQL> recover database;Media recovery complete.SQL> alter database open;Database altered.SQL>

      8、查看錶的資料，完全恢複

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

本文出自 “斜陽悠悠寸草心” 部落格，請務必保留此出處http://fly1116.blog.51cto.com/8301004/1338612