oracle flashback技术相关知识整理

oracle flashback是一个很强大的技术，它可以让你实现对一些已经commit的数据进行回滚。


一、ORACLE FLASHBACK 概述


oracle flashback可以看成一个家族名称，它是由不同的成员组成。
如下：
oracle flashback database             :which returns your entire database to a previous state without requiring you to restore files from backup;
闪回整个数据库到之前的时间点



oracle flashback table                :which returns one or more tables to their contents at a previous time;
闪回一张或多张表到之前的时间点



oracle flashback drop                 :which undoes the effects of the DROP TABLE operation;
闪回被drop掉的表


oracle flashback query                :which is used to query the contents of the database at a past time;
查询过去时间点的数据库信息


oracle flashback version query        :which lets you view past states of data;
允许对不同时间段内数据表的不同版本进行查询，查询可以反映不同时间段内数据表的变更




oracle flashback transaction query    :which is used to review transactions affecting a table over time.
事务闪回，审计了一个事务到底做了什么，甚至可以回滚一个已经提交的事务。
事务闪回其实是 oracle flashback version query查询的一个扩充。flashback version query说明了可以审计一段时间内表的所有改变，
但仅仅是能发现问题，对于错误的事务，没有好的处理办法。





二、oracle flashback启用


对于flashback database 要求数据库必须处于归档模式，且闪回之后必须使用resetlogs方式打开数据库


1、查看数据库的归档模式以及闪回是否启用


SQL> select log_mode,open_mode,flashback_on from v$database;


LOG_MODE     OPEN_MODE  FLASHBACK_ON
------------ ---------- ------------------
ARCHIVELOG   READ WRITE NO




2、查看以及设置闪回目录、闪回目录空间大小等


SQL> show parameter db_recovery


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\oracle\product\10.2.0/flash
                                                 _recovery_area
db_recovery_file_dest_size           big integer 2G
SQL>




3、设置闪回保留生存期      ------缺省为分钟
SQL> show parameter flashback


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL>


设置保留时间为1个小时，使用如下命令：
alter system set db_flashback_retention_target=120 scope=both;






4、在mount状态下启用flashback


==> shutdown immediate  


==> startup mount 


==> 


SQL> alter database flashback on;


数据库已更改。SQL> alter database flashback on;


数据库已更改。




==> alter database open;






三、oracle flashback各项技术的使用


1、FLASHBACK DATABASE


查看当前数据库可以闪回的最早scn和时间点


SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME    FROM V$FLASHBACK_DATABASE_LOG;


闪回步骤：（前提是归档可用）
==>关闭数据库
==>启动数据库到mount状态
==>闪回至某个时间点，scn，序列
==>使用resetlogs打开数据库


a、sqlplus常用的几种闪回数据库的方法：


基于scn闪回：
flashback database to scn 918989;




基于时间戳闪回：
flashback database to timestamp to_timestamp('2014-10-01 13:00:00','yyyy-mm-dd hh24:mi:ss');




基于还原点闪回：
flashback database to restore point  test_restore_point;   ----语法：flashback [standy] database [<database_name>] to [before] restore point <restore_point_name>




关于scn和时间戳的闪回，较为简单，只要确定好要闪回到的时间点或者scn即可，
而对于所谓的restore point闪回，之前没有接触过，便做下该实验：


基于时点闪回的实验：


SQL> select count(*) from king;


  COUNT(*)
----------
   1000022
   
SQL> create restore point test_restore_point;


还原点已创建。




SQL> truncate table king;


表被截断。


下面进行基于还原点闪回数据库：


SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。


Total System Global Area  452984832 bytes
Fixed Size                  1303812 bytes
Variable Size             130026236 bytes
Database Buffers          314572800 bytes
Redo Buffers                7081984 bytes
数据库装载完毕。
SQL> flashback database to restore point  test_restore_point;


闪回完成。


SQL> alter database open resetlogs;


数据库已更改。


查询king表：
SQL> conn king
输入口令:
已连接。
SQL> select count(*) from king;         ----------可以看到，被truncate的数据被闪回回来了。


  COUNT(*)
----------
   1000022


SQL>








b、使用rman 进行 flashback database


常用方法：
flashback database to scn=9879983;
flashback database to sequence=85 thread=1;




步骤也是一样，将oracle 启动到mount状态，进行闪回，最后采用resetlogs方式打开。






c、可以单独关闭某些表空间的闪回功能


alter tablespace users flashback off;


但是若要再次开启表空间的flashback，那么必须将数据库启动到mount状态进行：
alter tablespace users flashback on;




d、查看用户是否有flashback的权限


SQL> select * from session_privs where privilege like 'FLASHBACK%'
  2  ;


