案例3：（recover tablespace ，database open状态）
——database在open 状态下恢复数据文件（除了system tablespace）
（1） 模拟环境：
06:10:52 SQL> insert into scott.t01 values (4);
1 row created.
06:13:12 SQL> insert into scott.t01 values (5);
1 row created.
06:13:13 SQL> insert into scott.t01 values (6);
1 row created.
06:13:15 SQL> commit;
Commit complete.
06:13:17 SQL> select * from scott.t01;
ID
----------
1
2
3
4
5
6
6 rows selected.
——在open 状态下删除datafile
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/test*.dbf
[oracle@work ~]$
SQL> select * from scott.tb01;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
SQL> alter system flush buffer_cache; //清除data buffer
System altered.
SQL> select * from scott.db01;
select * from scott.db01
*
ERROR at line 1:
ORA-00942: table or view does not exist
06:15:09 SQL> select * from scott.t01;
select * from scott.t01
*
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/app/oracle/oradata/prod/test02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
（2）查看datafile信息
SQL> select a.name,b.file#,b.name from V$tablespace a,v$datafile b where a.ts#=b.ts#;
NAME FILE# NAME
-------------------------------------------------- ---------- --------------------------------------------------
SYSTEM 1 /u01/app/oracle/oradata/anny/system01.dbf
SYSAUX 3 /u01/app/oracle/oradata/anny/sysaux01.dbf
USERS 4 /u01/app/oracle/oradata/anny/user01.dbf
TEST 11 /u01/app/oracle/oradata/anny/test02.dbf
TEST 5 /u01/app/oracle/oradata/anny/test01.dbf
LX01 6 /u01/app/oracle/oradata/anny/lx01.dbf
UNDOTBS 7 /u01/app/oracle/oradata/anny/undotbs01.dbf
LX02 2 /u01/app/oracle/oradata/anny/lx02.dbf
LX03 8 /u01/app/oracle/oradata/anny/lx03.dbf
LX04 9 /u01/app/oracle/oradata/anny/lx04.dbf
INDEXES 10 /u01/app/oracle/oradata/anny/index01.dbf
11 rows selected.
——对数据文件脱机
06:17:39 SQL> alter database datafile 6,8 offline;
Database altered.
（3）转储datafile
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/test*.dbf /u01/app/oracle/oradata/prod/
（4）recover datafile 或 recover tablespace
SQL> recover datafile 5,11;
Media recovery complete.
告警日志信息：
ALTER DATABASE RECOVER datafile 5,11
Wed Mar 21 16:02:50 2012
Media Recovery Start
Wed Mar 21 16:02:50 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 16:02:50 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:02:50 2012
Media Recovery Complete (anny)
Completed: ALTER DATABASE RECOVER datafile 5,11
（5）验证
SQL> alter database datafile 5,11 online;
Database altered.
SQL> select * from scott.tb01;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
