[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