Data Versioning

New to the Oracle Database 10g are expanded Flashback capabilities. See Chapter 10 of the Oracle High Availability Architectures and Best Practices product documentation for details. The Flashback Versioning feature allows the user to view data as it has evolved over time. Depending on your auditing requirements, this may be precisely what you need.

Flashback Version Query

To illustrate this capability, refresh the data table and issue a 10 percent raise for everyone in the table:

scott@KNOX10g> -- Refresh data scott@KNOX10g> TRUNCATE TABLE emp_copy; Table truncated. scott@KNOX10g> INSERT INTO emp_copy   2    SELECT * FROM emp; 14 rows created. scott@KNOX10g> COMMIT ; Commit complete. scott@KNOX10g> -- Give everyone a 10% raise scott@KNOX10g> UPDATE emp_copy   2     SET sal = sal * 1.1; 14 rows updated. scott@KNOX10g> COMMIT ;

Behind the scenes, it appears the database has logged the updates. To access the data, you can use the following:

scott@KNOX10g> -- Show database record of values scott@KNOX10g> COL ename format a6 scott@KNOX10g> COL sal format a8 scott@KNOX10g> COL "Start" format a12 scott@KNOX10g> COL "End"   format a12 scott@KNOX10g> COL "XID" format a17 scott@KNOX10g> COL operation format a9 scott@KNOX10g> SELECT   ename,   2           TO_CHAR (sal) sal,   3           DECODE (versions_operation,   4                   'I', 'Insert',   5                   'U', 'Update',   6                   'D', 'Delete') operation,   7           versions_xid "XID",   8           TO_CHAR (versions_starttime,   9                    'MM/DD HH24:MI') "Start",  10           TO_CHAR (versions_endtime,  11                    'MM/DD HH24:MI') "End"  12      FROM emp_copy  13     VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE  14     WHERE deptno = 20  15  ORDER BY 1, 2; ENAME  SAL      OPERATION XID               Start        End ------ -------- --------- ----------------- ------------ ------------ ADAMS  1100                                              04/20 16:16 ADAMS  1210     Update    04000F00390B0000  04/20 16:16 FORD   3000                                              04/20 16:16 FORD   3300     Update    04000F00390B0000  04/20 16:16 JONES  2975                                              04/20 16:16 JONES  3272.5   Update    04000F00390B0000  04/20 16:16 SCOTT  3000                                              04/20 16:16 SCOTT  3300     Update    04000F00390B0000  04/20 16:16 SMITH  800                                               04/20 16:16 SMITH  880      Update    04000F00390B0000  04/20 16:16 10 rows selected.

The pseudocolumns and syntax that asks the database for the versioned data are in bold. This shows the old data, the new data, the type or operation, when the operation occurred, and that the update was part of the same transaction. Alternatively, the start and end values can be based on SCN.

Flashback Transaction Query

You can now use another feature called Flashback Transaction Query. The transaction ID returned in the preceding query can be used to get additional information stored in the FLASHBACK_TRANSACTION_QUERY view:

system@KNOX10g> COL table_owner format a11 system@KNOX10g> COL table_name format a10 system@KNOX10g> COL operation format a10 system@KNOX10g> COL logon_user format a10 system@KNOX10g> SELECT DISTINCT table_owner,   2                  table_name,   3                  operation,   4                  logon_user   5             FROM flashback_transaction_query   6            WHERE xid =   7                       HEXTORAW ('04000C00340B0000')   8              AND table_name IS NOT NULL; TABLE_OWNER TABLE_NAME OPERATION  LOGON_USER ----------- ---------- ---------- ---------- SCOTT       EMP_COPY   UPDATE     SCOTT

The Flashback capabilities, which allow you to restore data very efficiently, are discussed in the Oracle High Availability document. The view stores the SQL statements needed to recover the data to its original state, as you can see:

system@KNOX10g> SELECT undo_sql     2    FROM flashback_transaction_query   3   WHERE xid = HEXTORAW ('0400150012010000')   4     AND table_name IS NOT NULL     5     AND ROWNUM <= 1; UNDO_SQL   ---------------------------------------------------------------   update "SCOTT"."EMP_COPY" set "SAL" = '1300' where ROWID =  AAAMa1AAEAAACQUAAN';

If you wanted to recover the original salary values, you could run the following:

system@KNOX10g> -- current data   system@KNOX10g> SELECT ename, sal FROM scott.emp_copy     2   WHERE deptno = 20; ENAME             SAL   ---------- ----------   SMITH             880   JONES          3272.5   SCOTT            3300   ADAMS            1210   FORD             3300     system@KNOX10g> -- recover data   system@KNOX10g> DECLARE      2    l_undo_sql  VARCHAR2 (32767);   3  BEGIN    4    FOR rec IN      5      (SELECT undo_sql     6         FROM flashback_transaction_query    7        WHERE xid = HEXTORAW ('0400150012010000')   8          AND table_name IS NOT NULL)   9    LOOP    10      l_undo_sql := REPLACE (rec.undo_sql, ';', '');  11      EXECUTE IMMEDIATE l_undo_sql;  12    END LOOP;  13   14    COMMIT;  15  END;  16  /     PL/SQL procedure successfully completed. system@KNOX10g> SELECT ename, sal FROM scott.emp_copy     2   WHERE deptno = 20; ENAME             SAL   ---------- ----------   SMITH             800   JONES            2975 SCOTT            3000   ADAMS            1100   FORD             3000

The ability to recover the data isn’t limited to the transactions. The database supports additional flashback modes: Flashback Table, which allows you to recover an entire table to a point in time; Flashback Drop, which allows you to restore tables that were dropped; and Flashback Database, which recovers the entire database to a point in time. All of these may prove beneficial if a hacker attacks your database and starts corrupting or destroying data.

All of the flashback technology, except Flashback Database, is built on Oracle’s multiversion read consistency implementation. This means there is no auditing performance penalty. Oracle is always “logging” the changes. The flashback operations are a new way of exploiting an implementation design that has been available for several years.

Note 

The flashback features use the undo management system for data access. The data is therefore not permanent. Its lifetime is dependent on the UNDO_RETENTION value and the UNDO_MANAGEMENT initialization variables. To persist the data, you will have to copy it from the Flashback areas.