【oracle学习】12.闪回

以下的测试均使用控制台登录Oracle。

(1)闪回(Flashback)
在Oracle的操作过程中，会不可避免地出现操作失误或者用户失误，例如不小心删除了一个表等，
这些失误可能会造成重要数据的丢失，最终导致Oracle数据库停止。

在传统意义上，当发生数据丢失、数据错误等问题是，解决的主要办法是数据的导入导出、备份恢复
技术，这些方法都需要在发生错误前，有一个正确的备份才能进行恢复。

为了减少这方面的损失，Oracle提供了闪回技术。有了闪回技术，就可以实现数据的快速恢复，而且
不需要数据备份。

(2)闪回的类型
①闪回表:将表会退到过去的一个时间上
闪回表，实际上是将表中的数据快速恢复到过去的一个是焦点或者系统改变号SCN上。
实现表的闪回，需要使用到与撤销表空间相关的undo信息，通过show parameter undo命令可以了解这些
信息。

首先我们看一下目前的系统改变号

SQL> --SCN系统改变号(时间)
SQL> select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss*ff') 时间,
     timestamp_to_scn(systimestamp) SCN from dual;

时间                                                              SCN
---------------------------------------------------------- ----------
2016-09-23 10:06:56*686000                                    1377166

Oracle10g中的自动撤销管理（AUM）
在Oracle10g中对于回滚段的管理可以通过配置参数而实现自动管理。为启用撤销空间的自动管理，
首先必须在init.ora中或者SPFILE文件中指定自动撤销模式。其次需要创建一个专用的表空间来存放撤销信息，
这保证用户不会在SYSTEM表空间中保存撤销信息。此外还需要为撤销选择一个保留时间。
 
如果需要实现AUM，需要配置以下3个参数：
 
UNDO_MAMAGEMENT
UNDO_TABLESPACE
UNDO_RETENTION
 
查看初始化参数的设置：

SQL> show parameter undo;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
undo_management                      string                 AUTO
undo_retention                       integer                900
undo_tablespace                      string                 UNDO

如果将初始化参数UNDO_MANAGEMENT设置为AUTO，则Oracle10g将启用AUM。
可以在初始化参数UNDO_RETENTION中设置撤销保留时间的大小：
UNDO_RETENTION＝1800         设置保留时间为30分钟（1800秒）
UNDO_RETENTION参数默认设置为900秒。

UNDO_RETENTION的值应该设置为多少才合理？
不存在理想的UNDO_RETENTION的时间间隔。保留时间间隔依赖于估计最长的事务可能运行的时间长度。根据数据库中
最长事务长度的信息，可以给UNDO_RETENTION分配一个大致的时间。

注意，想要使用闪回功能，要给目前的数据库用户授予权限：
SQL> grant flashback any table to jack;

授权成功。

我们创建一张测试闪回的表，并且向其中添加一些数据

SQL> create table flashback_table
  2  (fid number,fname varchar2(20));

表已创建。

SQL> insert into flashback_table values(1,'Tom');

已创建 1 行。

SQL> insert into flashback_table values(2,'Mary');

已创建 1 行。

SQL> insert into flashback_table values(3,'Mike');

已创建 1 行。

SQL> commit;

提交完成。

然后我们看一下目前的SCN号:

SQL> select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss*ff') 时间,
          timestamp_to_scn(systimestamp) SCN from dual;

时间                                                              SCN
---------------------------------------------------------- ----------
2016-09-23 10:11:01*081000                                    1377271

然后看一下我们的表现在的数据:
SQL> select * from flashback_table;

       FID FNAME
---------- ----------------------------------------
         1 Tom
         2 Mary
         3 Mike

此时我们将Mary删除:

SQL> delete from flashback_table where fid=2;

已删除 1 行。

SQL> commit;

提交完成。

之后我们的表就剩下:

SQL> select * from flashback_table;

       FID FNAME
