當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

在oracle下我们如何正确的执行数据库恢复

發(fā)布時(shí)間：2023/12/20 数据库 28 豆豆

生活随笔收集整理的這篇文章主要介紹了在oracle下我们如何正确的执行数据库恢复小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

當(dāng)數(shù)據(jù)庫(kù)需要進(jìn)行介質(zhì)恢復(fù)時(shí)，為了確保數(shù)據(jù)庫(kù)能夠順利的執(zhí)行恢復(fù)過程，恢復(fù)數(shù)據(jù)庫(kù)到當(dāng)前狀態(tài)。我們要做的就是驗(yàn)證！驗(yàn)證什么呢？當(dāng)然是驗(yàn)證備份集和歸檔是否能夠進(jìn)行有效的恢復(fù)。防止我們restore后，執(zhí)行recover時(shí)卻發(fā)現(xiàn)歸檔缺少了一堆，頓時(shí)傻眼。

比方說，在數(shù)據(jù)庫(kù)當(dāng)前日志序列號(hào)為3時(shí)我們完全備份了數(shù)據(jù)庫(kù)。在數(shù)據(jù)庫(kù)當(dāng)前聯(lián)機(jī)日志序列號(hào)為13時(shí)數(shù)據(jù)庫(kù)損壞需要恢復(fù)。假設(shè)數(shù)據(jù)庫(kù)聯(lián)機(jī)日志組為3組，則可以推斷數(shù)據(jù)庫(kù)聯(lián)機(jī)日志序列號(hào)分別為11、12、13。因此當(dāng)數(shù)據(jù)庫(kù)執(zhí)行restore database后，再執(zhí)行recover時(shí)不難推斷數(shù)據(jù)庫(kù)需要應(yīng)用歸檔3、4、5、6、7、8、9、10以及聯(lián)機(jī)日志11、12、13來進(jìn)行完全恢復(fù)。

為了能夠順利的執(zhí)行完全恢復(fù)，我們?cè)趫?zhí)行恢復(fù)前，需要對(duì)restore調(diào)用的備份集進(jìn)行恢復(fù)驗(yàn)證（語句為：restorevalidate database）以及驗(yàn)證recover過程所需的歸檔3-10（語句為：restore validate archivelog sequence between 3 and10）。

以完全恢復(fù)為例，舉例如下：

1數(shù)據(jù)庫(kù)當(dāng)前日志seq號(hào)為59，我們備份數(shù)據(jù)庫(kù)

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 當(dāng)數(shù)據(jù)庫(kù)聯(lián)機(jī)日志為69時(shí)數(shù)據(jù)庫(kù)崩潰需要進(jìn)行介質(zhì)恢復(fù)

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

? GROUP# ARC ?SEQUENCE# STATUS

---------- --- ---------- ----------------

? ? ? ?1 YES ? ? ? ? 67 INACTIVE

? ? ? ?2 YES ? ? ? ? 68 INACTIVE

? ? ? ?3 NO ? ? ? ? ?69 CURRENT

注意：這里其實(shí)我們可以推斷，如果數(shù)據(jù)庫(kù)需要恢復(fù)到當(dāng)前狀態(tài)，那么歸檔59到歸檔66的所有歸檔，必須能夠進(jìn)行有效的恢復(fù)。我們只需要發(fā)起restore database preview命令，Oracle便可以給出我們歸檔列表，繼續(xù)往下看。

3 判定當(dāng)前數(shù)據(jù)庫(kù)恢復(fù)所需要備份集和歸檔條目

注意對(duì)于restore database preview列出的歸檔條目，recover執(zhí)行完全恢復(fù)時(shí)并不會(huì)完全應(yīng)用，因?yàn)橥耆謴?fù)recover過程是：應(yīng)用相關(guān)歸檔+ 所有聯(lián)機(jī)日志，seq號(hào)從小到大依次應(yīng)用。后面會(huì)抓取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資料庫(kù)中記錄的數(shù)據(jù)文件最新的備份

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號(hào)這一列顯示的最后一個(gè)歸檔seq為68（從前面可知數(shù)據(jù)庫(kù)當(dāng)前聯(lián)機(jī)日志文件seq號(hào)為69）也就是說restore database preview顯示的歸檔列表結(jié)果中最后一個(gè)歸檔seq號(hào)總是比當(dāng)前聯(lián)機(jī)日志（當(dāng)前聯(lián)機(jī)日志也就是查看v$log狀態(tài)為currnt的日志組）文件seq號(hào)小于1.

