作者:dbtan |【转载时请以超链接形式标明文章出处作者信息】


Oracle 10g 闪回查询特性的增强:

Oracle 9i提供了闪回特性增强,为数据恢复带来了极大的方便,但是Oracle 9i的闪回查询只能提供某个时间点的数据视图,并不能告诉用户这样的数据经过了几个事务、怎样的修改(UPDATE、INSERT、DELETE等),而这些信息在回滚段中是存在的,在Oracle 10g中,Oracle进一步加强了闪回查询的特性,提供了以下两种闪回查询:
·闪回版本查询(Flashback Versions Query);
·闪回事务查询(Flashback Transaction Query)。

闪回版本查询允许使用一个新的VERSIONS子句查询两个时间点或者SCN之间的数据版本。这些版本可以按照事务进行区分,闪回版本查询只返回提交数据,未提交数据不被显示。

通过以下示例,来理解闪回版本查询的信用。首先创建一个测试表,执行一系列的DML操作:

tq@TQGZS> create table t as select username,user_id from dba_users;
Table created.
tq@TQGZS> select * from t;   

USERNAME                          USER_ID
------------------------------ ----------
TQ                                     76
RMAN                                   71
T2                                     69
PERFSTAT                               62
TEST                                   66
HR                                     55
OE                                     56
DIP                                    19
SH                                     58
IX                                     57
MDDATA                                 50
PM                                     59
T4                                     72
BI                                     60
T5                                     73
SQLLDR                                 75
T1                                     68
T6                                     74
TSMSYS                                 21
T3                                     70
SCOTT                                  54
OLAPSYS                                47
SI_INFORMTN_SCHEMA                     45
ORDPLUGINS                             44
XDB                                    38
SYSMAN                                 51
ANONYMOUS                              39
CTXSYS                                 36
WMSYS                                  25
DBSNMP                                 24
DMSYS                                  35
EXFSYS                                 34
ORDSYS                                 43
MDSYS                                  46
SYSTEM                                  5
SYS                                     0
MGMT_VIEW                              53
CSMIG                                  64
OUTLN                                  11
39 rows selected.
tq@TQGZS> delete from t where username='OUTLN';
1 row deleted.
tq@TQGZS> commit;
Commit complete.
tq@TQGZS> delete from t where username='TEST';
1 row deleted.
tq@TQGZS> commit;
Commit complete.
tq@TQGZS> update t set user_id = 6 where username = 'TQ';
1 row updated.
tq@TQGZS> commit;
Commit complete.
tq@TQGZS> delete from t where user_id > 10;
34 rows deleted.
tq@TQGZS> commit;
Commit complete.
tq@TQGZS> select * from t;
USERNAME                USER_ID
-------------------- ----------
TQ                            6
SYSTEM                        5
SYS                           0
tq@TQGZS> insert into t values('DBTAN',8);
1 row created.
tq@TQGZS> commit;
Commit complete.

至此数据库中已经交替执行了多个事务,进行了众多的数据修改,现在的测试表已经与最初完全不同了。如果使用Oracle 9i的闪回查询,是很难区分这些不同事务的变更,找到合适的、正确的数据将变得极为困难。

再了看看Oracle 10g的闪回版本查询,通过vertions子句和对数据表引入了系列的伪列(versions_starttime等),可以获得对数据表的所有事务操作,注意以下输出中versions_operation代表了不同类型的操作(D-Delete、I-Insert、U-Update),VERSIONS_XID是一个重要数据,代表了不同版本的事务ID

tq@TQGZS> select versions_starttime,versions_endtime,versions_xid,
  2  versions_operation,username,user_id                    
  3  from t versions between timestamp minvalue and maxvalue;

VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_XID     V USERNAME             USER_ID
------------------------- ------------------------- ---------------- - -------------------- -------
04-JAN-10 11.28.50 AM                               0300280024190000 D CSMIG                     64
04-JAN-10 11.28.50 AM                               0300280024190000 D MGMT_VIEW                 53
04-JAN-10 11.28.50 AM                               0300280024190000 D MDSYS                     46
04-JAN-10 11.28.50 AM                               0300280024190000 D ORDSYS                    43
04-JAN-10 11.28.50 AM                               0300280024190000 D EXFSYS                    34
04-JAN-10 11.28.50 AM                               0300280024190000 D DMSYS                     35
04-JAN-10 11.28.50 AM                               0300280024190000 D DBSNMP                    24
04-JAN-10 11.28.50 AM                               0300280024190000 D WMSYS                     25
04-JAN-10 11.28.50 AM                               0300280024190000 D CTXSYS                    36
04-JAN-10 11.28.50 AM                               0300280024190000 D ANONYMOUS                 39
04-JAN-10 11.28.50 AM                               0300280024190000 D SYSMAN                    51
04-JAN-10 11.28.50 AM                               0300280024190000 D XDB                       38
04-JAN-10 11.28.50 AM                               0300280024190000 D ORDPLUGINS                44
04-JAN-10 11.28.50 AM                               0300280024190000 D SI_INFORMTN_SCHEMA        45
04-JAN-10 11.28.50 AM                               0300280024190000 D OLAPSYS                   47
04-JAN-10 11.28.50 AM                               0300280024190000 D SCOTT                     54
04-JAN-10 11.28.50 AM                               0300280024190000 D T3                        70
04-JAN-10 11.28.50 AM                               0300280024190000 D TSMSYS                    21
04-JAN-10 11.28.50 AM                               0300280024190000 D T6                        74
04-JAN-10 11.28.50 AM                               0300280024190000 D T1                        68
04-JAN-10 11.28.50 AM                               0300280024190000 D SQLLDR                    75
04-JAN-10 11.28.50 AM                               0300280024190000 D T5                        73
04-JAN-10 11.28.50 AM                               0300280024190000 D BI                        60
04-JAN-10 11.28.50 AM                               0300280024190000 D T4                        72
04-JAN-10 11.28.50 AM                               0300280024190000 D PM                        59
04-JAN-10 11.28.50 AM                               0300280024190000 D MDDATA                    50
04-JAN-10 11.28.50 AM                               0300280024190000 D IX                        57
04-JAN-10 11.28.50 AM                               0300280024190000 D SH                        58
04-JAN-10 11.28.50 AM                               0300280024190000 D DIP                       19
04-JAN-10 11.28.50 AM                               0300280024190000 D OE                        56
04-JAN-10 11.28.50 AM                               0300280024190000 D HR                        55
04-JAN-10 11.28.50 AM                               0300280024190000 D PERFSTAT                  62
04-JAN-10 11.28.50 AM                               0300280024190000 D T2                        69
04-JAN-10 11.28.50 AM                               0300280024190000 D RMAN                      71
04-JAN-10 11.28.29 AM                               0700190084200000 U TQ                         6
                          04-JAN-10 11.28.29 AM                        TQ                        76
                          04-JAN-10 11.28.50 AM                        RMAN                      71
                          04-JAN-10 11.28.50 AM                        T2                        69
                          04-JAN-10 11.28.50 AM                        PERFSTAT                  62
                          04-JAN-10 11.28.50 AM                        HR                        55
                          04-JAN-10 11.28.50 AM                        OE                        56
                          04-JAN-10 11.28.50 AM                        DIP                       19
                          04-JAN-10 11.28.50 AM                        SH                        58
                          04-JAN-10 11.28.50 AM                        IX                        57
                          04-JAN-10 11.28.50 AM                        MDDATA                    50
                          04-JAN-10 11.28.50 AM                        PM                        59
                          04-JAN-10 11.28.50 AM                        T4                        72
                          04-JAN-10 11.28.50 AM                        BI                        60
                          04-JAN-10 11.28.50 AM                        T5                        73
                          04-JAN-10 11.28.50 AM                        SQLLDR                    75
                          04-JAN-10 11.28.50 AM                        T1                        68
                          04-JAN-10 11.28.50 AM                        T6                        74
                          04-JAN-10 11.28.50 AM                        TSMSYS                    21
                          04-JAN-10 11.28.50 AM                        T3                        70
                          04-JAN-10 11.28.50 AM                        SCOTT                     54
                          04-JAN-10 11.28.50 AM                        OLAPSYS                   47
                          04-JAN-10 11.28.50 AM                        SI_INFORMTN_SCHEMA        45
                          04-JAN-10 11.28.50 AM                        ORDPLUGINS                44
                          04-JAN-10 11.28.50 AM                        XDB                       38
                          04-JAN-10 11.28.50 AM                        SYSMAN                    51
                          04-JAN-10 11.28.50 AM                        ANONYMOUS                 39
                          04-JAN-10 11.28.50 AM                        CTXSYS                    36
                          04-JAN-10 11.28.50 AM                        WMSYS                     25
                          04-JAN-10 11.28.50 AM                        DBSNMP                    24
                          04-JAN-10 11.28.50 AM                        DMSYS                     35
                          04-JAN-10 11.28.50 AM                        EXFSYS                    34
                          04-JAN-10 11.28.50 AM                        ORDSYS                    43
                          04-JAN-10 11.28.50 AM                        MDSYS                     46
                                                                       SYSTEM                     5
                                                                       SYS                        0
                          04-JAN-10 11.28.50 AM                        MGMT_VIEW                 53
                          04-JAN-10 11.28.50 AM                        CSMIG                     64
