Oracle Flashback Technology (II) - Flashback Transactions




Oracle Flashback technology
Logical Flashback (do not depend on RMAN, rely on undo data)
Physical flashback
New on Oracle 11g:





Oracle Flashback Transaction
  • Allow you to rollback changes made by one or more transactions.
  • Flashback Transaction generates and executes the DMLs necessary to undo a given transactions and (optionally) all its dependent transactions.
  • Requisites:
    • ARCHIVELOG mode
    • At least one archive log already generated
    • Minimal and PK (plus FK) supplemental logging enabled
    Privileges required:
    • FLASHBACK and SELECT on the object or
    • FLASHBACK ANY TABLE.

Configuring the database to use Flashback Transaction

(1) Check the database log mode

SQL> Select log_mode from v$database;

LOG_MODE     
------------ 
NOARCHIVELOG

(2) Change Log mode to archivelog:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

SQL> Select log_mode from v$database;

LOG_MODE     
------------ 
ARCHIVELOG

(3) Switch logfile

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

(4) Check log files

SQL> select group#, thread#, bytes/1024/1024 Mb, members, archived, status
     from v$log;

GROUP#                 THREAD#                MB                     MEMBERS                ARCHIVED STATUS           
---------------------- ---------------------- ---------------------- ---------------------- -------- ---------------- 
1                      1                      50                     1                      YES      ACTIVE           
2                      1                      50                     1                      NO       CURRENT          
3                      1                      50                     1                      YES      INACTIVE       

SQL> select name, archived, applied, status, completion_time, compressed
     from v$archived_log;

NAME                                                      ARCHIVED APPLIED   STATUS COMPLETION_TIME           COMPRESSED 
--------------------------------------------------------- -------- --------- ------ ------------------------- ---------- 
/u01/.../flash_recovery_area/SID/archivelog/date/name.arc YES      NO        A      08-DEC-10                 NO         

(5) Enable Minimal and PK (and FK) supplemental logging 

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

(3) Check the current setting for supplemental logging
  (V$DATABASE, DBA_LOG_GROUPS, DBA_LOG_GROUP_COLUMNS)

SQL> Select supplemental_log_data_min, supplemental_log_data_all,
            supplemental_log_data_UI,  supplemental_log_data_FK
     from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK 
------------------------- ------------------------- ------------------------ ------------------------ 
YES                       NO                        NO                       YES                     

Using Flashback Transaction
  • Use DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure to rollback a transaction and its dependent transactions.
  • Can be done with the database online.
Restrictions
  • DDL operations cannot be flashed back
  • Transactions that use LOB datatypes (BFILE, BLOB, CLOB, NCLOB) cannot be flashed back
  • Transactions cannot use features or datatypes not supported by the Log Miner
  • Ensure that undo segments are large enough to maintain undo information for the period you may need to flashback.

Example 1: Flashback transactions that have no dependent transactions.
  1. Drop and create tables dept and emp
  2. Transaction 1 and Transaction 2 insert into dept and emp
  3. To flashback Transaction 2 you need:
    • Find out the transaction ID (XID): you can use OEM and Log Miner, or use a flashback transaction query.
    • Identify the transaction to be flashed back and use dbms_flashback.transaction_backout

(1) Drop and recreate dept and emp tables.
SQL> drop table emp;
SQL> drop table dept;

-- Check the current database SCN
SQL> select current_scn scn_before_tblcreation 
     from v$database;

SCN_BEFORE_TBLCREATION 
---------------------- 
1473663               


SQL> create table dept
    (deptno number primary key,
     deptname varchar2(20));
  
SQL> create table emp
     (empno number primary key, 
      empname varchar2(10),
      deptno number, 
      salary number,
      constraint fk_dept foreign key(deptno)
                         references dept(deptno));

-- Check the current database SCN
SQL> select current_scn scn_before_transactions 
     from v$database;

SCN_BEFORE_TRANSACTIONS 
----------------------- 
1473743   
(2) Insert data into emp and dept
-- Transaction 1: Insert new row (id=1)
insert into dept values (1, 'accounting');
insert into dept values (2, 'sales');
insert into emp values (1, 'John', 1, 2000);
commit;

-- Transaction 2: Insert new rows (ids=2,3)
insert into emp values (2, 'Mary', 1, 2500);
insert into emp values (3, 'Jack', 2, 2000);
insert into dept values (3, 'IT');
commit;

-- Check the current database SCN
SQL> select current_scn scn_after_transactions 
     from v$database;

SCN_AFTER_TRANSACTIONS 
---------------------- 
1473781 

-- Check inserted data
SQL> select * from dept;

DEPTNO                 DEPTNAME             
---------------------- -------------------- 
1                      accounting           
2                      sales                
3                      IT                   

SQL> select * from emp;

EMPNO                  EMPNAME    DEPTNO                 SALARY                 
---------------------- ---------- ---------------------- ---------------------- 
1                      John       1                      2000                   
2                      Mary       1                      2500                   
3                      Jack       2                      2000  
(3) Now use the flashback transaction query to find information about all transactions in a time or SCN interval
SQL> select versions_xid xid, versions_startscn start_scn,
            versions_endscn end_scn, versions_operation operation,
            empno, empname, deptno, salary
     from emp
        versions between scn 1473743 and 1473781;