2 ?結(jié)合當(dāng)前數(shù)據(jù)庫(kù)的聯(lián)機(jī)日志組seq號(hào)分別為67 68 69，可以判斷：在recover應(yīng)用最后一個(gè)歸檔seq號(hào)為66后，oracle會(huì)讀取seq號(hào)為67、68、69聯(lián)機(jī)日志文件繼續(xù)推進(jìn)該數(shù)據(jù)庫(kù)來實(shí)現(xiàn)整個(gè)數(shù)據(jù)庫(kù)完全恢復(fù)過程。

下面將演示整個(gè)驗(yàn)證和恢復(fù)過程:

4 驗(yàn)證恢復(fù)時(shí)需要用到的備份集是否能夠正?；謴?fù)。

RMAN> restore validate database;

注意：這條命令直接會(huì)去rman資料庫(kù)中找最新的備份集進(jìn)行驗(yàn)證，也就是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 驗(yàn)證恢復(fù)時(shí)應(yīng)用的歸檔

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 執(zhí)行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字節(jié)地址(RBA)的seq號(hào)(也就是是FHRBA_SEQ字段)可以得到restore database 后數(shù)據(jù)文件頭部記錄的rba.seq號(hào), 該值近一步表明recover過程需要從seq號(hào)為59歸檔開始應(yīng)用。

或者也可以從restore database后數(shù)據(jù)文件頭部的scn值，對(duì)比歸檔的first_change# 和 next_change# 推斷出recover 需要應(yīng)用歸檔開始。

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.

當(dāng)然restore database 后，我們也可以直接查詢v$recvoery_log來得到recover過程需要應(yīng)用的歸檔條目，如下所示：

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

注意：這里可以清楚的看到應(yīng)用的歸檔條目（紅色標(biāo)記處）

7 跟蹤recover過程內(nèi)容如下：

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)

注意：通過跟蹤整個(gè)恢復(fù)過程，可以清楚的觀察到在用recover進(jìn)行完全恢復(fù)時(shí)，先應(yīng)用歸檔，后再通過所有聯(lián)機(jī)日志文件推進(jìn)整個(gè)數(shù)據(jù)庫(kù)來實(shí)現(xiàn)完全恢復(fù)的過程。

8 如果數(shù)據(jù)庫(kù)進(jìn)行不完全恢復(fù)如何獲取恢復(fù)所需要的歸檔

以基于時(shí)間點(diǎn)恢復(fù)為例，我們可以這么使用得出恢復(fù)到這個(gè)時(shí)間點(diǎn)數(shù)據(jù)庫(kù)需要的歸檔列表。

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;

}

總結(jié)：

1 在對(duì)數(shù)據(jù)庫(kù)進(jìn)行恢復(fù)的時(shí)，第一步先看看數(shù)據(jù)庫(kù)是否歸檔，第二步看看數(shù)據(jù)庫(kù)是否有備份，第三步驗(yàn)證備份和歸檔的有效性。最后執(zhí)行整個(gè)恢復(fù)過程。

2 完全恢復(fù)時(shí)，通過對(duì)比restore database preview 顯示的歸檔列表seq號(hào)和聯(lián)機(jī)日志組的seq號(hào)，我們便可以清楚的推出數(shù)據(jù)庫(kù)完全恢復(fù)時(shí)，recover需要應(yīng)用的歸檔。

轉(zhuǎn)載于:https://blog.51cto.com/jiujian/1361353

總結(jié)

以上是生活随笔為你收集整理的在oracle下我们如何正确的执行数据库恢复的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。