无备份情况下数据文件恢复

        如果没有备份但是有归档日志的情况下，普通数据文件可以通过日志恢复，但是system表空间和undo表空间的数据文件无法恢复。

 

普通数据文件无备份情况下的恢复例子(DB Oracle 10g 10.2.0.4)：

SYS@orcl>create tablespace test datafile  '/u01/app/oracle/oradata/orcl/test01.dbf' size 10m;              ---创建表空间

Tablespace created.

 

SYS@orcl>select file_id, tablespace_name,file_name from dba_data_files where  tablespace_name='TEST';                         ---查看test表空间的数据文件

FILE_ID     TABLESPACE_NAME            FILE_NAME

----------     --------------------------                  ----------------------------------

        6                       TEST                           /u01/app/oracle/oradata/orcl/test01.dbf

 

SYS@orcl>create table scott.test1 tablespace test as select * from scott.emp;                         ---在test表空间上创建test1表

Table created.                          

 

SYS@orcl>select count(*) from scott.test1;

  COUNT(*)

----------

14

 

SYS@orcl>host rm  /u01/app/oracle/oradata/orcl/test01.dbf;              ---删除test表空间的数据文件

 

SYS@orcl>create table scott.test2  tablespace testas select * from scott.dept;   ---无法再在test表空间上创建表

create tablescott.test2 tablespace test as select * from scott.dept

                                                               *

ERROR at line 1:

ORA-01658: unable tocreate INITIAL extent for segment in tablespace TEST

 

SYS@orcl>startup force;                           

ORACLE instancestarted.

 

Total System GlobalArea  536870912 bytes

FixedSize                    2085360 bytes

VariableSize                  251661840 bytes

DatabaseBuffers          276824064 bytes

RedoBuffers                    6299648 bytes

Database mounted.

ORA-01157:cannot identify/lock data file 6 - see DBWR trace file

ORA-01110:data file 6 :'/u01/app/oracle/oradata/orcl/test01.dbf'

 

SYS@orcl>alter database datafile 6 offline;                      ---使test表空间的数据文件离线

Database altered.

 

SYS@orcl>alter database  open;                ---打开数据库

Database altered.

 

SYS@orcl>alter database create datafile 6;                ---创建新的空白数据文件

Database altered.

 

SYS@orcl>exit

Disconnected fromOracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With thePartitioning, OLAP, Data Mining and Real Application Testing options

[oracle@oraedu ~]$rman target /

 

Recovery Manager:Release 10.2.0.4.0 - Production on Mon Dec 24 20:12:20 2012

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

connected to targetdatabase: ORCL (DBID=1320402727)

 

RMAN> recover datafile 6;                       ---应用日志恢复6号数据文件

Starting recover at24-DEC-12

using targetdatabase control file instead of recovery catalog

allocated channel:ORA_DISK_1

channel ORA_DISK_1:sid=148 devtype=DISK

 

starting mediarecovery

media recoverycomplete, elapsed time: 00:00:02

 

Finished recover at24-DEC-12

 

SYS@orcl>alter database datafile 6 online;                        ---将数据文件上线

Database altered.

 

SYS@orcl>select  count(*) from scott.test1;

  COUNT(*)

----------

14

2013-01-02晚补充

今天在做控制文件备份恢复的测试时发现数据文件被删除后恢复的另一种方式。以上的步骤到执行'startup force'命令的步骤都不变，然后在rman中还原和恢复数据文件：

RMAN> recover datafile 6;

Starting recover at 02-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/02/2013 22:15:48
RMAN-06094: datafile 6 must be restored

RMAN> restore datafile 6;

Starting restore at 02-JAN-13
using channel ORA_DISK_1

creating datafile file number=6 name=/u01/app/oracle/oradata/orcl/test01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 02-JAN-13

RMAN> recover datafile 6;

Starting recover at 02-JAN-13
using channel ORA_DISK_1

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

Finished recover at 02-JAN-13

       最后在sqlplus中执行'alter database open'打开数据库。