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   
Marker checkpoint scn during mount SCN 12723361567213 
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   
        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           
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)

*** 2015-04-28 14:12:22.283