oracle手工完全恢复（五）新建的表空间，没有备份，datafile被删除

案例4：recover datafile

——新建的表空间，没有备份，datafile被删除

恢复步骤：起库，通过V$recover_file 查看需要恢复的文件，由于没有备份，只能将数据文件offline，进行数据文件重建，再进行recover datafile ，online即可

（1)模拟环境

SQL> create tablespace cuug

2  datafile '/u01/app/oracle/oradata/anny/cuug01.dbf' size 10m;

Tablespace created.

SQL> conn scott/tiger

Connected.

SQL> create table tb02(id int) tablespace cuug;

Table created.

SQL> insert into tb02 values(1);

1 row created.

SQL> insert into tb02 values(2);

1 row created.

SQL> insert into tb02 values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select  * from tb02;

ID

----------

1

2

3

SQL> conn /as sysdba

Connected.

SQL> shutdown abort

ORACLE instance shut down.

SQL> !

本文URL地址：http://www.bianceng.cn/database/Oracle/201410/45395.htm

[oracle@solaris10 ~]$rm /u01/app/oracle/oradata/anny/cuug01.dbf——删除表空间

[oracle@solaris10 ~]$!exp

export ORACLE_SID=anny

[oracle@solaris10 ~]$!sql

（2）启动 database

SQL> startup

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  1279964 bytes

Variable Size              58722340 bytes

Database Buffers          251658240 bytes

Redo Buffers                2912256 bytes

Database mounted.

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

ORA-01110: data file 12: '/u01/app/oracle/oradata/anny/cuug01.dbf'

SQL> select file#,error from v$recover_file;——查找需要恢复的文件信息

FILE# ERROR

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

12 FILE NOT FOUND

（3）恢复

SQL> alter database datafile 12 offline;

Database altered.

SQL> alter database open;

Database altered.

——没有备份，不能做restore，只能重建数据文件【通过os 删除，在controlfile文件仍然有datafile 记录】，然后recover datafile

SQL> alter database create datafile '/u01/app/oracle/oradata/anny/cuug01.dbf';——重建数据文件

Database altered.

SQL> recover datafile 12;——恢复数据文件

Media recovery complete.

告警日志信息：

ALTER DATABASE RECOVER  datafile 12

Wed Mar 21 16:18:20 2012

Media Recovery Start

Wed Mar 21 16:18:20 2012

Recovery of Online Redo Log: Thread 1 Group 4 Seq 4 Reading mem 0

Mem# 0 errs 0: /disk3/oradata/anny/redo04a.log

Mem# 1 errs 0: /disk1/oradata/anny/redo04b.log

Mem# 2 errs 0: /disk2/oradata/anny/redo04c.log

Wed Mar 21 16:18:20 2012

Recovery of Online Redo Log: Thread 1 Group 3 Seq 5 Reading mem 0

Mem# 0 errs 0: /disk3/oradata/anny/redo03a.log

Mem# 1 errs 0: /disk1/oradata/anny/redo03b.log

Mem# 2 errs 0: /disk2/oradata/anny/redo03c.log

Wed Mar 21 16:18:20 2012

Media Recovery Complete (anny)

Completed: ALTER DATABASE RECOVER  datafile 12

（4）验证

SQL> alter database datafile 12 online;

Database altered.

SQL> select * from scott.tb02;

ID

----------

1

2

3