Accidental deletion of Oracle data file Recovery

Despite repeated caution, there is a human error deleting the database file. Simple steps, perhaps the key moment can be a big help.

Environment: CENTOS 6.5

Analog error Operation: The database is in normal operation, the manual direct RM dropped the data file. --1. Test environment Situation: $ Cat/etc/redhat-releasecentos Release 6.5 (Final) select file_name from dba_data_files;/u01/app/oracle/ oradata/orcl/test.dbf$ sqlplus/as sysdbasql*plus:release 11.2.0.4.0 Production on Thu March 5 15:55:14 2015Copyright (c) 1  982, Oracle. All rights reserved. Connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit productionwith the partitioning , OLAP, Data Mining and Real application testing optionssql> alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';     Open Database Archive archive log list; database log mode non-archive mode AutoArchive disable archive endpoint use_db_recovery_file_dest oldest online log sequence 3002 Current log sequence 3004mkdir/u01/archalter system set log_archive_dest_1= ' Location=/u01/arch ' scope=spfile; sql> shutdown immediate database has been closed. The database has been uninstalled. The ORACLE routine has been closed. Sql> startup Mount;oracle Routine has been started. Total System Global area 1.0088E+10 bytesfixed size 2261928 bytesvariable size 1644170328 bytesdatabase buffers 8422162 432 Bytesredo BufFers 19595264 bytes Database is loaded. sql> ALTER DATABASE Archivelog;   sql> archive log list; database log mode archive Mode AutoArchive enable archive endpoint/u01/arch oldest online log sequence 3002 next archive log sequence 3004 Current log sequence 3004sql> alter DATABASE open; Sql> Select Open_mode from V$database;open_mode------------------------------------------------------------READ Write--2. New test Data select file_name from Dba_data_files; file_name--------------------------------------------------------------------------------/u01/app/oracle/ Oradata/orcl/users01.dbfsql> Create tablespace wind datafile '/u01/app/oracle/oradata/orcl/wind01.dbf ' size 200m; Sql> create user wind identified by wind01 default Tablespace wind; Sql> Grant CONNECT,RESOURCE,DBA to Wind;        $ sqlplus wind/wind01 CREATE TABLE t1 (SID int not null primary key,sname VARCHAR2 (Ten)) Tablespace wind;--Loop Import data declare        MAXRECORDS constant int:=100000;    I int: = 1; Begin for I in 1..maxrecords loop insert into T1 values (i, ' Ocpyang ');    End Loop; Dbms_output.put_line (' Success in data entry!    ');    Commit End  /select Count (*) from T1; COUNT (*)----------100000--3. Simulate delete data sql> col tablespace_name for a20sql> select Tablespace_name,status from dba_t Ablespaces;     Tablespace_name STATUS-----------------------------------------------SYSTEM onlinesysaux ONLINEUNDOTBS1 Onlinetemp onlineusers onlinettspace Onlineocpyang onlineocpyangindex Onlinelobocpyang ONLINELOB      OCPYANG01 ONLINELOBOCPYANG02 onlinetablespace_name STATUS-----------------------------------------------Wind onlinerm-rf/u01/app/oracle/oradata/orcl/wind01.dbfls/u01/app/oracle/oradata/orcl/| grep windsql> Show user;  USER is "Wind" sql> Select COUNT (*) from T1; COUNT (*)----------100000sql> desc T1 is the name empty? Type-----------------------------------------------------------------------------SID not NULL number (SNAME VA)RCHAR2 (sql>) Delete from T1 where sid>99000; has deleted 1000 rows. Sql> commit; commit completed.  Sql> Select COUNT (*) from T1;        COUNT (*)----------99000--4. Recovery ps-eaf|grep dbw0 |grep-v greporacle 1928 1 0 15:59? 00:00:00 ora_dbw0_orclsql> col tablespace_name for a20sql> select Tablespace_name,status from Dba_tablespaces;     Tablespace_name STATUS-----------------------------------------------SYSTEM onlinesysaux ONLINEUNDOTBS1 Onlinetemp onlineusers onlinettspace Onlineocpyang onlineocpyangindex Onlinelobocpyang ONLINELOB      OCPYANG01 ONLINELOBOCPYANG02 onlinetablespace_name STATUS-----------------------------------------------Wind  ONLINECD/PROC/1928/FDLLLR-X------1 Oracle Oinstall 5 16:20 0-/dev/nulll-wx------1 Oracle Oinstall 64 Mar 5 16:20 1-/dev/nulllrwx------1 Oracle Oinstall 5 16:20/u01/app/oracle/product/11.2.0/db_1/d BS/LKORCLLR-X------1 Oracle Oinstall 5 16:20 11-/U01/APP/ORACLE/PRODUCT/11.2.0/DB_1/RDBMS/MESG/ORAUS.MSBL-WX------1 Oracle Oinstall 5 16:20 2/dev/ NULLLRWX------1 Oracle Oinstall 5 16:20/u01/app/oracle/oradata/orcl/control01.ctllrwx------1 Oracle O   Install 5 16:20 257-/u01/app/oracle/fast_recovery_area/orcl/control02.ctllrwx------1 Oracle Oinstall Mar 5 16:20 258-/u01/app/oracle/oradata/orcl/system01.dbflrwx------1 Oracle Oinstall-5 16:20 259 PP/ORACLE/ORADATA/ORCL/SYSAUX01.DBFLRWX------1 Oracle Oinstall 5 16:20 260-/u01/app/oracle/oradata/orcl/un DOTBS01.DBFLRWX------1 Oracle Oinstall 5 16:20 261/u01/app/oracle/oradata/orcl/users01.dbflrwx------1 ora CLE Oinstall 5 16:20 262/u01/app/oracle/oradata/orcl/ttspace01.dbflrwx------1 Oracle Oinstall April 5 16 : 263-/U01/APP/ORACLE/ORADATA/ORCL/OCPYANG01.DBFLRWX------1 Oracle Oinstall 5 16:20/u01/app/or Acle/oradata/orcl/ocpyangINDEX01.DBFLRWX------1 Oracle Oinstall 5 16:20 265-/u01/app/oracle/oradata/orcl/lobocpyang01.dbflrwx------ 1 Oracle Oinstall 5 16:20 266-/u01/app/oracle/oradata/orcl/lobocpyang0101.dbflrwx------1 Oracle Oinstall 6 4 Mar 5 16:20 267-/u01/app/oracle/oradata/orcl/lobocpyang0202.dbflrwx------1 Oracle Oinstall 5 16:20 268- >/u01/app/oracle/oradata/orcl/temp01.dbflrwx------1 Oracle Oinstall 5 16:20 269-/u01/app/oracle/oradata /ORCL/WIND01.DBF (Deleted)--note lr-x------1 Oracle Oinstall 5 16:20 3/dev/nulllr-x------1 Oracle Oinstall 6  4 Mar 5 16:20 4-/dev/nulllr-x------1 Oracle Oinstall 5 16:20 5/dev/nulllr-x------1 Oracle Oinstall 5 16:20 6-/u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msblr-x------1 Oracle Oinstall 5 16   : 7-/proc/1928/fdlr-x------1 Oracle Oinstall 5 16:20 8-/dev/zerolrwx------1 Oracle Oinstall Mar 5 16:20 9-/u01/apP/oracle/product/11.2.0/db_1/dbs/hc_orcl.dat direct CP The handle file name back to the original location: $ pwd/proc/1928/fdcp/proc/1928/fd/269/u01/app/oracle /oradata/orcl/wind01.dbfnote: Since the database is always open and the data is constantly changing, the SCN will change constantly, and the current information of CP data file and database is obviously inconsistent. The data file needs to be recover.--offline data file ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/ORCL/WIND01.DBF ' offline;select count (* ) from T1; Error 1th: ORA-00376: Unable to read file 11ora-01110: Data file: '/u01/app/oracle/oradata/orcl/wind01.dbf '--Recover data file recover DataFile '/u01/app/oracle/oradata/orcl/wind01.dbf ';--online data file ALTER DATABASE DataFile '/u01/app/oracle/oradata/orcl/  Wind01.dbf ' Online;select count (*) from T1; COUNT (*)----------99000 successfully recovered.----5. Simple Verification ls/u01/app/oracle/oradata/orcl/|  grep windwind01.dbfselect Count (*) from T1; COUNT (*)----------99000alter system switch logfile; sql> shutdown immediate; The database is closed. The database has been uninstalled. The ORACLE routine has been closed. The sql> startuporacle routine has been started. Total System Global area 1.0088E+10 bytesfixed size 2261928 bytesvariable size 1644170328 bytesdatabase buffers 8422162  432 Bytesredo Buffers 19595264 bytes Database is loaded. The database is already open. sql> INSERT INTO T1 values (9999999, ' rmfiletest '); 1 rows have been created. Sql> commit; commit completed.       Sql> SELECT * from t1 where rownum=1 the order by Sid Desc; SID SNAME----------------------------------------9999999 rmfiletest

Accidental deletion of Oracle data file Recovery