---------- ----------------------------------------
         1 Tom
         3 Mike

我们想把刚刚的删除给回退了，但是我们已经commit了，不能使用rollback，
所以要使用闪回，下面是闪回的语法：

flashback table[schema.]<table_name>
to
{[before drop [rename to table]]
[SCN|TIMESTAMP]expr
[enable|disable]triggers}

其中
schema:模式名，一般为用户名。
to timestamp:系统邮戳，包含年、月、日、时、分、秒。
to scn:系统更改号。
enable triggers:表示触发器恢复以后为enable状态，默认为disable状态。
to before drop:表示回复到删除之前。
rename to table:表示更换表明。

记得我们在删除Mary数据之前，SCN号是1377271，那么我们可以恢复到这个SCN
号所在的时间点。
在此之前我们要开启表的移动功能:

SQL> alter table flashback_table enable row movement;

表已更改。

语句:flashback table flashback_table to scn 1377271;
执行之后我们在此查看表，发现数据回来了:

SQL> flashback table flashback_table to scn 1377271;

闪回完成

SQL> select * from flashback_table;

       FID FNAME
---------- ----------------------------------------
         1 Tom
         2 Mary
         3 Mike

闪回表：需要考虑的事情
A.flashback table命令作为单一的事务执行，会得到一个单一的DML锁。
B.表的统计数据不会被闪回
C.当前的索引和从属的对象会被维持
D.闪回表操作:
  系统表不能被回退
  不能跨越DDL操作
  会被写入警告日志
  产生撤销和重做的数据


②闪回删除:Oracle回收站
闪回删除，实际上从系统的回收站中将已删除的对象，恢复到删除
之前的状态。
系统的回收站只对普通用户有作用，管理员是看不到的。

我们先来看看我们都有什么表:

SQL> select * from tab;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
FLASHBACK_TABLE                                              TABLE
TEST2                                                                       TABLE
TESTSAVEPOINT                                                    TABLE
EMP20                                                                      TABLE
EMPINCOME                                                           TABLE
MYPERSON                                                             TABLE
EMP                                                                           TABLE
DEPT                                                                         TABLE
PROCEDURE_TEST                                              TABLE

我们删除一个EMP20表

SQL> drop table EMP20;

已删除

然后我们看一下我们的回收站:

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP20            BIN$VRtNpSR4Sey7glocVofzZQ==$0 TABLE        2016-09-23:10:46:20

现在我们要想将回收站中的数据恢复回来，就需要使用闪回了。
刚刚我们在看回收站的数据的时候，有一个RECYCLEBIN NAME字段中
存储了很长的语句，这是什么呢？
说一下回收站中对象的命名规则：
为了确保添加到回收站中的对象名称都是唯一的，系统会对保存到回收站中
的对象进行重命名，重命名的格式如下：
BIN$globalUID$version
其中：BIN表示RECYCLEBIN；globalUID是一个全局唯一的、24个字非长的对象，
该标识与原对象名没有任何关系；version指数据库分配的版本号。

其实这个RECYCLEBIN NAME就是回收站给该表的重命名，我们删除这张表后，
其实在表空间中此表还存在，只是以RECYCLEBIN NAME的名称存在，所以我们
查询我们删除的表是不存在的：

SQL> select * from EMP20;
select * from EMP20
              *
第 1 行出现错误:
ORA-00942: 表或视图不存在

但是查询回收站的RECYCLEBIN NAME的表是存在的：

SQL> select * from "BIN$VRtNpSR4Sey7glocVofzZQ==$0";

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      1110 张三                 主管                     1110 12-3月 -14      6.82E+003          0      20
      1111 李四                 销售                     1116 03-11月-15      4.62E+003   5.0E+002      30
      1112 王五                 销售                     1116 25-4月 -12      5.72E+003   8.0E+002      30
      1113 赵二                 后勤                     1110 30-5月 -11     4.235E+003          0      40
      1114 李磊磊               会计                     1110 22-12月-15      3.19E+003          0      50
      1115 张少丽               销售                     1110 11-3月 -16      3.52E+003   1.4E+003      30
      1116 林建国               主管                     1116 22-1月 -16      7.37E+003          0      20
      1117 马富邦               后勤                     1116 22-7月 -13      3.52E+003          0      40
      1118 沈倩                 会计                     1116 06-5月 -10      2.75E+003          0      50

