闪回版本查询和闪回事务查询实例(flashback version query & flashback transaction query)

 

 

Oracle 的闪回版本查询功能(Flashback Version Query)提供了一个审计行改变的查询功能,它能找到所有已经提交了的行的记录。借助这个特殊的功能,我们可以看到什么时间执行了什么操作。使用该功能,可以很轻松地实现对应用系统进行审计,而没有必要使用细粒度的审计功能或者是使用LOGMNR了。

 

闪回版本查询功能依赖于AUM(Automatic Undo Management),AUM指的是采用撤销表空间记录来增、删、改数据的方法。

 

要用Flashback Version Query实现对数据行改变记录进行查询,主要采用SELECT 语句带flashback_query子语句来实现,Flashback_query子语句的语法格式如下:

 

SELECT <column1>,… FROM <table>

 

 

VERSION BETWEEN [ SCN | TIMESTAMP ] [ <expr> | MAXVALUE] AND <expr> | MINVALUE]

| AS OF [SCN |TIMESTAMP ] <expr>

 

其中各项参数的说明如下。

 

l          AS OF:表示恢复单个版本;

 

l          SCN:系统更改号;

 

l          TIMESTAMP:时间。

SQL> select current_scn from v$database;

 

CURRENT_SCN

-----------

    1067877

SQL> create table scott.emp2 as select * from scott.emp;  --创建一个示例表

 

Table created

SQL> select * from scott.emp2;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7369 SMITH      CLERK      7902 1980-12-17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30

 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30

 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20

 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30

 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10

 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20

 7839 KING       PRESIDENT       1981-11-17    5000.00               10

 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30

 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20

 7900 JAMES      CLERK      7698 1981-12-3      950.00               30

 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20

 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

 

14 rows selected

 

SQL> select current_scn from v$database;

 

CURRENT_SCN

-----------

    1068035

 

SQL> delete from scott.emp2 where empno>7900; --删除两行

 

2 rows deleted

 

其中有几个字段,注意一下

versions_operation: 操作类型

versions_xid: 事务编号

versions_starttime: 开始时间

versions_endtime: 结束时间,如果有值,证明这记录已非当前记录

versions_startscn: 开始SCN号

versions_endscn: 结束SCN号 如果有值,证明这记录已非当前记录

SQL> select empno,ename,versions_operation,versions_xid,versions_starttime,versions_endtime,versions_startscn from scott.emp2

  2  versions between scn 1068035 and maxvalue; --从结果看,我们没有看到前面删除动作的信息,因为还没有提交,这里和FGA不同,FGA不管是否提交,都能记录操作

 

EMPNO ENAME      VERSIONS_OPERATION VERSIONS_XID     VERSIONS_STARTTIME                                                               VERSIONS_ENDTIME                                                                 VERSIONS_STARTSCN

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

 7369 SMITH                                                                                                                                                                                                            

 7499 ALLEN                                                                                                                                                                                                            

 7521 WARD                                                                                                                                                                                                             

 7566 JONES                                                                                                                                                                                                            

 7654 MARTIN                                                                                                                                                                                                           

 7698 BLAKE                                                                                                                                                                                                            

 7782 CLARK                                                                                                                                                                                                            

 7788 SCOTT                                                                                                                                                                                                            

 7839 KING                                                                                                                                                                                                             

 7844 TURNER                                                                                                                                                                                                           

 7876 ADAMS                                                                                                                                                                                                            

 7900 JAMES                                                                                                                                                                                                            

 7902 FORD                                                                                                                                                                                                             

 7934 MILLER                                                                                                                                                                                                           

 

14 rows selected

 

SQL> commit; --提交刚刚的删除操作

 

Commit complete

 

SQL> select empno,ename,versions_operation,versions_xid,versions_starttime,versions_endtime,versions_startscn from scott.emp2

  2  versions between scn 1068035 and maxvalue; --这里能看到刚刚的删除操作

 

EMPNO ENAME      VERSIONS_OPERATION VERSIONS_XID     VERSIONS_STARTTIME                                                               VERSIONS_ENDTIME                                                                 VERSIONS_STARTSCN

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

 7934 MILLER     D                  030011007D020000 06-APR-10 09.58.07 AM                                                                                                                                                       1068207

 7902 FORD       D                  030011007D020000 06-APR-10 09.58.07 AM                                                                                                                                                       1068207

 7369 SMITH                                                                                                                                                                                                            

 7499 ALLEN                                                                                                                                                                                                            

 7521 WARD                                                                                                                                                                                                             

 7566 JONES                                                                                                                                                                                                            

 7654 MARTIN                                                                                                                                                                                                           

 7698 BLAKE                                                                                                                                                                                                            

 7782 CLARK                                                                                                                                                                                                            

 7788 SCOTT                                                                                                                                                                                                            

 7839 KING                                                                                                                                                                                                             

 7844 TURNER                                                                                                                                                                                                           

 7876 ADAMS                                                                                                                                                                                                            

 7900 JAMES                                                                                                                                                                                                            

 7902 FORD                                                                                                                            06-APR-10 09.58.07 AM                                                            

 7934 MILLER                                                                                                                          06-APR-10 09.58.07 AM                                                            

 

