oracle闪回版本和闪回事务查询 详解

--- 说明闪回数据库

--- 使用闪回表将表内容还原到过去的特定时间点

--- 从删除表中进行恢复

--- 使用闪回查询查看截止到任一时间点的数据库内容

--- 使用闪回版本查询查看某一行在一段时间内的各个版本

--- 使用闪回事务查询查看事务处理历史记录或行


优点

闪回技术由于只能处理更改数据,所以从根本上改变了恢复技术。使用这个技术时,从错误中恢复花费的时间等于制造错误所花费的时间。当闪回技术使用时,它与介质恢复相比,在易用性、可用性和还原时间方面有明显的优势。


闪回数据库使用闪回日志执行闪回。闪回删除使用回收站。其他所有功能都使用还原数据。

oracle闪回版本和闪回事务查询  详解


闪回时间浏览

闪回技术提供的功能可用于查询方案对象的过去版本、查询历史记录数据以及执行更改分析。每个事务处理在逻辑上都会生成新版本数据库。使用闪回技术,可通过浏览这些版本来查找错误以及原因。

· 闪回查询:查询特定时间点的所有数据。

· 闪回版本查询:查看两个时间之间行的所有版本已经更改了行的事务处理。

· 闪回事务处理查询:查看事务处理做的所有更改。

使用闪回查询功能时,可以对自特定时间起的数据库执行查询。通过使用select语句的 as of 子句,可指定要查看其数据的时间戳。这有助于分析数据差异。


实验一:闪回查询

实验一:闪回查询:as of timestamp

SYS@ORCL>conn tyger/tyger
Connected.
TYGER@ORCL>create table fb_query as select * from scott.dept;


Table created.


TYGER@ORCL>select * from fb_query;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


TYGER@ORCL>set time on;

09:51:36 TYGER@ORCL>delete fb_query where deptno=10;


1 row deleted.


09:51:53 TYGER@ORCL>commit;


Commit complete.


09:51:57 TYGER@ORCL>select * from fb_query;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


09:52:06 TYGER@ORCL>select * from fb_query as of timestamp sysdate-1/1440;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


实验二:闪回查询应用

10:25:04 TYGER@ORCL>drop table fb_tyger purge;


Table dropped.


10:25:10 TYGER@ORCL>create table fb_tyger as select * from scott.dept;


Table created.


10:25:33 TYGER@ORCL>select * from fb_tyger;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


10:25:44 TYGER@ORCL>select sysdate from dual;


SYSDATE
---------
14-MAR-14


10:26:02 TYGER@ORCL>alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss‘;


Session altered.


10:26:30 TYGER@ORCL>select sysdate from dual;


SYSDATE
-------------------
2014-03-14 10:26:38


10:26:38 TYGER@ORCL>update fb_tyger set dname=‘‘;


4 rows updated.


10:26:51 TYGER@ORCL>commit;


Commit complete.


10:26:54 TYGER@ORCL>select * from fb_tyger;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10                NEW YORK
        20                DALLAS
        30                CHICAGO
        40                BOSTON


10:27:12 TYGER@ORCL>select * from fb_tyger as of timestamp to_timestamp(‘2014-03-14 10:26:38‘,‘yyyy-mm-dd hh24:mi:ss‘);


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

此处遇到错误:

ERROR at line 1:
ORA-01466: unable to read data - table definition has changed       

参考文档: http://blog.csdn.net/wanghui5767260/article/details/21227101

10:29:21 TYGER@ORCL>select * from fb_tyger as of timestamp sysdate-3/1440;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


10:29:35 TYGER@ORCL>select * from fb_tyger;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10                NEW YORK
        20                DALLAS
        30                CHICAGO
        40                BOSTON

10:46:22 TYGER@ORCL>set time off

TYGER@ORCL>update fb_tyger t 
  2  set dname =
  3     (select dname from fb_tyger as of timestamp
  4             to_timestamp(‘2014-03-14 10:26:38‘,‘yyyy-mm-dd hh24:mi:ss‘)
  5     where t.deptno=fb_tyger.deptno);


4 rows updated.


TYGER@ORCL>commit;


Commit complete.


TYGER@ORCL>select * from fb_tyger;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


实验三:闪回查询 as of scn

TYGER@ORCL>conn / as sysdba
Connected.
SYS@ORCL>grant execute on dbms_flashback to tyger;


Grant succeeded.

TYGER@ORCL>select dbms_flashback.get_system_change_number from dual;


GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1107246


TYGER@ORCL>select * from fb_tyger;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


TYGER@ORCL>delete fb_tyger where deptno<=30;


3 rows deleted.


TYGER@ORCL>commit;


Commit complete.


TYGER@ORCL>select * from fb_tyger;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON


TYGER@ORCL>select * from fb_tyger as of scn 1107246;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

实验四:利用PL/SQL包dbms_flashback 

语法:

·  会话启用闪回指定时间:

DBMS_FLASHBACK.ENABLE_AT_TIME(query_time  IN  TIMESTAMP);

