Flashback database基础知识问答

最新推荐文章于 2021-04-08 15:32:25 发布

cuiqiong6888

最新推荐文章于 2021-04-08 15:32:25 发布

阅读量272

点赞数

文章标签：数据库

汇总一下学习flashback database过程中容易被问到的一些问题，不是单纯谈理论，辅有实际操作验证

//
//如何配置flashback database
//
满足条件：
数据库必须要是archivelog mode，否则会有ORA-38706、ORA-38707
数据库可以为open或者mount状态，若处于mount状态则上一次shutdown时不能使用abort，否则会有ORA-38706、ORA-38714
，否则会有ORA-38706、ORA-38709
设置好db_recovery_file_dest_size、db_recovery_file_dest参数
db_flashback_retention_target参数为可选，默认值是1440分钟(如果设置为0，等同于禁用flashback database)，表示Flashback database能够将数据库带回到的最早的那个时间点与当前时间的间隔，如果db_flashback_retention_target=1440，那么是最早的时间点就是sysdate-1，db_flashback_retention_target不是一个硬指标，如果fast recovery area设置的比较小而flashback log又增长的比较快，以至于FRA里没有可用的空间存放新增的flashback log，较早的flashback log可能会被清除掉，就无法保证database能flashback到db_flashback_retention_target指定的时间点。因为flashback log里存放的都是data block的before-image，对空间上的要求比online redolog要高的多，可以在数据库运行一段时间以后参照V$FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE来估算flashback log的大小，从而合理的设置db_recovery_file_dest_size

###flashback on database
SQL> show parameter db_flashback_retention_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 120

SQL> show parameter recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata06/fra
db_recovery_file_dest_size           big integer 10G

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

###记录flashback log的后台进程RVWR启动，在fast recovery area目录下有名为flashback的目录生成，初始有两个.flb文件，大小和Redolog file size一致
oracle@jq570322b:/oradata06/fra/TSTDB1/archivelog>ps -ef|grep -i rvwr | grep -v grep
tstdb1 18089330        1   0 17:25:46      - 0:00 ora_rvwr_tstdb1

oracle@jq570322b:/oradata06/fra>ls -l TSTDB1
total 16
drwxr-x---    8 oracle   oinstall       8192 Apr 16 17:28 archivelog
drwxr-x---    3 oracle   oinstall         96 Apr 16 17:36 autobackup
drwxr-x---    2 oracle   oinstall         96 Apr 16 17:29 flashback

oracle@jq570322b:/oradata06/fra>ls -l TSTDB1/flashback
total 524320
-rw-r----- 1 oracle oinstall 134225920 Apr 16 18:03 o1_mf_1j6D2RXXr_.flb
-rw-r----- 1 oracle oinstall 134225920 Apr 16 18:17 o1_mf_1j6D2HxYT_.flb

###进行dml操作后，观察到.flb从2个增加到了5个
create table t0416_1 as select * from all_users;
insert into t0416_1 select * from t0416_1; <---执行n遍
select count(*) from t0416_1;

COUNT(*)
----------
1720320

oracle@jq570322b:/oradata06/fra>ls -l TSTDB1/flashback
total 1310800
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 18:25 o1_mf_1j6D2HxYT_.flb
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 18:25 o1_mf_1j6D2RXXr_.flb
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 18:25 o1_mf_1j6G7_Xrb_.flb
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 18:30 o1_mf_1j6G9Ptth_.flb
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 18:25 o1_mf_1j6GAilaq_.flb

###记录一下当前的系统时间，这个时间将会是后面flashback database的第一个目标时间点
SQL> select sysdate from dual;

SYSDATE
-----------------
20150416 18:51:24

###删除表中的部分记录，记录当前的系统时间20150416 20:35:05database的第二个目标时间点
SQL> delete t0416_1 where user_id>50;

573440 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from t0416_1;

COUNT(*)
----------
1146880

SQL> select sysdate from dual;

SYSDATE
-----------------
20150416 20:35:05

###再创建一个测试表t0416_2
SQL> create table t0416_2 tablespace users as select * from dict;

Table created.

SQL> select count(*) from t0416_2;

COUNT(*)
----------
1792

SQL> select sysdate from dual;

SYSDATE
-----------------
20150416 20:37:28

###重新启动至mount状态后，flashback到20150416 18:51:24这个时间点
SQL> flashback database to timestamp to_date('20150416 18:51:24','yyyymmdd hh24:mi:ss');
flashback database to timestamp to_date('20150416 18:51:24','yyyymmdd hh24:mi:ss')
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2233480 bytes
Variable Size            4328524664 bytes
Database Buffers         2063597568 bytes
Redo Buffers               19324928 bytes
Database mounted.

SQL> flashback database to timestamp to_date('20150416 18:51:24','yyyymmdd hh24:mi:ss'); <--------------此处的报错见下面的解释
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 12723360396485 to SCN End-of-Redo
ORA-38761: redo log sequence 510 in thread 1, incarnation 1 could not be
accessed

--从v$archived_log里看到sequence#=510这个redolog没有被正常归档就被清除了，它的next_change#=281474976710655使得oracle认为要恢复到的SCN 12723360396485就包含在这个log里，所以必须去applied这个log，因此出现了上面这个错误，这个错误在后面的测试里也会多次出现，阅读的过程中可以忽略，它并不是重点
SYS@tstdb1-SQL> select sequence#,first_change#,first_time,NEXT_CHANGE#,next_time,archived,name from v$archived_log where SEQUENCE#=510;

--但实际上我们看到sequence#=596这个redolog才是真正包含这个SCN的redolog
SYS@tstdb1-SQL> select sequence#,first_change#,first_time,NEXT_CHANGE#,next_time,archived,name from v$archived_log where first_change#<=12723360396485 and next_change#>=12723360396485;

       SEQUENCE#    FIRST_CHANGE# FIRST_TIME            NEXT_CHANGE# NEXT_TIME         ARC NAME
---------------- ---------------- ----------------- ---------------- ----------------- --- --------------------------------------------------
             510   12723358967141 20150330 21:30:05 281474976710655                   NO
             596   12723360396183 20150416 18:26:01   12723360405745 20150416 20:27:11 YES /oradata06/fra/TSTDB1/archivelog/2015_04_16/o1_mf_
                                                                                           1_596_1j6Myy1FQ_.arc

---显然RMAN里flashback database更聪明，能正确找到sequence#=596这个redolog进行apply
RMAN> flashback database to time '20150416 18:51:24';

Starting flashback at 20150417 05:05:06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=463 device type=DISK

starting media recovery

archived log for thread 1 with sequence 596 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_04_16/o1_mf_1_596_1j6Myy1FQ_.arc
media recovery complete, elapsed time: 00:00:02
Finished flashback at 20150417 05:05:24

---运行flashback database期间alert.log里能告诉我们当前恢复到的时间点是04/16/2015 18:51:38(比指定的时间18:51:24晚了14秒)，对应的SCN是12723360398209
Fri Apr 17 05:05:07 2015
alter database recover datafile list clear
Completed: alter database recover datafile list clear
RMAN flashback database to time 04/16/2015 18:51:24
Flashback Restore Start <----restore是将flashback log里保存的data block修改前的image还原到datafile里
Flashback Restore Complete
Flashback Media Recovery Start <----recover就是apply redo的过程
started logmerger process
Parallel Media Recovery started with 16 slaves
Fri Apr 17 05:05:23 2015
Flashback Media Recovery Log /oradata06/fra/TSTDB1/archivelog/2015_04_16/o1_mf_1_596_1j6Myy1FQ_.arc
Fri Apr 17 05:05:23 2015
Incomplete Recovery applied until change 12723360398209 time 04/16/2015 18:51:38
Flashback Media Recovery Complete
Completed: RMAN flashback database to time 04/16/2015 18:51:24

