丢失数据文件恢复操作(有备份)

恢复的前提条件

1.必须是在归档模式下

2.必须拥有一个可用的全备，并且拥有该全备以后知道数据文件丢失以后所有的归档日志文件，

及数据文件丢失后的联机重做日志文件

场景：

SQL> create tablespace tbs1 datafile '/oracle/app/oracle/oradata/orcl/tbs01.dbf' size 5m;


Tablespace created.


SQL> create table t(id number) tablespace tbs1;


Table created.


SQL> insert into t values(1);


1 row created.


SQL> commit;


Commit complete.


SQL> select * from t; 


        ID
----------
         1

RMAN> backup database;

[oracle@redhat5 orcl]$ rm tbs01.dbf

SQL> shutdown immediate;
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/oracle/app/oracle/oradata/orcl/tbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

恢复步骤

a.

SQL> startup force mount;

b.

RMAN> restore tablespace tbs1;

也可直接存取要恢复的数据文件:restore datafile '/oracle/app/oracle/oradata/orcl/tbs01.dbf'

c.recover tablespace tbs1

d.alter database open;

其实就算不做rman备份也是可以恢复的，因为利用重做日志就可以恢复。

比如：

SQL> create table t tablespace mytbs1 as select * from all_objects where rownum<10;


Table created.

[oracle@redhat5 orcl]$ rm mytbs*

SQL> startup force mount;

RMAN> list failure;


using target database control file instead of recovery catalog
List of Database Failures
=========================


Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
382        HIGH     OPEN      28-MAR-16     One or more non-system datafiles are offline


RMAN> restore tablespace mytbs1;


Starting restore at 29-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK


creating datafile file number=6 name=/oracle/app/oracle/oradata/orcl/mytbs01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 29-MAR-16


RMAN> recover tablespace mytbs1;


Starting recover at 29-MAR-16
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:00


Finished recover at 29-MAR-16


RMAN> sql 'alter database open';

RMAN> list failure;