·  会话启用闪回指定SCN:

DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(query_scn  IN  NUMBER);

·  关闭闪回:

DBMS_FLASHBACK.DISABLE;


TYGER@ORCL>conn / as sysdba
Connected.
SYS@ORCL>grant execute on dbms_flashback to tyger;


Grant succeeded.

SYS@ORCL>conn tyger/tyger
Connected.
TYGER@ORCL>
TYGER@ORCL>
TYGER@ORCL>
TYGER@ORCL>create table fb_query1 as select * from scott.dept;


Table created.


TYGER@ORCL>create table fb_query2 as select * from scott.dept;


Table created.


TYGER@ORCL>commit;


Commit complete.


TYGER@ORCL>select * from fb_query1;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


TYGER@ORCL>select * from fb_query2;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


TYGER@ORCL>set time on;
11:03:38 TYGER@ORCL>update fb_query1 set loc=‘‘;


4 rows updated.


11:03:52 TYGER@ORCL>commit;


Commit complete.


11:03:54 TYGER@ORCL>update fb_query2 set dname=‘‘;


4 rows updated.


11:04:14 TYGER@ORCL>commit;


Commit complete.


11:04:15 TYGER@ORCL>
11:04:15 TYGER@ORCL>select * from fb_query1;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS


11:04:23 TYGER@ORCL>select * from fb_query2;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10                NEW YORK
        20                DALLAS
        30                CHICAGO
        40                BOSTON

// 闪回定位到5分钟前,此时若访问sysdate等时间函数,那么返回的是当前值而非5分钟之前。

11:04:30 TYGER@ORCL>exec dbms_flashback.enable_at_time(sysdate-5/1440);


PL/SQL procedure successfully completed.


11:05:09 TYGER@ORCL>select * from fb_query1;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


11:05:29 TYGER@ORCL>select * from fb_query2;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

//处于闪回会话模式时,不允许执行DML 、 DDL 操作

11:05:45 TYGER@ORCL>update fb_query1 set dname=‘‘;
update fb_query1 set dname=‘‘
       *
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode




11:05:59 TYGER@ORCL>exec dbms_flashback.disable;


PL/SQL procedure successfully completed.


11:06:18 TYGER@ORCL>select * from fb_query1;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS


11:06:30 TYGER@ORCL>select * from fb_query2;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10                NEW YORK
        20                DALLAS
        30                CHICAGO
        40                BOSTON


11:06:37 TYGER@ORCL>update fb_query1 set dname=‘‘ where deptno=10;


1 row updated.


11:07:10 TYGER@ORCL>select * from fb_query1;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10
        20 RESEARCH
        30 SALES
        40 OPERATIONS


// sys 用户不允许使用dbms_flashback 包
11:07:20 TYGER@ORCL>conn / as sysdba
Connected.
11:07:35 SYS@ORCL>set time off
SYS@ORCL>exec dbms_flashback.enable_at_time(sysdate-5/1440);
BEGIN dbms_flashback.enable_at_time(sysdate-5/1440); END;


*
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS
ORA-06512: at "SYS.DBMS_FLASHBACK", line 3
ORA-06512: at line 1


实验二:闪回版本

---通过闪回版本可审计表行,检索影响行的事务处理的有关信息。然后可使用返回的事务处理标识符来执行事务处理挖掘(通过使用LogMiner)或执行闪回版本查询。

所谓版本(version)指的是每次事务所引起的数据行的变化情况,每次变化就是一个版本,oracle提供了闪回版本查询,从而可以让让我们很清楚地看到数据行的整个变化过程,这里的变化都是已经提交了的事务引起的变化,没有提交的事务引起的变化不会显示,闪回版本查询利用的是undo表空间里记录的undo数据。

使用伪列  获取一段时间内的版本

伪列:versions_starttime、versions_endtime、versions_startscn、versions_endscn、versions_xid、versions_operation、

versions_startscn   versions_starttime

操作时的SCN和时间  如果为空  表示该行在查询范围之外创建

versions_endscn   versions_endtime

失效时的SCN和时间  如果为空  表示该行被删除或在查询范围内无改动

versions_xid

事务ID

versions_operation

该行被执行的操作 I(insert) D(delete)   U(update)

minvalue  maxvalue

版本的最大时间值和最小时间值

注意事项:

versions子句不能用于查询以下特殊表:

·  外部表

·  临时表

·  固定表(x$开头的表,也就是oracle内部的表,用于数据字典的基表),下面sql语句可查询相关表

select  *  from  v$fixed_table

不能使用versions子句查询视图。但是,在视图定义中可使用versions子句。

·  versions子句不能跨DDL命令使用

·  过滤掉段收缩操作过的行

实验:闪回版本查询

TYGER@ORCL>create table tyger as select ename,job,sal from scott.emp where rownum<5;


Table created.


TYGER@ORCL>select * from tyger;


ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK            857
ALLEN      SALESMAN        1656
WARD       SALESMAN        1306
JONES      MANAGER         3031