PRIVILEGE
----------------------------------------
FLASHBACK ANY TABLE








关于flashback database的小结：


闪回数据库的实现原理是闪回日志，只要配置了flashback，就会自动的创建闪回日志，
只要数据库里的数据发生变化，oracle就会将数据修改前的旧值保存在闪回日志里面（有点像undo），
当我们需要闪回数据库时候，oracle会读取闪回日志里的记录，同时应用归档日志实现一致性，从而将数据库回退到历史上的某个时间点。


个人感觉，基于时间戳的闪回用到的概率会比较大，即：
基于时间戳闪回：
flashback database to timestamp('2014-10-01 13:00:00','yyyy-mm-dd hh24:mi:ss');




-----------------------------------------------------------------------------------------------------------------------------------


2、oracle flashback table


flashback table是使用 undo tablespace 的内容来实现对数据的回退。


用法：flashback table table_name to scn（to timestamp）


注意：如果要对表进行flashback，必须允许表的row movement
alter table table_name enable row movement;


flashback table 支持同时操作多个表，表名中间用逗号分隔即可，如果你执行一条flashback table 命令同时指定了多个表，要记住单个flashback table 是在同一个事务中，
因此这些表的恢复操作要么成功，要么都失败。






实验：
SQL> select count(*) from king_test;


  COUNT(*)
----------
    100000
    
SQL> set numwidth 20;
SQL> select current_scn from v$database;


         CURRENT_SCN
--------------------
      14098165887959
    
SQL> delete from king_test;


已删除100000行。


SQL> commit;


提交完成。


SQL> select count(*) from king_test;


            COUNT(*)
--------------------
                   0
                   
开始根据scn号进行闪回：
记住，要先对表开启row movement


SQL> alter table king_test enable row movement;


表已更改。


执行闪回：
flashback table king_test  to scn 14098165887959;


SQL> flashback table king_test  to scn 14098165887959;


闪回完成。


SQL> select count(*) from king_test;                       -----可以看到，刚才被delete的数据被闪回回来了。


            COUNT(*)
--------------------
              100000
              






关于flashback table的小结：


flashback table是通过使用undo实现的，这也就意味着，对于ddl操作，flashback table则无法使用。
说不上来的感觉，比如对于一个大并发的生产库，总觉得闪回技术用武之地很少，总不能时常记着scn啊，时间点吧。
但对于一个相对静态的库，就另说了。






附加一点：sys 不支持flashback table。
实验验证：


SQL> show user
USER 为 "SYS"
SQL> create table test(id int,name varchar2(20));


表已创建。


SQL> insert into test values(1,'king');


已创建 1 行。


SQL> commit;


提交完成。


SQL> select current_scn from v$database;


         CURRENT_SCN
--------------------
      14098171604111
      
      


此时：delete 该表数据
SQL> delete from test;


已删除 1 行。


SQL> commit;


提交完成。






进行flashback table：


SQL> alter table test enable row movement;


表已更改。


flashback table test to scn 14098171604111;                   ----可以看到，oracle自己都提醒你sys不支持闪回


SQL> flashback table test to scn 14098171604111;
flashback table test to scn 14098171604111
                *
第 1 行出现错误:
ORA-08185: 用户 SYS 不支持闪回






下面，依然使用sys用户创建表，但是表的默认表空间改为users


进一步进行验证：
create table test_1 (id int,name varchar2(20)) tablespace users;


SQL> create table test_1 (id int,name varchar2(20)) tablespace users;


表已创建。      


SQL> insert into test_1 values(1,'king');


已创建 1 行。


SQL> commit;


提交完成。


SQL> select current_scn from v$database;


         CURRENT_SCN
--------------------
      14098172744145
      
      


SQL> delete from test_1;


已删除 1 行。


SQL> commit;


提交完成。




进行闪回操作：


SQL> alter table test_1 enable row movement;


表已更改。


SQL> flashback table test_1 to scn 14098172744145;                    -------------发现依然不可以
flashback table test_1 to scn 14098172744145
                *
第 1 行出现错误:
ORA-08185: 用户 SYS 不支持闪回


-----------------------
测试system用户：
SQL> show user
USER 为 "SYSTEM"


SQL>
SQL> create table test_3(id int,name varchar2(20));


表已创建。


SQL> insert into test_3 values(1,'king');


已创建 1 行。


SQL> commit;


提交完成。


SQL> select current_scn from v$database;


         CURRENT_SCN
--------------------
      14098295148887


SQL> delete from test_3;


已删除 1 行。


SQL> commit;


提交完成。


SQL> alter table test_3 enable row movement;


表已更改。


SQL> flashback table test_3 to scn  14098295148887;                   -----------说明flashback table 对system可用


闪回完成。


SQL> select * from test_3;


                  ID NAME
