oracle单个文件误删在线恢复

oracle单个文件误删在线恢复

当数据库在线的时候误删除数据文件可以通过在线方式重新创建数据文件
刚删除的文件操作系统句柄还没立刻释放可以通过操作系统的方式来进行恢复
如果是句柄已经释放，可以在线从数据库中来进行恢复
由于控制文件中包含数据文件的信息，通过归档和日志可以在线来恢复
目前数据文件情况

11202_test> select * from v$dbfile;

FILE# NAME
---------- ----------------------------------------
4 /oracle/11g/oradata/ora11/users01.dbf
3 /oracle/11g/oradata/ora11/undotbs01.dbf
2 /oracle/11g/oradata/ora11/sysaux01.dbf
1 /oracle/11g/oradata/ora11/system01.dbf

创建测试表空间，测试表

11202_test> create tablespace test datafile '/ora23/ora11_test.dbf' size 10M;

Tablespace created.

11202_test> create table ty.ty tablespace test  as select rownum id from dba_objects where rownum<10;

Table created.

11202_test> select count(*) from ty.ty;

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

切换几次日志

11202_test> alter system switch logfile;

System altered.

11202_test> /

System altered.

11202_test> /

System altered.

11202_test> select  * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1          1          7   52428800        512          1 YES INACTIVE               1574646 23-SEP-14      1574649 23-SEP-14
2          1          8   52428800        512          1 YES INACTIVE               1574649 23-SEP-14      1574652 23-SEP-14
3          1          9   52428800        512          1 NO  CURRENT                1574652 23-SEP-14   2.8147E+14

11202_test>

删除测试表空间test的数据文件
由于是在线删除，操作系统文件句柄还没释放，先offline，再online的时候报错了

11202_test> !ls /ora23/ora11*.dbf
/ora23/ora11_test.dbf

11202_test> !rm -rf /ora23/ora11*.dbf

11202_test> alter database datafile '/ora23/ora11_test.dbf' offline;

Database altered.

11202_test> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/ora23/ora11_test.dbf'

恢复此文件
办法1：

11202_test> alter database create datafile 5;

Database altered.

办法2，rman来恢复，此时数据库无备份，读取归档来恢复

RMAN> list backup;

specification does not match any backup in the repository

RMAN> restore datafile 5;

Starting restore at 23-SEP-14
using channel ORA_DISK_1

creating datafile file number=5 name=/ora23/ora11_test.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 23-SEP-14

RMAN>

recover数据文件

11202_test> recover datafile 5;
ORA-00279: change 1574323 generated at 09/23/2014 23:09:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch/ORA11/archivelog/2014_09_23/o1_mf_1_6_b233czm3_.arc
ORA-00280: change 1574323 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

重新online数据文件，验证数据

11202_test> alter database datafile 5 online;

Database altered.

11202_test> select count(*) from ty.ty;

COUNT(*)
----------
9

11202_test>

如果是做全库恢复，数据文件是备份后创建的，备份时候的控制文件不包含这个数据文件的信息，用rman可以直接恢复出该数据文件，当然归档需要和日志需要存在，如：

RMAN> recover database;

Starting recover at 23-SEP-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file /oracle/11g/oradata/ora11/redo03.log
archived log file name=/oracle/11g/oradata/ora11/redo03.log thread=1 sequence=9
creating datafile file number=5 name=/ora23/ora11_test.dbf
archived log file name=/oracle/11g/oradata/ora11/redo03.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-SEP-14