---检查datafile和controlfile当前的SCN号均为12723360398209
set numwidth 16
col name format a50
set linesize 150

SYS@tstdb1-SQL> select name,checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/oradata06/testaaaaa/system01.dbf                      12723360398209
/oradata06/testaaaaa/sysaux01.dbf                      12723360398209
/oradata06/testaaaaa/undotbs01.dbf                     12723360398209
/oradata06/testaaaaa/users01.dbf                       12723360398209
/oradata06/testaaaaa/ts0329_1.dbf                      12723360398209
/oradata06/testaaaaa/xdbts1.dbf                        12723360398209
/oradata06/testaaaaa/ts0212.dbf                        12723357761339 <--- read only tablespace除外
/oradata06/testaaaaa/ts0212_1.dbf                      12723357761339 <--- read only tablespace除外

8 rows selected.

SYS@tstdb1-SQL> select name,checkpoint_change# from v$datafile_header;

###先将数据开启至read only，看一下flashback后的数据是否我们所需要的
SYS@tstdb1-SQL> alter database open read only;

Database altered.

SCOTT@tstdb1-SQL> select count(*) from t0416_1; <---04/16/2015 18:51:24时刻t0416_1表的记录数确实是1720320

COUNT(*)
----------
1720320

SCOTT@tstdb1-SQL> select count(*) from t0416_2; <---04/16/2015 18:51:24时刻t0416_2表还没有创建
select count(*) from t0416_2
*
ERROR at line 1:
ORA-00942: table or view does not exist

//
//flashback database过程中如何选定所需要的flashback log
//
Flashback log介绍

flashback log是在数据库开启闪回模式(alter database flashback on)或者创建了garanteed restore point的情况下才会生成，flashback log里保存的是data block修改前的image。当我们对一个data block修改之前，该data block会从buffer cache复制到shared pool中的flashback generation buffer区域，RVWR进程会将flashback generation buffer里保存的data block before-image写入到flashback log。flashback log必须存放在Fast recovery area里，其命名方式遵循OMF格式，例如：o1_mf_1j6D2RXXr_.flb。db_flashback_retention_target参数设置决定了flashback log的保留时间，对于超出保留时间的flashback log会被清除(在Fast Recovery Area空间紧张时)或者重复利用，如果数据库的dml操作非常频繁，且现有的flashback log都没有超出db_flashback_retention_target所指定的保留时间，那么会有新的flashback log生成，这一点和online redo稍有不同，另外flashback log不属于备份恢复体系中的成员，我们无法针对flashback log进行备份或者使用backup recovery area实现对于flashback log间接备份的目的，因为flashback log作用主要在于逻辑错误的修复，备份flashback log并没有实际意义

我们先配置一个flashback database的环境，后面关于flashback log诸多特性的介绍都将基于这个测试环境

在Flashback database的过程中Flashback log是如何被利用的？

Flashback Database的整个过程由两个阶段组成，第一阶段：apply flashback log，将flashback log里记录的data block before-image覆盖到对应数据文件的对应block，这时各个数据文件可能是不一致的；第二阶段：apply redolog(archived redo or online redo)，目的是将数据库带到一个consistent point
在上面搭建的flashback database环境的过程中，我们做了一个简单的测试：将数据库从current状态flashback到'20150416 18:51:24'这一时间点。期间我们仅能从alert.log里或者通过RMAN的命令输出观察到第二阶段：apply redolog的过程，对于第一阶段：apply flashback log的过程我们能找到的仅是alert.log里
Flashback Restore Start
Flashback Restore Complete
这两行内容，没有更多的细节展示出来，也没有已知的Event设置能够丰富这方面的输出。
我们只能将FRA里的flashback log改名，以尽可能多的获取apply flashback log过程中的细节

--改名前
oracle@jq570322b:/oradata06/fra/TSTDB1/flashback>ls -rlt
total 2621600
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 18:25 o1_mf_1j6D2RXXr_.flb
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 18:25 o1_mf_1j6G7_Xrb_.flb
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 20:00 o1_mf_1j6G9Ptth_.flb
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 20:27 o1_mf_1j6GAilaq_.flb
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 21:55 o1_mf_1j6LVKto5_.flb
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 23:55 o1_mf_1j6D2HxYT_.flb
-rw-r-----    1 oracle   oinstall 134225920 Apr 17 02:25 o1_mf_1j6RuNt1f_.flb
-rw-r-----    1 oracle   oinstall 134225920 Apr 17 04:55 o1_mf_1j6YbsKVD_.flb
-rw-r-----    1 oracle   oinstall 134225920 Apr 17 04:55 o1_mf_1j6pMheJF_.flb
-rw-r-----    1 oracle   oinstall 134225920 Apr 17 14:21 o1_mf_1j6g-H73H_.flb

--改名
oracle@jq570322b:/oradata06/fra/TSTDB1/flashback>ls -1 *.flb | xargs -n1 -I{} mv {} {}.old

--改名后
oracle@jq570322b:/oradata06/fra/TSTDB1/flashback>ls -rlt
total 2621600
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 18:25 o1_mf_1j6D2RXXr_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 18:25 o1_mf_1j6G7_Xrb_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 20:00 o1_mf_1j6G9Ptth_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 20:27 o1_mf_1j6GAilaq_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 21:55 o1_mf_1j6LVKto5_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 23:55 o1_mf_1j6D2HxYT_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 17 02:25 o1_mf_1j6RuNt1f_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 17 04:55 o1_mf_1j6YbsKVD_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 17 04:55 o1_mf_1j6pMheJF_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 17 14:21 o1_mf_1j6g-H73H_.flb.old

在执行flashback database的时候可能有两种场景：
1、flashback的目标时间早于数据库当前所处的时间，比如数据库datafile的最新时间戳是10:00，要flashback到8:00的状态
2、flashback的目标时间晚于数据库当前所处的时间，比如在经过1次flashback database操作后数据库datafile的最新时间戳是8:00，发现8:00那一时刻的数据漏掉了一些关键的更新，现在又要再次flashback database到9:00时的状态
官方文档里并没有对这两种情况进行详细的阐述，我们暂且把第1种场景称为后向flashback，第2种场景称为前向flashback，这两个场景里flashback databae的过程稍有区别

###后向flashback的场景(这也是我们用的最多，最容易理解的场景)
--数据库当前所处的时间点是'20150416 21:00:00'，在sqlplus里执行flashback database回到'20150416 18:51:24'这个时间点
SYS@tstdb1-SQL> select name,checkpoint_change#,CHECKPOINT_TIME from v$datafile;

NAME                                               CHECKPOINT_CHANGE# CHECKPOINT_TIME
-------------------------------------------------- ------------------ -----------------
/oradata06/testaaaaa/system01.dbf                      12723360408715 20150416 21:00:01
/oradata06/testaaaaa/sysaux01.dbf                      12723360408715 20150416 21:00:01
/oradata06/testaaaaa/undotbs01.dbf                     12723360408715 20150416 21:00:01
/oradata06/testaaaaa/users01.dbf                       12723360408715 20150416 21:00:01
/oradata06/testaaaaa/ts0329_1.dbf                      12723360408715 20150416 21:00:01
/oradata06/testaaaaa/xdbts1.dbf                        12723360408715 20150416 21:00:01
/oradata06/testaaaaa/ts0212.dbf                        12723357761339 20150315 09:56:23
/oradata06/testaaaaa/ts0212_1.dbf                      12723357761339 20150315 09:56:23

