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博客 ” ，链接：https://blog.itpub.net/17013648/viewspace-1155522/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- 2015.09.18 网络公开课 《Oracle 11G OCP试题精讲（4）》
- 2015 最新OCM 10g考试资料汇总 Skillset 7（1）
- 2015 最新OCM 10g考试资料汇总 Skillset 6（2）
- 2015 最新OCM 10g考试资料汇总 Skillset 6（1）
- 2015.05.15 网络公开课 《Oracle 11G RAC深入探索之RAC和RAC One Node之间的切换》
- 2015 最新OCM 10g考试资料汇总 Skillset 5（5）
- 2015 最新OCM 10g考试资料汇总 Skillset 5（3）
- 2015 最新OCM 10g考试资料汇总 Skillset 5（2）
- 2015.04.10 网络公开课 《玩转DG系列之-使用RMAN创建物理DG》
- 2015 最新OCM 10g考试资料汇总 Skillset 5（1）