04-JAN-10 11.29.35 AM                               07001D0084200000 I DBTAN                      8
73 rows selected.

通过以上输出,根据VERSIONS_XID可以清晰地区分不同事务在不同时间对数据所做的更改。具备了flashback versions query查询的基础,就可以进行基于flashback versions query的事务级恢复,这就是flashback transaction query

flashback transaction query可以从FLASHBACK_TRANSACTION_QUERY视图中获得指定事务级的历史信息以及Undo_SQL,通过这个UNDO_SQL,就可以撤销特定的提交事务。

flashback transaction query需要用到FLASHBACK_TRANSACTION_QUERY视图,先看一下视图:

sys@TQGZS> desc flashback_transaction_query;
Name                    Null?    Type
----------------------- -------- ------------------------
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)

该视图的定义为:

sys@TQGZS> select dbms_metadata.get_ddl('VIEW','FLASHBACK_TRANSACTION_QUERY') from dual;
DBMS_METADATA.GET_DDL('VIEW','FLASHBACK_TRANSACTION_QUERY')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."FLASHBACK_TRANSACTION_QUERY" ("XID", "STAR
T_SCN", "START_TIMESTAMP", "COMMIT_SCN", "COMMIT_TIMESTAMP", "LOGON_USER", "UNDO
_CHANGE#", "OPERATION", "TABLE_NAME", "TABLE_OWNER", "ROW_ID", "UNDO_SQL") AS
  select xid, start_scn, start_timestamp,
          decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)
          commit_scn, commit_timestamp,
          logon_user, undo_change#, operation, table_name, table_owner,
          row_id, undo_sql
from sys.x$ktuqqry

继续前面的测试,如果需要撤销XID=0300280024190000的事务,可以通过如下步骤进行(注意当查询FLASHBACK_TRANSACTION_QUERY视图时如果直接引用XID则查询会因为无法使用索引而极其耗时):
tq@TQGZS> set timing on
tq@TQGZS> set autotrace on
tq@TQGZS> select undo_sql from flashback_transaction_query
  2  where xid = '0300280024190000';                 