TYGER@ORCL>alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss‘;


Session altered.


TYGER@ORCL>select sysdate from dual;


SYSDATE
-------------------
2014-03-14 14:41:46


TYGER@ORCL>update tyger set sal=sal+100 where ename=‘SMITH‘;


1 row updated.


TYGER@ORCL>commit;


Commit complete.


TYGER@ORCL>update tyger set sal=sal+100 where ename=‘SMITH‘;


1 row updated.


TYGER@ORCL>commit;


Commit complete.


TYGER@ORCL>update tyger set sal=sal+100 where ename=‘SMITH‘;


1 row updated.


TYGER@ORCL>commit;


Commit complete.


TYGER@ORCL>select * from tyger;


ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK           1157
ALLEN      SALESMAN        1656
WARD       SALESMAN        1306
JONES      MANAGER         3031

TYGER@ORCL>col starttime for a30
TYGER@ORCL>l
  1  select to_char(versions_starttime,‘yyyy-mm-dd hh24:mi:ss‘) as starttime,
  2  versions_xid,ename,job,sal
  3  from tyger versions between timestamp to_date(‘2014-03-14 14:41:46‘,‘yyyy-mm-dd hh24:mi:ss‘)
  4* and sysdate where ename=‘SMITH‘
TYGER@ORCL>/


STARTTIME                     VERSIONS_XID     ENAME      JOB              SAL
------------------------------      ----------------              ----------       ---------         ----------
2014-03-14 14:42:32  080016000F020000  SMITH      CLERK           1157
2014-03-14 14:42:26  01002C00F1010000 SMITH      CLERK           1057
2014-03-14 14:42:17  0600180025020000  SMITH      CLERK            957
                                                                             SMITH      CLERK            857

或者

TYGER@ORCL>col versions_starttime for a22
TYGER@ORCL>col versions_endtime for a22
TYGER@ORCL>l
  1  select versions_starttime,versions_endtime,versions_xid,versions_operation,ename
  2* from tyger versions between timestamp to_timestamp(‘2014-03-14 14:41:46‘,‘yyyy-mm-dd hh24:mi:ss‘) and maxvalue order by 1
TYGER@ORCL>/


VERSIONS_STARTTIME     VERSIONS_ENDTIME       VERSIONS_XID     V ENAME
---------------------- ---------------------- ---------------- - ----------
14-MAR-14 02.42.17 PM  14-MAR-14 02.42.26 PM  0600180025020000 U SMITH
14-MAR-14 02.42.26 PM  14-MAR-14 02.42.32 PM  01002C00F1010000 U SMITH
14-MAR-14 02.42.32 PM                         080016000F020000 U SMITH
                                                                 WARD
                                                                 ALLEN
                                                                 JONES
                       14-MAR-14 02.42.17 PM                     SMITH


7 rows selected.


实验三:闪回事务查询

flashback 的事务查询时通过查询flashback_transaction_query视图来实现的

通过查询该视图能够获得一些事务执行时的信息,甚至包括UNDO语句。

每个事务都有事务ID以及SCN关联关系

闪回事务处理查询是一中诊断工具,可以用来查看在事务处理级对数据库所做的更改。这样,可诊断数据库中的问题并对事务处理执行分析和审计。

可以使用FLASHBACK_TRANSACTION_QUERY视图来确定所有必要的SQL语句,这些语句可用来还原特定事务处理或特定时间段内所做的修改。

·  在数据库中,DDL操作只是对数据字典所做的一系列空间管理操作和更改。通过执行DDL对事务处理执行闪回事务处理查询时,会显示对数据字典所做的更改。

·  当闪回事务处理查询涉及到已从数据库中删除的表时,就不会反映表名称。而是使用对象编号。

·  如果闪回了执行事务处理的用户,则该事务处理的闪回事务处理查询只显示相应的用户ID,而不是用户名。

TYGER@ORCL>conn / as sysdba
Connected.
SYS@ORCL>
SYS@ORCL>
SYS@ORCL>select undo_sql from flashback_transaction_query where xid=‘080016000F020000‘;


UNDO_SQL
--------------------------------------------------------------------------------
update "TYGER"."TYGER" set "SAL" = ‘1057‘ where ROWID = ‘AAANQ3AAGAAAAYMAAA‘;




SYS@ORCL>grant select any transaction to tyger;


Grant succeeded.


SYS@ORCL>conn tyger/tyger
Connected.
TYGER@ORCL>select undo_sql from flashback_transaction_query where xid=‘080016000F020000‘;


UNDO_SQL
--------------------------------------------------------------------------------
update "TYGER"."TYGER" set "SAL" = ‘1057‘ where ROWID = ‘AAANQ3AAGAAAAYMAAA‘;            //undo语句

oracle闪回版本和闪回事务查询 详解,布布扣,bubuko.com

oracle闪回版本和闪回事务查询 详解

上一篇:数据字典


下一篇:Photoshop将悉尼歌剧院图片调制出霞光效果