案例2： recover tablespace（恢复表空间（删除了tablespace的所有的datafile））

在关库状态下删除数据文件时，这样恢复：转储datafile，使丢失的datafile脱机， recover tablespace


（1）模拟环境

SQL> conn scott/tiger

Connected.

SQL> col tname for a30

SQL> select * from tab;

TNAME                          TABTYPE                CLUSTERID

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

DEPT                           TABLE

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

EMP1                           TABLE

ADMIN_EXT_EMPLOYEES            TABLE

EMPLOYEES                      TABLE

EMP2                           TABLE

TB01                           TABLE

9 rows selected.

SQL> select * from tb01;

ID

----------

1

2

3

SQL> insert into tb01 values(4);

1 row created.

SQL> insert into tb01 values(5);

1 row created.

SQL> insert into tb01 values(6);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tb01;

ID

----------

1

2

3

4

5

6

6 rows selected.

SQL> shutdown abort;

ORACLE instance shut down.

SQL> !

[oracle@solaris10 ~]$rm /u01/app/oracle/oradata/anny/test*.dbf——在关库状态下删除数据文件


（2）启动数据库

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 5 - see DBWR trace file

ORA-01110: data file 5: '/u01/app/oracle/oradata/anny/test01.dbf'

SQL> select file#,error from v$recover_file;

FILE# ERROR

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

5 FILE NOT FOUND

11 FILE NOT FOUND


（3）转储数据文件

[oracle@solaris10 cold_bak]$cp /disk1/backup/anny/cold_bak/test*.dbf /u01/app/oracle/oradata/anny/


（4）数据文件offline

SQL> alter database datafile 5,11 offline;

Database altered.

SQL> alter database open;

Database altered.


（5） recover tablespace

SQL> recover tablespace test;

Media recovery complete.

查看告警日志：

ALTER DATABASE RECOVER  tablespace test

Wed Mar 21 15:46:48 2012

Media Recovery Start

Wed Mar 21 15:46:48 2012

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

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

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

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

Wed Mar 21 15:46:48 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 15:46:49 2012

Media Recovery Complete (anny)

Completed: ALTER DATABASE RECOVER  tablespace test


（6）验证：

SQL> alter database datafile 5,11 online;

Database altered.

SQL> select * from scott.tb01;

ID

----------

1

2

3

4

5

6

6 rows selected.


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html