【12c】12c RMAN新特性之UNTIL AVAILABLE REDO--自動恢復到REDO終點的步驟簡化
【12c】12c RMAN新特性之UNTIL AVAILABLE REDO--自動恢復到REDO終點的步驟簡化
在Oracle 12.2之前，當需要恢復資料庫到某個時間點的時候，需要確定SCN，或者日誌序列號，或者一個時間點，以便儘可能多的應用歸檔日誌，進而儘可能多的恢復資料。從12.2開始，RMAN新增引數“UNTIL AVALIABLE REDO”，語法如下:
RMAN> RECOVER DATABASE UNTIL AVALIABLE REDO;
RMAN將會根據控制檔案資訊和歸檔日誌、線日誌、歸檔日誌備份集的物理可用性，將資料庫恢復到最後一個可用的歸檔日誌。所以在進行恢復的時候，可以不需要指定SCN，或者時間或者日誌序列號。需要注意的是，資料檔案仍然需要在一致的情況下，資料庫才能開啟。
需要注意的是，這些新特性有如下的限制條件：
l 不能針對恢復資料檔案或者表空間使用這個命令。
l 不能針對恢復PDB使用這個命令。
l 只能針對全庫恢復使用這個命令。
| 12.2 NEW FEATURE : -RECOVER DATABASE UNTIL AVAILABLE REDO (文件 ID 2300465.1) |
In this Document
| Goal |
| Solution |
|
A> Using Current Controlfile. |
|
B> When using backup or restored controlfile. |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.2.0.1 to 12.2.0.1 [Release 12.2]Information in this document applies to any platform.
GOAL
Goal of this Document is to explain how to use the New Feature in 12.2 Recover database until available redo.
The New Syntax available in 12.2 is
Finds the last available archived redo log and online redo logs, and recovers the database to the point where a log is missing.
The database files still needs to be consistent enough to open the database.
LIMITATIONS/RESTRICTIONS
- You cannot recover a Datafile or Tablepace with this command.
- You cannot recover PDB using this command.
- You can use this command only while performing recovery of whole database.
SOLUTION
Whenever a database needs to be restored to a Point in time we need to determine SCN, or the Sequence or time in order to apply as many archived logs as possible, to recover as much data as possible.
The new syntax helps reduce this work as rman would physically check for the available Archive/redo copy or backupset and do the recovery till the last available Archivelog based on information in Controlfile and physical availability of the Archivelog copy/redo log/Archive log backupsets.So you don't have to specify any until SCN ,Sequence or time.
Here is an example which explains the new feature.
In this example we will delete couple of Archivelog's after the backups are taken.
A> Using Current Controlfile.
Step1 :- Take a backup of the database
Rman> backup database plus archivelog
Starting backup at 24-AUG-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=56 STAMP=952838603
channel ORA_DISK_1: starting piece 1 at 24-AUG-17
channel ORA_DISK_1: finished piece 1 at 24-AUG-17
piece handle=/u01/app/cdb/backupset/2017_08_24/o1_mf_annnn_TAG20170824T052759_dswryzts_.bkp tag=TAG20170824T052759 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=15 STAMP=952145574
input archived log thread=1 sequence=6 RECID=16 STAMP=952145576
input archived log thread=1 sequence=7 RECID=17 STAMP=952145579
input archived log thread=1 sequence=10 RECID=39 STAMP=952146261
input archived log thread=1 sequence=11 RECID=40 STAMP=952146361
channel ORA_DISK_1: starting piece 1 at 24-AUG-17
channel ORA_DISK_1: finished piece 1 at 24-AUG-17
piece handle=/u01/app/cdb/backupset/2017_08_24/o1_mf_annnn_TAG20170824T052759_dswrz70f_.bkp tag=TAG20170824T052759 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=57 STAMP=952838697
input archived log thread=1 sequence=3 RECID=58 STAMP=952838705
input archived log thread=1 sequence=4 RECID=59 STAMP=952838715
input archived log thread=1 sequence=5 RECID=60 STAMP=952838721
input archived log thread=1 sequence=6 RECID=61 STAMP=952838751
input archived log thread=1 sequence=7 RECID=62 STAMP=952838785
input archived log thread=1 sequence=8 RECID=63 STAMP=952838861
input archived log thread=1 sequence=9 RECID=64 STAMP=952838879
channel ORA_DISK_1: starting piece 1 at 24-AUG-17
channel ORA_DISK_1: finished piece 1 at 24-AUG-17
piece handle=/u01/app/cdb/backupset/2017_08_24/o1_mf_annnn_TAG20170824T052759_dswrz861_.bkp tag=TAG20170824T052759 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=44 STAMP=952146606
input archived log thread=1 sequence=16 RECID=45 STAMP=952146607
input archived log thread=1 sequence=17 RECID=55 STAMP=952147123
channel ORA_DISK_1: starting piece 1 at 24-AUG-17
channel ORA_DISK_1: finished piece 1 at 24-AUG-17
piece handle=/u01/app/cdb/backupset/2017_08_24/o1_mf_annnn_TAG20170824T052759_dswrz9bz_.bkp tag=TAG20170824T052759 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-AUG-17
Starting backup at 24-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/cdb/datafile/o1_mf_users_dqlzw5mf_.dbf
input datafile file number=00001 name=/u01/app/cdb/datafile/o1_mf_system_dqlz2fhb_.dbf
input datafile file number=00003 name=/u01/app/cdb/datafile/o1_mf_sysaux_dqlz2fj2_.dbf
input datafile file number=00004 name=/u01/app/cdb/datafile/o1_mf_undotbs1_dqlz2fjo_.dbf
input datafile file number=00013 name=/u01/app/cdb/datafile/o1_mf_abc_dqlz2fk4_.dbf
channel ORA_DISK_1: starting piece 1 at 24-AUG-17
channel ORA_DISK_1: finished piece 1 at 24-AUG-17
piece handle=/u01/app/cdb/backupset/2017_08_24/o1_mf_nnndf_TAG20170824T052810_dswrzc1c_.bkp tag=TAG20170824T052810 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/cdb/52A23D56DF2E3CC1E0535C08DC0A83FF/datafile/o1_mf_sysaux_dqlzwyxk_.dbf
input datafile file number=00009 name=/u01/app/cdb/52A23D56DF2E3CC1E0535C08DC0A83FF/datafile/o1_mf_system_dqlzwyyh_.dbf
input datafile file number=00011 name=/u01/app/cdb/52A23D56DF2E3CC1E0535C08DC0A83FF/datafile/o1_mf_undotbs1_dqlzwyz0_.dbf
input datafile file number=00012 name=/u01/app/cdb/52A23D56DF2E3CC1E0535C08DC0A83FF/datafile/o1_mf_users_dqlzwz06_.dbf
channel ORA_DISK_1: starting piece 1 at 24-AUG-17
channel ORA_DISK_1: finished piece 1 at 24-AUG-17
piece handle=/u01/app/cdb/52A23D56DF2E3CC1E0535C08DC0A83FF/backupset/2017_08_24/o1_mf_nnndf_TAG20170824T052810_dsws128w_.bkp tag=TAG20170824T052810 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/cdb/52A2312195952E57E0535C08DC0A137A/datafile/o1_mf_sysaux_dqlzxqtm_.dbf
input datafile file number=00005 name=/u01/app/cdb/52A2312195952E57E0535C08DC0A137A/datafile/o1_mf_system_dqlzxqvs_.dbf
input datafile file number=00008 name=/u01/app/cdb/52A2312195952E57E0535C08DC0A137A/datafile/o1_mf_undotbs1_dqlzxqwc_.dbf
channel ORA_DISK_1: starting piece 1 at 24-AUG-17
channel ORA_DISK_1: finished piece 1 at 24-AUG-17
piece handle=/u01/app/cdb/52A2312195952E57E0535C08DC0A137A/backupset/2017_08_24/o1_mf_nnndf_TAG20170824T052810_dsws1v45_.bkp tag=TAG20170824T052810 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 24-AUG-17
channel ORA_DISK_1: finished piece 1 at 24-AUG-17
piece handle=/u01/app/cdb/backupset/2017_08_24/o1_mf_ncsnf_TAG20170824T052810_dsws2o9d_.bkp tag=TAG20170824T052810 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-AUG-17
Starting backup at 24-AUG-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=65 STAMP=952838998 ===================> This is the minimum archivelog till which recovery would make database consistent to open.
channel ORA_DISK_1: starting piece 1 at 24-AUG-17
channel ORA_DISK_1: finished piece 1 at 24-AUG-17
piece handle=/u01/app/cdb/backupset/2017_08_24/o1_mf_annnn_TAG20170824T052958_dsws2pw8_.bkp tag=TAG20170824T052958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-AUG-17
So the last archivelog backed up is sequence 10 and thats the Minimum archivelog till which recovery needs to be done to open the database after restore.
Step 2 :- Do couple of Log switch and delete Couple of Archivelogs
Do couple of Logswitch on the Database
SQL>Alter system switch logfile ; ------------------>4-5 times
Archivelog the logfiles
SQL>Alter system archive log current ;
SQL> Select * from v$log ;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 16 209715200 512 1 NO CURRENT 5431318 24-AUG-17 1.8447E+19 0
2 1 14 209715200 512 1 YES INACTIVE 5431295 24-AUG-17 5431305 24-AUG-17 0
3 1 15 209715200 512 1 YES INACTIVE 5431305 24-AUG-17 5431318 24-AUG-17 0
Archivelog copies are present under
$ cd /u01/app/cdb/archivelog/2017_08_24
o1_mf_1_8_dswryf57_.arc
o1_mf_1_9_dswryzdm_.arc
o1_mf_1_10_dsws2plq_.arc
o1_mf_1_11_dsws3m5c_.arc
o1_mf_1_12_dsws3rom_.arc
o1_mf_1_13_dsws3xcf_.arc ----------------------> We delete this using rm
o1_mf_1_14_dsws40t3_.arc ----------------------> We delete this using rm
o1_mf_1_15_dsws496x_.arc
Now we will delete archivelog sequence 13 and 14 from OS using rm command . (Archivelog sequence 15 is still intact)
So we have All archivelogs until sequence 12 and then Archivelog sequence 15 and Current redo log having Sequence 16.
Step 3 : Restore the database from backup taken in Step 1
Restore the database from the backup taken from step 1
SQL>Shutdown immediate ;
SQL>Startup mount
rman target /
Rman> Restore database ;
Starting restore at 24-AUG-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2543 device type=DISK
skipping datafile 5; already restored to file /u01/app/cdb52A2312195952E57E0535C08DC0A137A/datafile/o1_mf_system_dqlzxqvs_.dbf
skipping datafile 6; already restored to file /u01/app/cdb52A2312195952E57E0535C08DC0A137A/datafile/o1_mf_sysaux_dqlzxqtm_.dbf
skipping datafile 8; already restored to file /u01/app/cdb52A2312195952E57E0535C08DC0A137A/datafile/o1_mf_undotbs1_dqlzxqwc_.dbf
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 /u01/app/cdbdatafile/o1_mf_system_dqlz2fhb_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/cdbdatafile/o1_mf_sysaux_dqlz2fj2_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/cdbdatafile/o1_mf_undotbs1_dqlz2fjo_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/cdbdatafile/o1_mf_users_dqlzw5mf_.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/cdbdatafile/o1_mf_abc_dqlz2fk4_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/cdb/backupset/2017_08_24/o1_mf_nnndf_TAG20170824T052810_dswrzc1c_.bkp
channel ORA_DISK_1: piece handle=/u01/app/cdbbackupset/2017_08_24/o1_mf_nnndf_TAG20170824T052810_dswrzc1c_.bkp tag=TAG20170824T052810
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
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 00009 to /u01/app/cdb52A23D56DF2E3CC1E0535C08DC0A83FF/datafile/o1_mf_system_dqlzwyyh_.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/cdb52A23D56DF2E3CC1E0535C08DC0A83FF/datafile/o1_mf_sysaux_dqlzwyxk_.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/cdb52A23D56DF2E3CC1E0535C08DC0A83FF/datafile/o1_mf_undotbs1_dqlzwyz0_.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/cdb52A23D56DF2E3CC1E0535C08DC0A83FF/datafile/o1_mf_users_dqlzwz06_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/cdb/backupset/2017_08_24/o1_mf_nnndf_TAG20170824T052810_dsws128w_.bkp
channel ORA_DISK_1: piece handle=/u01/app/cdb/backupset/2017_08_24/o1_mf_nnndf_TAG20170824T052810_dsws128w_.bkp tag=TAG20170824T052810
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 24-AUG-17
Step 4:- Use the new syntax
Rman> List copy of archivelog all ;
List of Archived Log Copies for database with db_unique_name CDB
=====================================================================
67 1 12 A 24-AUG-17
Name: /u01/app/cdb/u01/app/cdb/archivelog/2017_08_24/o1_mf_1_12_dsws3rom_./u01/app/cdb
68 1 13 A 24-AUG-17
Name: /u01/app/cdb/u01/app/cdb/archivelog/2017_08_24/o1_mf_1_13_dsws3xcf_./u01/app/cdb ===========> Shown Available as it was deleted from OS level and not Rman
69 1 14 A 24-AUG-17
Name: /u01/app/cdb/u01/app/cdb/archivelog/2017_08_24/o1_mf_1_14_dsws40t3_./u01/app/cdb ===========> Shown Available as it was deleted from OS level and not Rman
70 1 15 A 24-AUG-17
Name: /u01/app/cdb/u01/app/cdb/archivelog/2017_08_24/o1_mf_1_15_dsws496x_./u01/app/cdb
RMAN> Recover database until available redo ;
Starting recover at 24-AUG-17
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/cdb/archivelog/2017_08_24/o1_mf_1_10_dsws2plq_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/app/cdb/archivelog/2017_08_24/o1_mf_1_11_dsws3m5c_.arc
archived log for thread 1 with sequence 12 is already on disk as file /u01/app/cdb/archivelog/2017_08_24/o1_mf_1_12_dsws3rom_.arc
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/cdb/onlinelog/o1_mf_2_ds7ofpsb_.log
archived log for thread 1 with sequence 15 is already on disk as file /u01/app/cdb/archivelog/2017_08_24/o1_mf_1_15_dsws496x_.arc
archived log for thread 1 with sequence 16 is already on disk as file /u01/app/cdb/onlinelog/o1_mf_1_ds7ofmw0_.log
archived log file name=/u01/app/cdb/archivelog/2017_08_24/o1_mf_1_10_dsws2plq_.arc thread=1 sequence=10
archived log file name=/u01/app/cdb/archivelog/2017_08_24/o1_mf_1_11_dsws3m5c_.arc thread=1 sequence=11
archived log file name=/u01/app/cdb/archivelog/2017_08_24/o1_mf_1_12_dsws3rom_.arc thread=1 sequence=12
warning: attempt media recovery until thread 1, sequence 13 -----------------------------> Recover done Until sequence 13 (so applies last sequence 12)
Finished recover at 24-AUG-17
Here you can see the new syntax check which archivelogs are Physically available. In this case sequence 13 and sequence 14 were missing from OS level so it does a Recovery until Sequence 13 ;
RMAN> alter database open resetlogs;
using target database control file instead of recovery catalog
Statement processed
RMAN>
B> When using backup or restored controlfile.
The only change is Once the controlfile is restored and database is mounted ensure you Catalog all the backuppieces that are required so restored controlfile is aware about the backuppiece's.
You can use Catalog start with or Catalog backuppiece '<path and name>' command.
The steps to restore database and recovery are same as Step A.
About Me
.............................................................................................................................................
● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除
● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest
● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● DBA寶典今日頭條號地址：
.............................................................................................................................................
● QQ群號：230161599（滿）、618766405
● 微信群：可加我微信，我拉大家進群，非誠勿擾
● 聯絡我請加QQ好友（646634621），註明新增緣由
● 於 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成
● 最新修改時間：2018-04-01 06:00 ~ 2018-04-31 24:00
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
.............................................................................................................................................
● 小麥苗的微店：
● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/
● 小麥苗OCP、OCM、高可用網路班：http://blog.itpub.net/26736162/viewspace-2148098/
.............................................................................................................................................
使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。
小麥苗的微信公眾號 小麥苗的DBA寶典QQ群2 《DBA筆試面試寶典》讀者群 小麥苗的微店
.............................................................................................................................................
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2152715/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 12C新特性-RMAN恢復表Oracle
- 【12c】12c RMAN新特性之recover table（表級別恢復）
- Oracle 12C 新特性之 恢復表Oracle
- oracle 12c新特性：Rman表恢復之直接匯入系統Oracle
- 12c RMAN新特性之Recover Table
- 【12c】12c RMAN新特性之通過網路遠端恢復資料庫（RESTORE/Recover from Service）資料庫REST
- oracle 12C rman下表的恢復Oracle
- [20190718]12c rman新特性 表恢復.txt
- Oracle 12c RMAN 異機恢復Oracle
- Oracle 12C 新特性：Rman的單個表恢復測試--未匯入系統Oracle
- Oracle 12c新特性之Sequence的Session特性OracleSession
- 用rman進行恢復資料，簡單步驟！
- oracle 12c R1 新特性對單個表的恢復Oracle
- 12c恢復表到指定時刻
- Oracle 12c 新特性之 temp undoOracle
- 【RECO_ORACLE】NBU 8.2新特性——快速拉起的恢復步驟Oracle
- 12c新特性-Oracle Sharding簡介Oracle
- 10G RMAN恢復新特性
- Oracle 12c新特性Oracle
- 12C PDB使用RMAN的4種完全恢復場景
- Oracle9i RMAN 的優缺點及RMAN 備份及恢復步驟Oracle
- Oracle 12C 新特性之級聯truncateOracle
- Oracle 12C使用UNTIL SEQUENCE子句對Non-CDB中的表執行按時間點恢復Oracle
- 10g新特性:rman恢復時候資料檔案的自動建立
- Oracle 12C使用RMAN將PDB中分表的多個分割槽恢復到新使用者方案中Oracle
- Oracle 12c升級需遵循的簡單步驟NFOracle
- Oracle 12c 新特性（四）Oracle
- Oracle 12c 新特性（三）Oracle
- Oracle 12c 新特性（二）Oracle
- Oracle 12c 新特性（一）Oracle
- oracle 12c 新特性之不可見欄位Oracle
- oracle 12C 新特性之臨時undo控制Oracle
- rman恢復到指定時間點
- Oracle 12C使用RMAN將Non-CDB中分表的多個分割槽恢復到新使用者方案中Oracle
- Oracle 12C新特性-History命令Oracle
- Oracle 12c 兩個新特性Oracle
- Oracle 12C新特性In-MemoryOracle
- Oracle GoldenGate 12c 新特性OracleGo