一、Flashback Database(flashback Log回滚数据库)

启用Flashback Database

1、启用Flash Recovery Area
SQL> alter system set db_recovery_file_dest='/backup/flashback' scope=both;
SQL> alter system set db_recovery_file_dest_size=5g scope=both;
SQL> select name,value from v$parameter where name like '%flash%' or name like '%recovery%';

查看Flashback database是否开启
SQL> select name,current_scn,flashback_on from v$database;

禁用Flash Recovery Area
SQL> alter system set db_recovery_file_dest='';

2、启用数据库Flashback功能
SQL> startup mount;
SQL> alter database flashback on;

确认Flashback database是否开启
SQL> select name,current_scn,flashback_on from v$database;

cd /backup/flashback

3、设置db_flashback_retention_target参数(单位为分钟)
SQL> alter system set db_flashback_retention_target=1440 scope=both;
SQL> alter database open;

命令和视图
查看Flash Recovery Area的使用情况
SQL> select name,space_limit/(1024*1024),space_used/(1024*1024) from v$recovery_file_dest;

查看哪些归档日志放在Falsh Recovery Area中
SQL> select name,is_recovery_dest_file from v$archived_log;

SQL> v$flashback_database_log
SQL> v$flashback_database_logfile
SQL> v$flashback_database_stat

Flashback Database操作

1、模拟用户误操作
备份数据库
SQL> select * from scott.emp;
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
SQL> truncate table scott.emp;
SQL> select * from scott.emp;
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

2、确认能够恢复的时间点
SQL> select oldest_flashback_scn,oldest_flashback_time,retention_target,
flashback_size,estimated_flashback_size
from v$flashback_database_log;
SQL> select log#,bytes,first_change#,first_time from v$flashback_database_logfile;

3、Flashback Database 实际是对数据库的一个不完全恢复操作，但不同的是，它不需要执行restore操作
SQL> shutdown immediate;
SQL> startup mount;
=================================================================================
模拟时间点选择太晚，恢复失败
SQL> flashback database to timestamp to_timestamp('2009-12-01 15:18:34','yyyy-mm-dd hh24:mi:ss');
SQL> alter database open read only;
SQL> select * from scott.emp;
SQL> shutdown immediate;
SQL> startup mount;
==================================================================================
SQL> flashback database to timestamp to_timestamp('2009-12-01 15:12:00','yyyy-mm-dd hh24:mi:ss');
SQL> alter database open read only;
SQL> select * from scott.emp;

4、恢复成功后，以resetlog方式打开数据库
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open resetlogs;
SQL> select * from scott.emp;
备份数据库

二、Flashback Drop(Tablespace Recycle Bin回收站恢复用户误删除的表)
这个功能是从Oracle 10g开始出现的，用于恢复用户误删除的对象(表、索引等)。这个技术依赖于Tablespace Recycle Bin(表空间回收站)。

1、查看初始化参数recyclebin参数用于控制是否启用recyclebin功能，默认是ON
SQL> show parameter recyclebin

2、删除表
SQL> drop table t2;
SQL> commit;

3、查看recycle bin的对象列表
SQL> show recyclebin
SQL> select object_name,original_name from recyclebin;
SQL> select * from "BIN$eiU7Mp3Fwz/gQAB/AQARdA==$0";

4、Flashback Drop操作
SQL> flashback table t2 to before drop;
or
SQL> flashback table "BIN$eiU7Mp3Fwz/gQAB/AQARdA==$0" to before drop;
or
SQL> flashback table t2 to before drop rename to t1;

5、Recycle Bin的维护
SQL> purge tablespace tablespace_name 删除指定表空间的Recycle Bin对象
SQL> purge tablespace ... user ... 删除指定表空间的Recycle Bin中指定用户的对象
SQL> purge recyclebin   删除当前用户的Recycle Bin对象
SQL> purge dba_recyclebin  删除所有用户的Recycle Bin对象
SQL> drop table table_name purge 彻底删除表
SQL> purge index recycle_bin_object_name 

三、Flashback Query和Flashback Table(undo)
Flashback Query实际包含3个工具，即Flashback Query,Flashback Version Query和Flashback Transaction Query。它们都是利用Undo的内容来实现回退功能，用于找出想要回退到的时间点，而后使用Flashback Table完成回退操作。

