1.准备测试：

CREATE TABLESPACE tools DATAFILE
'/u01/app/oracle11g/oradata/test/tools01.dbf' SIZE 64M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

2.在表空间上建立表:

ALTER USER SCOTT QUOTA UNLIMITED ON TOOLS;
create table t1 tablespace tools as select rownum id,'test' name from dual connect by level <=10000;

3.备份:

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    710      SYSTEM               ***     /u01/app/oracle11g/oradata/test/system01.dbf
2    720      SYSAUX               ***     /u01/app/oracle11g/oradata/test/sysaux01.dbf
3    85       UNDOTBS1             ***     /u01/app/oracle11g/oradata/test/undotbs01.dbf
4    11       USERS                ***     /u01/app/oracle11g/oradata/test/users01.dbf
5    100      EXAMPLE              ***     /u01/app/oracle11g/oradata/test/example01.dbf
6    64       RMAN                 ***     /u01/app/oracle11g/oradata/test/rman01.dbf
7    64       TOOLS                ***     /u01/app/oracle11g/oradata/test/tools01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       /u01/app/oracle11g/oradata/test/temp01.dbf

RMAN> backup datafile 7;

Starting backup at 2011-12-27 15:30:02
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle11g/oradata/test/tools01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-12-27 15:30:03
channel ORA_DISK_1: finished piece 1 at 2011-12-27 15:30:04
piece handle=/u01/app/oracle11g/flash_recovery_area/TEST/backupset/2011_12_27/o1_mf_nnndf_TAG20111227T153002_7hlwzv9o_.bkp tag=TAG20111227T153002 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-12-27 15:30:04

4. 开始测试：
SQL> alter database datafile 7 offline drop;
Database altered.

--假设现在发现干错了，需要恢复，如何操作呢？

RMAN> restore datafile 7;

Starting restore at 2011-12-27 15:33:13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle11g/oradata/test/tools01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle11g/flash_recovery_area/TEST/backupset/2011_12_27/o1_mf_nnndf_TAG20111227T153002_7hlwzv9o_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle11g/flash_recovery_area/TEST/backupset/2011_12_27/o1_mf_nnndf_TAG20111227T153002_7hlwzv9o_.bkp tag=TAG20111227T153002
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 2011-12-27 15:33:17

RMAN> recover datafile 7;
Starting recover at 2011-12-27 15:33:48
using channel ORA_DISK_1

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

Finished recover at 2011-12-27 15:33:49

RMAN> sql 'alter database datafile 7 online ';

sql statement: alter database datafile 7 online

5.测试数据是否丢失：

15:35:48 SQL> select count(*) from scott.t1;

COUNT(*)
----------
10000

OK，一切正常。一直以为出现这种情况要进行不完成恢复，实际情况不需要。

6.重复测试，改用：
DROP TABLESPACE TOOLS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

实际上这样就不行了。

RMAN> recover datafile 7;
Starting recover at 2011-12-27 15:38:52
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/27/2011 15:38:53
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 7

ITPub博客

[20111227]Alter database datafile offline drop后的恢复.txt

最新文章