16 rows selected

 

SQL> alter table scott.emp2 drop column hiredate; --删除一列,改变了表的结构

 

Table altered

 

SQL> select * from scott.emp2;

 

EMPNO ENAME      JOB         MGR       SAL      COMM DEPTNO

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

 7369 SMITH      CLERK      7902    800.00               20

 7499 ALLEN      SALESMAN   7698   1600.00    300.00     30

 7521 WARD       SALESMAN   7698   1250.00    500.00     30

 7566 JONES      MANAGER    7839   2975.00               20

 7654 MARTIN     SALESMAN   7698   1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839   2850.00               30

 7782 CLARK      MANAGER    7839   2450.00               10

 7788 SCOTT      ANALYST    7566   3000.00               20

 7839 KING       PRESIDENT         5000.00               10

 7844 TURNER     SALESMAN   7698   1500.00      0.00     30

 7876 ADAMS      CLERK      7788   1100.00               20

 7900 JAMES      CLERK      7698    950.00               30

 

12 rows selected

 

SQL> select empno,ename,versions_operation,versions_xid,versions_starttime,versions_endtime,versions_startscn from scott.emp2

  2  versions between scn 1068035 and maxvalue; --如果查询的时间段中,表结构发生了改变,会报错,提示“table definition has changed”

 

select empno,ename,versions_operation,versions_xid,versions_starttime,versions_endtime,versions_startscn from scott.emp2

versions between scn 1068035 and maxvalue

 

ORA-01466: unable to read data - table definition has changed

 

SQL> select current_scn from v$database;

 

CURRENT_SCN

-----------

    1068618 

 

SQL> update scott.emp2 set ename='shitou' where empno=7900; --更新1行

 

1 rows deleted

 

SQL> commit;

 

Commit complete

 

SQL> select empno,ename,versions_operation,versions_xid,versions_starttime,versions_endtime,versions_startscn from scott.emp2

  2  versions between scn 1068618 and maxvalue;

 

EMPNO ENAME      VERSIONS_OPERATION VERSIONS_XID     VERSIONS_STARTTIME                                                               VERSIONS_ENDTIME                                                                 VERSIONS_STARTSCN

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

 7900 shitou     U                  05002A008F020000 06-APR-10 10.04.13 AM                                                                                                                                                       1068682

 7369 SMITH                                                                                                                                                                                                            

 7499 ALLEN                                                                                                                                                                                                            

 7521 WARD                                                                                                                                                                                                             

 7566 JONES                                                                                                                                                                                                            

 7654 MARTIN                                                                                                                                                                                                           

 7698 BLAKE                                                                                                                                                                                                            

 7782 CLARK                                                                                                                                                                                                            

 7788 SCOTT                                                                                                                                                                                                            

 7839 KING                                                                                                                                                                                                             

 7844 TURNER                                                                                                                                                                                                           

 7876 ADAMS                                                                                                                                                                                                            

 7900 JAMES                                                                                                                           06-APR-10 10.04.13 AM                                                            

 

13 rows selected

 

SQL> update scott.emp2 set ename='hehe' where empno=7900; --再次更新该行

 

1 row updated

 

SQL> rollback; --回滚

 

Rollback complete

 

SQL> select empno,ename,versions_operation,versions_xid,versions_starttime,versions_endtime,versions_startscn from scott.emp2

  2  versions between scn 1068618 and maxvalue;  --回滚的操作在这里查询不到,也就是查询不到update scott.emp2 set ename='hehe' where empno=7900; 的相关记录

 

EMPNO ENAME      VERSIONS_OPERATION VERSIONS_XID     VERSIONS_STARTTIME                                                               VERSIONS_ENDTIME                                                                 VERSIONS_STARTSCN

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

 7900 shitou     U                  05002A008F020000 06-APR-10 10.04.13 AM                                                                                                                                                       1068682

 7369 SMITH                                                                                                                                                                                                            

 7499 ALLEN                                                                                                                                                                                                            

 7521 WARD                                                                                                                                                                                                             

 7566 JONES                                                                                                                                                                                                            

 7654 MARTIN                                                                                                                                                                                                           

 7698 BLAKE                                                                                                                                                                                                            

 7782 CLARK                                                                                                                                                                                                            

 7788 SCOTT                                                                                                                                                                                                            

 7839 KING                                                                                                                                                                                                             

 7844 TURNER                                                                                                                                                                                                           

 7876 ADAMS                                                                                                                                                                                                            

 7900 JAMES                                                                                                                           06-APR-10 10.04.13 AM                                                            

 