1、Flashback Query

1.1、删除数据
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
SQL> delete from t2;
SQL> commit;

1.2、通过Flashback Query，查看误删除之前的状态
SQL> select * from t2 as of timestamp to_timestamp('2009-12-07 22:53:01','yyyy-mm-dd hh24:mi:ss');

1.3、利用Flash Query进行数据恢复
SQL> insert into t2 select * from t2 as of timestamp to_timestamp('2009-12-07 22:53:01','yyyy-mm-dd hh24:mi:ss');

SQL> update emp e1 set e1.sal=
(select sal from emp as of timestamp
to_timestamp('2009-12-07 22:53:01','yyyy-mm-dd hh24:mi:ss') e2 where e1.empno=e2.empno);

2、Flashback Version Query

创建表时如果没有使用rowdependencise关键字，则ORA_ROWSCN使用的是数据块头的SCN，因此一个数据块内的所有记录的ORA_ROWSCN都一样。如果使用了rowdependencise关键字，则每个记录都有自己的ORA_ROWSCN，这个值来自ITL或dscn字段。
SQL> create table t1(id number(4),name varchar2(10)) rowdependencies;

SQL> insert into t1 values(1,'hjp');
SQL> commit;

SQL> insert into t1 values(2,'jds');
SQL> commit;

SQL> insert into t1 values(3,'kbb');
SQL> commit;

SQL> insert into t1 values(4,'zym');
SQL> commit;

SQL> analyze table t1 compute statistics;
SQL> select blocks from user_tables where table_name='T1';

SQL> select ora_rowscn,id,name from t1;
ORA_ROWSCN         ID NAME
---------- ---------- ----------
    570777          1 hjp
    570794          2 jds
    570812          3 kbb
    570816          4 zym

SQL> update t1 set name='clb' where id=4;
SQL> update t1 set name='zym' where id=4;
SQL> delete t1 where id = 4;

SQL> select id,name from t1 versions between scn minvalue and maxvalue where id=4;
SQL> select id,name,versions_xid,versions_startscn,versions_endscn,versions_operation,
versions_starttime,versions_endtime
from scott.t1 versions between scn minvalue and maxvalue;
or
SQL> select id,name,versions_xid,versions_startscn,versions_endscn,
decode(versions_operation,'I','Insert','U','Update','D','Delete','Original')"Operation"
from t1 versions between scn minvalue and maxvalue;

3、Flashback Transaction Query
查看某个事务执行的所有变化

3.1、准备测试表
SQL> create table t4(id number,name varchar2(10));
SQL> create table t5(id number,did number,name varchar(10));

3.2、第一个事务
SQL> insert into t4 values(1,'dept1');
SQL> insert into t5 values(1,1,'emp1');
SQL> commit;

3.3、执行多个事务
SQL> insert into t5 values(2,1,'emp2');
SQL> commit;

SQL> delete from t5 where id=2;
SQL> commit;

SQL> insert into t5 values(2,1,'emp3');
SQL> commit;

3.4、查看视图
SQL> select xid,operation,commit_scn,undo_sql
from flashback_transaction_query where xid in
(select versions_xid from scott.t5 versions between scn minvalue and maxvalue);

4、Flashback Table

根据以上查询获得的SCN或TIMESTAMP对表进行Flashback，先必须允许表的row movement。
SQL> alter table t5 enable row movement;
SQL> flashback table t4,t5 to scn 574516;

Flashback功能依赖UNDO中的内容，可以反复进行Flashback尝试，只要UNDO中的内容足够。

UNDO Retntion参数定义的是提交修改后的UNDO记录还要保留多长时间，Oracle并不强制保留这么长时间，可以设置Retention Guarantee来强制这种保留时间限制。

查询默认保留时间
SQL> show parameter undo_retention
NAME               TYPE        VALUE
------------------ ----------- ------
undo_retention     integer     900

SQL> select tablespace_name,retention from dba_tablespaces;
TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       NOGUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY

SQL> alter tablespace undotbs1 retention guarantee;

SQL> select tablespace_name,retention from dba_tablespaces;
TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY