flashback database怎么選擇需要應用的flashback log

179次閱讀

共計 9451 個字符，預計需要花費 24 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 flashback database 怎么選擇需要應用的 flashback log，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

flashback database 過程由 flashback restore 和 flashback recover 兩階段組成，flashback restore 是 apply flashback log，flashback recover 是 apply redolog
最近在使用 flashback database 時有個疑問，flashback restore 到底是從哪個 flashback logfile 開始 apply 的？

要把這個問題說清楚，先從下面兩個隱含參數說起
_flashback_barrier_interval：缺省值為 30 分鐘，表示隔多長時間往 flashback log 里寫入一個標記，這個標記的主要內容就是當時的 SCN 號，flashback restore 無一例外都必須將這 SCN 號作為 restore 的終點，flashback log 是按照 scn 號從大到小的逆向順序應用的，_flashback_barrier_interval 值越大表示數據庫發生更改時 data block before image 會以較低的頻次寫入 flashback log，因此 Flashback log writes 對數據庫的 IO 消耗越小，但在 flashback recover 時需要應用的 redolog 就會比較多。如果_flashback_barrier_interval 設置的比較小，before image 會被更頻繁的寫入 flashback log，Flashback log writes 對數據庫的 IO 消耗會較高，但 flashback recover 時需要應用的 redolog 會減少，因為標記里包含的 scn 與我們要 flashback 的目標 scn 距離更近。舉個例子，當前 flashback log 里已經有了如下一些標記(假設每 30 分鐘寫入一次標記)
標記的生成時間標記值
08:00 scn=100
08:30 scn=130
09:00 scn=155
09:30 scn=172

假設現在的時間是 9:38，我們要 flashback 到 8:50 這個時間點，那么在 flashback restore 的時候會按照 09:30- 09:00- 08:30 的順序以 08:30 作為終點，從 08:30 演進到 08:50 必須依靠 redolog，也就是必須要 apply 至少 20 分鐘的 Redolog，如果將_flashback_barrier_interval 將標記的寫入間隔時間縮短為 10 分鐘，那么 flashback recover 最多也只會 apply 10 分鐘的 redolog。_flashback_barrier_interval 對 flashback log 寫入次數的影響可以參考 http://blog.itpub.net/53956/viewspace-1602019/

_flashback_verbose_info：默認值為 FALSE，我們把它設成 TRUE，在 flashback 過程中輸出更多的日志

為減少篇幅，沒有把整個測試過程都加進來，但摘錄了一些足以能說明問題的片段，

— 執行 flashback database
flashback database to scn 12723361569813;

—flashback database 時的日志輸出(包括 alert.log 和.trc 文件的)
***alert.log 的信息輸出
Mon Apr 27 14:08:13 2015
flashback database to scn 12723361569813
Flashback Restore Start
Mon Apr 27 14:08:27 2015
Flashback Restore Complete
Flashback Media Recovery Start
started logmerger process
Mon Apr 27 14:08:27 2015
Flashback mount Marker scn during SCN 12723361569676 —flashback restore 的終點，scn barrier
Marker checkpoint scn during mount SCN 12723361567213 —flashback recover 的起點，從這個點開始應用 redolog
Marker fgda seq 5 bno 9045
Flashback mount unfinished crash recovery 1
Parallel Media Recovery started with 16 slaves
Flashback unfinished crash recovery is set during start of media recovery
Flashback Media Recovery Log /oradata06/fra/TSTDB1/archivelog/2015_04_27/o1_mf_1_1301_1jJsF6HsA_.arc
Recovery deleting file #9: /oradata06/testaaaaa/ts0422_1.dbf from controlfile.
Recovery dropped tablespace TS0422_1
Flashback recovery: Added file #9 to control file as OFFLINE and UNNAMED00009
because it was dropped during the flashback interval
or it was added during flashback media recovery.
File was originally created as:
/oradata06/testaaaaa/ts0422_1.dbf
File will have to be restored from a backup or
recreated using ALTER DATABASE CREATE DATAFILE command,
and the file has to be onlined and recovered.
Flashback Media Recovery Log /oradata06/fra/TSTDB1/archivelog/2015_04_27/o1_mf_1_1302_1jJsFPtbI_.arc
Flashback Media Recovery Log /oradata06/fra/TSTDB1/archivelog/2015_04_27/o1_mf_1_1303_1jJsSKVVs_.arc
Flashback Media Recovery Log /oradata06/fra/TSTDB1/archivelog/2015_04_27/o1_mf_1_1304_1jJsVb6To_.arc
Flashback Media Recovery Log /oradata06/fra/TSTDB1/archivelog/2015_04_27/o1_mf_1_1305_1jJscXQFZ_.arc
Incomplete Recovery applied until change 12723361569814 time 04/27/2015 14:02:27
Flashback Media Recovery Complete
Completed: flashback database to scn 12723361569813

***tstdb1_ora_15271434.trc 的信息輸出
Total Elapsed time 0.04s
Number of sequential reads: 1 = 1024Kb per read
Sequential read speed: 1024Kb in 0.04s = 27.03 Mb/sec
Total waited on sequential reads to complete 0.01s
Number of random reads: 1 = 16Kb per read
Random read speed: 16Kb in 0.04s = 0.00 Mb/sec
Total waited on random reads to complete 0.01s
No async reads.
Number of position calls/cache misses: 1/1
Record buffer resizes = 0
———————————————-
Excluding datafile 7 from restore portion of flashback because it
does not have any changes before the flashback target.
Excluding datafile 8 from restore portion of flashback because it
does not have any changes before the flashback target.
————– Flashback Parameters ————–
Target (to-before) scn: 0x0b92.63725c16 [2962.1668439062]
Restore (to-before) scn: 0x0b92.63725c16 [2962.1668439062]
Current inc 1, Restore target inc 1, Recovery target inc 1
Recovery start checkpoint:
scn: 0x0b92.637251ed [2962.1668436461] 04/27/2015 13:54:24 —–0x0b92.637251ed 十進制：12723361567213
thread:1 rba:(0x515.1e6.0)

alert.log 里：
Flashback mount Marker scn during SCN 12723361569676 表示 flashback restore 的終點，也稱為 scn barrier
Marker checkpoint scn during mount SCN 12723361567213 表示 flashback recover 的起點，從這個點開始到 Flashback 目標 scn 的 redolog 都會應用到

我們從 tstdb1_ora_15271434.trc 文件里也可以看到這么一段相關的內容，0x0b92.637251ed 轉換成十進制后正是 12723361567213，表示 flashback recover 的起點，0x0b92.63725b8c 轉換成十進制后是 12723361569676，表示 flashback restore 的終點，與 alert.log 里反應的信息一致。
Recovery start checkpoint:
scn: 0x0b92.637251ed [2962.1668436461] 04/27/2015 13:54:24
。。。。。省略部分無關內容
。。。。。
Marker:
Previous logical record fba: (lno 5 thr 1 seq 5 bno 9292 bof 80)
Record scn: 0x0b92.63725b8e [2962.1668438926]
Marker scn: 0x0b92.63725b8c [2962.1668438924] 04/27/2015 14:00:23
Flag 0x0
Flashback threads: 1, Enabled redo threads 1
Recovery Start Checkpoint:
scn: 0x0b92.637251ed [2962.1668436461] 04/27/2015 13:54:24
thread:1 rba:(0x515.1e6.0)

