Oracle 11g Flashback Technology (Part II)

Oracle11g Flashback (II)

Executive Summary

The second part on this Oracle Flashback Technology white paper focuses on advanced query options with case scenarios and various capabilities useful primarily to the system DBAs, without neglecting the usefulness for both the developers and development DBAs.

Use of Oracle Flashback Transaction Query with Oracle Flashback Version Query

In this scenario, the DBA performs the following actions via SQL statements, as shown:

DROP TABLE personnel;

CREATE TABLE personnel

       ( empid NUMBER PRIMARY KEY,
         empname VARCHAR2(16),
         salary NUMBER,
         comments VARCHAR2(4000)
       );

INSERT INTO personnel (empid, empname, salary, comments')
VALUES (119, 'Smith', 80000, ,'New employee');

COMMIT;

DROP TABLE dept;

CREATE TABLE dept ( deptid NUMBER, deptname VARCHAR2(32) );

INSERT INTO dept (deptid, deptname)
VALUES (50, 'Finance'); 

COMMIT;

Now personnel and dept have one row each. In terms of row versions, each table has one version of one row. Suppose that an erroneous transaction deletes empid 119 from table personnel:

UPDATE personnel
        SET salary = salary + 15000
 WHERE empid = 119;

INSERT INTO dept (deptid, deptname)
        VALUES (70, 'IT');

DELETE FROM personnel
 WHERE empid = 119;

COMMIT;

Later, a transaction reinserts empid 119 into the personnel table with a new employee name:

INSERT INTO personnel (empid, empname, salary)
VALUES (119, 'Noriega', 100000);

UPDATE personnel
        SET salary = salary + 20000
WHERE empid = 119; 

COMMIT;

After a careful analysis, the database administrator detects the application error and must diagnose the problem. The database administrator issues this query to retrieve versions of the rows in the personnel table that correspond to empid 119. The query uses Oracle Flashback Version Query pseudo-columns, as shown below:

SELECT  versions_xid XID,
                 versions_startscn START_SCN,
                 versions_endscn END_SCN,
                 versions_operation OPERATION,
                 empname,
                 salary
  FROM   personnel
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE WHERE empid = 119;

Results are similar to:

XID               START_SCN    END_SCN O EMPNAME              SALARY
---------------- ---------- ---------- - ---------------- ----------
08001100B2300000  210093467 210093487  I Noriega              120000   
040002002B220000  210093453 210093457  D Smith                 80000
0900120096800000  210093379 210093389  I Smith                 80000
 
3 rows selected.

Indeed, the results table rows are in descending chronological order. The third row corresponds to the version of the row in the table personnel that was inserted in the table when the table was created. The second row corresponds to the row in personnel that the erroneous transaction deleted. The first row corresponds to the version of the row in personnel that was reinserted with a new employee name.

The database administrator identifies transaction 040002002B220000 as the erroneous transaction and uses Oracle Flashback Transaction Query to audit all changes made by this transaction:

SELECT xid,

               start_scn,

               commit_scn,

               operation,

               logon_user,

               undo_sql

 FROM  flashback_transaction_query

WHERE xid = HEXTORAW('040002002B220000');

Results are similar to:

XID               START_SCN COMMIT_SCN OPERATION LOGON_USER
---------------- ---------- ---------- --------- ------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
 
040002002B220000   210093453   210093457 DELETE    HCM
insert into "HCM"."personnel"("empid","EMPNAME","SALARY",”COMMENTS”) values ('119','Smith','80000', 'New employee');
 
040002002B220000   210093453   210093457 INSERT    HCM
delete from "HCM"."DEPT" where ROWID = 'AAATjuAAEAAAAJrAAA';
 
...

 
4 rows selected.

In order to make the result set more readable, the administrator uses these SQL*Plus commands:

COLUMN operation FORMAT A10
COLUMN table_name FORMAT A11
COLUMN table_owner FORMAT A12

And subsequently, to see the details of the erroneous transaction and all subsequent transactions, the database administrator performs this query:

SELECT  xid, 

        start_scn, 

        commit_scn, 

        operation, 

        table_name, 

        table_owner

  FROM  flashback_transaction_query
 WHERE  table_owner = 'HCM'
   AND  start_timestamp >=
        TO_TIMESTAMP ('2009-12-31 14:30:00','YYYY-MM-DD HH:MI:SS');

The results would look similar to:

XID               START_SCN  COMMIT_SCN  OPERATION  TABLE_NAME  TABLE_OWNER
---------------- ----------  ----------  ---------- ----------- ------------
02000E0074200000   210093369  210093377  INSERT     DEPT        HCM 
040002002B220000   210093453  210093457  DELETE     PERSONNEL   HCM
040002002B220000   210093453  210093457  INSERT     DEPT        HCM
040002002B220000   210093453  210093457  UPDATE     PERSONNEL   HCM
…
 
8 rows selected.

Use of ORA_ROWSCN

A special Oracle11g Flashback feature is the use of ORA_ROWSCN, which is a pseudo-column in any table that is not fixed or external. It represents the SCN of the most recent change to a given row in the current session; that is, the most recent COMMIT operation for the row in the current session. For example:

SELECT  ora_rowscn, 

  last_name, 

  salary

  FROM  personnel
 WHERE  empid = 119;

Result is similar to:

ORA_ROWSCN LAST_NAME                     SALARY
---------- ------------------------- ----------
    974322 Smith                          80000

Besides, the ORA_ROWSCN pseudo-column can also be used in tables with Virtual Private Databases (VPD) for further control.

Use of ORA_ROWSCN and Tables with Virtual Private Database (VPD)

When a VPD policy is added to a table, it is no longer possible to select the ORA_ROWSCN pseudo-column. However, because ORA_ROWSCN is available inside the policy function, the DBA can:

1. Create a function that returns a row SCN
   
2. In the policy predicate function, add a predicate that stores the row SCN in the context that the function uses while processing rows. For instance:
   

||' AND fun_ora_rowscn('||object_name||'.ora_rowscn) = 1'

3. Use the function to fetch the row. For example:
   

SELECT  t2.*, 

  fun_get_rowscn(t2.rowid) "ORA_ROWSCN" 

  FROM  example_table t2;

Function Used to Return Row SCN from Table that has VPD

-- Creates the context that the function will use when processing rows:

 
CREATE OR REPLACE FUNCTION fun_ora_rowscn
 (p_rowscn IN NUMBER)
 RETURN NUMBER
AS
BEGIN
  DBMS_SESSION.SET_CONTEXT('STORE_ROWSCN','ROWSCN',p_rowscn);
  RETURN 1;
END;
/

 
CREATE CONTEXT store_rowscn USING fun_ora_rowscn;
 
-- Creates the function that returns row SCN for each row:
 
CREATE OR REPLACE FUNCTION fun_get_rowscn
  (p_rid IN ROWID)
  RETURN VARCHAR2
AS
BEGIN 
  RETURN sys_context('STORE_ROWSCN','ROWSCN');
END;
/

DBMS_FLASHBACK Package

Both DBAs and developers can use the DBMS_FLASHBACK package, which provides the same functionality as Oracle Flashback Query, but Oracle Flashback Query is sometimes more convenient.
To use the DBMS_FLASHBACK package in the PL/SQL code, the administrator should:

1. Specify a past time by invoking either DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER.
   
2. Perform regular queries (that is, queries without special flashback-feature syntax such as AS OF); and not perform DDL or DML operations.
   

The database is queried at the specified past time.

3. Return to the present by invoking DBMS_FLASHBACK.DISABLE.
   

Invoke DBMS_FLASHBACK.DISABLE before invoking DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER again. The DBA cannot nest enable/disable pairs.

To use a cursor to store the results of queries, it is required for the user to open the cursor before invoking DBMS_FLASHBACK.DISABLE. After storing the results and invoking DBMS_FLASHBACK.DISABLE, it is absolutely possible to:

• Perform INSERT or UPDATE operations to modify the current database state by using the stored results from the past.
• Compare current data with the past data:

1. After invoking DBMS_FLASHBACK.DISABLE, open a second cursor.  
2. Fetch from the first cursor to retrieve past data;
3. fetch from the second cursor to retrieve current data.
4. Get the difference: A query obtaining the difference between result sets (before and after states) can be accomplish by storing the past data in a temporary table utilizing the MINUS or UNION to filter the data accordingly.

Concluding Remarks

Oracle11g Flashback provides various features to flashback a transaction with version query, also provides ORA_ROWSCN for all tables that are neither fixed nor external, and the DBMS_FLASHBACK package useful to both developers and DBAs.