虽然一再小心，但是还是发生人为误删除数据库文件.简单步骤，或许关键时刻可以帮大忙. 环境：CENTOS 6.5模拟误操作：数据库在正常运行,人工直接rm 掉了数据文件。–1.测试环境情况:$ 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 星期四 3月 5 15:55:14 2015Copyright (c) 1982, 2013, Oracle. All rights reserved.连接到: 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';开启数据库归档archive log list;数据库日志模式非存档模式自动存档禁用存档终点USE_DB_RECOVERY_FILE_DEST最早的联机日志序列3002当前日志序列3004mkdir /u01/archalter 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 bytesFixed Size2261928 bytesVariable Size 1644170328 bytesDatabase Buffers 8422162432 bytesRedo Buffers19595264 bytes数据库装载完毕。SQL> alter database archivelog;数据库已更改。SQL> archive log list;数据库日志模式存档模式自动存档启用存档终点/u01/arch最早的联机日志序列3002下一个存档日志序列 3004当前日志序列3004SQL> 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.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/wind01create table t1(sid int not null primary key,sname varchar2(10))tablespace wind;–循环导入数据declaremaxrecords constant int:=100000;i int :=1;beginfor i in 1..maxrecords loopinsert 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 a20SQL> select tablespace_name,status from dba_tablespaces;TABLESPACE_NAMESTATUS——————– —————————SYSTEMONLINESYSAUXONLINEUNDOTBS1ONLINETEMPONLINEUSERSONLINETTSPACEONLINEOCPYANGONLINEOCPYANGINDEXONLINELOBOCPYANGONLINELOBOCPYANG01ONLINELOBOCPYANG02ONLINETABLESPACE_NAMESTATUS——————– —————————WINDONLINErm -rf /u01/app/oracle/oradata/orcl/wind01.dbfls /u01/app/oracle/oradata/orcl/ | grep windSQL> show user;USER 为 "WIND"SQL> select count(*) from t1;COUNT(*)———-100000SQL> desc t1 名称是否为空? 类型 —————————————– ——– —————————- SIDNOT NULL NUMBER(38) SNAMEVARCHAR2(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 greporacle 19281 0 15:59 ?00:00:00 ora_dbw0_orclSQL> col tablespace_name for a20SQL> select tablespace_name,status from dba_tablespaces;TABLESPACE_NAMESTATUS——————– —————————SYSTEMONLINESYSAUXONLINEUNDOTBS1ONLINETEMPONLINEUSERSONLINETTSPACEONLINEOCPYANGONLINEOCPYANGINDEXONLINELOBOCPYANGONLINELOBOCPYANG01ONLINELOBOCPYANG02ONLINETABLESPACE_NAMESTATUS——————– —————————WINDONLINEcd /proc/1928/fdlllr-x—— 1 oracle oinstall 64 Mar 5 16:20 0 -> /dev/nulll-wx—— 1 oracle oinstall 64 Mar 5 16:20 1 -> /dev/nulllrwx—— 1 oracle oinstall 64 Mar 5 16:20 10 -> /u01/app/oracle/product/11.2.0/db_1/dbs/lkORCLlr-x—— 1 oracle oinstall 64 Mar 5 16:20 11 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msbl-wx—— 1 oracle oinstall 64 Mar 5 16:20 2 -> /dev/nulllrwx—— 1 oracle oinstall 64 Mar 5 16:20 256 -> /u01/app/oracle/oradata/orcl/control01.ctllrwx—— 1 oracle oinstall 64 Mar 5 16:20 257 -> /u01/app/oracle/fast_recovery_area/orcl/control02.ctllrwx—— 1 oracle oinstall 64 Mar 5 16:20 258 -> /u01/app/oracle/oradata/orcl/system01.dbflrwx—— 1 oracle oinstall 64 Mar 5 16:20 259 -> /u01/app/oracle/oradata/orcl/sysaux01.dbflrwx—— 1 oracle oinstall 64 Mar 5 16:20 260 -> /u01/app/oracle/oradata/orcl/undotbs01.dbflrwx—— 1 oracle oinstall 64 Mar 5 16:20 261 -> /u01/app/oracle/oradata/orcl/users01.dbflrwx—— 1 oracle oinstall 64 Mar 5 16:20 262 -> /u01/app/oracle/oradata/orcl/ttspace01.dbflrwx—— 1 oracle oinstall 64 Mar 5 16:20 263 -> /u01/app/oracle/oradata/orcl/ocpyang01.dbflrwx—— 1 oracle oinstall 64 Mar 5 16:20 264 -> /u01/app/oracle/oradata/orcl/ocpyangindex01.dbflrwx—— 1 oracle oinstall 64 Mar 5 16:20 265 -> /u01/app/oracle/oradata/orcl/lobocpyang01.dbflrwx—— 1 oracle oinstall 64 Mar 5 16:20 266 -> /u01/app/oracle/oradata/orcl/lobocpyang0101.dbflrwx—— 1 oracle oinstall 64 Mar 5 16:20 267 -> /u01/app/oracle/oradata/orcl/lobocpyang0202.dbflrwx—— 1 oracle oinstall 64 Mar 5 16:20 268 -> /u01/app/oracle/oradata/orcl/temp01.dbflrwx—— 1 oracle oinstall 64 Mar 5 16:20 269 -> /u01/app/oracle/oradata/orcl/wind01.dbf (deleted) –注lr-x—— 1 oracle oinstall 64 Mar 5 16:20 3 -> /dev/nulllr-x—— 1 oracle oinstall 64 Mar 5 16:20 4 -> /dev/nulllr-x—— 1 oracle oinstall 64 Mar 5 16:20 5 -> /dev/nulllr-x—— 1 oracle oinstall 64 Mar 5 16:20 6 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msblr-x—— 1 oracle oinstall 64 Mar 5 16:20 7 -> /proc/1928/fdlr-x—— 1 oracle oinstall 64 Mar 5 16:20 8 -> /dev/zerolrwx—— 1 oracle oinstall 64 Mar 5 16:20 9 -> /u01/app/oracle/product/11.2.0/db_1/dbs/hc_orcl.dat直接cp该句柄文件名回原位置：$ pwd/proc/1928/fdcp /proc/1928/fd/269 /u01/app/oracle/oradata/orcl/wind01.dbfNOTE:由于数据库一直是打开使用的，数据在不断变更则SCN也会不断的变化，cp数据文件和数据库当前的信息明显不一致，此时需要对数据文件进行recover.–脱机数据文件alter database datafile '/u01/app/oracle/oradata/orcl/wind01.dbf' offline;select count(*) from t1;第 1 行出现错误:ORA-00376: 此时无法读取文件 11ORA-01110: 数据文件 11: '/u01/app/oracle/oradata/orcl/wind01.dbf'–恢复数据文件recover datafile '/u01/app/oracle/oradata/orcl/wind01.dbf';–联机数据文件alter database datafile '/u01/app/oracle/oradata/orcl/wind01.dbf' online;select count(*) from t1;COUNT(*)———-99000成功恢复.—-5.简单验证ls /u01/app/oracle/oradata/orcl/ | grep windwind01.dbfselect count(*) from t1;COUNT(*)———-99000alter system switch logfile;SQL> shutdown immediate;数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL> startupORACLE 例程已经启动。Total System Global Area 1.0088E+10 bytesFixed Size2261928 bytesVariable Size 1644170328 bytesDatabase Buffers 8422162432 bytesRedo Buffers19595264 bytes数据库装载完毕。数据库已经打开。SQL> insert into t1 values(9999999,'rmfiletest');已创建 1 行。SQL> commit;提交完成。SQL> select * from t1 where rownum=1 order by sid desc;SID SNAME———- ——————————9999999 rmfiletest
，继续期待我的下一个旅行，拿起背包，