8 rows selected.

SYS@tstdb1-SQL> select name,checkpoint_change#,CHECKPOINT_TIME from v$datafile_header;

SYS@tstdb1-SQL> flashback database to timestamp to_date('20150416 18:51:24','yyyymmdd hh24:mi:ss');
flashback database to timestamp to_date('20150416 18:51:24','yyyymmdd hh24:mi:ss')
*
ERROR at line 1:
ORA-38701: Flashback database log 9 seq 14 thread 1:
"/oradata06/fra/TSTDB1/flashback/o1_mf_1j6g-H73H_.flb" <---因为改名了所以提示找不到此文件
ORA-17503: ksfdopn:4 Failed to open file
/oradata06/fra/TSTDB1/flashback/o1_mf_1j6g-H73H_.flb
ORA-17500: ODM err:File does not exist

--我们从V$FLASHBACK_DATABASE_LOGFILE里看一下o1_mf_1j6g-H73H_.flb这个flashback log的信息，该log的sequence#=14,first_time是最近的时间20150417 04:55:26,表明是当前正在使用的flashback log
col name format a30
col type format a10
set numwidth 16
set linesize 150 pagesize 100
SYS@tstdb1-SQL> select * from V$FLASHBACK_DATABASE_LOGFILE order by sequence#;
NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIME TYPE
------------------------------ ---------------- ---------------- ---------------- ---------------- ---------------- ----------------- ----------
/oradata06/fra/TSTDB1/flashbac 10 1 1 134217728 0 RESERVED
k/o1_mf_1j6pMheJF_.flb

/oradata06/fra/TSTDB1/flashbac 2 1 6 134217728 12723360387123 20150416 18:25:00 NORMAL
k/o1_mf_1j6D2RXXr_.flb

/oradata06/fra/TSTDB1/flashbac 3 1 7 134217728 12723360390761 20150416 18:25:21 NORMAL
k/o1_mf_1j6G7_Xrb_.flb

/oradata06/fra/TSTDB1/flashbac 4 1 8 134217728 12723360394268 20150416 18:25:44 NORMAL
k/o1_mf_1j6G9Ptth_.flb

/oradata06/fra/TSTDB1/flashbac 5 1 9 134217728 12723360403709 20150416 20:00:59 NORMAL
k/o1_mf_1j6GAilaq_.flb

/oradata06/fra/TSTDB1/flashbac 6 1 10 134217728 12723360405698 20150416 20:27:08 NORMAL
k/o1_mf_1j6LVKto5_.flb

/oradata06/fra/TSTDB1/flashbac 1 1 11 134217728 12723360414458 20150416 21:55:23 NORMAL
k/o1_mf_1j6D2HxYT_.flb

/oradata06/fra/TSTDB1/flashbac 7 1 12 134217728 12723360445736 20150416 23:55:26 NORMAL
k/o1_mf_1j6RuNt1f_.flb

/oradata06/fra/TSTDB1/flashbac 8 1 13 134217728 12723360459837 20150417 02:25:26 NORMAL
k/o1_mf_1j6YbsKVD_.flb

/oradata06/fra/TSTDB1/flashbac 9 1 14 134217728 12723360476287 20150417 04:55:26 NORMAL
k/o1_mf_1j6g-H73H_.flb

因为是flashback操作所以要从最新的一个flashback log开始往前恢复

--将o1_mf_1j6g-H73H_.flb改回原来的名称，继续执行flashback
mv o1_mf_1j6g-H73H_.flb.old o1_mf_1j6g-H73H_.flb

SYS@tstdb1-SQL> flashback database to timestamp to_date('20150416 18:51:24','yyyymmdd hh24:mi:ss');
flashback database to timestamp to_date('20150416 18:51:24','yyyymmdd hh24:mi:ss')
*
ERROR at line 1:
ORA-38701: Flashback database log 8 seq 13 thread 1: "/oradata06/fra/TSTDB1/flashback/o1_mf_1j6YbsKVD_.flb" <---这次要寻找的是sequence#=13的log
ORA-17503: ksfdopn:4 Failed to open file /oradata06/fra/TSTDB1/flashback/o1_mf_1j6YbsKVD_.flb
ORA-17500: ODM err:File does not exist

sequence#=13的first_time是20150417 02:25:26

--将o1_mf_1j6YbsKVD_.flb改回原来的名称，继续执行flashback
mv o1_mf_1j6YbsKVD_.flb.old o1_mf_1j6YbsKVD_.flb

SYS@tstdb1-SQL> flashback database to timestamp to_date('20150416 18:51:24','yyyymmdd hh24:mi:ss');
flashback database to timestamp to_date('20150416 18:51:24','yyyymmdd hh24:mi:ss')
*
ERROR at line 1:
ORA-38701: Flashback database log 7 seq 12 thread 1: "/oradata06/fra/TSTDB1/flashback/o1_mf_1j6RuNt1f_.flb" <---这次要寻找的是sequence#=12的log
ORA-17503: ksfdopn:4 Failed to open file /oradata06/fra/TSTDB1/flashback/o1_mf_1j6RuNt1f_.flb
ORA-17500: ODM err:File does not exist

sequence#=12的first_time是20150416 23:55:26，照此我们可以推断出如果要flashback到'20150416 18:51:24'，必须一直应用到sequence#=8对应的o1_mf_1j6G9Ptth_.flb

--我们先把sequence#=12、11、10、9三个文件改回原来的名称，继续执行flashback
mv o1_mf_1j6RuNt1f_.flb.old o1_mf_1j6RuNt1f_.flb
mv o1_mf_1j6GAilaq_.flb.old o1_mf_1j6GAilaq_.flb
mv o1_mf_1j6LVKto5_.flb.old o1_mf_1j6LVKto5_.flb
mv o1_mf_1j6D2HxYT_.flb.old o1_mf_1j6D2HxYT_.flb

SYS@tstdb1-SQL> flashback database to timestamp to_date('20150416 18:51:24','yyyymmdd hh24:mi:ss');
flashback database to timestamp to_date('20150416 18:51:24','yyyymmdd hh24:mi:ss')
*
ERROR at line 1:
ORA-38701: Flashback database log 4 seq 8 thread 1: "/oradata06/fra/TSTDB1/flashback/o1_mf_1j6G9Ptth_.flb"
ORA-17503: ksfdopn:4 Failed to open file /oradata06/fra/TSTDB1/flashback/o1_mf_1j6G9Ptth_.flb
ORA-17500: ODM err:File does not exist

--我们把sequence#=8所对应的o1_mf_1j6G9Ptth_.flb改回原来的名称，再执行flashback database时不在索要新的flashback log，在apply完o1_mf_1j6G9Ptth_.flb这个flashback log后数据库所处的时间点应该大于20150416 18:25:44，因为o1_mf_1j6G9Ptth_.flb的first time=20150416 18:25:44，随后进入apply redolog阶段
mv o1_mf_1j6G9Ptth_.flb.old o1_mf_1j6G9Ptth_.flb

SYS@tstdb1-SQL> flashback database to timestamp to_date('20150416 18:51:24','yyyymmdd hh24:mi:ss');
flashback database to timestamp to_date('20150416 18:51:24','yyyymmdd hh24:mi:ss')
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 12723360396485 to SCN End-of-Redo
ORA-38761: redo log sequence 510 in thread 1, incarnation 1 could not be accessed

--在RMAN里执行flashback database成功
RMAN> flashback database to time '20150416 18:51:24';

Starting flashback at 20150418 16:31:39
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=794 device type=DISK

starting media recovery

--因为apply完flashback log后数据库所处的时间大于等于20150416 18:25:44，而sequence#=596这个archivelog涵盖了'20150416 18:51:24'时间点，FIRST_TIME<'20150416 18:51:24'<NEXT_TIME，所以只需要sequence#=596这一个archivelog就可完成
set numwidth 16
col name format a50
set linesize 150
SYS@tstdb1-SQL> select sequence#,first_change#,first_time,NEXT_CHANGE#,next_time,archived,name from v$archived_log where SEQUENCE# in (596);

       SEQUENCE#    FIRST_CHANGE# FIRST_TIME            NEXT_CHANGE# NEXT_TIME         ARC NAME
---------------- ---------------- ----------------- ---------------- ----------------- --- --------------------------------------------------
             596   12723360396183 20150416 18:26:01   12723360405745 20150416 20:27:11 YES /oradata06/fra/TSTDB1/archivelog/2015_04_16/o1_mf_
                                                                                           1_596_1j6Myy1FQ_.arc

我们推荐使用RMAN来进行flashback database操作，RMAN的操作更为自动化，比如当所需要的archivelog备份之后从磁盘上删除了，rman能自动执行restore archivelog的操作。上面在sqlplus里执行flashback database只是为了看清apply flashback log的过程

后向flashback的总结：
在apply flashback log阶段总是从sequence#最大的那个flashback log开始，按照sequence#从大到小的顺序逐个进行apply，直至应用到first_time<flashback目标时间的那个flashback log为止，在apply某个特定flashback log的时候也是先从文件尾部开始往文件头方向逆向的读取，因为flashback log是顺序写入的，先更新的data block的before-image较之后更新的data block的before-image存放在更加靠近文件头的位置，所以在flashback的时候要从后往前apply，总之对于flashback过程中涉及的操作都要逆向的去考虑。apply redolog阶段选取的archivelog其时间范围一定要处于"最后一个被应用的flashback log的first_time"及"flashback目标时间"所构成的这个时间范围，上面的例子里就是涵盖20150416 18:25:44~20150416 18:51:24这一范围的archivelog都会被apply。

###前向flashback的场景
如果进行了一次后向flashback后，发现这个时间太旧了进而要继续flashback到一个较近的时间点，这个时候就要用到前向恢复了。我们延用后向flashback的结果，
--假设数据库当前所处的时间点是'20150416 21:00:01'现在要flashback到'20150417 02:00:00'这个时间点
set numwidth 16
col name format a50
set linesize 150
SYS@tstdb1-SQL> select name,checkpoint_change#,CHECKPOINT_TIME from v$datafile_header;

8 rows selected.

SYS@tstdb1-SQL> select name,checkpoint_change#,CHECKPOINT_TIME from v$datafile;

SYS@tstdb1-SQL> select status from v$instance;

STATUS
------------
MOUNTED

--仍然先将flashback log改名
oracle@jq570322b:/oradata06/fra/TSTDB1/flashback>ls -1 *.flb | xargs -n1 -I{} mv {} {}.old

oracle@jq570322b:/oradata06/fra/TSTDB1/flashback>ls -rlt
total 2621600
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 18:25 o1_mf_1j6D2RXXr_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 18:25 o1_mf_1j6G7_Xrb_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 20:00 o1_mf_1j6G9Ptth_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 20:27 o1_mf_1j6GAilaq_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 21:55 o1_mf_1j6LVKto5_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 16 23:55 o1_mf_1j6D2HxYT_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 17 02:25 o1_mf_1j6RuNt1f_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 17 04:55 o1_mf_1j6YbsKVD_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 17 04:55 o1_mf_1j6pMheJF_.flb.old
-rw-r-----    1 oracle   oinstall 134225920 Apr 18 17:52 o1_mf_1j6g-H73H_.flb.old

--当前flashback log信息
col name format a30
set linesize 170 pagesize 100
select * from V$FLASHBACK_DATABASE_LOGFILE order by sequence#;
NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIME TYPE
------------------------------ ---------------- ---------------- ---------------- ---------------- ---------------- ----------------- ---------
/oradata06/fra/TSTDB1/flashbac 4 1 8 134217728 12723360394268 20150416 18:25:44 RESERVED
k/o1_mf_1j6G9Ptth_.flb

/oradata06/fra/TSTDB1/flashbac 5 1 9 134217728 12723360403709 20150416 20:00:59 NORMAL
k/o1_mf_1j6GAilaq_.flb

/oradata06/fra/TSTDB1/flashbac 6 1 10 134217728 12723360405698 20150416 20:27:08 NORMAL
k/o1_mf_1j6LVKto5_.flb

/oradata06/fra/TSTDB1/flashbac 1 1 11 134217728 12723360414458 20150416 21:55:23 NORMAL
k/o1_mf_1j6D2HxYT_.flb

/oradata06/fra/TSTDB1/flashbac 7 1 12 134217728 12723360445736 20150416 23:55:26 NORMAL
k/o1_mf_1j6RuNt1f_.flb

/oradata06/fra/TSTDB1/flashbac 8 1 13 134217728 12723360459837 20150417 02:25:26 NORMAL
k/o1_mf_1j6YbsKVD_.flb

/oradata06/fra/TSTDB1/flashbac 9 1 14 134217728 12723360476287 20150417 04:55:26 NORMAL
k/o1_mf_1j6g-H73H_.flb

--仍然是一个不断改回原来名称的过程
SYS@tstdb1-SQL> flashback database to timestamp to_date('20150417 02:00:00','yyyymmdd hh24:mi:ss');
flashback database to timestamp to_date('20150417 02:00:00','yyyymmdd hh24:mi:ss')
*
ERROR at line 1:
ORA-38701: Flashback database log 9 seq 14 thread 1:
"/oradata06/fra/TSTDB1/flashback/o1_mf_1j6g-H73H_.flb"
ORA-17503: ksfdopn:4 Failed to open file
/oradata06/fra/TSTDB1/flashback/o1_mf_1j6g-H73H_.flb
ORA-17500: ODM err:File does not exist

mv o1_mf_1j6g-H73H_.flb.old o1_mf_1j6g-H73H_.flb <----将sequence#=14的flashback log恢复原来的名称

SYS@tstdb1-SQL> r
1* flashback database to timestamp to_date('20150417 02:00:00','yyyymmdd hh24:mi:ss')
flashback database to timestamp to_date('20150417 02:00:00','yyyymmdd hh24:mi:ss')
*
ERROR at line 1:
ORA-38701: Flashback database log 8 seq 13 thread 1:
"/oradata06/fra/TSTDB1/flashback/o1_mf_1j6YbsKVD_.flb"
ORA-17503: ksfdopn:4 Failed to open file
/oradata06/fra/TSTDB1/flashback/o1_mf_1j6YbsKVD_.flb
ORA-17500: ODM err:File does not exist

mv o1_mf_1j6YbsKVD_.flb.old o1_mf_1j6YbsKVD_.flb <----将sequence#=13的flashback log恢复原来的名称

SYS@tstdb1-SQL> r
1* flashback database to timestamp to_date('20150417 02:00:00','yyyymmdd hh24:mi:ss')
flashback database to timestamp to_date('20150417 02:00:00','yyyymmdd hh24:mi:ss')
*
ERROR at line 1:
ORA-38701: Flashback database log 7 seq 12 thread 1:
"/oradata06/fra/TSTDB1/flashback/o1_mf_1j6RuNt1f_.flb"
ORA-17503: ksfdopn:4 Failed to open file
/oradata06/fra/TSTDB1/flashback/o1_mf_1j6RuNt1f_.flb
ORA-17500: ODM err:File does not exist

