课程目标：
- 配置闪回数据库
- 进行闪回数据库
- 监控闪回数据库
1、闪回数据库
使用闪回数据库，可以取消某个时间点之后的所有更改，快速将数据库回到这个时间点。由于不需要还原备份，所有此操作很快。可以使用此功能来解决数据逻辑错误问题。
当使用闪回数据库，Oracle数据库使用过去的块映像来更改数据库。在正常的数据库操作时，数据库记录这些块映像到闪回日志。闪回日志顺序写入且不归档。Oracle数据库在快速恢复区中自动创建，删除和调整闪回日志大小。用户需要在监控性能和决定快速恢复区中闪回日志大小时关注闪回日志。
闪回数据库需要的时间取决于闪回的时间点以及该时间点后数据库的活动量。还原和恢复数据库可能需要更长的时间。闪回日志中的前映像只能将数据库还原到过去的某一个时间点，向前的恢复用于将数据库恢复到过去的某一时间一致的状态。数据库将数据文件返回到先前的某个时间点，但不影响辅助文件，比如初始化参数文件。闪回数据库也可以用于Data Guard和Recovery Advisor以及同步复制数据库。
2、闪回数据库架构
![clipboard[1]](http://img.blog.itpub.net/blog/attachment/201605/23/28536251_14639765667BN6.png "clipboard[1]")
当启用闪回数据库，RVWR（Flashback Write）后台进程启动。这个后台程序从闪回缓冲区按顺序将闪回数据库数据写到闪回日志，闪回日志循环使用。当发出FLASHBACK DATABASE命令，使用闪回日志将数据块还原到之前的映像，使用重做数据前滚到指定的时间点。
启用闪回数据库的开销取决于读/写的混合数据库负载。由于查询不需要记录任何闪回数据，写密集型的负载越大，打开闪回数据库的开销越高。
3、配置闪回数据库
![clipboard[2]](http://img.blog.itpub.net/blog/attachment/201605/23/28536251_1463976566iJWW.png "clipboard[2]")
配置闪回数据库步骤如下：
（1）配置快速恢复区。
（2）使用初始化参数DB_FLASHBACK_RETENTION_TARGET设置保留时间目标，指定可以闪回数据库的时间上限，单位为分钟。上图例子中指定2880分钟，2天。这个参数仅仅是一个目标值，而不保证一定可以达到，闪回时间间隔取决于快速恢复区保留了多少闪回数据。
（3）使用以下命令启用闪回数据库：
ALTER DATABASE FLASHBACK ON;
在启用闪回数据库之前，数据库必须配置为归档模式。
使用下面语句查询是否启用了闪回数据库：
SELECT flashback_on FROM v$database;
使用ALTER DATABASE FLASHBACK OFF命令禁用闪回数据库，同时自动删除闪回日志。
注意：数据库只能在mount in exclusive模式下启用闪回数据库。
例子：配置闪回数据库
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ------------ ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 4040M
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 235
Next log sequence to archive 237
Current log sequence 237
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
SQL> alter system set db_flashback_retention_target=2880 scope=both;
System altered.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: not enough space for first flashback database log file
SQL> host oerr ora 38708
38708, 00000, "not enough space for first flashback database log file"
// *Cause: An ALTER DATABASE FLASHBACK ON command failed because there
// was not enough space in the Recovery Area for the first
// flashback database log file.
// *Action: Make more space in the Recovery Area. For example, this
// can be done by increasing the value of
// DB_RECOVERY_FILE_DEST_SIZE.
SQL> select file_type,percent_space_used from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED
-------------------- ------------------
CONTROL FILE .25
REDO LOG 3.79
ARCHIVED LOG 35.3
BACKUP PIECE .74
IMAGE COPY 58.84
FLASHBACK LOG 0
FOREIGN ARCHIVED LOG 0
7 rows selected.
RMAN> delete backup;
RMAN> delete copy;
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
4、需要做的事情
![clipboard[3]](http://img.blog.itpub.net/blog/attachment/201605/23/28536251_14639765688XXx.png "clipboard[3]")
登录到EM。在可用页面上，选择备份/恢复区域中的恢复设置。确保数据库在ARCHIVELOG模式。如果没有的话，选择归档模式，然后单击“继续”。需要关闭并重新启动实例以生效更改。
![clipboard[4]](http://img.blog.itpub.net/blog/attachment/201605/23/28536251_1463976570Rl4h.png "clipboard[4]")
![clipboard[5]](http://img.blog.itpub.net/blog/attachment/201605/23/28536251_14639765731E1H.png "clipboard[5]")
![clipboard[6]](http://img.blog.itpub.net/blog/attachment/201605/23/28536251_1463976575fB7G.png "clipboard[6]")
5、闪回数据库：示例
![clipboard[7]](http://img.blog.itpub.net/blog/attachment/201605/23/28536251_1463976576dC2N.png "clipboard[7]")
可以使用RMAN的FLASHBACK DATABASE命令执行闪回数据库操作。可以使用SEQUENCE和THREAD指定一个重做日志序列号和线程作为一个下限。RMAN选择可以闪回的文件，但不包括指定的序列号。
可以使用SQL的FLASHBACK DATABASE命令将数据库恢复到过去的某个时间或SCN。如果使用SCN子句，必须提供一个SCN号。如果使用TO TIMESTAMP子句，必须提供一个时间戳。还可以指定一个还原点名称。
可以使用V$SESSION_LONGOPS视图监控闪回数据库过程。
注意：数据库必须在mounted in exclusive模式运行FLASHBACK DATABASE命令，以只读打开数据库查看改变。闪回完成后需要使用RESETLOGS选项打开数据库。
例子：闪回数据库
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-02-01 21:57:52
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> delete from emp;
107 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
RMAN> flashback database to time="to_date('2016-02-01 21:57:52','yyyy-mm-dd hh24:mi:ss')";
Starting flashback at 01-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:26
Finished flashback at 01-FEB-16
SQL> alter database open read only;
Database altered.
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from emp;
COUNT(*)
----------
107
6、闪回数据库注意事项
![clipboard[8]](http://img.blog.itpub.net/blog/attachment/201605/23/28536251_1463976576rR5f.png "clipboard[8]")
在不能使用闪回数据库的时候，使用不完整恢复将数据库恢复到特定的时间。闪回数据库操作完成后，可以以只读模式打开数据库验证是否使用了正确的目标时间或SCN。如果不是，可以再次闪回数据库，或执行一个恢复操作前滚数据库。因此，向前恢复数据库以撤消闪回数据库的操作。
不能使用闪回数据库恢复一个被删除的数据文件，只会将数据文件信息增加到控制文件并标记为脱机，但并没有闪回该数据文件。闪回数据库也不能闪回数据文件到数据文件收缩之前，如果想闪回数据文件到数据文件收缩之前，需要在闪回数据库操作之前离线该数据文件。可以对配置了自动扩展的数据文件使用闪回数据库。可以在FLASHBACK DATABASE命令中使用TO BEFORE RESETLOGS子句以便闪回到上一个RESETLOGS操作之前。
注意：闪回保留时间目标不是一个绝对保证时间，如果快速恢复区空间不够，将会自动删除闪回日志。
例子：闪回数据库后撤销闪回操作
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> create restore point fb1;
Restore point created.
SQL> delete from emp;
107 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
SQL> flashback database to restore point fb1;
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select count(*) from emp;
COUNT(*)
----------
0
例子：数据文件收缩后闪回失败
SQL> alter database datafile '+DATA/stone/datafile/fda_tbs1.281.902663435' resize 80M;
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
SQL> flashback database to restore point fb1;
flashback database to restore point fb1
*
ERROR at line 1:
ORA-38766: cannot flashback data file 17; file resized smaller
ORA-01110: data file 17: '+DATA/stone/datafile/fda_tbs1.281.902663435'
SQL> alter database datafile '+DATA/stone/datafile/fda_tbs1.281.902663435' offline;
Database altered.
SQL> flashback database to restore point fb1;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from emp;
COUNT(*)
----------
107
再次online数据文件报错：
SQL> alter database datafile '+DATA/stone/datafile/fda_tbs1.281.902663435' online;
alter database datafile '+DATA/stone/datafile/fda_tbs1.281.902663435' online
*
ERROR at line 1:
ORA-01190: control file or data file 17 is from before the last RESETLOGS
ORA-01110: data file 17: '+DATA/stone/datafile/fda_tbs1.281.902663435'
例子：闪回到上一次resetlogs之前
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
SQL> flashback database to before resetlogs;
Flashback complete.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
7、监控闪回数据库
![clipboard[9]](http://img.blog.itpub.net/blog/attachment/201605/23/28536251_1463976577BGJr.png "clipboard[9]")
通过监测快速恢复区的空间使用情况可以确定是否满足保留时间目标。使用V$FLASHBACK_DATABASE_LOG视图监视闪回数据库保留时间目标。
- ESTIMATED_FLASHBACK_SIZE字段估计满足当前闪回保留时间目标所有要的快速恢复区磁盘空间。
- FLASHBACK_SIZE字段为闪回数据的当前大小，以字节为单位。
- OLDEST_FLASHBACK_SCN和OLDEST_FLASHBACK_TIME字段显示数据库可以闪回到的最小的SCN及时间的大概值，V$DATABASE中的CURRENT_SCN字段为当前数据库的SCN。
使用V$FLASHBACK_DATABASE_STAT视图监控在闪回数据库日志中记录闪回数据的开销。此视图包含24小时的信息，每行表示1小时的时间间隔。可以使用此视图确定闪回数据生成速率的变化。
SQL> SELECT begin_time, end_time, flashback_data, db_data,
2 redo_data, estimated_flashback_size AS EST_FB_SZE
3 FROM V$FLASHBACK_DATABASE_STAT;
BEGIN_TIM END_TIME FLASHBACK_DATA DB_DATA REDO_DATA EST_FB_SZE
--------- --------- -------------- ---------- ---------- ----------
12-FEB-09 12-FEB-09 16384 0 24576 0
12-FEB-09 12-FEB-09 6594560 7471104 1533440 815923200
12-FEB-09 12-FEB-09 17235968 12361728 5150920 839467008
12-FEB-09 12-FEB-09 311648256 37249024 10272768 855195648
基于以上信息，可能需要调整保留时间或者快速恢复区大小。
FLASHBACK_DATA和REDO_DATA字段分别表示闪回数据和重做数据写入字节数。DB_DATA字段表示数据块读写字节数。
使用V$RECOVERY_FILE_DEST视图查看快速恢复区的信息。字段描述如下：
- NAME：快速恢复区名字。
- SPACE_LIMIT：参数DB_RECOVERY_FILE_DEST_SIZE指定的磁盘可用限制值。
- SPACE_USED：使用了多少字节。
- SPACE_RECLAIMABLE：通过空间管理算法删除过期、冗余以及低优先级的文件而可以回收的空间。
- NUMBER_OF_FILES：文件数量。
SQL> SELECT name, space_limit AS quota,
2 space_used AS used,
3 space_reclaimable AS reclaimable,
4 number_of_files AS files
5 FROM v$recovery_file_dest ;
NAME QUOTA USED RECLAIMABLE FILES
------------------------ ---------- ---------- ----------- -----
/u01/flash_recovery_area 5368707120 2507809104 203386880 226
例子：通过V$FLASHBACK_DATABASE_LOG查询闪回数据相关信息
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
4350231 2016-02-02 14:38:55 1440 104857600 331431936
例子：使用V$FLASHBACK_DATABASE_STAT查询记录闪回日志的信息
SQL> select * from v$flashback_database_stat;
BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------- ------------------- -------------- ---------- ---------- ------------------------
2016-02-02 14:51:45 2016-02-02 15:47:18 12812288 19611648 7825408 0
例子：使用V$RECOVERY_FILE_DEST视图查看快速恢复区的信息
SQL> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------ ----------- ---------- ----------------- ---------------
/u01/app/oracle/fast_recovery_ 8589934592 3987886592 1587984384 17
area
8、使用EM监控闪回数据库
![clipboard[10]](http://img.blog.itpub.net/blog/attachment/201605/23/28536251_1463976578myC7.png "clipboard[10]")
大多数在前面提到的闪回数据库统计信息可以从恢复设置页面查看。这些指标包括所有闪回日志的当前使用的空间，最低的SCN及其对应的时间。
9、保证还原点
![clipboard[11]](http://img.blog.itpub.net/blog/attachment/201605/23/28536251_1463976579gg6n.png "clipboard[11]")
像普通的还原点一样，保证还原点可以用于恢复操作的SCN的别名。一个主要的区别是，保证恢复点永远不会从控制文件中过期，且必须显式删除。
即使数据库没有启用闪回日志，创建特定SCN的保证还原点确保可以执行闪回数据库操作将数据库回到该SCN的状态。如果启用了闪回日志，创建一个保证还原点确保可以闪回数据库到之后的任何时间点。
只有快速恢复区有足够的空间存储闪回日志，就可以使用保证还原点将数据库回到几天或者几周之前。
而且与正常的还原点一样，保证还原点可以用来指定RECOVER DATABASE操作的时间点。
注意：适用于闪回数据库的限制也适用于保证还原点。例如，收缩数据文件或删除一个表空间会阻止受影响的数据文件闪回到保证还原点。
例子：创建保证还原点及删除保证还原点
SQL> create restore point before_upgrade guarantee flashback database;
Restore point created.
SQL> drop restore point before_upgrade;
Restore point dropped.
10、闪回数据库和保证还原点
![clipboard[12]](http://img.blog.itpub.net/blog/attachment/201605/23/28536251_1463976579qlbe.png "clipboard[12]")
使用保证还原点，数据库必须满足以下前提条件：
- COMPATIBLE初始化参数必须设置为10.2或更高。
- 数据库必须运行在归档模式。
- FLASHBACK DATABASE命令需要还原点之后的归档重做日志文件。
- 必须配置快速恢复区。保证还原点使用类似于闪回日志的机制。
- 如果没有启用闪回数据库，那么在创建第一个保证还原点的时候（或者删除了所有以前创建的保证还原点），数据库必须挂载而不打开（mounted，not open）。
闪回数据库和保证还原点的日志包括数据文件块改变之前的前映像。闪回日志和保证还原点日志的主要区别是块记录的时间以及在快速恢复区空间不足时是否可以删除。这些差异影响日志空间使用和数据库的性能。
如果启用闪回数据库并定义一个或多个保证还原点，然后数据库执行正常闪回日志。在这种情况下，恢复区保留闪回到当前至保证还原点之间任意时间的闪回日志。闪回日志如果需要用于满足保证还原点，则在空间不足时是不会被自动删除的。
例子：创建保证还原点
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 507513936 bytes
Database Buffers 322961408 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL> alter database flashback off;
Database altered.
SQL> create restore point before_upgrade guarantee flashback database;
Restore point created.
SQL> alter database open;
Database altered.
11、相关习题
（1）You plan to execute the following command to perform a Flashback Database operation in your database:
SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE -5/24);
Which two statements are true about this? (Choose two)
A. The database must have multiplexed redo log files.
B. The database must be in the MOUNT state to execute the command.
C. The database must be in the NOMOUNT state to execute the command.
D. The database must be opened in RESTRICTED mode before this operation.
E. The database must be opened with the RESETLOGS option after the flashback operation.
答案：BE
（2）Flashback Database relies on which technologies to recover to a point in time
A. Flashback Data Archive
B. Flashback logs in the flash recovery area
C. FlashbUndo tablespace
D. RMAN command line
E. None of the above
答案：B
（3）The _______ writes the Flashback Database logs in the flash recovery area.
A. FLSH
B. FLDB
C. RVWR
D. RVRW
E. FBDA
答案：C
（4）Which of these are valid Flashback Database recovery point parameters? (Choose all that apply.)
A. SCN
B. Timestamp
C. Named recovery point
D. Transaction ID
E. Session ID
答案：ABC
（5）On Friday at 11:30 am you decided to flash back the database because of a user error that occurred at 8:30 am.
Which option must you use to check whether a flashback operation can recover the database to the specified time?
A. Check the alert log file
B. Query the V$FLASHBACK_DATABASE_LOG view
C. Query the V$RECOVERY_FILE_DEST_SIZE view
D. Query the V$FLASHBACK_DATABASE_STAT view
E. Check the value assigned for the UNDO_RETENTION parameter
答案：B
（6）What two are the prerequisites for enabling Flashback Database? (Choose two)
A. The database must be in ARCHIVELOG mode
B. The database must be in MOUNT EXCLUSIVE mode
C. The database must be opened in RESTRICTED mode
D. The database instance must be started in the NOMOUNT state
E. The database instance must have the keep buffer pool defined
答案：AB
（7）You want to configure the Flashback Database feature and retain flashback logs for three days.
Presently the database is open and configured in ARCHIVELOG mode.
The following steps must be performed in the correct sequence to do this:
1. Set the retention target:
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
2. Enable Flashback Database:
SQL> ALTER DATABASE FLASHBACK ON;
3. Mount the database:
SQL> STARTUP MOUNT EXCLUSIVE
4. Shutdown the instance:
SQL> SHUTDOWN IMMEDIATE
5. Open the database:
SQL> ALTER DATABASE OPEN;
Choose the correct sequence.
A. 4, 1, 3, 2, 5
B. 4, 3, 1, 2, 5
C. 2, 4, 3, 5, 1
D. 4, 2, 5, 3, 1
答案：B
（8）What does the DB_FLASHBACK_RETENTION_TARGET parameter configure?
A. An upper limit on how far you can flash back the database, depending on the information in the redo logs
B. An upper limit on how far you can flash back the database, depending on the information in the undo tablespace
C. The amount of time for which the flashback data is to be kept in the flash recovery area, provided that there is enough space
D. The amount of time for which the flashback data is guaranteed to be kept in the undo tablespace, provided that there is enough space
答案：C
（9）You executed the following query:
此主题相关图片如下：![clipboard[13]](http://img.blog.itpub.net/blog/attachment/201605/23/28536251_1463976580uf0x.png "clipboard[13]")
Considering that all the redo logs are available, what information can you derive from the output of the preceding query?
A.The time when the last flashback operation in your database was performed
B.The time when the first flashback operation in your database was performed
C.The approximate time and the lowest system change number (SCN) to which you can flash back your database
D.The system change number (SCN) and the time when the Flashback Database was enabled in the database instance
答案：C
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28536251/viewspace-2105238/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/28536251/viewspace-2105238/
6418
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
