For how recovery works, see the first blog on the Restoration topic:
Http://fly1116.blog.51cto.com/8301004/1337681
For a brief introduction to the fly. sh script used in the restoration process and other scripts, see the second blog on the Restoration topic:
Http://fly1116.blog.51cto.com/8301004/1338316
If the database is not in the archive State and there is no backup, you can use the operating system command rm to accidentally delete all data files. How can this problem be recovered?
1. The database version is 11.1.0.7.0 and the database is not archived.
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. Create a fly table under the fly user. The table record is: 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. View and delete all data files
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. An error is reported when a fly user creates a table.
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. Stop the listener and kill the processes connected by the listener.
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. Check the dbw0 process pid, and check which data files have been deleted. Copy the deleted data files to their original locations.
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. Shut down the database and perform the recover database operation after confirming that the online log and control file are not lost.
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. view the table data and restore it completely
SQL> conn fly/flyConnected.SQL> select count(*) from fly;COUNT(*)----------2256800
This article from the "Yang youyoucuncaoxin" blog, please be sure to keep this source http://fly1116.blog.51cto.com/8301004/1338612