mv o1_mf_1j6RuNt1f_.flb.old o1_mf_1j6RuNt1f_.flb <----最后将sequence#=12的flashback log改回原名

SYS@tstdb1-SQL> flashback database to timestamp to_date('20150417 02:00:00','yyyymmdd hh24:mi:ss') <---再次执行flashback后提示需要archivelog，说明apply flashback log阶段结束，进入apply redolog阶段
flashback database to timestamp to_date('20150417 02:00:00','yyyymmdd hh24:mi:ss')
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 12723360398209 to SCN End-of-Redo
ORA-38761: redo log sequence 510 in thread 1, incarnation 1 could not be accessed

从v$flashback_database_logfile能看到o1_mf_1j6RuNt1f_.flb的时间跨度在20150416 23:55:26~20150417 02:25:26，我们要flashback的目标时间20150417 02:00:00包含在此范围，o1_mf_1j6RuNt1f_.flb应该是apply flashback log的终点
select * from V$FLASHBACK_DATABASE_LOGFILE order by sequence#;
NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIME TYPE
------------------------------ ---------------- ---------------- ---------------- ---------------- ---------------- ----------------- ---------
/oradata06/fra/TSTDB1/flashbac 7 1 12 134217728 12723360445736 20150416 23:55:26 NORMAL
k/o1_mf_1j6RuNt1f_.flb

--这时我们观察一下v$datafile、v$datafile_header两个视图，发现checkpoint_time还是维持在'20150416 21:00:01'，所以还需要recover
set numwidth 16
col name format a50
set linesize 150
SYS@tstdb1-SQL> select name,checkpoint_change#,CHECKPOINT_TIME from v$datafile_header;

8 rows selected.

SYS@tstdb1-SQL> select name,checkpoint_change#,CHECKPOINT_TIME from v$datafile;

--在RMAN里执行flashback database以完成整个flashback过程
RMAN> flashback database to time '20150417 02:00:00';

Starting flashback at 20150419 05:21:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=596 device type=DISK

starting media recovery

archived log for thread 1 with sequence 597 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_04_16/o1_mf_1_597_1j6QDIw3d_.arc
archived log for thread 1 with sequence 598 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_04_17/o1_mf_1_598_1j6b6vl8N_.arc
archived log for thread 1 with sequence 599 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_04_17/o1_mf_1_599_1j6nSR03C_.arc
media recovery complete, elapsed time: 00:00:07
Finished flashback at 20150419 05:22:03

--sequence#=597、598、599三个archivelog的时间跨度是20150416 20:27:11~20150417 04:21:16
set numwidth 16
col name format a50
set linesize 150
SYS@tstdb1-SQL> select sequence#,first_change#,first_time,NEXT_CHANGE#,next_time,archived,name from v$archived_log where SEQUENCE# in (597,598,599);
       SEQUENCE#    FIRST_CHANGE# FIRST_TIME            NEXT_CHANGE# NEXT_TIME         ARC NAME
---------------- ---------------- ----------------- ---------------- ----------------- --- --------------------------------------------------
             597   12723360405745 20150416 20:27:11   12723360412398 20150416 21:25:26 YES /oradata06/fra/TSTDB1/archivelog/2015_04_16/o1_mf_
                                                                                           1_597_1j6QDIw3d_.arc

598 12723360412398 20150416 21:25:26 12723360449943 20150417 00:40:30 YES /oradata06/fra/TSTDB1/archivelog/2015_04_17/o1_mf_
1_598_1j6b6vl8N_.arc

599 12723360449943 20150417 00:40:30 12723360473840 20150417 04:21:16 YES /oradata06/fra/TSTDB1/archivelog/2015_04_17/o1_mf_
1_599_1j6nSR03C_.arc

被apply的redolog的起始点是数据库执行flashback前的时间点20150416 21:00:01，而非之前应用的最后一个flashback log o1_mf_1j6RuN1f_.flb所对应的first_time:20150416 23:55:26(如果这样仅需从598这个archivelog开始)

---观察datafile的checkpoint_time是20150417 02:00:03比我们目标时间快了3秒钟，因为我们使用的是time而不是scn作为恢复目标的，有一定偏差是正常的
SYS@tstdb1-SQL> select name,checkpoint_change#,CHECKPOINT_TIME from v$datafile_header;

NAME                                               CHECKPOINT_CHANGE# CHECKPOINT_TIME
-------------------------------------------------- ------------------ -----------------
/oradata06/testaaaaa/system01.dbf                      12723360457457 20150417 02:00:03
/oradata06/testaaaaa/sysaux01.dbf                      12723360457457 20150417 02:00:03
/oradata06/testaaaaa/undotbs01.dbf                     12723360457457 20150417 02:00:03
/oradata06/testaaaaa/users01.dbf                       12723360457457 20150417 02:00:03
/oradata06/testaaaaa/ts0329_1.dbf                      12723360457457 20150417 02:00:03
/oradata06/testaaaaa/xdbts1.dbf                        12723360457457 20150417 02:00:03
/oradata06/testaaaaa/ts0212.dbf                        12723357761339 20150315 09:56:23
/oradata06/testaaaaa/ts0212_1.dbf                      12723357761339 20150315 09:56:23

8 rows selected.

SYS@tstdb1-SQL> select name,checkpoint_change#,CHECKPOINT_TIME from v$datafile;

NAME                                               CHECKPOINT_CHANGE# CHECKPOINT_TIME
-------------------------------------------------- ------------------ -----------------
/oradata06/testaaaaa/system01.dbf                      12723360457457 20150417 02:00:03
/oradata06/testaaaaa/sysaux01.dbf                      12723360457457 20150417 02:00:03
/oradata06/testaaaaa/undotbs01.dbf                     12723360457457 20150417 02:00:03
/oradata06/testaaaaa/users01.dbf                       12723360457457 20150417 02:00:03
/oradata06/testaaaaa/ts0329_1.dbf                      12723360457457 20150417 02:00:03
/oradata06/testaaaaa/xdbts1.dbf                        12723360457457 20150417 02:00:03
/oradata06/testaaaaa/ts0212.dbf                        12723357761339 20150315 09:56:23
/oradata06/testaaaaa/ts0212_1.dbf                      12723357761339 20150315 09:56:23

前向flashback的总结：
回顾一下上面的例子，数据库所处时间是20150416 21:00:01，flashback的目标时间是20150417 02:00:00，在apply flashback log阶段还是从sequence#最大的那个flashback log开始，按照sequence#从大到小的顺序逐个进行apply，最后一个被apply的flashback log其first_time刚好小于20150417 02:00:00，这个逻辑与后向flashback一样。但在apply redolog的时候却仅参考数据库在执行flashback前所处的时间20150416 21:00:01，选取20150416 21:00:01~flashback目标时间20150417 02:00:00时间段内Archivelog进行apply，并没有将最后一个被apply的flashback log o1_mf_1j6RuNt1f_.flb所对应的20150416 23:55:26这一时间作为选择archivelog的起始时间，这一点和后向flashback是有区别的。其实我们仔细想一下，前向flashback就是recover的过程，只需Apply redolog，flashback log可以不参与进来，但RMAN还是循规蹈矩的把它作为一次flashback来看待先apply flashback log再apply redolog，这样做的好处是使用上比较方便使用者不必去关心目标时间点与数据库当前所处时间点间的先后关系，但当flashback log比较多的时候效率会降低不如直接执行recover来的快。

--使用recover database将database 从20150416 21:00:01 flashback到20150417 02:00:00
SYS@tstdb1-SQL> select name,checkpoint_change#,CHECKPOINT_TIME from v$datafile;