-------------------- --------------------
                   1 king


SQL>






----------------------------------------------
继续验证：使用king用户建表，但是表的默认表空间改为system


SQL> show user
USER 为 "KING"
SQL> create table test_2 (id int,name varchar2(20)) tablespace system;


表已创建。


SQL> insert into test_2 values(1,'king');


已创建 1 行。


SQL> commit;


提交完成。


SQL> select current_scn from v$database;


         CURRENT_SCN
--------------------
      14098293963925


SQL> delete from test_2;


已删除 1 行。


SQL> commit;


提交完成。


SQL>




进行闪回：


SQL> alter table test_2 enable row movement;


SQL> flashback table test_2 to scn 14098293963925;   ------实验出真知啊，flashback table可用


闪回完成。


SQL> select * from test_2;


                  ID NAME
-------------------- --------------------
                   1 king












得到结论：只有sys用户不支持flashback table，其他诸如system用户都支持flashback table（原因应该是硬性规定了，至于深层次的原因我就不得而知了。）


----------------------
疑问：
对于flashback database而言，使用的是闪回日志和归档日志
对于flashback table，使用的是undo信息（我现在也不确认到底是使用了undo还是闪回日志，理论上感觉都可以说的通）      
对于flashback drop，因为system表空间下的被drop的对象不放入回收站中，所以无法支持flashback drop


---------------------------------------------------------------------------------------------------------------------------------




3、oracle flashback drop


oracle flashback drop是从oracle 10g开始出现，用户恢复用户误删除的对象（包括表和索引等），这个技术依赖于tablespace recyclebin技术。
这个功能和数据库闪回其实没啥关系，和flashback参数没有关系，表也不要求row movement


比如我 alter database flashback off;
依然可以使用flashback drop的功能，但回收站一定要开启：


SQL> show parameter recyclebin


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
SQL>






关于flashback drop的实验：


SQL> show user
USER 为 "KING"
SQL> drop table test_2;


表已删除。


SQL> flashback table test_2 to before drop;
flashback table test_2 to before drop
*
第 1 行出现错误:
ORA-38305: 对象不在回收站中




====之所以会出现问题，是因为我当时test_2这张表是建在system表空间中的


重新建表：
SQL> create table test_4 (id int,name varchar2(20));


表已创建。


SQL> insert into test_4 values(1,'king');


已创建 1 行。


SQL> commit;


提交完成。


SQL> drop table test_4;


表已删除。


SQL> desc test_4;
ERROR:
ORA-04043: 对象 test_4 不存在




SQL> flashback table test_4 to before drop ;                  ------------闪回成功。


闪回完成。


SQL> desc test_4;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(20)


SQL>




----------------------------


关于flashback drop另外一种情况：就是表test_4被删除后，用户又重新建立一张test_4，闪回时候需要重新命名


flashback table test_4 to before drop rename to test_5;










关于flashback drop的小结：


flashback drop 只能用于非system表空间和本地管理的表空间;
               对象的约束不会被恢复，指向该对象的外键约束需要重建;
               对象能否恢复成功，取决于对象空间是否被覆盖（也就是回收站中的数据被覆盖）;
               当删除表时，信赖于该表的物化视图也会同时被删除，但是由于物化视图并不会被放入回站;
               因此当我们执行flashback table to before drop时，也不能恢复依赖其的物化视图，需要手工重建;
               recyclebin中的对象，只支持查询。
               




-----------------------------------------------------------------------------------------------------


4、oracle flashback query


oracle闪回查询是指针对特定的表来查询特定的时间段内的数据变化情况来确定是否将表闪回到某一个特定的时刻以保证数据无讹误存在。


这个特性大大的减少了采用还原点恢复所需的工作量以及数据库脱机的时间。


闪回查询通常分为：
a、flashback query 闪回查询


b、flashback version query 闪回版本查询


c、flashback transaction query 闪回事务查询




a、flashback query


通常用于检索一条记录的所有版本，倒退单独的事务或者倒退从指定时间点以来对特定表的所有变化。
flashback query的所有形式取决于undo 表空间


两种形式：scn和timestamp


select * from test as of timestamp to_timestamp('2014-10-01 15:20:00','yyyy-mm-dd hh24:mi:ss');


select * from test as scn 9876837848;






b、flashback version query 闪回版本查询   ps:感觉是是比flashback query多个查询范围的功能


闪回版本查询指的是oracle可以针对特定的对象来查询某一特定时间段内该对象变化的所有情况，可以对此跟踪该对象的变更情况。
也可以根据特定的需要来将该对象修正到特定的时刻。
闪回版本查询同闪回查询和闪回表一样，都是使用了undo段的数据，即数据变更的多次镜像，当undo段的数据由于空间压力被清除后，闪回查询无法使用。


