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

66次阅读

共计 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)