首頁 > 開發者 > 數據庫管理員

在Oracle下我們如何正確的執行資料庫恢複

來源：互聯網
上載者：User
創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

當資料庫需要進行介質恢複時，為了確保資料庫能夠順利的執行恢複過程，恢複資料庫到目前狀態。我們要做的就是驗證！驗證什麼呢？當然是驗證備份組和歸檔是否能夠進行有效恢複。防止我們restore後，執行recover時卻發現歸檔缺少了一堆，頓時傻眼。

比方說，在資料庫當前記錄序號為3時我們完全備份了資料庫。在資料庫當前聯機記錄序號為13時資料庫損壞需要恢複。假設資料庫聯機日誌組為3組，則可以推斷資料庫聯機記錄序號分別為11、12、13。因此當資料庫執行restore database後，再執行recover時不難推斷資料庫需要應用歸檔3、4、5、6、7、8、9、10以及聯機日誌11、12、13來進行完全恢複。

為了能夠順利的執行完全恢複，我們在執行恢複前，需要對restore調用的備份組進行恢複驗證（語句為：restorevalidate database）以及驗證recover過程所需的歸檔3-10（語句為：restore validate archivelog sequence between 3 and10）。

以完全恢複為例，舉例如下：

1資料庫當前日誌seq號為59，我們備份資料庫

SQL> selectgroup#,archived,sequence#,status from v$log;


  GROUP# ARC  SEQUENCE# STATUS
---------- --- ---------- ----------------
      1 YES        58 INACTIVE
      2 NO        59 CURRENT
      3 YES        57 INACTIVE


RMAN> backup database format'/backup/fullbk-%T-%U.bak';     


Starting backup at 2014-02-17 12:03:28
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafilebackup set
channel ORA_DISK_1: specifying datafile(s)in backup set
input datafile file number=00004name=/Oracle/CRM/CRM/users01.dbf
input datafile file number=00001name=/oracle/CRM/CRM/system01.dbf
input datafile file number=00002name=/oracle/CRM/CRM/sysaux01.dbf
input datafile file number=00003name=/oracle/CRM/CRM/undotbs01.dbf
input datafile file number=00005name=/oracle/CRM/CRM/crm.dbf
input datafile file number=00006name=/oracle/CRM/test.dbf
input datafile file number=00008name=/oracle/CRM/jxc.dbf
input datafile file number=00007name=/oracle/CRM/user01.dbf
channel ORA_DISK_1: starting piece 1 at2014-02-17 12:03:29
channel ORA_DISK_1: finished piece 1 at2014-02-17 12:05:57
piecehandle=/backup/fullbk-20140217-3ep0rj0h_1_1.bak tag=TAG20140217T120328 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:02:28
channel ORA_DISK_1: starting full datafilebackup set
channel ORA_DISK_1: specifying datafile(s)in backup set
including current control file in backupset
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at2014-02-17 12:06:01
channel ORA_DISK_1: finished piece 1 at2014-02-17 12:06:02
piecehandle=/backup/fullbk-20140217-3fp0rj56_1_1.bak tag=TAG20140217T120328comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01
Finished backup at 2014-02-17 12:06:02


2 當資料庫聯機日誌為69時資料庫崩潰需要進行介質恢複


SQL> selectgroup#,archived,sequence#,status from v$Log;


  GROUP# ARC  SEQUENCE# STATUS
---------- --- ---------- ----------------
      1 YES        67 INACTIVE
      2 YES        68 INACTIVE
      3 NO          69 CURRENT


注意：這裡其實我們可以推斷，如果資料庫需要恢複到目前狀態，那麼歸檔59到歸檔66的所有歸檔，必須能夠進行有效恢複。我們只需要發起restore database preview命令，Oracle便可以給出我們歸檔列表，繼續往下看。

 


3 判定當前資料庫恢複所需要備份組和歸檔條目


注意對於restore database preview列出的歸檔條目，recover執行完全恢複時並不會完全應用，因為完全恢複recover過程是：應用相關歸檔+ 所有聯機日誌，seq號從小到大依次應用。後面會抓取recover過程，這裡先暫且提一下。


RMAN> restore database preview;


Starting restore at 2014-02-17 16:14:21
using target database control file insteadof recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

 


List of Backup Sets
===================

 


BS Key Type LV Size      Device TypeElapsed Time Completion Time   
------- ---- -- ---------- ----------------------- -------------------
108    Full    2.03G      DISK        00:02:26    2014-02-17 12:05:38
      BP Key: 108  Status:AVAILABLE  Compressed: NO  Tag: TAG20140217T120328