闪回版本查询的语法：使用versions between


select * from test versions between scn 10000 and 20000;


select * from test versions between timestamp to_timestamp('2014-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_timestamp('2014-10-02 00:00:00','yyyy-mm-dd hh24:mi:ss');






c、flashback transaction query 闪回事务查询


闪回事务查询是对闪回版本查询的扩展。从某种程度上来说，闪回版本查询通常用于更细粒度的查询，如针对特定的记录。
而闪回事务查询则是针对某一事务进行闪回，是基于事务级别的。
闪回事务查询通过查询事务flashback_transaction_query来获得某个或多个特定事务信息，
同时可以根据该视图中提供的undo_sql列中的语句来反转事务，从而保证数据完整性。
查询该视图需要具有select any transaction权限




做个实验，加深认识：


SQL> select * from test_4;


                  ID NAME
-------------------- --------------------
                   1 king
                   
SQL> update test_4 set name='wang' where id=1;              --------第一次更新记录


已更新 1 行。          


SQL> commit;                                                ---------提交事务 1


提交完成。




SQL> update test_4 set name='killvoon' where id=1;          ---------第二次更新记录


已更新 1 行。


SQL> commit;                                                ----------提交事务2


提交完成。




使用闪回版本查询


select *
  from test_4 versions between timestamp to_timestamp('2014-10-16 18:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2014-10-16 19:00:00','yyyy-mm-dd hh24:mi:ss')
 where id = 1;




SQL> select *
  2    from test_4 versions between timestamp to_timestamp('2014-10-16 18:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2014-10-16 19:00:00',
'yyyy-mm-dd hh24:mi:ss')
  3   where id = 1;


                  ID NAME
-------------------- --------------------
                   1 killvoon            ----第二次修改的值，也是最新的值
                   1 wang                ----第一次修改的值
                   1 king                ----最开始的值


SQL>




select id,name ,versions_xid,versions_operation from test_4
versions between timestamp to_timestamp('2014-10-16 18:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2014-10-16 19:00:00',
'yyyy-mm-dd hh24:mi:ss')
where id=1;




SQL> select id,name ,versions_xid,versions_operation from test_4
  2  versions between timestamp to_timestamp('2014-10-16 18:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2014-10-16 19:00:00',
  3  'yyyy-mm-dd hh24:mi:ss')
  4  where id=1;


                  ID NAME                 VERSIONS_XID     V
-------------------- -------------------- ---------------- -
                   1 killvoon             0300070068F90000 U
                   1 wang                 0300140064F90000 U
                   1 king








select operation,undo_sql from flashback_transaction_query where xid=hextoraw('0300140064F90000');  ---根据事务号获得第一个反转该事务的dml语句




SQL> select operation,undo_sql from flashback_transaction_query where xid=hextoraw('0300140064F90000');


OPERATION
--------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
UPDATE
update "KING"."TEST_4" set "NAME" = 'king' where ROWID = 'AABQBJAAEAAAM4MAAA';        -----反转语句


BEGIN






select operation,undo_sql from flashback_transaction_query where xid=hextoraw('0300070068F90000');  ---根据事务号获得第二个反转该事务的dml语句


SQL> select operation,undo_sql from flashback_transaction_query where xid=hextoraw('0300070068F90000');


OPERATION
--------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
UPDATE
update "KING"."TEST_4" set "NAME" = 'wang' where ROWID = 'AABQBJAAEAAAM4MAAA';


BEGIN






--------------------------------
附：


versions_xid                       ----记录指定版本的事务的唯一标识符


versions_startscn                  ----记录的起始scn号


versions_endscn                    ----记录的终止scn号


versions_operation                 ----记录的操作类型（dml操作：I表示插入，U表示更新，D表示删除）


versions_starttime                 ----记录被修改的起始时间


versions_endtime                   ----记录被修改的终止时间








关于flashback query的小结：


闪回查询包括了版本，事务闪回着几块内容，使用不同的语法，通过查询undo信息来获得数据的过去种种状态。


闪回查询只能笼统的查看过去所有数据的变化情况  （as of scn / timestamp）
而版本查询相关于多个where，可以加上时间段的限制（versions between scn/timestamp）
事务查询呢，就是在版本查询的基础上，获得过去事务的事务id（versions_xid），利用该事务id在视图flashback_transaction_query 查看undo_sql，实现事务的反转。








----------------------------------------------------------------------------------------------------------------------------------------
总结：
大体上将oracle flashback 闪回技术整理完了，之前对闪回家族各个成员都不甚了解，而今也较为清楚，剩下的事情就是要活学活用了。
以期在日后的工作中可以根据不同情况选择合适的技术手段来处理问题。