8 rows selected.

SYS@tstdb1-SQL> select name,checkpoint_change#,CHECKPOINT_TIME from v$datafile_header;

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.

***alert.log显示598、599两个是直接应用了online redo：
Sun Apr 19 07:27:50 2015
ALTER DATABASE RECOVER database until time '20150417 02:00:00'
Media Recovery Start
started logmerger process
Parallel Media Recovery started with 16 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER database until time '20150417 02:00:00' ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /oradata06/fra/TSTDB1/archivelog/2015_04_16/o1_mf_1_597_1j6QDIw3d_.arc
Sun Apr 19 07:27:59 2015
Recovery of Online Redo Log: Thread 1 Group 3 Seq 598 Reading mem 0
Mem# 0: /oradata06/testaaaaa/redo03a.log
Mem# 1: /oradata06/testaaaaa/redo03b.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 599 Reading mem 0
Mem# 0: /oradata06/testaaaaa/redo02a.log
Mem# 1: /oradata06/testaaaaa/redo02b.log
Incomplete Recovery applied until change 12723360457448 time 04/17/2015 02:00:00
Sun Apr 19 07:28:03 2015
Media Recovery Complete (tstdb1)
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT

SYS@tstdb1-SQL> select name,checkpoint_change#,CHECKPOINT_TIME from v$datafile;

NAME                                               CHECKPOINT_CHANGE# CHECKPOINT_TIME
-------------------------------------------------- ------------------ -----------------
/oradata06/testaaaaa/system01.dbf                      12723360457448 20150417 02:00:00
/oradata06/testaaaaa/sysaux01.dbf                      12723360457448 20150417 02:00:00
/oradata06/testaaaaa/undotbs01.dbf                     12723360457448 20150417 02:00:00
/oradata06/testaaaaa/users01.dbf                       12723360457448 20150417 02:00:00
/oradata06/testaaaaa/ts0329_1.dbf                      12723360457448 20150417 02:00:00
/oradata06/testaaaaa/xdbts1.dbf                        12723360457448 20150417 02:00:00
/oradata06/testaaaaa/ts0212.dbf                        12723357761339 20150315 09:56:23
/oradata06/testaaaaa/ts0212_1.dbf                      12723357761339 20150315 09:56:23

8 rows selected.

SYS@tstdb1-SQL> select name,checkpoint_change#,CHECKPOINT_TIME from v$datafile_header;

NAME                                               CHECKPOINT_CHANGE# CHECKPOINT_TIME
-------------------------------------------------- ------------------ -----------------
/oradata06/testaaaaa/system01.dbf                      12723360457448 20150417 02:00:00
/oradata06/testaaaaa/sysaux01.dbf                      12723360457448 20150417 02:00:00
/oradata06/testaaaaa/undotbs01.dbf                     12723360457448 20150417 02:00:00
/oradata06/testaaaaa/users01.dbf                       12723360457448 20150417 02:00:00
/oradata06/testaaaaa/ts0329_1.dbf                      12723360457448 20150417 02:00:00
/oradata06/testaaaaa/xdbts1.dbf                        12723360457448 20150417 02:00:00
/oradata06/testaaaaa/ts0212.dbf                        12723357761339 20150315 09:56:23
/oradata06/testaaaaa/ts0212_1.dbf                      12723357761339 20150315 09:56:23

//
//该给flashback log预留多少空间
//
--查询select * from v$flashback_database_log
SYS@tstdb1-SQL> select * from v$flashback_database_log;

FLASHBACK_SIZE：是当前flashback log的大小
ESTIMATED_FLASHBACK_SIZE：根据当前的系统Activity以及retention_target估算出的flashback log可能使用到的最大空间

--改小db_flashback_retention_target后，ESTIMATED_FLASHBACK_SIZE立马降低到4439108266
SYS@tstdb1-SQL> alter system set db_flashback_retention_target=4000 scope=both;

System altered.

SYS@tstdb1-SQL> select * from v$flashback_database_log
2 ;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_ RETENTION_TARGET   FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ----------------- ---------------- ---------------- ------------------------
      12723360406546 20150416 20:35:53             4000        939524096               4439108266

通常是设定好db_flashback_retention_target，让数据库运行一段时间后根据ESTIMATED_FLASHBACK_SIZE的值再加上一定的冗余度来合理的设计Fast Recovery Area的大小

--也可以通过v$FLASH_RECOVERY_AREA_USAGE获得flashback log在FRA里的占比
SYS@tstdb1-SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ----------------
CONTROL FILE                          0                         0                0
REDO LOG                              0                         0                0
ARCHIVED LOG                      54.82                         0              141
BACKUP PIECE                       1.01                         0               14
IMAGE COPY                          .23                         0                1
FLASHBACK LOG                      6.88                         0               11
FOREIGN ARCHIVED LOG                  0                         0                0

//
//flashback log所产生的IO量有多大
//
set linesize 120
SYS@tstdb1-SQL> select n.name,s.value from v$statname n,v$sysstat s where s.statistic#=n.statistic# and n.name in ('flashback log writes','flashback log write bytes','physical write bytes','physical writes');
2 ;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical writes                                                      145525
physical write bytes                                             1192140800
flashback log writes                                                   2941
flashback log write bytes                                        1313931264

SYS@tstdb1-SQL> select * from v$flashback_database_stat;

BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- -------------- ---------- ---------- ------------------------
20150419 17:06:12 20150419 17:17:29 1313931264 1191690240 1837481984 0

可以通过v$sysstat和v$flashback_database_stat两个视图了解flashback log上的IO开销及其与数据库总的写入IO之间的比例关系
其中
flashback log write bytes：Total size in bytes of flashback database data written by RVWR to flashback database logs
physical write bytes：Total size in bytes of all disk writes from the database application activity

//
//单个flashback log的大小如何决定
//
oracle并没有提供设定flashback log大小的参数，flashback log的创建和维护完全是自动的，我们只能通过隐含参数来控制flashback log的大小，和flashback log相关的隐含参数如下
col ksppinm format a50
col ksppstvl format a20
set linesize 100
select ksppinm,ksppstvl from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and ksppinm in ('_flashback_size_based_on_redo','_db_flashback_log_min_size','_flashback_log_size','_flashback_log_min_size','_ksmg_granule_size');

KSPPINM                                            KSPPSTVL
-------------------------------------------------- --------------------
_ksmg_granule_size                                 16777216
_flashback_log_size                                1000
_flashback_log_min_size                            100
_db_flashback_log_min_size                         16777216
_flashback_size_based_on_redo                      TRUE

默认情况下_flashback_size_based_on_redo参数值为TRUE，表示flashback log的大小参照redolog大小的平均值，简单的测试验证一下
---当前数据库有三组redolog group，每组两个member，每个member大小为134217728
col member format a50
set linesize 120
SYS@tstdb1-SQL> select l.group#,f.member,l.bytes from v$log l,v$logfile f where l.group#=f.group#;

    GROUP# MEMBER                                                  BYTES
---------- -------------------------------------------------- ----------
         1 /oradata06/testaaaaa/redo01a.log                    134217728
         1 /oradata06/testaaaaa/redo01b.log                    134217728
         2 /oradata06/testaaaaa/redo02a.log                    134217728
         2 /oradata06/testaaaaa/redo02b.log                    134217728
         3 /oradata06/testaaaaa/redo03a.log                    134217728
         3 /oradata06/testaaaaa/redo03b.log                    134217728

---当前flashback log大小是134217728
SYS@tstdb1-SQL> select distinct bytes from v$flashback_database_logfile;
BYTES
----------------
134217728