Piece Name:/backup/fullbk-20140217-3ep0rj0h_1_1.bak 
注意：這裡顯示備份片總是rman資料庫中記錄的資料檔案最新的備份
List of Datafiles in backup set 108
File LV Type Ckp SCN    CkpTime            Name
---- -- ---- ---------- ------------------- ----
1      Full 4028039    2014-02-17 12:03:29/oracle/CRM/CRM/system01.dbf
2      Full 4028039    2014-02-17 12:03:29/oracle/CRM/CRM/sysaux01.dbf
3      Full 4028039    2014-02-17 12:03:29/oracle/CRM/CRM/undotbs01.dbf
4      Full 4028039    2014-02-17 12:03:29/oracle/CRM/CRM/users01.dbf
5      Full 4028039    2014-02-17 12:03:29 /oracle/CRM/CRM/crm.dbf
6      Full 4028039    2014-02-17 12:03:29 /oracle/CRM/test.dbf
7      Full 4028039    2014-02-17 12:03:29 /oracle/CRM/user01.dbf
8      Full 4028039    2014-02-17 12:03:29 /oracle/CRM/jxc.dbf
using channel ORA_DISK_1


List of Archived Log Copies for databasewith db_unique_name CRM
=====================================================================


Key    Thrd Seq    S Low Time         
------- ---- ------- - -------------------
131    1    59      A 2014-02-17 11:55:37
Name:/oracle/archivelog/arch_1_59_839098938.arch


132    1    60      A 2014-02-17 12:10:20
Name:/oracle/archivelog/arch_1_60_839098938.arch


133    1    61      A 2014-02-17 12:10:21
Name:/oracle/archivelog/arch_1_61_839098938.arch


134    1    62      A 2014-02-17 12:10:26
Name:/oracle/archivelog/arch_1_62_839098938.arch


135    1    63      A 2014-02-17 12:10:30
Name:/oracle/archivelog/arch_1_63_839098938.arch


136    1    64      A 2014-02-17 12:10:31
Name:/oracle/archivelog/arch_1_64_839098938.arch


137    1    65      A 2014-02-17 12:10:32
Name:/oracle/archivelog/arch_1_65_839098938.arch


138    1    66      A 2014-02-17 12:10:33
Name:/oracle/archivelog/arch_1_66_839098938.arch


139    1    67      A 2014-02-17 12:10:34
 Name:/oracle/archivelog/arch_1_67_839098938.arch


140    1    68      A 2014-02-17 12:10:36
Name:/oracle/archivelog/arch_1_68_839098938.arch


Media recovery start SCN is 4028039
Recovery must be done beyond SCN 4028039 toclear datafile fuzziness
Finished restore at 2014-02-17 16:14:24


注意：
1 上面seq號這一列顯示的最後一個歸檔seq為68（從前面可知資料庫當前聯機記錄檔seq號為69）也就是說restore database preview顯示的歸檔列表結果中最後一個歸檔seq號總是比當前聯機日誌（當前聯機日誌也就是查看v$log狀態為currnt的日誌組）檔案seq號小於1.


2  結合當前資料庫的聯機日誌組seq號分別為67 68 69，可以判斷：在recover應用最後一個歸檔seq號為66後，oracle會讀取seq號為67、68、69聯機記錄檔繼續推進該資料庫來實現整個資料庫完全恢複過程。
下面將示範整個驗證和恢複過程:


4 驗證恢複時需要用到的備份組是否能夠正常恢複。


RMAN> restore validate database;


注意：這條命令直接會去rman資料庫中找最新的備份組進行驗證，也就是restore database preview命令顯示的備份組。


Starting restore at 2014-02-17 16:14:59
using channel ORA_DISK_1


channel ORA_DISK_1: starting validation ofdatafile backup set
channel ORA_DISK_1: reading from backuppiece /backup/fullbk-20140217-3ep0rj0h_1_1.bak
channel ORA_DISK_1: piecehandle=/backup/fullbk-20140217-3ep0rj0h_1_1.bak tag=TAG20140217T120328
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete,elapsed time: 00:00:36
Finished restore at 2014-02-17 16:15:35

 


5 驗證恢複時應用的歸檔

 


RMAN> restore validate archivelogsequence between 59 and 66;


Starting restore at 2014-02-17 16:16:34
using channel ORA_DISK_1


