闪回数据库概念： 10g新增功能，在启用flashback database功能后，数据库会定期将发生变化的数据块的 前镜像写入闪回日志的日志文件中， 在进行数据库闪回时，这些数据块可以直接复制回来以满足数据库的恢复需要。 redo log可以用来辅助数据恢复到更精确时间
闪回数据库概念：
10g新增功能，在启用flashback database功能后，数据库会定期将发生变化的数据块的 前镜像写入闪回日志的日志文件中，
在进行数据库闪回时，这些数据块可以直接复制回来以满足数据库的恢复需要。
redo log可以用来辅助数据恢复到更精确时间点，可以缩短恢复时间。
闪回数据库的日志文件由recovery writer rvwr进程写入
闪回日志文件由rvwr进程在闪回恢复区中自动创建和维护。
闪回数据库特性默认是关闭，需要在mount状态打开。
闪回时可将无关的表空间暂时offline以加快恢复速度,只将system undo 及所要闪回的数据对应的表空间在线即可。
不能使用闪回数据库的场景：数据文件物理损坏--只用进行物理恢复了
数据文件shrink
使用备份的控制文件--或者重建了控制文件，因为闪回日志信息记录在控制文件中
表空间删除--
想闪回到比闪回日志中最小scn更早的时间点--
如删除用户，可以先闪回到删除前，read only模式打开，然后expdp导出被删除用户。
完全恢复数据库，read write打开，再将用户导入。
总结一下开启闪回数据库功能简要步骤：
1.重启数据库到mount状态-startup mount;
2.打开归档模式-alter database archivelog; 非归档时执行打开闪回数据库将报错：ora-38707: media recovery is not enabled.
3.设置闪回区域--需要提前在os中创建好目录并指定大小- 未指定或指定空间不足报：ora-38709: recovery area is not enabled.ora-38708: not enough space for first flashback database log file
alter system set db_recovery_file_dest_size=2g;
alter system set db_recovery_file_dest='/backup/flashback_area';
4.打开数据库，查询归档模式-archive log list; 并查询v$database.flashback_on字段的状态，
下面实验打开闪回数据库功能，并实际进行一次闪回数据库操作来恢复数据。
1.查看数据库归档状态并打开闪回数据库功能22:08:49 sql> conn / as sysdba
connected.
22:08:54 sql> archive log list;
database log mode archive mode
automatic archival enabled
archive destination /u01/app/oracle/archbys001/
oldest online log sequence 46
next log sequence to archive 48
current log sequence 48
如未打开归档，则报以下错误：sys@ bys3>alter database noarchivelog;
database altered.
sys@ bys3>alter database flashback on;
alter database flashback on
*
error at line 1:
ora-38706: cannot turn on flashback database logging.
ora-38707: media recovery is not enabled.
查看闪回状态，默认是no，没打开。
22:08:56 sql> select flashback_on from v$database;
flashback_on
------------------
no
22:09:18 sql> host
filesystem size used avail use% mounted on
/dev/sda2 19g 9.2g 8.9g 51% /
/dev/sda1 99m 21m 74m 22% /boot
tmpfs 3.0g 484m 2.6g 16% /dev/shm
/dev/sda5 4.6g 1.4g 3.1g 31% /backup
[oracle@oel-01 ~]$ cd /backup
[oracle@oel-01 backup]$mkdir flashback_area
[oracle@oel-01 backup]$ ls
flashback_area
full
lost+found
[oracle@oel-01 backup]$ exit
exit
手动指定一下闪回区的大小和目录所在，可以使用默认值。
22:10:53 sql> alter system set db_recovery_file_dest_size=1g;
system altered.
22:11:47 sql> alter system set db_recovery_file_dest='/backup/flashback_area';
system altered.
如未指定闪回区，报错如下：sys@ bys3>alter database archivelog;
database altered.
sys@ bys3>alter database flashback on;
alter database flashback on
*
error at line 1:
ora-38706: cannot turn on flashback database logging.
ora-38709: recovery area is not enabled.
定义一个时间上限，设置数据库能够闪回的最大时间上限，单位分钟。2880是两天。可以使用默认值。
22:11:58 sql> alter system set db_flashback_retention_target=2880;
system altered.
22:14:12 sql> shutdown immediate;
database closed.
database dismounted.
oracle instance shut down.
22:14:36 sql> startup mount;
oracle instance started.
total system global area 845348864 bytes
fixed size 1339796 bytes
variable size 583011948 bytes
database buffers 255852544 bytes
redo buffers 5144576 bytes
database mounted.
22:20:43 sql> show parameter db_recovery
name type value
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /backup/flashback_area
db_recovery_file_dest_size big integer 1g
打开闪回数据库特性
22:21:18 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
22:21:32 sql> alter system set db_recovery_file_dest_size=2g;
system altered.
22:26:22 sql> alter database flashback on;
database altered.
22:26:27 sql> alter database open;
database altered.
22:26:57 sql> select flashback_on from v$database;
flashback_on
------------------
yes
2.在数据库中做ddl及dml操作，并删除所建表22:27:35 sql> conn bys/bys
connected.
22:34:41 sql> create table test7 as select * from test6;
table created.
22:37:43 sql> select * from test7;
abc
---------
1
2
22:37:53 sql> select current_scn from v$database;
current_scn
-----------
1373570
22:38:02 sql> drop table test7;
table dropped.
22:38:19 sql> show recyclebin;
original name recyclebin name object type drop time
---------------- ------------------------------ ------------ -------------------
test7 bin$39q/pnkxlungqfxequariq==$0 table 2013-06-23:22:38:18
22:38:24 sql> purge recyclebin;
recyclebin purged.
22:39:52 sql> show recyclebin;
22:39:54 sql> select oldest_flashback_scn from v$flashback_database_log;
oldest_flashback_scn
--------------------
1373161
22:40:28 sql> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
session altered.
查询最早的scn对应的时间---我这里是上面打开闪回数据库特性的时间。
也可以从oldest_flashback_scn列查最早scn
22:40:35 sql> select oldest_flashback_time from v$flashback_database_log;
oldest_flashback_tim
--------------------
23-jun-2013 22:26:27
3.重启数据库到mount状态进行闪回数据库操作22:42:19 sql> shutdown immediate;
ora-01031: insufficient privileges
22:43:00 sql> conn / as sysdba
connected.
22:43:05 sql> shutdown immediate;
database closed.
database dismounted.
oracle instance shut down.
22:43:16 sql> startup mount;
oracle instance started.
total system global area 845348864 bytes
fixed size 1339796 bytes
variable size 583011948 bytes
database buffers 255852544 bytes
redo buffers 5144576 bytes
database mounted.
22:49:14 sql> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
session altered.
22:49:20 sql> select sysdate from dual;
sysdate
--------------------
23-jun-2013 22:49:25
22:49:25 sql> select oldest_flashback_time from v$flashback_database_log;
oldest_flashback_tim
--------------------
23-jun-2013 22:26:27
22:50:38 sql> select oldest_flashback_scn from v$flashback_database_log;
oldest_flashback_scn
--------------------
1373161
22:50:48 sql> flashback database to scn 1373570;
flashback complete.
下面语句是测试过可用的使用时间戳闪回，我未使用。
sql> flashback database to timestamp to_timestamp('2013-06-26 14:05:10','yyyy-mm-dd hh24:mi:ss');
resetlogs打开之后，不能再闪回到 resetlogs之前的时间点。
22:51:53 sql> alter database open resetlogs;
database altered.
22:53:18 sql> show user
user is sys
4.使用bys用户登陆验证第2步删除表操作之前的数据是否正常22:54:12 sql> conn bys/bys
connected.
22:55:13 sql> select * from test7;
abc
---------
1
2
补充：不确定闪回的时间或scn是否足够使数据恢复，可以以read only方式打开数据库
即flashback database to scn 1373570;或flashback database to timestamp to_timestamp('2013-06-26 14:05:10','yyyy-mm-dd hh24:mi:ss');
然后 alter database open read only;然后查询数据看是否需要的数据已经恢复。
如果未恢复，可以继续使用flashback database to scn/timestamp这样语句来恢复，直到恢复出需要的数据。
确定恢复出全部需用的数据后，就可以使用alter database open resetlogs;语句打开数据库了。
resetlogs打开数据库后，就不能再使用flashback回到resetlogs之前的时间点了。
增加一个强制检查点的恢复实验--可用于打psu时刷新数据字典时sql> startup mount;
oracle instance started.
total system global area 536870912 bytes
fixed size 1220432 bytes
variable size 125829296 bytes
database buffers 406847488 bytes
redo buffers 2973696 bytes
database mounted.
sql> create restore point p1 guarantee flashback database;
restore point created.
sql> alter database open;
database altered.
sql> create user bys identified by bys;
user created.
sql> grant dba to bys;
grant succeeded.
sql> flashback database to restore point p1;
flashback database to restore point p1
*
error at line 1:
ora-38757: database must be mounted and not open to flashback.
sql> conn bys/bys
connected.
sql> conn / as sysdba
connected.
sql> shutdown immediate;
database closed.
database dismounted.
oracle instance shut down.
sql> startup mount;
oracle instance started.
total system global area 536870912 bytes
fixed size 1220432 bytes
variable size 125829296 bytes
database buffers 406847488 bytes
redo buffers 2973696 bytes
database mounted.
sql> flashback database to restore point p1;
flashback complete.
sql> alter database open;
alter database open
*
error at line 1:
ora-01589: must use resetlogs or noresetlogs option for database open
sql> alter database open resetlogs;
database altered.
sql> select username from dba_users;
username
------------------------------
oltp_user
oe
hr
sst
sh
tsmsys
dip
dbsnmp
outln
sys
system
11 rows selected.
sql>