Oracle Flashback Technology (III) - Flashback Data Archive



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



Using Flashback Data Archive (Oracle Total Recall)
  • With Data Archive you can store and track transactional changes to a record over its lifetime.
  • It permanently stores undo information on flashback archives, allowing you to keep the transactional history of a object since its creation.
  • Flashback archives are enabled on individual tables and are located in tablespaces, and have a name, a specified retention period and a space quota on the tablespace.
  • A database can have multiple flashback archives.
  • when a DML transaction commits on a flashback archive enabled table, the Flashback Data Archiver (FBDA) process stores the pre-image of the rows into a flashback archive.
  • FBDA also manages the data within the flashback archives (purging data beyond retention period).
  • Historical data can be queried using the Flashback Query AS OF clause.
  • Useful for compliance with record stage policies and audit requirements.


To enable flashback archiving for a table:
  • You need FLASHBACK ARCHIVE privilege on a flashback data archive
  • Table cannot be clustered, nested, temporary, remote or external
  • Table cannot have LONG nor nested columns


Create a Flashback Data Archive
(1) Create a new tablespace (you may also use an existing one)

SQL> create tablespace fda_ts
   datafile '/u01/app/oracle/oradata/test112/fda1_01.dbf'
   size 1m autoextend on next 1m;

SQL> select tablespace_name, status, contents, retention
  from dba_tablespaces
  where tablespace_name ='FDA_TS';

TABLESPACE_NAME                STATUS    CONTENTS  RETENTION   
------------------------------ --------- --------- ----------- 
FDA_TS                         ONLINE    PERMANENT NOT APPLY   

(2) Create Flashback archvies:

SQL> create flashback archive default fda_1m tablespace fda_ts   -- Must be SYSDBA to create DEFAULT FDA
  quota 1G retention 1 month;                                -- To change use ALTER FLASHBACK ARCHIVE...SET DEFAULT

SQL> create flashback archive fda_2yr tablespace fda_ts retention 2 year;
  
SQL> create flashback archive fda_10d tablespace fda_ts retention 10 day;

Managing Flashback Data Archives:
(1) Manage FDA tablespaces:

ALTER FLASHBACK ARCHIVE...
   ...SET DEFAULT;
   ... ADD TABLESPACE... QUOTA...;
   ... MODIFY TABLESPACE...
   ... REMOVE TABLESPACE...

(2) Manage retention period:

ALTER FLASHBACK ARCHIVE fda_name MODIFY RETENTION n [Year | Month | day ];

(3) Purge historical data

ALTER FLASHBACK ARCHIVE...
   ...PURGE ALL;                          -- Purge ALL historical data
   ...PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL 'n' DAY);
   ...PURGE BEFORE SCN scn_num;

(4) Drop FDA:

DROP FLASHBACK ARCHIVE fda_name;           -- Drops FDA. Keeps tablespace.

Enabling FDAs on objects:
  • FDA is disabled by default
  • User needs FLASHBACK ARCHIVE privilege to create enable flashback archive on object.
SQL> conn sys/pwd as sysdba;

SQL> grant flashback archive on fda_1m to userA;

SQL> conn userA/pwd;

SQL> Create table emp 
  (empno number primary key,
   ename varchar2(20),
   salary number) 
  flashback archive fda_1m;


-- To Disable Flashback archive on table
SQL> ALTER TABLE emp NO flashback archive;

Information about Flashback data Archives:
DBA_FLASHBACK_ARCHIVE, DBA_FLASHBACK_ARVHIE_TS and DBA_FLASHBACK_ARCHIVE_TABLES
SQL> select owner_name, flashback_archive_name, retention_in_days, status, 
       to_char(last_purge_time, 'dd-mon-yy hh24:mi:ss')
from dba_flashback_archive;