---增加两组redolog，每个member的大小是256M
alter database add logfile group 4 ('/oradata06/testaaaaa/redo04a.log','/oradata06/testaaaaa/redo04b.log') size 256m;
alter database add logfile group 5 ('/oradata06/testaaaaa/redo05a.log','/oradata06/testaaaaa/redo05b.log') size 256m;

SYS@tstdb1-SQL> select l.group#,f.member,l.bytes from v$log l,v$logfile f where l.group#=f.group#;

          GROUP# MEMBER                                                        BYTES
---------------- -------------------------------------------------- ----------------
               1 /oradata06/testaaaaa/redo01a.log                          134217728
               1 /oradata06/testaaaaa/redo01b.log                          134217728
               2 /oradata06/testaaaaa/redo02a.log                          134217728
               2 /oradata06/testaaaaa/redo02b.log                          134217728
               3 /oradata06/testaaaaa/redo03a.log                          134217728
               3 /oradata06/testaaaaa/redo03b.log                          134217728
               4 /oradata06/testaaaaa/redo04a.log                          268435456
               4 /oradata06/testaaaaa/redo04b.log                          268435456
               5 /oradata06/testaaaaa/redo05a.log                          268435456
               5 /oradata06/testaaaaa/redo05b.log                          268435456

---为使新的flashback log size生效，需要重新关闭并打开flashback功能
alter database flashback off; <---off后原有的flashback log会被自动清除
alter database flashback on;

---新的size是187899904，通过(134217728x3+268435456x2)/5 计算得到
SYS@tstdb1-SQL> select distinct bytes from v$flashback_database_logfile;

BYTES
----------------
187899904

当_flashback_size_based_on_redo=FALSE时flashback log size如何决定？
alter system set "_flashback_size_based_on_redo"=FALSE scope=memory;

alter database flashback off;
alter database flashback on;

SYS@tstdb1-SQL> select distinct bytes from v$flashback_database_logfile;

BYTES
----------------
16777216

16777216正好是_db_flashback_log_min_size的参数值

alter system set "_db_flashback_log_min_size"=20m scope=memory;

alter database flashback off;
alter database flashback on;

SYS@tstdb1-SQL> select distinct bytes from v$flashback_database_logfile;

BYTES
----------------
20971520

alter system set "_db_flashback_log_min_size"=4m scope=memory;

alter database flashback off;
alter database flashback on;

SYS@tstdb1-SQL> select distinct bytes from v$flashback_database_logfile;

BYTES
----------------
15941632

因为当前SGA里的最小内存分配单位是由_ksmg_granule_size指定的，当前为16777216，flashback log file大小不能小于_ksmg_granule_size值，所以尽管我们将_db_flashback_log_min_size设置为4m，生成出来的flashback log还是接近16M，我们把_ksmg_granule_size改为4m后，是否生成flashback log size就会是4M一个了
alter system set "_flashback_size_based_on_redo"=FALSE scope=spfile;
alter system set "_ksmg_granule_size"=4194304 scope=spfile;
alter system set "_db_flashback_log_min_size"=4m scope=spfile;

startup force

KSPPINM                                            KSPPSTVL
-------------------------------------------------- --------------------
_ksmg_granule_size                                 4194304
_flashback_log_size                                1000
_flashback_log_min_size                            100
_db_flashback_log_min_size                         4194304
_flashback_size_based_on_redo                      FALSE

alter database flashback off;
alter database flashback on;

SYS@tstdb1-SQL> select distinct bytes from v$flashback_database_logfile;

BYTES
----------
8192000

修改_ksmg_granule_size=4194304后，发现flashback log size变成了8M，因为_flashback_log_size参数生效了，_flashback_log_size=1000表示一个flashback log的大小是1000个block size，当数据库block_size=8192是，flashback log size就是8M一个，再将_flashback_log_size设置为500
alter system set "_flashback_log_size"=500 scope=spfile;

startup force

SYS@tstdb1-SQL> select ksppinm,ksppstvl from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and ksppinm in ('_flashback_size_based_on_redo','_db_flashback_log_min_size','_flashback_log_size','_flashback_log_min_size','_ksmg_granule_size');

KSPPINM                                            KSPPSTVL
-------------------------------------------------- --------------------
_ksmg_granule_size                                 4194304
_flashback_log_size                                500
_flashback_log_min_size                            100
_db_flashback_log_min_size                         4194304
_flashback_size_based_on_redo                      FALSE

alter database flashback off;
alter database flashback on;

SYS@tstdb1-SQL> select distinct bytes from v$flashback_database_logfile;

BYTES
----------
4194304

至此，终于将flashback log size调整为4M大小了，但是别忘了还有一个参数_flashback_log_min_size尚未发挥过作用，_flashback_log_min_size=100表示的是flashback log的最小size是100个block，对于block_size=8k来说就是800k，我们把_flashback_log_min_size调大
alter system set "_flashback_log_min_size"=768 scope=spfile;

startup force

col ksppinm format a50
col ksppstvl format a20
set linesize 100
SYS@tstdb1-SQL> select ksppinm,ksppstvl from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and ksppinm in ('_flashback_size_based_on_redo','_db_flashback_log_min_size','_flashback_log_size','_flashback_log_min_size','_ksmg_granule_size');
KSPPINM                                            KSPPSTVL
-------------------------------------------------- --------------------
_ksmg_granule_size                                 4194304
_flashback_log_size                                500
_flashback_log_min_size                            768
_db_flashback_log_min_size                         4194304
_flashback_size_based_on_redo                      FALSE

alter database flashback off;
alter database flashback on;

SYS@tstdb1-SQL> select distinct bytes from v$flashback_database_logfile;

BYTES
----------
6291456

这下变成了6M

对于控制flashback log size的几个隐含参数总结如下：

_flashback_log_min_size和_db_flashback_log_min_size都能用来指定flashback log size的最小值，_flashback_log_min_size的单位是blocks，_db_flashback_log_min_size的单位是字节，_db_flashback_log_min_size的值不能小于_ksmg_granule_size，因为_ksmg_granule_size是SGA里分配的最小单位；
_flashback_log_size是以blocks为单为来指定flashback log size的大小。这几个参数的生效逻辑如下：
1、_flashback_size_based_on_redo=TRUE时
由redolog的平均大小决定flashback log size，其它隐含参数的值不予考虑

2、_flashback_size_based_on_redo=FALSE时
flashback log size最小值=min(_ksmg_granule_size，block_size*_flashback_log_min_size，_db_flashback_log_min_size)
若_flashback_log_size*block_size > flashback log size最小值则采用_flashback_log_size*block_size为flashback log size
若_flashback_log_size*block_size < flashback log size最小值则由flashback log size最小值决定flashback log size

//
// flashback generation buffer大小是如何设定的
//
flashback generation buffer是shared pool里一块分配给flashback使用的内存空间，存放从buffer cache复制过来的data block before image，RVWR进程会将flashback generation buffer的内容写入flashback log。oracle同样也没有提供能设置flashback generation buffer大小的方法，只有名为_flashback_generation_buffer_size的隐含参数：
col ksppinm format a50
col ksppstvl format a20
set linesize 100
SYS@tstdb1-SQL> select ksppinm,ksppstvl from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and ksppinm in ('_flashback_generation_buffer_size');
KSPPINM KSPPSTVL
-------------------------------------------------- --------------------
_flashback_generation_buffer_size 67108864

--
SYS@tstdb1-SQL> select * from v$sgastat where pool='shared pool' and name like '%flash%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool flashback_marker_cache_si        9200
shared pool flashback generation buff    15937344

