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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- flashback database 恢復誤刪除的表空間。Database
- Oracle RMAN 表空間恢復Oracle
- ORACLE 11g TSPITR恢復被刪除的表空間Oracle
- Oracle 11g RMAN恢復-只讀表空間的恢復Oracle
- oracle8i誤刪除臨時表空間後的恢復Oracle
- Oracle RMAN 表空間的完全恢復Oracle
- oracle級聯刪除使用者，刪除表空間Oracle
- 【北亞資料恢復】誤刪除oracle表和誤刪除oracle表資料的資料恢復方法資料恢復Oracle
- 刪除表空間,有rman全備的恢復(使用dbms_backup_restore來進行恢復)REST
- Oracle 11g RMAN恢復-只讀表空間的恢復（備份是在表空間只讀狀態下做的)Oracle
- rman恢復資料檔案 恢復表空間
- 【Oracle】rman 恢復只讀表空間資料庫Oracle資料庫
- Oracle 11g 表空間的誤刪除前傳, 有關控制檔案的備份和恢復（預熱）Oracle
- oracle實驗記錄 （恢復-表空間基於時間點恢復(rman)）Oracle
- 通過flashback database恢復被刪除的表空間Database
- oracle恢復誤刪除資料Oracle
- Oracle 11g 表空間的誤刪除, 當前的控制檔案不識別該表空間Oracle
- oracle誤刪除表空間的資料檔案Oracle
- Oracle系統表空間剛新增的一個資料檔案誤刪除恢復處理Oracle
- lsof恢復oracle誤刪除檔案Oracle
- 【Oracle 恢復表空間】 實驗Oracle
- 恢復Oracle表空間的方法Oracle
- 用RMAN執行表空間及時點恢復——RMAN使用者手冊
- Oracle閃回刪除恢復誤刪資料Oracle
- 【RMAN】表空間基於時間點的RMAN恢復-TSPITR
- DB2 恢復誤刪除的表DB2
- 非歸檔庫誤刪表空間後的資料恢復資料恢復
- oracle 失誤刪掉資料檔案後，刪除表空間操作Oracle
- Oracle恢復誤操作刪除掉的表Oracle
- ORACLE表空間的建立修改刪除Oracle
- MySQL資料庫表誤刪除恢復(一)MySql資料庫
- oracle建立/刪除表空間、建立/刪除使用者並賦予許可權Oracle
- 執行RMAN表空間及時點恢復——使用者管理備份和恢復手冊
- Oracle 10g 中誤刪除(drop)表的恢復處理Oracle 10g
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- oracle 刪除表空間試驗面面觀Oracle
- Oracle恢復誤刪除的資料檔案Oracle
- 非系統表空間損壞，rman備份恢復