13 rows selected

 

--下面模拟误更新了两行的一个操作,要利用闪回事务操作来纠正此误更新操作。

SQL> update scott.emp2 set ename='ppppp' where empno between 7700 and 7800; --错误更新两行

 

2 rows updated

 

SQL> commit;

 

Commit complete

 

SQL> select empno,ename,versions_operation,versions_xid,versions_starttime,versions_endtime,versions_startscn from scott.emp2

  2  versions between scn 1068618 and maxvalue; --闪回版本查询,可以看到上面错误更新的事务号05002F0090020000 

--当然,这个例子如果想恢复该误更新之前的值,如果确认就两行,直接查到该两行之前的值,构造sql语句就可以。不过,大部分情况下,我们一个事务不知道影响有多大,手工查询事务执行之前的值,然后手工构造sql语句,工作量太大,所以要借助闪回事务查询,找到对应的undo_sql,构造脚本批量执行undo_sql以达到撤销事务的目的。

 

EMPNO ENAME      VERSIONS_OPERATION VERSIONS_XID     VERSIONS_STARTTIME                                                               VERSIONS_ENDTIME                                                                 VERSIONS_STARTSCN

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

 7782 ppppp      U                  05002F0090020000 06-APR-10 10.08.05 AM                                                                                                                                                       1068836

 7788 ppppp      U                  05002F0090020000 06-APR-10 10.08.05 AM                                                                                                                                                       1068836

 7900 shitou     U                  05002A008F020000 06-APR-10 10.04.13 AM                                                                                                                                                       1068682

 7369 SMITH                                                                                                                                                                                                            

 7499 ALLEN                                                                                                                                                                                                            

 7521 WARD                                                                                                                                                                                                             

 7566 JONES                                                                                                                                                                                                            

 7654 MARTIN                                                                                                                                                                                                           

 7698 BLAKE                                                                                                                                                                                                            

 7782 CLARK                                                                                                                           06-APR-10 10.08.05 AM                                                            

 7788 SCOTT                                                                                                                           06-APR-10 10.08.05 AM                                                            

 7839 KING                                                                                                                                                                                                             

 7844 TURNER                                                                                                                                                                                                           

 7876 ADAMS                                                                                                                                                                                                            

 7900 JAMES                                                                                                                           06-APR-10 10.04.13 AM                                                            

 

15 rows selected

 

闪回事务查询是一种诊断工具,用于帮助识别数据库发生的事务级变化,可以用于事务审计的数据分析。通过闪回事务分析,可以识别在一个特定的时间段内所发生的所有变化,也可以对数据库表进行事务级恢复。

 

闪回事务查询的基础仍然是依赖于撤销数据(Undodata),它也是利用初始化的数据库参数UNDO_RETENTION来确定已经提交的撤销数据在数据库中的保存时间。

 

另外,我们在前面介绍的Flashback Version Query可以实现审计一段时间内表的所有改变,但是这仅仅是发现在某个时间段内所进行过的操作,对于错误的事务还不能进行撤销处理。而Flashback Transaction Query可实现撤销处理,因为可以从FLASHBACK_TRANSACTION_QUERY中获得事务的历史操作并撤销语句(undo_sql)。也就是说,我们可以审计一个事务到底做了什么,也可以撤销一个已经提交的事务。

闪回事务查询依赖于flashback_transaction_query。注意,这个表中也记录了没有提交的事务,如果COMMIT_SCN为空,证明该事务还没有提交。

SQL> desc flashback_transaction_query

Name             Type           Nullable Default Comments                                  

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

XID              RAW(8)         Y                Transaction identifier                    

START_SCN        NUMBER         Y                Transaction start SCN                     

START_TIMESTAMP  DATE           Y                Transaction start timestamp               

COMMIT_SCN       NUMBER         Y                Transaction commit SCN                    

COMMIT_TIMESTAMP DATE           Y                Transaction commit timestamp              

LOGON_USER       VARCHAR2(30)   Y                Logon user for transaction                

UNDO_CHANGE#     NUMBER         Y                1-based undo change number                

OPERATION        VARCHAR2(32)   Y                forward operation for this undo           

TABLE_NAME       VARCHAR2(256)  Y                table name to which this undo applies     

