Oracle RMAN 表空間恢復
需求：
恢復1天前(05/06/2021 08:05:05)資料庫內一個使用者的資料。
環境說明：
DB:Oracle 11204 RAC
OS:AIX 7.1
操作：
1.由於之前只有rman備份，沒有邏輯備份，只能進行rman恢復。 2.全庫有3T多，資料量較大，恢復機剩餘空間不到1T，無法進行全庫恢復。 3.檢視使用者所在表空間很小，只有300G，考慮只恢復使用者資料所在表空間，在加上system,sysaux,undotbs表空間，不恢復其他業務使用者的表空間。 4.啟動資料庫時，把其他沒有恢復的表空間offline後，就可以正常開啟資料庫。
具體操作如下：
1.恢復控制檔案
###Rman Script:
run {
SET DBID 3311060971;
allocate channel ch1 type 'sbt_tape'
PARMS="###省略###"
TRACE 0;
restore controlfile from autobackup until time = "TO_DATE('05/06/2021 08:05:05','MM/DD/YYYY HH24:MI:SS')" ;
}
exit;
問題:
此處恢復控制檔案的方式是有問題的，因為最終是想將資料庫恢復到05/06/2021 08:05:05時間點。 而在恢復控制檔案時，由於並沒有05/06/2021 08:05:05這一時刻的控制檔案備份。 在恢復過程中沒有報錯，而是直接恢復了離05/06/2021 08:05:05時間點最近的一次控制檔案進行恢復。 這就導致了控制檔案恢復的時間點提前了，很多歸檔等備份資訊不全，最終導致資料庫在recovery時提示找不到歸檔，恢復出的資料庫也提前了。 實際上要恢復的控制檔案備份的時間可以稍往後推一些。
恢復日誌如下：
RMAN> 2> 3> 4> 5> 6> 7> allocated channel: ch1 channel ch1: SID=541 device type=SBT_TAPE channel ch1: CommVault Systems for Oracle: Version 11.0.0(BUILD80) sent command to channel: ch1 Starting restore at May 04 2021 17:34:56 channel ch1: looking for AUTOBACKUP on day: 20210506 channel ch1: AUTOBACKUP found: c-3311060971-20210506-01 channel ch1: restoring control file from AUTOBACKUP c-3311060971-20210506-01 channel ch1: control file restore from AUTOBACKUP complete output file name=/oradata/chenj/control01 output file name=/oradata/chenj/control02 Finished restore at May 04 2021 17:40:07 released channel: ch1 RMAN> Recovery Manager complete.
2.執行restore
run {
allocate channel ch1 type 'sbt_tape'
PARMS="###省略###"
TRACE 0;
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/cccc_xxxxxx_data_01.dbf' to '/oradata/chenj/cccc_xxxxxx_data_01.dbf';
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/cccc_xxxxxx_data_02.dbf' to '/oradata/chenj/cccc_xxxxxx_data_02.dbf';
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/sysaux.302.1042642447' to '/oradata/chenj/sysaux.302.1042642447';
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/system.285.1042646103' to '/oradata/chenj/system.285.1042646103';
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/undotbs1.301.1042643625' to '/oradata/chenj/undotbs1.301.1042643625';
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/undotbs2.322.1042633105' to '/oradata/chenj/undotbs2.322.1042633105';
set newname for datafile '+AA_BB_CJCXX_DATA/chenjch/datafile/users.284.1042646223' to '/oradata/chenj/users.284.1042646223';
restore (tablespace 'SYSTEM','SYSAUX','USERS','UNDOTBS1','UNDOTBS2','AAAA_XXXXX_UUUU') until time = "TO_DATE('05/06/2021 08:05:05','MM/DD/YYYY HH24:MI:SS')" ;
switch datafile all;
}
exit;
日誌：
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> allocated channel: ch1 channel ch1: SID=464 device type=SBT_TAPE channel ch1: CommVault Systems for Oracle: Version 11.0.0(BUILD80) allocated channel: ch2 channel ch2: SID=541 device type=SBT_TAPE channel ch2: CommVault Systems for Oracle: Version 11.0.0(BUILD80) sent command to channel: ch1 sent command to channel: ch2 executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at May 04 2021 18:30:45 new media label is V_241064_3217058 for piece 484407_chenj_aj038d7e_1_1 new media label is V_241064_3217091 for piece 484407_chenj_ak038ehq_1_1 new media label is V_241064_3217092 for piece 484407_chenj_al038emc_1_1 new media label is V_241064_3217061 for piece 484407_chenj_ai038d7d_1_1 new media label is V_241064_3217058 for piece 484407_chenj_aj038d7e_1_1 new media label is V_241064_3217091 for piece 484407_chenj_ak038ehq_1_1 new media label is V_241064_3217092 for piece 484407_chenj_al038emc_1_1 new media label is V_241064_3217061 for piece 484407_chenj_ai038d7d_1_1 channel ch1: starting datafile backup set restore channel ch1: specifying datafile(s) to restore from backup set channel ch1: restoring datafile 00001 to /oradata/chenj/system.285.1042646103 channel ch1: restoring datafile 00004 to /oradata/chenj/undotbs2.322.1042633105 channel ch1: reading from backup piece 484407_chenj_aj038d7e_1_1 channel ch2: starting datafile backup set restore channel ch2: specifying datafile(s) to restore from backup set channel ch2: restoring datafile 00051 to /oradata/chenj/cccc_xxxxxx_data_01.dbf channel ch2: reading from backup piece 484407_chenj_ai038d7d_1_1 channel ch2: piece handle=484407_chenj_ai038d7d_1_1 tag=TAG20210506T040045 channel ch2: restored backup piece 1 channel ch2: restore complete, elapsed time: 00:31:05 channel ch2: starting datafile backup set restore channel ch2: specifying datafile(s) to restore from backup set channel ch2: restoring datafile 00002 to /oradata/chenj/sysaux.302.1042642447 channel ch2: reading from backup piece 484407_chenj_ak038ehq_1_1 channel ch1: piece handle=484407_chenj_aj038d7e_1_1 tag=TAG20210506T040045 channel ch1: restored backup piece 1 channel ch1: restore complete, elapsed time: 01:05:11 channel ch1: starting datafile backup set restore channel ch1: specifying datafile(s) to restore from backup set channel ch1: restoring datafile 00005 to /oradata/chenj/users.284.1042646223 channel ch1: restoring datafile 00003 to /oradata/chenj/undotbs1.301.1042643625 channel ch1: restoring datafile 00052 to /oradata/chenj/cccc_xxxxxx_data_02.dbf channel ch1: reading from backup piece 484407_chenj_al038emc_1_1 channel ch2: piece handle=484407_chenj_ak038ehq_1_1 tag=TAG20210506T040045 channel ch2: restored backup piece 1 channel ch2: restore complete, elapsed time: 01:02:30 channel ch1: piece handle=484407_chenj_al038emc_1_1 tag=TAG20210506T040045 channel ch1: restored backup piece 1 channel ch1: restore complete, elapsed time: 00:57:05 Finished restore at May 04 2021 20:33:24 datafile 1 switched to datafile copy input datafile copy RECID=12 STAMP=1077050007 file name=/oradata/chenj/system.285.1042646103 datafile 2 switched to datafile copy input datafile copy RECID=13 STAMP=1077050008 file name=/oradata/chenj/sysaux.302.1042642447 datafile 5 switched to datafile copy input datafile copy RECID=14 STAMP=1077050008 file name=/oradata/chenj/users.284.1042646223 datafile 3 switched to datafile copy input datafile copy RECID=15 STAMP=1077050008 file name=/oradata/chenj/undotbs1.301.1042643625 datafile 4 switched to datafile copy input datafile copy RECID=16 STAMP=1077050008 file name=/oradata/chenj/undotbs2.322.1042633105 datafile 51 switched to datafile copy input datafile copy RECID=17 STAMP=1077050008 file name=/oradata/chenj/cccc_xxxxxx_data_01.dbf datafile 52 switched to datafile copy input datafile copy RECID=18 STAMP=1077050008 file name=/oradata/chenj/cccc_xxxxxx_data_02.dbf released channel: ch1 released channel: ch2 RMAN> Recovery Manager complete.
3.執行recovery
run {
allocate channel ch1 type 'sbt_tape'
PARMS="###省略###"
TRACE 0;
recover database skip tablespace 'DATA_TBS1','DATA_TBS2','DATA_TBS3','DATA_TBS4','DATA_TBS5','...'
until time = "TO_DATE('05/06/2021 08:05:05','MM/DD/YYYY HH24:MI:SS')";
}
exit;
日誌：
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> allocated channel: ch1 channel ch1: SID=541 device type=SBT_TAPE channel ch1: CommVault Systems for Oracle: Version 11.0.0(BUILD80) allocated channel: ch2 channel ch2: SID=618 device type=SBT_TAPE channel ch2: CommVault Systems for Oracle: Version 11.0.0(BUILD80) sent command to channel: ch1 sent command to channel: ch2 Starting recover at May 04 2021 20:33:57 ###沒有restore出來的資料檔案自動執行了offline操作### Executing: alter database datafile 8 offline Executing: alter database datafile 9 offline Executing: alter database datafile 11 offline Executing: alter database datafile 13 offline Executing: alter database datafile 14 offline Executing: alter database datafile 15 offline Executing: alter database datafile 16 offline Executing: alter database datafile 17 offline Executing: alter database datafile 18 offline Executing: alter database datafile 20 offline Executing: alter database datafile 21 offline Executing: alter database datafile 24 offline Executing: alter database datafile 25 offline Executing: alter database datafile 26 offline Executing: alter database datafile 27 offline Executing: alter database datafile 28 offline Executing: alter database datafile 29 offline Executing: alter database datafile 30 offline Executing: alter database datafile 31 offline Executing: alter database datafile 32 offline Executing: alter database datafile 33 offline Executing: alter database datafile 34 offline Executing: alter database datafile 35 offline Executing: alter database datafile 36 offline Executing: alter database datafile 37 offline Executing: alter database datafile 38 offline Executing: alter database datafile 39 offline Executing: alter database datafile 40 offline Executing: alter database datafile 41 offline Executing: alter database datafile 42 offline Executing: alter database datafile 43 offline Executing: alter database datafile 44 offline Executing: alter database datafile 45 offline Executing: alter database datafile 46 offline Executing: alter database datafile 47 offline Executing: alter database datafile 48 offline Executing: alter database datafile 49 offline Executing: alter database datafile 50 offline Executing: alter database datafile 54 offline Executing: alter database datafile 55 offline Executing: alter database datafile 56 offline Executing: alter database datafile 57 offline Executing: alter database datafile 58 offline Executing: alter database datafile 59 offline Executing: alter database datafile 60 offline Executing: alter database datafile 63 offline Executing: alter database datafile 64 offline Executing: alter database datafile 65 offline Executing: alter database datafile 66 offline Executing: alter database datafile 67 offline Executing: alter database datafile 71 offline Executing: alter database datafile 72 offline Executing: alter database datafile 73 offline Executing: alter database datafile 74 offline Executing: alter database datafile 75 offline Executing: alter database datafile 76 offline Executing: alter database datafile 77 offline Executing: alter database datafile 78 offline Executing: alter database datafile 79 offline Executing: alter database datafile 80 offline Executing: alter database datafile 83 offline Executing: alter database datafile 84 offline Executing: alter database datafile 85 offline Executing: alter database datafile 86 offline Executing: alter database datafile 87 offline Executing: alter database datafile 88 offline Executing: alter database datafile 89 offline Executing: alter database datafile 90 offline Executing: alter database datafile 91 offline Executing: alter database datafile 92 offline Executing: alter database datafile 93 offline Executing: alter database datafile 94 offline Executing: alter database datafile 95 offline Executing: alter database datafile 96 offline Executing: alter database datafile 97 offline Executing: alter database datafile 98 offline Executing: alter database datafile 99 offline Executing: alter database datafile 100 offline Executing: alter database datafile 101 offline Executing: alter database datafile 102 offline Executing: alter database datafile 103 offline Executing: alter database datafile 104 offline Executing: alter database datafile 105 offline Executing: alter database datafile 106 offline Executing: alter database datafile 107 offline Executing: alter database datafile 108 offline Executing: alter database datafile 109 offline Executing: alter database datafile 110 offline Executing: alter database datafile 111 offline Executing: alter database datafile 112 offline Executing: alter database datafile 113 offline Executing: alter database datafile 114 offline Executing: alter database datafile 115 offline Executing: alter database datafile 116 offline Executing: alter database datafile 6 offline Executing: alter database datafile 7 offline Executing: alter database datafile 23 offline Executing: alter database datafile 10 offline Executing: alter database datafile 12 offline Executing: alter database datafile 19 offline Executing: alter database datafile 22 offline Executing: alter database datafile 53 offline Executing: alter database datafile 68 offline Executing: alter database datafile 69 offline Executing: alter database datafile 70 offline Executing: alter database datafile 82 offline Executing: alter database datafile 81 offline Executing: alter database datafile 61 offline Executing: alter database datafile 62 offline starting media recovery new media label is V_241064_3217122 for piece 484407_chenj_ap038g7b_1_1 new media label is V_241064_3217123 for piece 484407_chenj_aq038gaa_1_1 new media label is V_241064_3217122 for piece 484407_chenj_ap038g7b_1_1 new media label is V_241064_3217123 for piece 484407_chenj_aq038gaa_1_1 channel ch1: starting archived log restore to default destination channel ch1: restoring archived log archived log thread=1 sequence=180348 channel ch1: restoring archived log archived log thread=2 sequence=182735 channel ch1: restoring archived log archived log thread=1 sequence=180349 channel ch1: restoring archived log archived log thread=1 sequence=180350 channel ch1: reading from backup piece 484407_chenj_ap038g7b_1_1 channel ch2: starting archived log restore to default destination channel ch2: restoring archived log archived log thread=2 sequence=182736 channel ch2: reading from backup piece 484407_chenj_aq038gaa_1_1 channel ch2: piece handle=484407_chenj_aq038gaa_1_1 tag=TAG20210506T045152 channel ch2: restored backup piece 1 channel ch2: restore complete, elapsed time: 00:00:07 channel ch1: piece handle=484407_chenj_ap038g7b_1_1 tag=TAG20210506T045152 channel ch1: restored backup piece 1 channel ch1: restore complete, elapsed time: 00:02:25 archived log file name=/oradata/chenj/arch/1_180348_864829931.dbf thread=1 sequence=180348 archived log file name=/oradata/chenj/arch/2_182735_864829931.dbf thread=2 sequence=182735 archived log file name=/oradata/chenj/arch/1_180349_864829931.dbf thread=1 sequence=180349 archived log file name=/oradata/chenj/arch/1_180350_864829931.dbf thread=1 sequence=180350 archived log file name=/oradata/chenj/arch/2_182736_864829931.dbf thread=2 sequence=182736 unable to find archived log archived log thread=1 sequence=180351 released channel: ch1 released channel: ch2 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 05/07/2021 08:37:27 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 180351 and starting SCN of 542755860991 RMAN> Recovery Manager complete.
recover最後一步報錯了，提示找不到180351。 檢視180351歸檔生成時間，發現是5月5日的歸檔，說明資料也只恢復到了5月5日，丟失了1天的資料。 出現這個問題的原因就是前面提到的，控制檔案恢復的時間不對，導致控制檔案記錄的備份資訊不全。
解決方案：
由於恢復完成後，資料庫執行了open read only操作，而並沒有執行open resetlogs操作，說明資料庫還可以繼續執行recovery操作，
之前還原的資料檔案不需要在執行restore操作了。
具體操作：
1.停庫備份控制檔案。 2.恢復出較新的控制檔案(時間略大約05/06/2021 08:05:05)。 3.用新的控制檔案掛載資料庫。 4.restore出需要的歸檔檔案。 5.停庫，用備份的控制檔案掛載資料庫(不在需要轉換路徑了)。 6.將新恢復出的歸檔日誌註冊到老控制檔案中。 7.繼續執行recovery操作。
###chenjuchao 20210711 11:30###
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2780725/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle RMAN 表空間的完全恢復Oracle
- Oracle 11g RMAN恢復-只讀表空間的恢復Oracle
- rman恢復資料檔案 恢復表空間
- 【Oracle】rman 恢復只讀表空間資料庫Oracle資料庫
- oracle實驗記錄 （恢復-表空間基於時間點恢復(rman)）Oracle
- 【Oracle 恢復表空間】 實驗Oracle
- 恢復Oracle表空間的方法Oracle
- 【RMAN】表空間基於時間點的RMAN恢復-TSPITR
- Oracle 11g RMAN恢復-只讀表空間的恢復（備份是在表空間只讀狀態下做的)Oracle
- Oracle 11g RMAN恢復-使用者誤刪除表空間Oracle
- 非系統表空間損壞，rman備份恢復
- RMAN恢復單個表空間或被DROP/DELETE/TRUNCATE的表delete
- 【TSPITR】RMAN表空間基於時間點的自動恢復
- 【管理篇備份恢復】rman恢復測試（一） 表空間資料檔案
- Oracle表空間時間點恢復技術TSPITROracle
- Oracle 傳輸表空間-RmanOracle
- oracle實驗記錄 （恢復-表空間基於時間點恢復(手動)）Oracle
- 用RMAN執行表空間及時點恢復——RMAN使用者手冊
- 【RMAN】SYSTEM表空間資料檔案丟失恢復模擬
- SYSAUX表空間管理及恢復UX
- Oracle rman 備份與恢復 臨時表空間的檔案問題解決Oracle
- Backup And Recovery User's Guide-從RMAN開始-恢復表空間GUIIDE
- 表空間TSPITR恢復-實驗
- 執行RMAN表空間及時點恢復——使用者管理備份和恢復手冊
- 刪除表空間,有rman全備的恢復(使用dbms_backup_restore來進行恢復)REST
- Backup And Recovery User's Guide-執行RMAN表空間時間點恢復（TSPIRT）GUIIDE
- Oracle 10g備份與恢復高階使用者指南--第八章 RMAN表空間時間點恢復（TSPITR）Oracle 10g
- Oracle可恢復空間分配技術Oracle
- 根據表空間的TSPITR恢復
- (Les16 執行資料庫恢復)-表空間恢復資料庫
- 利用可恢復空間分配技術自動分配表空間
- Oracle 12C新特性-RMAN恢復表Oracle
- oracle小知識點10--表空間時間點恢復(TSPITR)Oracle
- oracle監控表空間,JOB，rman備份Oracle
- ORACLE 11g TSPITR恢復被刪除的表空間Oracle
- RMAN遷移表空間
- mysql無備份恢復-獨立表空間MySql
- flashback database 恢復誤刪除的表空間。Database