Linux 平台误删 oracle 数据文件的恢复方法 

模拟误操作：

    数据库在正常运行,人工直接rm 掉了数据文件。

 
--1.测试环境情况:

$ cat /etc/redhat-release
CentOS release 6.5 (Final)
 
select file_name from dba_data_files;
/u01/app/oracle/oradata/orcl/test.dbf


$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 3月 5 15:55:14 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


连接到: 
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';


开启数据库归档


archive log list;

数据库日志模式             非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     3002
当前日志序列           3004

mkdir /u01/arch

alter system set log_archive_dest_1='location=/u01/arch' scope=spfile;



SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1.0088E+10 bytes
Fixed Size		    2261928 bytes
Variable Size		 1644170328 bytes
Database Buffers	 8422162432 bytes
Redo Buffers		   19595264 bytes
数据库装载完毕。
SQL> alter database archivelog;

数据库已更改。

SQL> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            /u01/arch
最早的联机日志序列     3002
下一个存档日志序列   3004
当前日志序列           3004
SQL> alter database open;

数据库已更改。

SQL> select open_mode from v$database;

OPEN_MODE
------------------------------------------------------------
READ WRITE






--2.新建测试数据

select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf


SQL> 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(10)
)
tablespace wind;





--循环导入数据
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(' 成功录入数据！ ');
    commit;
    end; 
/


select count(*) from t1;


  COUNT(*)
----------
    100000
 


--3.模拟删除数据

SQL> col tablespace_name for a20
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME      STATUS
-------------------- ---------------------------
SYSTEM		     ONLINE
SYSAUX		     ONLINE
UNDOTBS1	     ONLINE
TEMP		     ONLINE
USERS		     ONLINE
TTSPACE 	     ONLINE
OCPYANG 	     ONLINE
OCPYANGINDEX	     ONLINE
LOBOCPYANG	     ONLINE
LOBOCPYANG01	     ONLINE
LOBOCPYANG02	     ONLINE

TABLESPACE_NAME      STATUS
-------------------- ---------------------------
WIND		     ONLINE



rm -rf /u01/app/oracle/oradata/orcl/wind01.dbf


ls  /u01/app/oracle/oradata/orcl/ | grep wind



SQL> show user;
USER 为 "WIND"
SQL> select count(*) from t1;

  COUNT(*)
----------
    100000

SQL> desc t1
 名称                                    是否为空 类型
 ----------------------------------------- -------- ----------------------------
 SID					   NOT NULL NUMBER(38)
 SNAME						    VARCHAR2(10)

SQL> delete from t1 where sid>99000;

已删除1000行。

SQL> commit;

提交完成。

SQL> select count(*) from t1;

  COUNT(*)
----------
     99000





--4.恢复
ps -eaf|grep dbw0 |grep -v grep
oracle    1928     1  0 15:59         00:00:00 ora_dbw0_orcl


SQL> col tablespace_name for a20
SQL> select tablespace_name,status from dba_tablespaces;