使用RMAN對CDB執行按時間點恢復
使用RMAN對CDB和PDB執行按時間點恢復
RMAN能夠對CDB與PDB執行按時間點恢復操作。但是PDB只能使用RMAN來執行按時間點恢復。如是沒有使用恢復目錄資料庫,建議啟用自動控制檔案備份。否則當對PDB執行按時間點恢復時,當RMAN需要增加與刪除undo資料檔案時不能有效的執行。
PDB按時間點恢復與快速恢復區
當對PDB執行資料庫按時間點恢復時,對於這個PDB所有的資料檔案都將被執行恢復操作。然而,為了將PDB恢復到指定的時間點,RMAN在恢復目標時間點也是需要有UNDO表空間存在的。因為undo表空間是被所有PDB所共享的,它不能被恢復。RMAN會將root中的undo,system與sysaux表空間還原到輔助例項中,然後使用undo資訊來將pdb恢復到指定的時間點。如果配置了快速恢復區,Oracle將會使用它作為輔助例項的儲存目錄。如果快速恢復區沒有被配置,那麼必須使用auxiliary destination子句來指定輔助例項資料庫檔案的儲存目錄。確保在快速恢復區有足夠的空間可以用來還原root表空間與undo表空間。如果快速恢復區沒有足夠的空間,可以透過使用auxiliary destination子句來指定其它的目錄。
對CDB執行資料庫按時間點恢復
1.登入資料庫記錄當前SCN號,然後將表t1中的資料刪除。
SQL> conn jy/jy@jypdb Connected. SQL> SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ----------- 6041183 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SQL> select sysdate from dual; SYSDATE ------------------- 2017-12-18 18:28:30 SQL> select count(*) from t1; COUNT(*) ---------- 39 SQL> truncate table t1; Table truncated. SQL> select count(*) from t1; COUNT(*) ---------- 0
2.如果使用時間表示式來代替目標SCN,那麼在呼叫RMAN之前設定時間格式環境變數
[oracle@jytest1 ~]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
3.使用RMAN連線到root容器
[oracle@jytest1 ~]$ rman target/ catalog rco/abcd@jypdb_173 Recovery Manager: Release 12.2.0.1.0 - Production on Mon Dec 18 18:32:00 2017 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: JY (DBID=979425723) connected to recovery catalog database
4.將CDB重啟到mount狀態
RMAN> shutdown immediate starting full resync of recovery catalog full resync complete database closed database dismounted Oracle instance shut down RMAN> startup mount connected to target database (not started) Oracle instance started database mounted Total System Global Area 6442450944 bytes Fixed Size 8807168 bytes Variable Size 1895828736 bytes Database Buffers 4529848320 bytes Redo Buffers 7966720 bytes starting full resync of recovery catalog full resync complete
5.使用RUN塊來執行以下操作
a.對於資料庫按時間點鶋,使用set until來指定恢復的目標時間,scn或日誌序列號,或者使用set to來指定還原點。如果指定時間那麼使用環境變數nls_lang與nls_date_format中所指定的日期格式。
b.如果RMAN沒有配置自動通道,那麼需要手動分配磁碟與磁帶通道。
c.還原與恢復CDB
下面的命令將CDB恢復到SCN=6041183所在的狀態
RMAN> run 2> { 3> set until scn 6041183; 4> restore database; 5> recover database; 6> } executing command: SET until clause Starting restore at 2017-12-18 18:46:50 flashing back control file to SCN 6041183 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=764 instance=jy1 device type=DISK skipping datafile 5; already restored to file +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675 skipping datafile 6; already restored to file +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675 skipping datafile 8; already restored to file +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675 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 +DATA/JY/DATAFILE/system.317.962209603 channel ORA_DISK_1: restoring datafile 00003 to +DATA/JY/DATAFILE/sysaux.298.962209605 channel ORA_DISK_1: restoring datafile 00004 to +DATA/JY/DATAFILE/undotbs1.277.962209605 channel ORA_DISK_1: restoring datafile 00007 to +DATA/JY/DATAFILE/users.301.962209605 channel ORA_DISK_1: restoring datafile 00009 to +DATA/JY/DATAFILE/undotbs2.312.962209605 channel ORA_DISK_1: reading from backup piece +TEST/rman_backup/jy_979425723_962563410_10slv3ai_1_1 channel ORA_DISK_1: piece handle=+TEST/rman_backup/jy_979425723_962563410_10slv3ai_1_1 tag=TAG20171212T184328 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:05 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 00010 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649 channel ORA_DISK_1: restoring datafile 00011 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649 channel ORA_DISK_1: restoring datafile 00012 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649 channel ORA_DISK_1: restoring datafile 00013 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649 channel ORA_DISK_1: restoring datafile 00014 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649 channel ORA_DISK_1: restoring datafile 00015 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609 channel ORA_DISK_1: reading from backup piece +TEST/rman_backup/jy_979425723_962563516_11slv3ds_1_1 channel ORA_DISK_1: piece handle=+TEST/rman_backup/jy_979425723_962563516_11slv3ds_1_1 tag=TAG20171212T184328 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 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 00016 to +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409 channel ORA_DISK_1: restoring datafile 00017 to +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409 channel ORA_DISK_1: restoring datafile 00018 to +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409 channel ORA_DISK_1: restoring datafile 00019 to +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409 channel ORA_DISK_1: restoring datafile 00020 to +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409 channel ORA_DISK_1: restoring datafile 00021 to +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409 channel ORA_DISK_1: reading from backup piece +TEST/rman_backup/jy_979425723_962563551_12slv3ev_1_1 channel ORA_DISK_1: piece handle=+TEST/rman_backup/jy_979425723_962563551_12slv3ev_1_1 tag=TAG20171212T184328 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 Finished restore at 2017-12-18 18:49:09 Starting recover at 2017-12-18 18:49:11 using channel ORA_DISK_1 applied offline range to datafile 00010 offline range RECID=80 STAMP=963072332 applied offline range to datafile 00011 offline range RECID=79 STAMP=963072332 applied offline range to datafile 00012 offline range RECID=78 STAMP=963072332 applied offline range to datafile 00013 offline range RECID=77 STAMP=963072332 applied offline range to datafile 00014 offline range RECID=76 STAMP=963072332 applied offline range to datafile 00015 offline range RECID=75 STAMP=963072332 applied offline range to datafile 00016 offline range RECID=86 STAMP=963072332 applied offline range to datafile 00017 offline range RECID=85 STAMP=963072332 applied offline range to datafile 00018 offline range RECID=84 STAMP=963072332 applied offline range to datafile 00019 offline range RECID=83 STAMP=963072332 applied offline range to datafile 00020 offline range RECID=82 STAMP=963072332 applied offline range to datafile 00021 offline range RECID=81 STAMP=963072332 starting media recovery archived log for thread 1 with sequence 34 is already on disk as file +TEST/arch/1_34_961976319.dbf archived log for thread 1 with sequence 35 is already on disk as file +TEST/arch/1_35_961976319.dbf archived log for thread 1 with sequence 36 is already on disk as file +TEST/arch/1_36_961976319.dbf archived log for thread 1 with sequence 37 is already on disk as file +TEST/arch/1_37_961976319.dbf archived log for thread 1 with sequence 38 is already on disk as file +TEST/arch/1_38_961976319.dbf archived log for thread 1 with sequence 39 is already on disk as file +TEST/arch/1_39_961976319.dbf archived log for thread 1 with sequence 40 is already on disk as file +TEST/arch/1_40_961976319.dbf archived log for thread 1 with sequence 41 is already on disk as file +TEST/arch/1_41_961976319.dbf archived log for thread 1 with sequence 42 is already on disk as file +TEST/arch/1_42_961976319.dbf archived log for thread 1 with sequence 43 is already on disk as file +TEST/arch/1_43_961976319.dbf archived log for thread 1 with sequence 44 is already on disk as file +TEST/arch/1_44_961976319.dbf archived log for thread 1 with sequence 45 is already on disk as file +TEST/arch/1_45_961976319.dbf archived log for thread 1 with sequence 46 is already on disk as file +TEST/arch/1_46_961976319.dbf archived log for thread 1 with sequence 47 is already on disk as file +TEST/arch/1_47_961976319.dbf archived log for thread 1 with sequence 48 is already on disk as file +TEST/arch/1_48_961976319.dbf archived log for thread 1 with sequence 49 is already on disk as file +TEST/arch/1_49_961976319.dbf archived log for thread 1 with sequence 50 is already on disk as file +TEST/arch/1_50_961976319.dbf archived log for thread 1 with sequence 51 is already on disk as file +TEST/arch/1_51_961976319.dbf archived log for thread 1 with sequence 52 is already on disk as file +DATA/JY/ONLINELOG/group_2.302.961976321 archived log for thread 1 with sequence 53 is already on disk as file +DATA/JY/ONLINELOG/group_1.261.961976319 archived log for thread 2 with sequence 28 is already on disk as file +TEST/arch/2_28_961976319.dbf archived log for thread 2 with sequence 29 is already on disk as file +TEST/arch/2_29_961976319.dbf archived log for thread 2 with sequence 30 is already on disk as file +TEST/arch/2_30_961976319.dbf archived log for thread 2 with sequence 31 is already on disk as file +TEST/arch/2_31_961976319.dbf archived log for thread 2 with sequence 32 is already on disk as file +TEST/arch/2_32_961976319.dbf archived log for thread 2 with sequence 33 is already on disk as file +TEST/arch/2_33_961976319.dbf archived log for thread 2 with sequence 34 is already on disk as file +TEST/arch/2_34_961976319.dbf archived log for thread 2 with sequence 35 is already on disk as file +TEST/arch/2_35_961976319.dbf archived log for thread 2 with sequence 36 is already on disk as file +TEST/arch/2_36_961976319.dbf archived log for thread 2 with sequence 37 is already on disk as file +TEST/arch/2_37_961976319.dbf archived log for thread 2 with sequence 38 is already on disk as file +TEST/arch/2_38_961976319.dbf archived log for thread 2 with sequence 39 is already on disk as file +TEST/arch/2_39_961976319.dbf archived log for thread 2 with sequence 40 is already on disk as file +TEST/arch/2_40_961976319.dbf archived log for thread 2 with sequence 41 is already on disk as file +TEST/arch/2_41_961976319.dbf archived log for thread 2 with sequence 42 is already on disk as file +TEST/arch/2_42_961976319.dbf archived log for thread 2 with sequence 43 is already on disk as file +TEST/arch/2_43_961976319.dbf archived log for thread 2 with sequence 44 is already on disk as file +TEST/arch/2_44_961976319.dbf archived log for thread 2 with sequence 45 is already on disk as file +TEST/arch/2_45_961976319.dbf archived log for thread 2 with sequence 46 is already on disk as file +TEST/arch/2_46_961976319.dbf archived log for thread 2 with sequence 47 is already on disk as file +TEST/arch/2_47_961976319.dbf archived log for thread 2 with sequence 48 is already on disk as file +TEST/arch/2_48_961976319.dbf archived log for thread 2 with sequence 49 is already on disk as file +TEST/arch/2_49_961976319.dbf archived log for thread 2 with sequence 50 is already on disk as file +TEST/arch/2_50_961976319.dbf archived log for thread 2 with sequence 51 is already on disk as file +TEST/arch/2_51_961976319.dbf archived log for thread 2 with sequence 52 is already on disk as file +DATA/JY/ONLINELOG/group_4.262.961976705 archived log for thread 2 with sequence 53 is already on disk as file +DATA/JY/ONLINELOG/group_3.263.961976697 archived log file name=+TEST/arch/1_34_961976319.dbf thread=1 sequence=34 archived log file name=+TEST/arch/2_28_961976319.dbf thread=2 sequence=28 archived log file name=+TEST/arch/1_35_961976319.dbf thread=1 sequence=35 archived log file name=+TEST/arch/2_29_961976319.dbf thread=2 sequence=29 archived log file name=+TEST/arch/1_36_961976319.dbf thread=1 sequence=36 archived log file name=+TEST/arch/2_30_961976319.dbf thread=2 sequence=30 archived log file name=+TEST/arch/2_31_961976319.dbf thread=2 sequence=31 archived log file name=+TEST/arch/1_37_961976319.dbf thread=1 sequence=37 archived log file name=+TEST/arch/2_32_961976319.dbf thread=2 sequence=32 archived log file name=+TEST/arch/1_38_961976319.dbf thread=1 sequence=38 archived log file name=+TEST/arch/2_33_961976319.dbf thread=2 sequence=33 archived log file name=+TEST/arch/1_39_961976319.dbf thread=1 sequence=39 archived log file name=+TEST/arch/2_34_961976319.dbf thread=2 sequence=34 archived log file name=+TEST/arch/1_40_961976319.dbf thread=1 sequence=40 archived log file name=+TEST/arch/2_35_961976319.dbf thread=2 sequence=35 archived log file name=+TEST/arch/1_41_961976319.dbf thread=1 sequence=41 archived log file name=+TEST/arch/2_36_961976319.dbf thread=2 sequence=36 archived log file name=+TEST/arch/1_42_961976319.dbf thread=1 sequence=42 archived log file name=+TEST/arch/2_37_961976319.dbf thread=2 sequence=37 archived log file name=+TEST/arch/2_38_961976319.dbf thread=2 sequence=38 archived log file name=+TEST/arch/1_43_961976319.dbf thread=1 sequence=43 archived log file name=+TEST/arch/2_39_961976319.dbf thread=2 sequence=39 archived log file name=+TEST/arch/1_44_961976319.dbf thread=1 sequence=44 archived log file name=+TEST/arch/2_40_961976319.dbf thread=2 sequence=40 archived log file name=+TEST/arch/1_45_961976319.dbf thread=1 sequence=45 archived log file name=+TEST/arch/2_41_961976319.dbf thread=2 sequence=41 archived log file name=+TEST/arch/1_46_961976319.dbf thread=1 sequence=46 archived log file name=+TEST/arch/2_42_961976319.dbf thread=2 sequence=42 archived log file name=+TEST/arch/2_43_961976319.dbf thread=2 sequence=43 archived log file name=+TEST/arch/1_47_961976319.dbf thread=1 sequence=47 archived log file name=+TEST/arch/2_44_961976319.dbf thread=2 sequence=44 archived log file name=+TEST/arch/2_45_961976319.dbf thread=2 sequence=45 archived log file name=+TEST/arch/1_48_961976319.dbf thread=1 sequence=48 archived log file name=+TEST/arch/2_46_961976319.dbf thread=2 sequence=46 archived log file name=+TEST/arch/1_49_961976319.dbf thread=1 sequence=49 archived log file name=+TEST/arch/2_47_961976319.dbf thread=2 sequence=47 archived log file name=+TEST/arch/2_48_961976319.dbf thread=2 sequence=48 archived log file name=+TEST/arch/1_50_961976319.dbf thread=1 sequence=50 archived log file name=+TEST/arch/2_49_961976319.dbf thread=2 sequence=49 archived log file name=+TEST/arch/1_51_961976319.dbf thread=1 sequence=51 archived log file name=+TEST/arch/2_50_961976319.dbf thread=2 sequence=50 archived log file name=+TEST/arch/2_51_961976319.dbf thread=2 sequence=51 media recovery complete, elapsed time: 00:08:44 Finished recover at 2017-12-18 18:58:02
6.執行以下互斥操作
.以讀寫方式開啟CDB,放棄目標SCN之後的所有改變。在這種情況下,你必須將CDB重啟到mount狀態後,然後執行以下命令
alter database open resetlogs
.使用Data Pump匯出CDB中你所需要的物件。然後將CDB恢復到當前時間點並重新匯入物件,因此可以將需要的物件進行恢復而不用放棄所有其它物件所發生的改變。
RMAN> alter database open read only; Statement processed RMAN> alter pluggable database all open read only; Statement processed SQL> conn jy/jy@jypdb Connected. SQL> select count(*) from t1; COUNT(*) ---------- 39 RMAN> shutdown immediate database closed database dismounted Oracle instance shut down RMAN> startup mount connected to target database (not started) Oracle instance started database mounted Total System Global Area 6442450944 bytes Fixed Size 8807168 bytes Variable Size 1895828736 bytes Database Buffers 4529848320 bytes Redo Buffers 7966720 bytes RMAN> recover database; Starting recover at 2017-12-18 22:26:55 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=10 instance=jy1 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:05 Finished recover at 2017-12-18 22:27:05 RMAN> alter database open; Statement processed starting full resync of recovery catalog full resync complete RMAN> alter pluggable database all open read write; Statement processed starting full resync of recovery catalog full resync complete
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26015009/viewspace-2148924/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 使用RMAN對PDB執行按時間點恢復
- RMAN中使用until time子句對Non-CDB中的表執行按時間點恢復
- 使用RMAN對CDB的root執行完全恢復
- Oracle 12c使用RMAN備份對Non-CDB中的表按時間點進行恢復Oracle
- Oracle 12C使用UNTIL SEQUENCE子句對Non-CDB中的表執行按時間點恢復Oracle
- 用RMAN執行表空間及時點恢復——RMAN使用者手冊
- 執行RMAN表空間及時點恢復——使用者管理備份和恢復手冊
- rman恢復到指定時間點
- Backup And Recovery User's Guide-執行RMAN表空間時間點恢復(TSPIRT)GUIIDE
- RMAN恢復 執行重要檔案RMAN恢復
- 關於 RMAN 對於可插拔資料庫按時間點的恢復 (文件 ID 1984554.1)資料庫
- 【RMAN】表空間基於時間點的RMAN恢復-TSPITR
- 使用RMAN來PDB執行完全恢復
- 使用RMAN對CDB執行閃回資料庫操作資料庫
- oracle實驗記錄 (恢復-表空間基於時間點恢復(rman))Oracle
- 使用RMAN對PDB中的表空間或資料檔案執行完全恢復
- RMAN基於時間點恢復Oracle資料庫Oracle資料庫
- 恢復到特定點(時間點、scn、日誌序列號),rman不完全恢復
- 用rman執行塊恢復
- RMAN恢復 執行不重要檔案的RMAN恢復
- 【RMAN】使用RMAN備份將資料庫不完全恢復到指定時間點資料庫
- [RMAN]使用RMAN備份將資料庫不完全恢復到指定時間點資料庫
- PostgreSQL 時間點恢復SQL
- rman恢復 使用switch映像副本進行恢復
- 【TSPITR】RMAN表空間基於時間點的自動恢復
- OCP課程48:管理II之使用RMAN執行恢復
- rman恢復時跳過資料檔案,進行恢復
- Backup And Recovery User's Guide-執行資料庫時間點恢復GUIIDE資料庫
- Oracle RMAN 表空間恢復Oracle
- Oracle基於時間點的恢復Oracle
- rman恢復的時候可以使用LASTSCNAST
- Oracle 10g備份與恢復高階使用者指南--第八章 RMAN表空間時間點恢復(TSPITR)Oracle 10g
- 使用RMAN的不完全恢復-基於時間/SCN/日誌序列
- rman恢復資料檔案 恢復表空間
- 12C針對cdb全備與 PDB執行不完全恢復(基於SCN)
- 基於時間執行資料庫不完全恢復資料庫
- 刪除表空間,有rman全備的恢復(使用dbms_backup_restore來進行恢復)REST
- 獲取rman備份/恢復執行進度資訊