由此我們可以確定 flashback restore 期間會用到哪些 flashback logfile
col name format a30
set linesize 180
set numwidth 16
with maxlogchg as
(select max(first_change#) c1 from v$flashback_database_logfile where first_change# 12723361569676)
select * from v$flashback_database_logfile,maxlogchg where first_change# = maxlogchg.c1 and type!= RESERVED order by first_change# desc;

/oradata06/fra/TSTDB1/flashbac 17 1 17 202391552 12723361572254 20150427 14:57:12 NORMAL 12723361567203
k/o1_mf_1jJveoMJV_.flb

/oradata06/fra/TSTDB1/flashbac 11 1 11 303538176 12723361571241 20150427 14:04:02 NORMAL 12723361567203
k/o1_mf_1jJsffEsf_.flb

/oradata06/fra/TSTDB1/flashbac 10 1 10 227180544 12723361570959 20150427 14:03:38 NORMAL 12723361567203
k/o1_mf_1jJsefe5V_.flb

/oradata06/fra/TSTDB1/flashbac 9 1 9 158261248 12723361570755 20150427 14:03:22 NORMAL 12723361567203
k/o1_mf_1jJsdht9Y_.flb

/oradata06/fra/TSTDB1/flashbac 8 1 8 134217728 12723361570182 20150427 14:03:07 NORMAL 12723361567203
k/o1_mf_1jJscw8fZ_.flb

/oradata06/fra/TSTDB1/flashbac 7 1 7 134217728 12723361570028 20150427 14:02:54 NORMAL 12723361567203
k/o1_mf_1jJsc8ieR_.flb

/oradata06/fra/TSTDB1/flashbac 6 1 6 134217728 12723361569869 20150427 14:02:39 NORMAL 12723361567203
k/o1_mf_1jJnflWEZ_.flb

/oradata06/fra/TSTDB1/flashbac 5 1 5 134217728 12723361567203 20150427 12:34:10 NORMAL 12723361567203
k/o1_mf_1jJnfR1CI_.flb

上面的輸出是按照 flashback log 應用的先后順序排列的

也可以確定哪些 archivelog 會在 flashback recover 期間被 apply
col name format a50
set linesize 150 pagesize 100
with maxlogchg as
(select max(first_change#) c1 from v$archived_log where first_change# =12723361567213)
select name,sequence#,first_change#,next_change# from v$archived_log,maxlogchg where first_change# =maxlogchg.c1 and first_change# =12723361569813;

NAME SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
————————————————– —————- —————- —————-
/oradata06/fra/TSTDB1/archivelog/2015_04_27/o1_mf_ 1301 12723361566863 12723361568725
1_1301_1jJsF6HsA_.arc

/oradata06/fra/TSTDB1/archivelog/2015_04_27/o1_mf_ 1302 12723361568725 12723361568985
1_1302_1jJsFPtbI_.arc

/oradata06/fra/TSTDB1/archivelog/2015_04_27/o1_mf_ 1303 12723361568985 12723361569649
1_1303_1jJsSKVVs_.arc

/oradata06/fra/TSTDB1/archivelog/2015_04_27/o1_mf_ 1304 12723361569649 12723361569754
1_1304_1jJsVb6To_.arc

/oradata06/fra/TSTDB1/archivelog/2015_04_27/o1_mf_ 1305 12723361569754 12723361569954
1_1305_1jJscXQFZ_.arc

哪些地方有 scn barrier 的足跡？
_flashback_verbose_info=TRUE，會在 alert.log 里看到

Tue Apr 28 11:14:11 2015 —-30 分鐘顯示一次最新創建的 scn barrier
SCN barrier 12723361661038
Tue Apr 28 11:44:12 2015
SCN barrier 12723361662431
Tue Apr 28 12:14:13 2015
SCN barrier 12723361663960
Tue Apr 28 12:44:14 2015
SCN barrier 12723361665416
Tue Apr 28 13:14:15 2015
SCN barrier 12723361666861

當我們創建 guaranteed restore point 時，會強制的創建一個 scn barrier，在 alert.log 里會看到下面的信息
Mon Apr 27 15:23:08 2015
SCN barrier 12723361572579
Created guaranteed restore point AA

可以使用下面的命令將 log#= 5 的 flashback logfile 里包含 scn barrier 的段落 dump 出來
SQL ALTER SYSTEM DUMP FLASHBACK LOGFILE 5 TYPE 2;

生成的 trace 文件內容如下：

*** 2015-04-28 14:12:21.526
Current records:
**** Record at fba: (lno 5 thr 1 seq 5 bno 12032 bof 3748) ****
RECORD HEADER:
Type: 2 (Marker) Size: 300
RECORD DATA (Marker):
Previous logical record fba: (lno 5 thr 1 seq 5 bno 9292 bof 80)
Record scn: 0x0b92.63725b8e [2962.1668438926]
Marker scn: 0x0b92.63725b8c [2962.1668438924] 04/27/2015 14:00:23
Flag 0x0
Flashback threads: 1, Enabled redo threads 1
Recovery Start Checkpoint:
scn: 0x0b92.637251ed [2962.1668436461] 04/27/2015 13:54:24
thread:1 rba:(0x515.1e6.0)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
Flashback thread Markers:
Thread:1 status:0 fba: (lno 5 thr 1 seq 5 bno 9045 bof 744)
Redo Thread Checkpoint Info:
Thread:1 rba:(0x515.1e6.0)