--修改_flashback_generation_buffer_size=33554432，flashback generation buffer实际占用空间还是15937344
alter system set "_flashback_generation_buffer_size"=33554432 scope=spfile;

startup force

SQL> select ksppinm,ksppstvl from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and ksppinm in ('_flashback_generation_buffer_size');

KSPPINM KSPPSTVL
-------------------------------------------------- --------------------
_flashback_generation_buffer_size 33554432

SQL> select * from v$sgastat where pool='shared pool' and name like '%flash%';

--修改_ksmg_granule_size=33554432，恢复_flashback_generation_buffer_size至缺省值=67108864，flashback generation buffer实际占用空间变为了一个granule的大小31874880
alter system set "_flashback_generation_buffer_size"=67108864 scope=spfile;
alter system set "_ksmg_granule_size"=33554432 scope=spfile;

startup force

SQL> select ksppinm,ksppstvl from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and ksppinm in ('_flashback_generation_buffer_size');

KSPPINM KSPPSTVL
-------------------------------------------------- --------------------
_flashback_generation_buffer_size 67108864

SYS@tstdb1-SQL> select * from v$sgastat where pool='shared pool' and name like '%flash%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool flashback_marker_cache_si        9200
shared pool flashback generation buff    31874880

--设置_ksmg_granule_size=83886080，flashback generation buffer还是保持_flashback_generation_buffer_size的设置值67108864
alter system set "_ksmg_granule_size"=83886080 scope=spfile;

startup force

SQL> select ksppinm,ksppstvl from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and ksppinm in ('_flashback_generation_buffer_size');

KSPPINM KSPPSTVL
-------------------------------------------------- --------------------
_flashback_generation_buffer_size 67108864

SYS@tstdb1-SQL> select * from v$sgastat where pool='shared pool' and name like '%flash%';
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool flashback_marker_cache_si        9200
shared pool flashback generation buff    67108864

--将_flashback_generation_buffer_size调大至104857600，flashback generation buffer略微上升至71991760
alter system set "_flashback_generation_buffer_size"=104857600 scope=spfile;

startup force

col ksppinm format a50
col ksppstvl format a20
set linesize 100
SQL> select ksppinm,ksppstvl from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and ksppinm in ('_flashback_generation_buffer_size');

KSPPINM KSPPSTVL
-------------------------------------------------- --------------------
_flashback_generation_buffer_size 104857600

SQL> select * from v$sgastat where pool='shared pool' and name like '%flash%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool flashback_marker_cache_si        9200
shared pool flashback generation buff    71991760

--将_flashback_generation_buffer_size调大至204857600，flashback generation buffer升高至127504192
alter system set "_flashback_generation_buffer_size"=204857600 scope=spfile;

startup force

SQL> select ksppinm,ksppstvl from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and ksppinm in ('_flashback_generation_buffer_size');

KSPPINM KSPPSTVL
-------------------------------------------------- --------------------
_flashback_generation_buffer_size 204861440

SQL> select * from v$sgastat where pool='shared pool' and name like '%flash%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool flashback_marker_cache_si        9200
shared pool flashback generation buff   127504192

关于_flashback_generation_buffer_size参数在控制flashback generation buffer大小方面似乎没有太多的规律可循，我们暂且只能得出如下结论：

当_ksmg_granule_size >= _flashback_generation_buffer_size时，_flashback_generation_buffer_size参数生效
当_ksmg_granule_size < _flashback_generation_buffer_size时，flashback_generation_buffer的实际大小可能>_ksmg_granule_size也可能<_ksmg_granule_size，但不会超过_flashback_generation_buffer_size

//
// v$session_longops能观察到flashback的进度
//
SYS@tstdb1-SQL> select opname,target,sofar,totalwork,message from v$session_longops;

OPNAME               TARGET                    SOFAR TOTALWORK MESSAGE
-------------------- -------------------- ---------- ---------- --------------------------------------------------
Flashback Database                              1241       1256 Flashback Database: Flashback Data Applied : 1241
                                                                out of 1256 Megabytes done

//
//flashback log何时会被清理
//
早于DB_FLASHBACK_RETENTION_TARGET时间的flashback log会被自动清理出Fast Recovery Area。如果FRA空间不够用时会覆盖掉较早生成的flashback log，尽管这个被覆盖的flashback log处于DB_FLASHBACK_RETENTION_TARGET指定的保留期之内
当有新的flashback log需要生成而Fast Recovery Area又无可用空间的情况下，会从较早生成的flashback log开始重用(不管其是否在DB_FLASHBACK_RETENTION_TARGET定义的保留时间内)
当Fast Recovery Area空间用满，清理其中已经备份过的archived log时会连带清理掉使用该archived log的Flashback log

// 常用的监控flashback Database的视图有哪些

##1、v$flashback_database_logfile：flashback log的大小、位置、SCN等信息
col name format a50
set linesize 160 numwidth 16 pagesize 60
set numformat 99999999999999999
select * from V$FLASHBACK_DATABASE_LOGFILE;
NAME                                                             LOG#            THREAD#          SEQUENCE#              BYTES      FIRST_CHANGE#
-------------------------------------------------- ------------------ ------------------ ------------------ ------------------ ------------------
FIRST_TIME        TYPE
----------------- ---------
/oradata06/fra/TSTDB1/flashback/o1_mf_1l8k5mVou_.f                  1                  1                  1          134217728     12723365349159
lb
20150729 12:53:29 NORMAL

/oradata06/fra/TSTDB1/flashback/o1_mf_1l8k6047n_.f 2 1 1 134217728 0
lb
RESERVED

type=normal：处于db_flashback_retention_target保留期限内的log
type=RESERVED：下一个即将使用的flashback log，这个flashback log file可以是以前曾经使用过的文件，但目前已经过期；也可以是新创建出来的文件，该文件当前仅有一个flashback log的基本结构，没有实质内容，status=RESERVED的flashback log它的sequence#号为1

##2、V$FLASHBACK_DATABASE_LOG：db_flashback_retention_target设置值、实际能够闪回到的时间点、flashback logfile实际以及预估的空间占用量信息
select * from V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_ RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ----------------- ------------------ ------------------ ------------------------
12723365349159 20150729 12:53:29 2000 268435456 0

##3、select * from V$FLASHBACK_DATABASE_STAT：间隔时间内flashback data、redolog以及datafile读写所产生的字节数的对比，用来衡量flashback database在IO上产生的开销
BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- ------------------ ------------------ ------------------ ------------------------
20150729 12:53:29 20150729 13:04:21 4808704 1130496 1977856 0

// flashback log能否被dump

与online redolog类似，flashback logfile也能够被dump，常用命令如下：
###dump一个特定实例下的所有flashback logfile
ALTER SYSTEM DUMP FLASHBACK THREAD 【thread_number】

###dump某个特定的flashback logfile，【log_file_number】可以通过V$FLASHBACK_DATABASE_LOGFILE.log#获得
ALTER SYSTEM DUMP FLASHBACK LOGFILE 【log_file_number】

###从某个特定的flashback logfile里dump某一个数据块的所有记录
ALTER SYSTEM DUMP FLASHBACK LOGFILE 【log_file_number】DBA 【absolute_file_number】【block_number】;
例如：alter system dump flashback logfile 1 dba 9 40587; 表示dump出logfile 1里编号为9的数据文件里第40587个block的所有记录

###dump某个特定flashback logfile里的概要信息:
ALTER SYSTEM DUMP FLASHBACK LOGFILE 【log_file_number】 LOGICAL;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1755289/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-1755289/