No archiving, no backup, rm accidentally deleted all data files for recovery (4)

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.