flashback database怎么选择需要应用的flashback log

小编给大家分享一下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#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#select name,sequence#,first_change#,next_change# from v$archived_log,maxlogchg where first_change#>=maxlogchg.c1 and first_change#
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 ba免费云主机域名rrier 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看完了这篇文章，相信你对“flashback database怎么选择需要应用的flashback log”有了一定的了解，如果想了解更多相关知识，欢迎关注百云行业资讯频道，感谢各位的阅读！

相关推荐: 怎么掌握PostgreSQL Locks的基础知识

这篇文章主要讲解了“怎么掌握PostgreSQL Locks的基础知识”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么掌握PostgreSQL Locks的基础知识”吧！如下例所示,session 1执行up…