channel ORA_DISK_1: scanning archived log /oracle/archivelog/arch_1_59_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_60_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_61_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_62_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_63_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_64_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_65_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_66_839098938.arch
Finished restore at 2014-02-17 16:16:37

 

 


6 執行restore和recover過程如下

 


RMAN> restore database;


Starting restore at 2014-02-17 16:36:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK


channel ORA_DISK_1: starting datafilebackup set restore
channel ORA_DISK_1: specifying datafile(s)to restore from backup set
channel ORA_DISK_1: restoring datafile 00001to /oracle/CRM/CRM/system01.dbf
channel ORA_DISK_1: restoring datafile00002 to /oracle/CRM/CRM/sysaux01.dbf
channel ORA_DISK_1: restoring datafile00003 to /oracle/CRM/CRM/undotbs01.dbf
channel ORA_DISK_1: restoring datafile00004 to /oracle/CRM/CRM/users01.dbf
channel ORA_DISK_1: restoring datafile00005 to /oracle/CRM/CRM/crm.dbf
channel ORA_DISK_1: restoring datafile00006 to /oracle/CRM/test.dbf
channel ORA_DISK_1: restoring datafile00007 to /oracle/CRM/user01.dbf
channel ORA_DISK_1: restoring datafile00008 to /oracle/CRM/jxc.dbf
channel ORA_DISK_1: reading from backuppiece /backup/fullbk-20140217-3ep0rj0h_1_1.bak
channel ORA_DISK_1: piecehandle=/backup/fullbk-20140217-3ep0rj0h_1_1.bak tag=TAG20140217T120328
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:02:08
Finished restore at 2014-02-17 16:38:35


注意：restore後我們通過查詢x$kcvfh的redo位元組地址(RBA)的seq號(也就是是FHRBA_SEQ欄位)可以得到restore database 後資料檔案頭部記錄的rba.seq號, 該值近一步表明recover過程需要從seq號為59歸檔開始應用。


或者也可以從restore database後資料檔案頭部的scn值，對比歸檔的first_change# 和 next_change# 推斷出recover 需要應用歸檔開始。


SQL> select hxfil,fhscn,fhrba_seq fromx$kcvfh;


  HXFIL FHSCN            FHRBA_SEQ
---------- ---------------- ----------
      1 4028039                  59
      2 4028039                59
      3 4028039                  59
      4 4028039                  59
      5 4028039                  59
      6 4028039                  59
      7 4028039                  59
      8 4028039                  59


8 rows selected.


當然restore database 後，我們也可以直接查詢v$recvoery_log來得到recover過程需要應用的歸檔條目，如下所示：
select * from v$recovery_log;


 THREAD#  SEQUENCE# TIME      ARCHIVE_NAME
---------- ---------- --------- ------------------------------------------------------------
      1        59 17-FEB-14/oracle/archivelog/arch_1_59_839098938.arch
      1        60 17-FEB-14/oracle/archivelog/arch_1_60_839098938.arch
      1        61 17-FEB-14 /oracle/archivelog/arch_1_61_839098938.arch
      1        62 17-FEB-14/oracle/archivelog/arch_1_62_839098938.arch
      1        63 17-FEB-14/oracle/archivelog/arch_1_63_839098938.arch
      1        64 17-FEB-14/oracle/archivelog/arch_1_64_839098938.arch
      1        65 17-FEB-14/oracle/archivelog/arch_1_65_839098938.arch
      1        66 17-FEB-14/oracle/archivelog/arch_1_66_839098938.arch

 


RMAN> recover database;


Starting recover at 2014-02-17 16:45:01
using channel ORA_DISK_1


starting media recovery