TABLE_OWNER      VARCHAR2(32)   Y                owner of table to which this undo applies 

ROW_ID           VARCHAR2(19)   Y                rowid to which this undo applies          

UNDO_SQL         VARCHAR2(4000) Y                SQL corresponding to this undo 

 

 

 

SQL> col table_name for a18;

SQL> col table_owner for a10;

SQL> col logon_user for a12;

SQL> set long 1000;

SQL> select logon_user,table_name,table_owner,undo_sql from flashback_transaction_query where xid='05002F0090020000'; --查询事务的undo_sql

 

LOGON_USER   TABLE_NAME         TABLE_OWNE UNDO_SQL

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

SYS          EMP2               SCOTT      update "SCOTT"."EMP2" set "ENAME" = 'CLARK' where ROWID = 'AAANVLAAEAAAAcUAAG';

SYS          EMP2               SCOTT      update "SCOTT"."EMP2" set "ENAME" = 'SCOTT' where ROWID = 'AAANVLAAEAAAAcUAAH';

 

--执行事务的undo_sql,实际工作中,如果一个事务影响的行很多,可通过sql脚本(脚本中保存该事务所有的undo_sql)进行撤销.

SQL> update "SCOTT"."EMP2" set "ENAME" = 'CLARK' where ROWID = 'AAANVLAAEAAAAcUAAG';

 

1 row updated

 

SQL> update "SCOTT"."EMP2" set "ENAME" = 'SCOTT' where ROWID = 'AAANVLAAEAAAAcUAAH';

 

1 row updated

 

SQL> commit;

 

Commit complete

 

SQL> select empno,ename,versions_operation,versions_xid,versions_starttime,versions_endtime,versions_startscn from scott.emp2

  2  versions between scn 1068618 and maxvalue; --闪回版本查询,确认撤销动作已经执行

 

EMPNO ENAME      VERSIONS_OPERATION VERSIONS_XID     VERSIONS_STARTTIME                                                               VERSIONS_ENDTIME                                                                 VERSIONS_STARTSCN

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

 7788 SCOTT      U                  03002F007D020000 06-APR-10 10.15.03 AM                                                                                                                                                       1072326

 7782 CLARK      U                  03002F007D020000 06-APR-10 10.15.03 AM                                                                                                                                                       1072326

 7782 ppppp      U                  05002F0090020000 06-APR-10 10.08.05 AM                                                            06-APR-10 10.15.03 AM                                                                      1068836

 7788 ppppp      U                  05002F0090020000 06-APR-10 10.08.05 AM                                                            06-APR-10 10.15.03 AM                                                                      1068836

 7900 shitou     U                  05002A008F020000 06-APR-10 10.04.13 AM                                                                                                                                                       1068682

 7369 SMITH                                                                                                                                                                                                            

 7499 ALLEN                                                                                                                                                                                                            

 7521 WARD                                                                                                                                                                                                             

 7566 JONES                                                                                                                                                                                                            

 7654 MARTIN                                                                                                                                                                                                           

 7698 BLAKE                                                                                                                                                                                                            

 7782 CLARK                                                                                                                           06-APR-10 10.08.05 AM                                                            

 7788 SCOTT                                                                                                                           06-APR-10 10.08.05 AM                                                            

 7839 KING                                                                                                                                                                                                             

 7844 TURNER                                                                                                                                                                                                           

 7876 ADAMS                                                                                                                                                                                                            

 7900 JAMES                                                                                                                           06-APR-10 10.04.13 AM                                                            

 

17 rows selected 

 

SQL> select * from scott.emp2; --查询记录,确认撤销动作已经执行

 

EMPNO ENAME      JOB         MGR       SAL      COMM DEPTNO

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

 7369 SMITH      CLERK      7902    800.00               20

 7499 ALLEN      SALESMAN   7698   1600.00    300.00     30

 7521 WARD       SALESMAN   7698   1250.00    500.00     30

 7566 JONES      MANAGER    7839   2975.00               20

 7654 MARTIN     SALESMAN   7698   1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839   2850.00               30

 7782 CLARK      MANAGER    7839   2450.00               10

 7788 SCOTT      ANALYST    7566   3000.00               20

 7839 KING       PRESIDENT         5000.00               10

 7844 TURNER     SALESMAN   7698   1500.00      0.00     30

 7876 ADAMS      CLERK      7788   1100.00               20

 7900 shitou     CLERK      7698    950.00               30

 

12 rows selected

 

 

(flashback version query & flashback transaction query)的局限性:

依赖于回滚数据,所以对于较早的误操作无能为力,那时得借助logminer挖掘日志了。

 

 

参考:

 《Oracle 11g基础与提高》

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值