Restore accidentally deleted oracle data files

Restore accidentally deleted oracle data files

Recovery Method for accidental deletion of oracle data files on the Linux platform simulates misoperations: the database is running normally and data files are directly rm. -- 1. test Environment: $ 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 Thursday March 5 15:55:14 2015 Copyright (c) 1982,201 3, Oracle. all rights reserved. connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining Nd Real Application Testing optionsSQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss'; Enable archive database log list; database Log mode: Non-archive mode: automatic archiving disabled archiving end point USE_DB_RECOVERY_FILE_DEST earliest online log sequence 3002 current log sequence 3004 mkdir/u01/archalter system set log_archive_dest_1 = 'location =/u01/arch 'scope = spfile; SQL> shutdown the immediate database has been disabled. The database has been detached. The ORACLE routine has been disabled. SQL> startup mount; the ORACLE routine has been started. Total System Global Area 1.0088E + 10 bytesFixed Size 2261928 bytesVariable Size 1644170328 bytesDatabase Buffers 8422162432 bytesRedo Buffers 19595264 bytes database load is complete. SQL> alter database archivelog; the database has been changed. SQL> archive log list; automatic archiving in database log mode enable archiving end point/u01/arch earliest online log sequence 3002 next archived log sequence 3004 current log sequence 3004SQL> alter database open; the database has been changed. SQL> select open_mode from v $ database; OPEN_MODE------------------------------------------------------------READ WRITE--2. create test data select file_name from dba_data_files; FILE_NAME users/u01/app/oracle/oradata/orcl/users01.dbfSQL> create tablespace wind datafile '/u01/app/oracle/oradata/orcl/wind01.dbf 'size 200 m; SQL> create user w Ind 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 (10) tablespace wind; -- cyclically imported 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 ('Data Input successful! '); Commit; end;/select count (*) from t1; COUNT (*) ---------- 100000 -- 3. simulate data deletion SQL> col tablespace_name for a20SQL> select tablespace_name, status from dba_tablespaces; TABLESPACE_NAME STATUS when using SYSTEM when ONLINETEMP ONLINEUSERS ONLINETTSPACE ONLINEOCPYANG when ONLINELOBOCPYANG else ONL INETABLESPACE_NAME STATUS ------------------ restart WIND ONLINErm-rf/u01/app/oracle/oradata/orcl/wind01.dbfls/u01/app/oracle/oradata/orcl/| grep windSQL> show user; the USER is "WIND" SQL> select count (*) from t1; COUNT (*) ---------- 100000SQL> desc t1 name is blank? Type ----------------------------------------- -------- ---------------------------- sid not null number (38) SNAME VARCHAR2 (10) SQL> delete from t1 where sid> 99000; 1000 rows deleted. SQL> commit; submitted completely. SQL> select count (*) from t1; COUNT (*) ---------- 99000--4. Restore ps-eaf | grep dbw0 | grep-v greporacle 1928 1 0? 00:00:00 latency> col tablespace_name for a20SQL> select tablespace_name, status from region; TABLESPACE_NAME STATUS when using SYSTEM when using onlinetemp onlineusers onlinettspace onlineocpyang when ONLINELOBOCPYANG when starting STATUS ------------------------------- ---------------- WIND onlinmcm/proc/1928/fdlllr-x ------ 1 oracle oinstall 64 Mar 5 0->/dev/nulll-wx ------ 1 oracle oinstall 64 Mar 5 1->/ dev/nulllrwx ------ 1 oracle oinstall 64 Mar 5->/u01/app/oracle/product/11.2.0/db_1/dbs/lkORCLlr-x ------ 1 oracle oinstall 64 Mar 5 11->/u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus. msbl-wx ------ 1 oracle oinstall 64 Mar 5 2->/dev/nulllrwx ------ 1 oracle oinstall 64 Mar 5 256->/u01/app/oracle/oradata/orcl/control01.ctllrwx ------ 1 oracle oinstall 64 Mar 5 257 ->/u01/app/oracle/fast_recovery_area/orcl/control02.ctllrwx ------ 1 oracle oinstall 64 Mar 5 258->/u01/app/oracle/oradata/orcl/system01.dbflrwx ------ 1 oracle oinstall 64 Mar 5 259->/u01/app/oracle/oradata/orcl/sysaux01.d Bflrwx ------ 1 oracle oinstall 64 Mar 5 260->/u01/app/oracle/oradata/orcl/undotbs01.dbflrwx ------ 1 oracle oinstall 64 Mar 5 261->/u01/app/ oracle/oradata/orcl/users01.dbflrwx ------ 1 oracle oinstall 64 Mar 5 262->/u01/app/oracle/oradata/orcl/ttspace01.dbflrwx ------ 1 oracle oinstall 64 Mar 5 263- >/u01/app/oracle/oradata/orcl/ocpyang01.dbflrwx ------ 1 oracle oinstall 64 Mar 5 264->/u01/app/oracle/oradata/orcl/ocpyangindex01.dbflrwx ------ 1 oracle oinstall 64 Mar 5 265->/u01/app/oracle/oradata/orcl /lobocpyang01.dbflrwx ------ 1 oracle oinstall 64 Mar 5 266->/u01/app/oracle/oradata/orcl/lobocpyang0101.dbflrwx ------ 1 oracle oinstall 64 Mar 5 267->/u01/app /oracle/oradata/orcl/lobocpyang0202.dbflrwx ------ 1 oracle oinstall 64 Mar 5 268->/u01/app/oracle/oradata/orcl/temp01.dbflrwx ------ 1 oracle oinstall 64 Mar 5 269->/u01/app/oracle/oradata/orcl/wind01.dbf (deleted) -- Note lr-x ------ 1 oracle oinstall 64 Mar 5 3->/dev/nulllr-x ------ 1 oracle oinstall 64 Mar 5 4->/dev/nulllr-x ------ 1 oracle oinstall 64 Mar 5 5->/dev/nulllr-x ------ 1 oracle oinstall 64 Mar 5 6->/u01/app/oracle/produ Ct/11.2.0/db_1/rdbms/mesg/oraus. msblr-x ------ 1 oracle oinstall 64 Mar 5 7->/proc/1928/fdlr-x ------ 1 oracle oinstall 64 Mar 5 8->/dev/zerolrwx ------ 1 oracle oinstall 64 Mar 5 9->/u01/app/oracle/product/11.2.0/db_1/dbs/hc_orcl.dat directly cp the handle file name to the original location: $ pwd/proc/1928/fdcp/proc/1928/fd/269/u01/app/oracle/oradata/orcl/wind01.dbfNOTE: Because the database is always open for use, if the data is constantly changing, the SCN will also change. The cp data file and the current information of the database The data file must be recovered. -- offline data file alter database datafile '/u01/app/oracle/oradata/orcl/wind01.dbf' offline; select count (*) from t1; 1st Line Error: ORA-00376: at this time, the file 11ORA-01110: Data File 11: '/u01/app/oracle/oradata/orcl/wind01.dbf' -- recover the 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; CO UNT (*) ---------- 99000 successfully restored. ---- 5. simple verification ls/u01/app/oracle/oradata/orcl/| grep windwind01.dbfselect count (*) from t1; COUNT (*) ---------- 99000 alter system switch logfile; SQL> shutdown immediate; the database has been closed. The database has been detached. The ORACLE routine has been disabled. The SQL> startupORACLE routine has been started. Total System Global Area 1.0088E + 10 bytesFixed Size 2261928 bytesVariable Size 1644170328 bytesDatabase Buffers 8422162432 bytesRedo Buffers 19595264 bytes database load is complete. The database has been opened. SQL> insert into t1 values (9999999, 'rmfiletest'); 1 row has been created. SQL> commit; submitted completely. SQL> select * from t1 where rownum = 1 order by sid desc; SID SNAME ---------- ------------------------------ 9999999 rmfiletest