Oracle 11g RMAN恢复-用户误删除表空间

--0. 场景模拟 --0.1 查看当前的备份，确定有控制文件和初始化参数文件的备份 RMAN> list backup;   using target database control file instead of recovery catalog   List of Backup Sets ===================     BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 91      Full    1.29G      DISK        00:01:36     14-AUG-13              BP Key: 91   Status: AVAILABLE  Compressed: NO  Tag: TAG20130814T193012         Piece Name: /pooldisk02/backup03/3joha2a5_1_1   List of Datafiles in backup set 91   File LV Type Ckp SCN    Ckp Time  Name   ---- -- ---- ---------- --------- ----   1       Full 2785919    14-AUG-13 /oradata/system01.dbf   2       Full 2785919    14-AUG-13 /oradata/sysaux01.dbf   3       Full 2785919    14-AUG-13 /oradata/undotbs01.dbf   4       Full 2785919    14-AUG-13 /oradata/users01.dbf   5       Full 2785919    14-AUG-13 /oradata/example01.dbf   6       Full 2785919    14-AUG-13 /oradata/newts01.dbf   7       Full 2785919    14-AUG-13 /oradata/fbtbs01.dbf   8       Full 2785919    14-AUG-13 /oradata/users02.dbf   9       Full 2785919    14-AUG-13 /oradata/mynewts01.dbf   BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 92      Full    9.95M      DISK        00:00:01     14-AUG-13              BP Key: 92   Status: AVAILABLE  Compressed: NO  Tag: TAG20130814T193158         Piece Name: /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823462318_90r8cyy7_.bkp   SPFILE Included: Modification time: 14-AUG-13   SPFILE db_unique_name: TESTDB11   Control File Included: Ckp SCN: 2785972      Ckp time: 14-AUG-13   BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 93      Full    80.00K     DISK        00:00:00     14-AUG-13              BP Key: 93   Status: AVAILABLE  Compressed: NO  Tag: TAG20130814T193602         Piece Name: /pooldisk02/backup03/3loha2l2_1_1   SPFILE Included: Modification time: 14-AUG-13   SPFILE db_unique_name: TESTDB11   BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 94      Full    9.95M      DISK        00:00:00     14-AUG-13              BP Key: 94   Status: AVAILABLE  Compressed: NO  Tag: TAG20130814T193603         Piece Name: /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823462563_90r8mmsm_.bkp   SPFILE Included: Modification time: 14-AUG-13   SPFILE db_unique_name: TESTDB11   Control File Included: Ckp SCN: 2786150      Ckp time: 14-AUG-13 --0.2 查看当前的表空间，确定要删除mynewts表空间（删除之前确定它有备份） sys@TESTDB11>select tablespace_name from dba_tablespaces;   TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE NEWTS FBTBS MYNEWTS   9 rows selected.   --0.3 切3组日志 --0.4 确定当前的SCN，省去日志挖掘的步骤 sys@TESTDB11>select current_scn from v$database;   CURRENT_SCN -----------     2808329 --0.5 误删除表空间 sys@TESTDB11>drop tablespace mynewts including contents and datafiles;   Tablespace dropped. --0.6 切3组日志 --0.7 确定当前的控制文件中已经没有关于mynewts的信息了，所以需要从备份中还原控制文件 sys@TESTDB11>select tablespace_name from dba_tablespaces;   TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE NEWTS FBTBS   8 rows selected. --1 从备份中还原控制文件（到底还原哪个控制文件，可以确保该控制文件中包含被删除的表空间的信息呢) RMAN> run { 2> startup nomount force;                        --1.1 重新启动到NOMOUNT状态 3> restore controlfile from autobackup;       --1.2 还原备份的控制文件 4> }   Oracle instance started   Total System Global Area     855982080 bytes   Fixed Size                     2230792 bytes Variable Size                742393336 bytes Database Buffers             109051904 bytes Redo Buffers                   2306048 bytes   Starting restore at 15-AUG-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK   recovery area destination: /u01/app/oracle/fast_recovery_area database name (or database unique name) used for search: TESTDB11 channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823462563_90r8mmsm_.bkp found in the recovery area channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130815 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130814 channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823462563_90r8mmsm_.bkp channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=/u01/app/oracle/oradata/TestDB11/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl Finished restore at 15-AUG-13   --2. 加载还原的控制文件,并确定该控制文件中包含删除的表空间的信息 RMAN> mount database;   database mounted released channel: ORA_DISK_1   RMAN> report schema;   Starting implicit crosscheck backup at 15-AUG-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK Crosschecked 3 objects Finished implicit crosscheck backup at 15-AUG-13   Starting implicit crosscheck copy at 15-AUG-13 using channel ORA_DISK_1 Finished implicit crosscheck copy at 15-AUG-13   searching for all files in the recovery area cataloging files... cataloging done   List of Cataloged Files ======================= File Name: /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823462563_90r8mmsm_.bkp File Name: /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823460342_90r6g6dq_.bkp   RMAN-06139: WARNING: control file is not current for REPORT SCHEMA Report of database schema for database with db_unique_name TESTDB11   List of Permanent Datafiles =========================== File Size(MB) Tablespace           RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1    750      SYSTEM               ***     /oradata/system01.dbf 2    740      SYSAUX               ***     /oradata/sysaux01.dbf 3    290      UNDOTBS1             ***     /oradata/undotbs01.dbf 4    6        USERS                ***     /oradata/users01.dbf 5    345      EXAMPLE              ***     /oradata/example01.dbf 6    20       NEWTS                ***     /oradata/newts01.dbf 7    200      FBTBS                ***     /oradata/fbtbs01.dbf 8    50       USERS                ***     /oradata/users02.dbf 9    0        MYNEWTS              ***     /oradata/mynewts01.dbf   List of Temporary Files ======================= File Size(MB) Tablespace           Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1    500      TEMP                 500         /oradata/temp01.dbf   --3. 还原和恢复 RMAN> run { 2> set until scn 2808329;                    --3.1 以SCN设置恢复的终点 3> restore database;                          --3.2 还原 4> recover database;                          --3.3 恢复 5> }   executing command: SET until clause   Starting restore at 15-AUG-13 using channel ORA_DISK_1   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 00001 to /oradata/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /oradata/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /oradata/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /oradata/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /oradata/example01.dbf channel ORA_DISK_1: restoring datafile 00006 to /oradata/newts01.dbf channel ORA_DISK_1: restoring datafile 00007 to /oradata/fbtbs01.dbf channel ORA_DISK_1: restoring datafile 00008 to /oradata/users02.dbf channel ORA_DISK_1: restoring datafile 00009 to /oradata/mynewts01.dbf channel ORA_DISK_1: reading from backup piece /pooldisk02/backup03/3joha2a5_1_1 channel ORA_DISK_1: piece handle=/pooldisk02/backup03/3joha2a5_1_1 tag=TAG20130814T193012 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:35 Finished restore at 15-AUG-13   Starting recover at 15-AUG-13 using channel ORA_DISK_1   starting media recovery   archived log file name=/archive2/1_1_823462113.dbf thread=1 sequence=1 archived log file name=/archive2/1_2_823462113.dbf thread=1 sequence=2 archived log file name=/archive2/1_3_823462113.dbf thread=1 sequence=3 archived log file name=/archive2/1_4_823462113.dbf thread=1 sequence=4 archived log file name=/archive2/1_5_823462113.dbf thread=1 sequence=5 media recovery complete, elapsed time: 00:00:03 Finished recover at 15-AUG-13 --4. 开库 MAN> sql 'alter database open resetlogs';   sql statement: alter database open resetlogs --5. 验证表空间和数据文件都回来了 sys@TESTDB11>select tablespace_name, file_name from dba_data_files;   TABLESPACE_NAME                FILE_NAME ------------------------------ -------------------------------------------------- EXAMPLE                        /oradata/example01.dbf USERS                          /oradata/users01.dbf UNDOTBS1                       /oradata/undotbs01.dbf SYSAUX                         /oradata/sysaux01.dbf SYSTEM                         /oradata/system01.dbf NEWTS                          /oradata/newts01.dbf FBTBS                          /oradata/fbtbs01.dbf MYNEWTS                        /oradata/mynewts01.dbf USERS                          /oradata/users02.dbf   9 rows selected. --查看数据文件得以还原 sys@TESTDB11>!ls /oradata/mynewts01.dbf /oradata/mynewts01.dbf   --6. 删除原有备份，创建新的备份 RMAN> delete backup; RMAN> backup database;   --补充知识：解释如果确定还原哪个控制文件 --7.1 备份当前的控制文件 RMAN> backup current controlfile;   Starting backup at 15-AUG-13 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 15-AUG-13 channel ORA_DISK_1: finished piece 1 at 15-AUG-13 piece handle=/pooldisk02/backup03/3rohb929_1_1 tag=TAG20130815T063137 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 15-AUG-13   Starting Control File and SPFILE Autobackup at 15-AUG-13 piece handle=/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823501899_90sh0vk9_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 15-AUG-13   --7.2 确定系统当前scn,假定一会要恢复到这个SCN sys@TESTDB11>select current_scn from v$database;   CURRENT_SCN -----------     2809203 --7.3 系统中有多个控制文件的备份，找一个SCN比要恢复到的SCN小，而且最近的一个SCN,所以为98号备份集中的控制文件备份 --记录备份片的名称：u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823501899_90sh0vk9_.bkp RMAN> backup current controlfile;   Starting backup at 15-AUG-13 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 15-AUG-13 channel ORA_DISK_1: finished piece 1 at 15-AUG-13 piece handle=/pooldisk02/backup03/3tohb9bq_1_1 tag=TAG20130815T063642 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 15-AUG-13   Starting Control File and SPFILE Autobackup at 15-AUG-13 piece handle=/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823502204_90shbf2g_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 15-AUG-13 --7.4 再备份当前的控制文件 RMAN> list backup of controlfile;     List of Backup Sets ===================     BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 96      Full    9.95M      DISK        00:00:00     15-AUG-13              BP Key: 96   Status: AVAILABLE  Compressed: NO  Tag: TAG20130815T062836         Piece Name: /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823501716_90sgv4nz_.bkp   Control File Included: Ckp SCN: 2808932      Ckp time: 15-AUG-13   BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 97      Full    9.92M      DISK        00:00:01     15-AUG-13              BP Key: 97   Status: AVAILABLE  Compressed: NO  Tag: TAG20130815T063137         Piece Name: /pooldisk02/backup03/3rohb929_1_1   Control File Included: Ckp SCN: 2809052      Ckp time: 15-AUG-13   BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 98      Full    9.95M      DISK        00:00:00     15-AUG-13              BP Key: 98   Status: AVAILABLE  Compressed: NO  Tag: TAG20130815T063139         Piece Name: /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823501899_90sh0vk9_.bkp   Control File Included: Ckp SCN: 2809059      Ckp time: 15-AUG-13   BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 99      Full    9.92M      DISK        00:00:01     15-AUG-13              BP Key: 99   Status: AVAILABLE  Compressed: NO  Tag: TAG20130815T063642         Piece Name: /pooldisk02/backup03/3tohb9bq_1_1   Control File Included: Ckp SCN: 2809603      Ckp time: 15-AUG-13   BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 100     Full    9.95M      DISK        00:00:01     15-AUG-13              BP Key: 100   Status: AVAILABLE  Compressed: NO  Tag: TAG20130815T063644         Piece Name: /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823502204_90shbf2g_.bkp   Control File Included: Ckp SCN: 2809609      Ckp time: 15-AUG-13   --7.5 重启到nomount状态，用指定的控制文件备份还原控制文件（不要做） RMAN> run { 2> startup nomount force; 3> restore controlfile from '/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823501899_90sh0vk9_.bkp'; 4> }

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1155522/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1155522/