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

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博客 ” ，链接：http://blog.itpub.net/267265/viewspace-714008/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-714008/