XID              START_SCN   END_SCN  OPERATION EMPNO   EMPNAME    DEPTNO   SALARY  
---------------- ----------- -------- --------- ------- ---------- -------- -------- 
05000D0099040000 1473777              I         3       Jack       2         2000   
05000D0099040000 1473777              I         2       Mary       1         2500   -- Transaction 2
07000A006A030000 1473747              I         1       John       1         2000   

Identify the transaction you need to flashback. For example, if you choose to flashback transaction 2 only its three insert statements will be flashed back. Also, transaction 2 has no dependents, since:
  • No other subsequent transaction modified any data touched by transaction 2
  • No other subsequent transaction modified data linked through referential integrity with the rows touched by transaction 2.

To flashback a transaction, use DBMS_FLASHBACK.TRANSACTION_BACKOUT (numtxns, xids, options).
The procedure accepts one or more transaction IDs (XIDs).
It has also an OPTIONS parameter to indicate how dependent transactions should be treated. The possible values are:
  • NOCASCADE - The transaction should have no dependencies. If a dependency is found, this raises an error.
  • NOCASCADE_FORCE - The transaction is backed out without affecting dependent transactions. This option succeeds only if no constraints are violated in the process.
  • NONCONFLICT_ONLY - This option backout only the changes to the non-conflicting rows of the given transactions.
  • CASCADE - The transaction and all it's dependencies will be removed.
SQL> conn / as sysdba;

SQL> declare
 v_xid sys.xid_array;
begin
 v_xid := sys.xid_array('05000D0099040000');
 dbms_flashback.transaction_backout( numtxns => 1,
                                     xids    => v_xid,
                                     options => dbms_flashback.cascade);
end;
/
anonymous block completed
  • TRANSACTION_BACKOUT analyzes the transactional dependencies, performs DML operations, and generates reports.
  • It also DOES NOT commit the actions performed.
  • To make the transaction backout permanent, you need to explicitly commit the transaction.
(4) Check the effects of the flashback. Query the table contents. Commit the flashback transaction.
SQL> select * from dept;

DEPTNO                 DEPTNAME             
---------------------- -------------------- 
1                      accounting           
2                      sales                


SQL> select * from emp;

EMPNO                  EMPNAME    DEPTNO                 SALARY                 
---------------------- ---------- ---------------------- ---------------------- 
1                      John       1                      2000           

SQL> commit;

TRANSACTION_BACKOUT Reports
The backout procedure populates two static data dictionary views.
  • DBA_FLASHBACK_TXN_STATE
  • Shows information about the transactions backedout by the procedure.
    • COMPENSATING_XID - Transaction of ID of the compensating transaction. You will need to explictly commit this transaction in order to make the backout permanent.
    • XID - Backedout transaction
    • DEPENDENT_XID - Dependent transaction
(a) Query DBA_FLASHBACK_TXN_STATE view

SQL> select *
from dba_flashback_txn_state
where xid = '05000D0099040000';

COMPENSATING_XID XID              DEPENDENT_XID    BACKOUT_MODE     USERNAME                       
---------------- ---------------- ---------------- ---------------- ------------------------------ 
0800160056050000 05000D0099040000                  CASCADE          SYSTEM                

(b) Use the compensating_xid returned to query the DBA_FLASHBACK_TXN_REPORT view:

sql> select xid_report
     from dba_flashback_txn_report
     where compensating_xid = '0800160056050000';

XID_REPORT   
------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?> 
<COMP_XID_REPORT XID="0800160056050000" NAME="_SYS_COMP_TXN_1114255_TIM_1319522919"> 
 <TRANSACTION XID="05000D0099040000"> 
 <CHARACTERISTICS> 
 </CHARACTERISTICS> 
 <UNDO_SQL> 
  <USQL\="yes"> 
   delete from "SYSTEM"."DEPT" where "DEPTNO" = '3' and "DEPTNAME" = 'IT' 
  </USQL>
  <USQL exec="yes">
   delete from "SYSTEM"."EMP" where "EMPNO" = '3' and "EMPNAME" = 'Jack' and "DEPTNO" = '2' and "SALARY" = '2000'
  </USQL> 
  <USQL exec="yes"> 
   delete from "SYSTEM"."EMP" where "EMPNO" = '2' and "EMPNAME" = 'Mary' and "DEPTNO" = '1' and "SALARY" = '2500' 
  </USQL>  
 </UNDO_SQL> 
 <DEPENDENT_XIDS> 
 </DEPENDENT_XIDS>
 </TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>delete from "SYSTEM"."DEPT" where "DEPTNO" = '3' and "DEPTNAME" = 'IT' 
</EXEC_USQL> 
<EXEC_USQL>delete from "SYSTEM"."EMP" where "EMPNO" = '3' and "EMPNAME" = 'Jack' and "DEPTNO" = '2' and "SALARY" = '2000' 
</EXEC_USQL>
<EXEC_USQL>delete from "SYSTEM"."EMP" where "EMPNO" = '2' and "EMPNAME" = 'Mary' and "DEPTNO" = '1' and "SALARY" = '2500'
</EXEC_USQL>             
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>  


No comments:

Post a Comment