archived log for thread 1 with sequence 59is already on disk as file /oracle/archivelog/arch_1_59_839098938.arch
archived log for thread 1 with sequence 60is already on disk as file /oracle/archivelog/arch_1_60_839098938.arch
archived log for thread 1 with sequence 61is already on disk as file /oracle/archivelog/arch_1_61_839098938.arch
archived log for thread 1 with sequence 62is already on disk as file /oracle/archivelog/arch_1_62_839098938.arch
archived log for thread 1 with sequence 63is already on disk as file /oracle/archivelog/arch_1_63_839098938.arch
archived log for thread 1 with sequence 64is already on disk as file /oracle/archivelog/arch_1_64_839098938.arch
archived log for thread 1 with sequence 65is already on disk as file /oracle/archivelog/arch_1_65_839098938.arch
archived log for thread 1 with sequence 66is already on disk as file /oracle/archivelog/arch_1_66_839098938.arch
archived log for thread 1 with sequence 67is already on disk as file /oracle/archivelog/arch_1_67_839098938.arch
archived log for thread 1 with sequence 68is already on disk as file /oracle/archivelog/arch_1_68_839098938.arch
archived log filename=/oracle/archivelog/arch_1_59_839098938.arch thread=1 sequence=59
archived log file name=/oracle/archivelog/arch_1_60_839098938.archthread=1 sequence=60
archived log filename=/oracle/archivelog/arch_1_61_839098938.arch thread=1 sequence=61
archived log filename=/oracle/archivelog/arch_1_62_839098938.arch thread=1 sequence=62
archived log filename=/oracle/archivelog/arch_1_63_839098938.arch thread=1 sequence=63
archived log filename=/oracle/archivelog/arch_1_64_839098938.arch thread=1 sequence=64
archived log filename=/oracle/archivelog/arch_1_65_839098938.arch thread=1 sequence=65
archived log filename=/oracle/archivelog/arch_1_66_839098938.arch thread=1 sequence=66
media recovery complete, elapsed time:00:00:08
Finished recover at 2014-02-17 16:45:16
注意：這裡可以清楚的看到應用的歸檔條目（紅色標記處）


7 跟蹤recover過程內容如下：


alter database recoverlogfile '/oracle/archivelog/arch_1_59_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_59_839098938.arch
Mon Feb 17 16:45:12 2014
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_59_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_60_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_60_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_60_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_61_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_61_839098938.arch


ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_61_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_62_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_62_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_62_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_63_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_63_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_63_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_64_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_64_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_64_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_65_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_65_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_65_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_66_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_66_839098938.arch
Mon Feb 17 16:45:14 2014
Recovery of Online RedoLog: Thread 1 Group 1 Seq 67 Reading mem 0
Mem# 0: /oracle/CRM/CRM/redo01.log
Recovery of Online RedoLog: Thread 1 Group 2 Seq 68 Reading mem 0
Mem# 0: /oracle/CRM/CRM/redo02.log
Recovery of Online RedoLog: Thread 1 Group 3 Seq 69 Reading mem 0
Mem# 0: /oracle/CRM/CRM/redo03.log
Media Recovery Complete(CRM)


注意：通過跟蹤整個恢複過程，可以清楚的觀察到在用recover進行完全恢複時，先應用歸檔，後再通過所有聯機記錄檔推進整個資料庫來實現完全恢複的過程。

 


8 如果資料庫進行不完全恢複如何擷取恢複所需要的歸檔


以基於時間點恢複為例，我們可以這麼使用得出恢複到這個時間點資料庫需要的歸檔列表。


run{
sql 'alter session setnls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time='2013-12-09:05:50:12';
restore database preview;
}


總結：
1 在對資料庫進行恢複的時，第一步先看看資料庫是否歸檔，第二步看看資料庫是否有備份，第三步驗證備份和歸檔的有效性。最後執行整個恢複過程。


2 完全恢複時，通過對比restore database preview 顯示的歸檔列表seq號和聯機日誌組的seq號，我們便可以清楚的推出資料庫完全恢複時，recover需要應用的歸檔。

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

相關關鍵詞：
oracle oracle array oracle commit oracle curdate oracle http oracle last oracle oracle alias
相關文章
ORA-01658: 無法為資料表空間space中的段建立 INITIAL 區
Oracle ORA-02020 : 過多的資料庫連結在使用中解決步驟
ORA-12170 TNS 連線逾時解決方案
Oracle dump函數的用法，oracledump函數
ORA-24247: 網路訪問被存取控制清單 (ACL) 拒絕，ora-24247acl
sql資料庫還原，出現媒體家族的結構不正確，SQL Server無法處理此媒體家族的解決方案之一。，sqlserver

聯繫我們

該頁面正文內容均來源於網絡整理，並不代表阿里雲官方的觀點，該頁面所提到的產品和服務也與阿里云無關，如果該頁面內容對您造成了困擾，歡迎寫郵件給我們，收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容，歡迎發送郵件至： info-contact@alibabacloud.com 進行舉報並提供相關證據，工作人員會在 5 個工作天內聯絡您，一經查實，本站將立刻刪除涉嫌侵權內容。

熱門內容

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

  • Sales Support

    1 on 1 presale consultation

    Chat Contact Sales

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    Open a Ticket

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

    Learn More