在oracle下我們如何正确的執行資料庫恢複

當資料庫需要進行媒體恢複時，為了確定資料庫能夠順利的執行恢複過程，恢複資料庫到目前狀态。我們要做的就是驗證！驗證什麼呢？當然是驗證備份集和歸檔是否能夠進行有效的恢複。防止我們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

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;

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

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

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

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

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: 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

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需要應用的歸檔。

本文轉自 zhangxuwl 51CTO部落格，原文連結：http://blog.51cto.com/jiujian/1361353，如需轉載請自行聯系原作

在oracle下我們如何正确的執行資料庫恢複

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List