flashback技術之---flashback Transaction Query

lsq_008發表於2012-07-29
SQL> SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
  2       FROM flashback_transaction_query
  3       WHERE xid IN (SELECT versions_xid FROM t VERSIONS   between scn 314690 and 314700);

XID              OPERATION   START_SCN COMMIT_SCN LOGON_USER UNDO_SQL
---------------- ---------- ---------- ---------- ---------- ------------------------------------------------------------
01002600C2000000 INSERT         314698     314699 SCOTT      delete from "SCOTT"."T" where ROWID = 'AAALOBAADAAAAASAAC';
01002600C2000000 BEGIN          314698     314699 SCOTT
08001100D4000000 INSERT         314692     314694 SCOTT      delete from "SCOTT"."T" where ROWID = 'AAALOBAADAAAAASAAB';
08001100D4000000 BEGIN          314692     314694 SCOTT

--聯合flashback version query和flashback transaction query，可以將使用者某個事務做的誤操作進行恢復：

SQL> CREATE TABLE emp 
  2     (empno   NUMBER PRIMARY KEY, 
  3      empname VARCHAR2(16), 
  4      salary  NUMBER);

Table created.

SQL> INSERT INTO emp VALUES (111, 'Mike', 555);

1 row created.

SQL> commit;

Commit complete.

SQL> CREATE TABLE dept 
  2     (deptno   NUMBER, 
  3      deptname VARCHAR2(32));

Table created.

SQL> INSERT INTO dept VALUES (10, 'Accounting');

1 row created.

SQL> commit;

Commit complete.

--此時，emp和dept兩個表中各自有一條資料
--接下來另一個事務誤刪了empno=111的資料：

SQL> UPDATE emp SET salary = salary + 100 WHERE empno = 111;

1 row updated.

SQL> INSERT INTO dept VALUES (20, 'Finance'); 

1 row created.

SQL> DELETE FROM emp WHERE empno = 111;

1 row deleted.

SQL> commit;

Commit complete.

--另外一個事務插入了一條empno=111的資料，ename與之前的不同
SQL> INSERT INTO emp VALUES (111, 'Tom', 777);

1 row created.

SQL> UPDATE emp SET salary = salary + 100 WHERE empno = 111;

1 row updated.

SQL> UPDATE emp SET salary = salary + 50 WHERE empno = 111;

1 row updated.

SQL> commit;

Commit complete.

--此時，DBA發現了這個錯誤，需要將empno=111的資料恢復，執行如下查詢

SQL> SELECT versions_xid XID, versions_startscn START_SCN,
  2    versions_endscn END_SCN, versions_operation OPERATION,
  3    empname, salary FROM scott.emp
  4    VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
  5    where empno = 111;

XID               START_SCN    END_SCN OPERATION  EMPNAME              SALARY
---------------- ---------- ---------- ---------- ---------------- ----------
09000800E3000000     367562            I          Tom                     927
06002900BD000000     367525            D          Mike                    555
09000200E3000000     350554     367525 I          Mike                    555

--在查詢結果中，sql的實際執行順序是從下往上執行的，也就是09000200E3000000==>06002900BD000000==>09000800E3000000  
--可以看到事務06002900BD000000執行了delete操作，從flashback_transaction_query檢視中查到該事務：

SQL> SELECT  xid, start_scn, commit_scn,operation OP,logon_user,undo_sql 
  2  FROM flashback_transaction_query
  3  WHERE xid = HEXTORAW('06002900BD000000');

XID               START_SCN COMMIT_SCN OP                               LOGON_USER
---------------- ---------- ---------- -------------------------------- ----------
UNDO_SQL
------------------------------------------------------------
06002900BD000000     367517     367525 DELETE                           SCOTT
insert into "SCOTT"."EMP"("EMPNO","EMPNAME","SALARY") values
 ('111','Mike','655');

06002900BD000000     367517     367525 INSERT                           SCOTT
delete from "SCOTT"."DEPT" where ROWID = 'AAALOEAABAAAMraAAB
';

06002900BD000000     367517     367525 UPDATE                           SCOTT

XID               START_SCN COMMIT_SCN OP                               LOGON_USER
---------------- ---------- ---------- -------------------------------- ----------
UNDO_SQL
------------------------------------------------------------
update "SCOTT"."EMP" set "SALARY" = '555' where ROWID = 'AAA
LOCAABAAAMrKAAA';

06002900BD000000     367517     367525 BEGIN                            SCOTT

根據undo_sql，可以將資料恢復。

   

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10972173/viewspace-738213/，如需轉載，請註明出處，否則將追究法律責任。

相關文章