接下来我们执行闪回删除：

SQL> flashback table EMP20 to before drop;

闪回完成

此时表已经恢复，并且回收站中也是没有数据的。
注意，如果我们清空回收站，该表就不可恢复了。

如果表删除之后，有新表和删除之前的表重名，我们就可以在检出的时候
为之前的老表设置新名：

flashback table testtab to before drop rename to testtab_old;

③闪回版本查询:所有历史记录
闪回版本查询，提供了一个审计行改变的查询功能，通过它可以查到所有已经
提交的行记录。其语法格式如下：

select column_name[,column_name,...]
from table_name
version between [SCN|TIMESTAMP][expr|MINVALUE]
          and [expr|MAXVALUE] as of [SCN|TIMESTAMP] expr;

其中：column_name列名；table_name表名；between...and时间段；
SCN系统改变号；TIMESTAMP时间戳；AS OF表示回复单个版本；MAXVALUE
最大值；MINVALUE最小值；expr指定一个值或者表达式。

我们来查看一下我们之前闪回测试表的版本记录:

select fid,fname,versions_operation 操作,versions_starttime 起始时间,
versions_endtime 结束时间,versions_xid 事务号
from flashback_table versions between
TIMESTAMP MINVALUE and MAXVALUE order by 1,4;

执行上述语句之前，我们先进行以下操作：

SQL> insert into flashback_table values(4,'Jack');

已创建 1 行。

SQL> insert into flashback_table values(2,'Mary');

已创建 1 行。

SQL> update flashback_table set fname='Jean' where fid=2;

已更新 1 行。

SQL> commit;

提交完成。

然后执行刚刚的版本查询语句，得到结果:

可以看到我们刚刚的操作和时间等信息。

④闪回事务查询:通过select语句得到一个undo_sql
闪回事务查询实际上是闪回版本查询的一个扩充，通过它可以审计某个事务甚至撤销
一个已经提交的事务。

实现闪回事务查询，需要先了解flashback_transaction_query视图，从该视图中可以获取
事务的历史操作记录以及撤销语句(UNDO_SQL)。

SQL> desc flashback_transaction_query;

 名称                                                                                是否为空? 类型
 -------------------------------------------------------------- ----------------------------------------------
 XID                                                                                          RAW(8)
 START_SCN                                                                                    NUMBER
 START_TIMESTAMP                                                                              DATE
 COMMIT_SCN                                                                                   NUMBER
 COMMIT_TIMESTAMP                                                                             DATE
 LOGON_USER                                                                                   VARCHAR2(30)
 UNDO_CHANGE#                                                                                 NUMBER
 OPERATION                                                                                    VARCHAR2(32)
 TABLE_NAME                                                                                   VARCHAR2(256)
 TABLE_OWNER                                                                                  VARCHAR2(32)
 ROW_ID                                                                                       VARCHAR2(19)
 UNDO_SQL                                                                                     VARCHAR2(4000)

使用闪回事务查询，可以了解某个表的历史操作记录，这个操作记录对应一个撤销SQL
语句，如果想要撤销这个操作，就可以执行这个SQL语句。

注意，要查询flashback_transaction_query视图的信息，需要有select any transaction的权限。

SQL> grant select any transaction to jack;

授权成功

⑤闪回数据库
⑥闪回归档日志


(3)使用闪回的场景
①错误的删除了记录
②错误的删除了表
③查询历史记录

④撤销一个已经提交的事务

转载请注明出处：http://blog.csdn.net/acmman/article/details/52637602