OWNER_NAME    FLASHBACK_ARCHIVE_NAME  RETENTION_IN_DAYS      STATUS  LAST_PURGE_TIME           
------------- ----------------------- ---------------------- ------- ------------------------- 
SYS           FDA_1M                  30                     DEFAULT 25-oct-11 13:34:14 
SYS           FDA_2YR                 730                            25-oct-11 13:34:54 
SYSTEM        FDA_10D                 10                             25-oct-11 13:38:05

SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME  FLASHBACK_ARCHIVE#     TABLESPACE_NAME                QUOTA_IN_MB 
----------------------- ---------------------- ------------------------------ ------------
FDA_1M                  1                      FDA_TS                         1024        
FDA_2YR                 2                      FDA_TS                                     
FDA_10D                 3                      FDA_TS                                     


SQL> select * from dba_flashback_archive_tables;

TABLE_NAME  OWNER_NAME                     FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME   STATUS   
----------- ------------------------------ ---------------------- ------------------- -------- 
EMP         SYSTEM                         FDA_1M                 SYS_FBA_HIST_75434  ENABLED  


Example: Viewing table history.
(1) Insert data on emp
(2) Keep record of some points in time
(3) Query the historical data on emp
SQL> select to_char(systimestamp, 'dd-mon-yy hh24:mi:ss') start_time, 
       current_scn start_scn
     from v$database;

START_TIME         START_SCN              
------------------ ---------------------- 
25-oct-11 14:22:25 1498655       

SQL> select * from emp;

EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 

-- PL/SQL block performs a number of DMLs on emp and prints timestamps
set serveroutput on
declare
 procedure get_timestamp
 is
   v_time varchar2(25);
   v_scn  integer;
 begin
   select to_char(systimestamp, 'dd-mon-yy hh24:mi:ss') start_time, 
        current_scn start_scn into v_time, v_scn
   from v$database;
   dbms_output.put_line('timestamp: ' || v_time);
   dbms_output.put_line('SCN:       ' || v_scn);
end;
 
begin
  insert into emp values (1, 'John', 2000);
  commit;
  dbms_lock.sleep(2);
  get_timestamp();
  for i in 1 .. 10 
  loop
   update emp set salary =salary*1.05 where empno=1;
   commit;
   dbms_lock.sleep(2);
   if i=5 then
     insert into emp values (2, 'Mary', 3000);
     update emp set salary = 2500 where empno =1;
     commit;
     dbms_lock.sleep(2);
     update emp set ename = initcap(ename);
     commit;
     insert into emp values (3, 'Gary', 1500);
     delete from emp where empno=2;
     commit;
     get_timestamp();   
   end if;
  end loop;
  dbms_lock.sleep(2);
  get_timestamp();
end;
/

anonymous block completed
timestamp: 25-oct-11 14:22:27
SCN:       1498659
timestamp: 25-oct-11 14:22:39
SCN:       1498683
timestamp: 25-oct-11 14:22:51
SCN:       1498700

SQL> select * from emp;

EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 3190.70390625          
3                      Gary                 1500         

SQL> select to_char(systimestamp, 'dd-mon-yy hh24:mi:ss') end_time, 
       current_scn end_scn
     from v$database;
END_TIME           END_SCN                
------------------ ---------------------- 
25-oct-11 14:22:51 1498701


(a) Select data at a point in time
SQL> select *  from emp as of scn 1498683;

EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 2500                   
3                      Gary                 1500       

SQL> select * 
     from emp as of timestamp to_timestamp('25-oct-11 14:22:51', 'dd-mon-yy hh24:mi:ss');
EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 3190.70390625          
3                      Gary                 1500  


(b) select all versions of a row betwen two points in time

SQL> select *
     from emp
       versions between scn 1498659 and 1498700
     where empno =1;
EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 3190.70390625          
1                      John                 2000                   
1                      John                 2100                   
1                      John                 2205                   
1                      John                 2315.25                
1                      John                 2431.0125              
1                      John                 2552.563125            
1                      John                 2500                   
1                      John                 2500                   
1                      John                 2625                   
1                      John                 2756.25                
1                      John                 2894.0625              
1                      John                 3038.765625            
1                      John                 3190.70390625          

 14 rows selected 


SQL> select versions_xid xid, versions_startscn start_scn,
            versions_endscn end_scn, versions_operation operation,
            empno, ename, salary
     from emp
        versions between scn 1498659 and 1498700
     where empno =1;

XID              START_SCN   END_SCN                OPERATION EMPNO   ENAME                SALARY                 
---------------- ----------- ---------------------- --------- ------- -------------------- ---------------------- 
03000F008B040000 1498633     1498674                I         1       John                 3190.70390625          
05001F00AA040000 1498657     1498661                I         1       John                 2000                   
030003008B040000 1498661     1498664                U         1       John                 2100                   
02000A007E040000 1498664     1498667                U         1       John                 2205                   
01000D003A030000 1498667     1498670                U         1       John                 2315.25                
0400090075030000 1498670     1498672                U         1       John                 2431.0125              
06000B0094040000 1498672     1498674                U         1       John                 2552.563125            
0900080096040000 1498674     1498678                U         1       John                 2500                   
03001F008B040000 1498678     1498685                U         1       John                 2500                   
09001F0097040000 1498685     1498688                U         1       John                 2625                   
080010006C050000 1498688     1498691                U         1       John                 2756.25                
0700190078030000 1498691     1498694                U         1       John                 2894.0625              
03001A008B040000 1498694     1498697                U         1       John                 3038.765625            
05001E00AB040000 1498697                            U         1       John                 3190.70390625          

 14 rows selected 







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>  


Structured Storage approaches

(Extracts from a good discussion posted by James Hamilton in his blog. You can check the original article and comments here.)

Although a couple of years old, James Hamilton provided a good requisite-based breakdown of data storage systems. These are some of his points:
  • The world of structured storage extends far beyond relational (Oracle, DB2, SQL Server, MySQL, NoSQL, etc) systems.
  • Many applications do not need the rich programming model of relational systems and some are better seviced by lighter-weight, easier-to-administers, and easier-to-scale solutions.

  • Structured storage approaches can be classified based on customer major requirements.
  • These are Feature-first, scale-first, simple structured storage and purpose-optimized stores.

(1) Feature-First
  • Traditional Relational database management systems (RDBMS) are the structured storage system of choice here.
  • Driven by requirements for Enterprise financial systems, human resource systems, customer relationship management systems (FIN, HR, CRMs)
    • Examples here include Oracle, MySQL, SQL Server, PostgreSQL, Sybase, DB2.
  • Cloud solutions here include:

(2) Scale-First
  • This is the domain of very high scale website (i.e. facebook, Gmail, Amazon, Yahoo, etc)
  • Scaling capabilities are more important than more features and none could run on a single rdbms.
  • The problem here is that the full relational database model (including joins, aggregations, use of stored procedures) is difficult to scale (especially in distributed contexts).
  • Distributing data across tens to thousands of rdbms instances and still maintain support for the distributed data as if it were under a single rdbms engine is difficult.
  • As an alternative, very high scale may be supported with the use of key-value store solutions. These include HBase, Amazon SimpleDB (cloud-based), Project Valdemort, Cassandra, Hypertable, etc

(3) Simple Structure storage
  • Applications that have a structure storage requirement but do not need features, cost and complexity of RDBMSs neither have very high scalability requirements.
  • Some implementations include:
  • Facebook: email inbox search (Cassandra)
  • Amazon: retail shopping card (Dynamo)
  • Berkeey DB

(4) Purpose-Optimized stores
  • Mike Stonebraker argued that the existing commercial RDBMS offerings do not meet the needs of many important market segments
  • Some special purpose real-time, stream processing solutions (StreamBase, Vertica, VoltDB) have beat the RDBMS benchmart by +30x...


Readings:
Mike Stonebraker, One Size fits all