UNDO_SQL
--------------------------------------------------------------------------------
insert into "TQ"."T"("USERNAME","USER_ID") values ('CSMIG','64');
insert into "TQ"."T"("USERNAME","USER_ID") values ('MGMT_VIEW','53');
insert into "TQ"."T"("USERNAME","USER_ID") values ('MDSYS','46');
insert into "TQ"."T"("USERNAME","USER_ID") values ('ORDSYS','43');
insert into "TQ"."T"("USERNAME","USER_ID") values ('EXFSYS','34');
insert into "TQ"."T"("USERNAME","USER_ID") values ('DMSYS','35');
insert into "TQ"."T"("USERNAME","USER_ID") values ('DBSNMP','24');
insert into "TQ"."T"("USERNAME","USER_ID") values ('WMSYS','25');
insert into "TQ"."T"("USERNAME","USER_ID") values ('CTXSYS','36');
insert into "TQ"."T"("USERNAME","USER_ID") values ('ANONYMOUS','39');
insert into "TQ"."T"("USERNAME","USER_ID") values ('SYSMAN','51');
insert into "TQ"."T"("USERNAME","USER_ID") values ('XDB','38');
insert into "TQ"."T"("USERNAME","USER_ID") values ('ORDPLUGINS','44');
insert into "TQ"."T"("USERNAME","USER_ID") values ('SI_INFORMTN_SCHEMA','45');
insert into "TQ"."T"("USERNAME","USER_ID") values ('OLAPSYS','47');
insert into "TQ"."T"("USERNAME","USER_ID") values ('SCOTT','54');
insert into "TQ"."T"("USERNAME","USER_ID") values ('T3','70');
insert into "TQ"."T"("USERNAME","USER_ID") values ('TSMSYS','21');
insert into "TQ"."T"("USERNAME","USER_ID") values ('T6','74');
insert into "TQ"."T"("USERNAME","USER_ID") values ('T1','68');
insert into "TQ"."T"("USERNAME","USER_ID") values ('SQLLDR','75');
insert into "TQ"."T"("USERNAME","USER_ID") values ('T5','73');
insert into "TQ"."T"("USERNAME","USER_ID") values ('BI','60');
insert into "TQ"."T"("USERNAME","USER_ID") values ('T4','72');
insert into "TQ"."T"("USERNAME","USER_ID") values ('PM','59');
insert into "TQ"."T"("USERNAME","USER_ID") values ('MDDATA','50');
insert into "TQ"."T"("USERNAME","USER_ID") values ('IX','57');
insert into "TQ"."T"("USERNAME","USER_ID") values ('SH','58');
insert into "TQ"."T"("USERNAME","USER_ID") values ('DIP','19');
insert into "TQ"."T"("USERNAME","USER_ID") values ('OE','56');
insert into "TQ"."T"("USERNAME","USER_ID") values ('HR','55');
insert into "TQ"."T"("USERNAME","USER_ID") values ('PERFSTAT','62');
insert into "TQ"."T"("USERNAME","USER_ID") values ('T2','69');
insert into "TQ"."T"("USERNAME","USER_ID") values ('RMAN','71');
35 rows selected.
Elapsed: 00:00:22.71
Execution Plan
----------------------------------------------------------
Plan hash value: 1115820779
--------------------------------------
| Id  | Operation        | Name      |
--------------------------------------
|   0 | SELECT STATEMENT |           |
|*  1 |  FIXED TABLE FULL| X$KTUQQRY |
--------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(RAWTOHEX("XID")='0300280024190000')
Note
-----
   - rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
      28346  recursive calls
          0  db block gets
    1082754  consistent gets
          2  physical reads
          0  redo size
       3036  bytes sent via SQL*Net to client
        407  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
        255  sorts (memory)
          0  sorts (disk)
         35  rows processed

此时可以通过hextoraw转换利用底层索引,提高查询速度:

tq@TQGZS> set autotrace traceonly explain
tq@TQGZS> select undo_sql from flashback_transaction_query
  2  where xid = hextoraw('0300280024190000');
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1747778896
-----------------------------------------------------
| Id  | Operation               | Name              |
-----------------------------------------------------
|   0 | SELECT STATEMENT        |                   |
|*  1 |  FIXED TABLE FIXED INDEX| X$KTUQQRY (ind:1) |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("XID"=HEXTORAW('0300280024190000') )
Note
-----
   - rule based optimizer used (consider using cbo)

通过执行相应的UNDO语句可以撤销该事务,通过这些新特性,Oracle提供了一种“回滚”提交事务的手段,极大地方便了用户应对不同情况的数据库恢复。

- The End -