Oracle Flashback Query and Flashback table

Flashback is a feature in oracle to enable you to return database object/s to a previous state without using oracle recovery methods. 

With Oracle flashback query

You can recover past data based on timestamp or SCN.
You can get the metadata queries(sql statements) that show changes done to the database.
You can recover dropped tables.
You can rollback a transaction and its dependent transactions while the database is online.

Flashback uses Automatic Undo Management feature of oracle to obtain metadata and historical data of transactions.

Below are the grants you would need to give to a schema to perform oracle query flashback operations.

GRANT FLASHBACK ANY TABLE TO HR; 
GRANT SELECT ANY TRANSACTION TO HR; 
GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO HR; 

IF oracle flashback query is to be enabled for all tables in the database, you need to enable supplemental logging as follows.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
To disable supplemental logging,you can use the DROP keyword.
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

Supplemental logging can also be enabled for a specific table as follows.
ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; 

To flashback the query to its previous state row movement is to be enabled and then flashback table is to be employed.
ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;

Below is a working example of how to flashback data from the employees table in HR schema.

SELECT ora_rowscn, first_name, salary FROM employees WHERE employee_id = 200; 
ora_rowscn = 7543000452858

UPDATE HR.employees 
SET salary = 5002 
WHERE   
employee_id = 200 AND 
ora_rowscn = 1322563; 

SELECT ora_rowscn, first_name, salary FROM employees WHERE employee_id = 200; 
ora_rowscn = 1322563;

Use flashback versions query to retrieve the different versions of specific rows that existed during a given time interval.

SELECT versions_startscn, versions_starttime, 
       versions_endscn, versions_endtime, 
       versions_xid, versions_operation, 
       first_name, salary 
FROM employees 
VERSIONS BETWEEN TIMESTAMP 
    TO_TIMESTAMP('2016-10-14 11:00:00', 'YYYY-MM-DD HH24:MI:SS') 
AND TO_TIMESTAMP('2016-10-14 11:10:00', 'YYYY-MM-DD HH24:MI:SS') 
WHERE employee_id = 200; 


SELECT versions_xid XID, versions_startscn START_SCN, 
  versions_endscn END_SCN, versions_operation OPERATION, 
  first_name, salary FROM hr.employees 
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE 
  where employee_id = 200; 

With the flashback timestamp information available you can flashback your data to the required point in time for a given table/set of tables of a given transaction.

FLASHBACK TABLE hr.employees TO TIMESTAMP 
   TO_TIMESTAMP('2016-10-26 12:23:00', 'YYYY-MM-DD HH24:MI:SS'); 


Similarly once you have the scn history you can flashback the data to a point in time before a given SCN.
FLASHBACK TABLE HR.<table_name> TO SCN <scn number>;

Using flashback transactions query you can retrieve metadata and historical data for a given transaction or for all transactions in a given time interval.

select operation,logon_user,undo_sql 
    from flashback_transaction_query 
where xid=HEXTORAW('0100020039030000');  -- XID as got from query above.

This query can be modified to use a version query as a subquery to associate each row of a previous version of data with a specific user and operation.
The timestamp range is limited by the UNDO_RETENTION parameter as data older than the time specified by UNDO_RETENTION will be getting cleared based on transaction volumes on the database.
select operation,logon_user,undo_sql 
    from flashback_transaction_query 
where xid IN ( 
SELECT versions_xid FROM hr.employees VERSIONS BETWEEN TIMESTAMP 
        TO_TIMESTAMP('2016-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND 
          TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS') 
    );

The undo_sql can also be applied to reverse the data. Care must be taken to apply these undo_sqls in the proper order to prevent data getting corrupted or inconsistent.

You can also  use dbms_flashback to get the the version of data prior to a given transaction.
Below is an example

You will need to call the following procedures/functions from DBMS_FLASHBACK for the following examples.

DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER 

Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
QUERY_SCN                      NUMBER                  IN

DBMS_FLASHBACK.ENABLE_AT_TIME 

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 QUERY_TIME                     TIMESTAMP               IN

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER RETURNS NUMBER; 

DBMS_FLASHBACK.PROCEDURE DISABLE; 

DECLARE 

sysscn      NUMBER; 
v_salary    NUMBER; 

BEGIN 

    sysscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; 
    update hr.employees set salary = 99910 WHERE  EMPLOYEE_ID = 200; 

    COMMIT; 
    
    select SALARY INTO v_salary From hr.employees WHERE  EMPLOYEE_ID = 200; 
    dbms_output.put_line('v_salary  ' || v_salary); 
    
--This will reverse the update execute above.
    DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(sysscn); 
    
        select SALARY INTO v_salary From hr.employees WHERE  EMPLOYEE_ID = 200; 
    dbms_output.put_line('v_salary  ' || v_salary); 
  
    DBMS_FLASHBACK.DISABLE; 

END; 


If you get the below error when trying to get a flashback version of a prior scn it means the transaction is not commited.

ORA-08183: Flashback cannot be enabled in the middle of a transaction 

ORA-06512: at "SYS.DBMS_FLASHBACK", line 12
ORA-06512: at line 15
08183. 00000 -  "Flashback cannot be enabled in the middle of a transaction"
*Cause:    user tried to do Flashback in the middle of a transaction
*Action:   do a commit

You will need to issue a commit to complete the transaction before you issue ENABLE_AT_SYSTEM_CHANGE_NUMBER for a prior scn.

To use dbms_flashback to rollback to a point in time you can use the below plsql black. For this you will need to know the timestamp to which you need to roll back the data.
You can use SCN_TO_TIMESTAMP in case the SCN to which the data to be rolled back is known.

DECLARE 

sysscn number; 
v_salary  NUMBER; 

BEGIN 


    sysscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; 
-- with this SCN you can use SCN_TO_TIMESTAMP to get the timestamp

    update hr.employees set salary = 99300 WHERE  EMPLOYEE_ID = 200; 

    COMMIT; 
    
    select SALARY INTO v_salary From hr.employees WHERE  EMPLOYEE_ID = 200; 

    dbms_output.put_line('v_salary  ' || v_salary); 
    
--    DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(sysscn); 
      dbms_flashback.enable_at_time(TO_TIMESTAMP( '28-10-2016 16:39:00','DD-MM-YYYY HH24:MI:SS')); 

  select SALARY INTO v_salary From hr.employees WHERE  EMPLOYEE_ID = 200; 
  
  dbms_output.put_line('v_salary  ' || v_salary); 
  
  DBMS_FLASHBACK.DISABLE; 

END; 

DBMS_FLASHBACK also has a TRANSACTION_BACKOUT procedure to rollback a transaction or a set of transactions to a previous state.
a good example of this is given in
http://www.oracleflash.com/31/Oracle-11g-DBMS_FLASHBACK-TRANSACTION_BACKOUT.html 


To get the timestamp for a given scn you can use the following
SELECT SCN_TO_TIMESTAMP('7543002541417') scn_time from dual; 

To get the scn for a given timestamp you can execute the below query
SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) CURRENT_SCN FROM DUAL; 

In case you try to get the SCN for a point in time (example : SYSTIMESTAMP - 10)which is too old you will get the following error.

ORA-08180: no snapshot found based on specified time 

ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1
08180. 00000 -  "no snapshot found based on specified time"
*Cause:    Could not match the time to an SCN from the mapping table.
*Action:   try using a larger time.

To get the current SCN from the database
SELECT CURRENT_SCN FROM V$DATABASE; 

Oracle Flashack Table Steps

To flashback a table to before DROP condition 

CREATE TABLE HR.BKP_EMPLOYEES AS SELECT * FROM HR.EMPLOYEES; 

SELECT * FROM HR.BKP_EMPLOYEES; 

DROP TABLE HR.BKP_EMPLOYEES; 

FLASHBACK TABLE HR.BKP_EMPLOYEES TO BEFORE DROP;

Oracle Flashback Database

Another flashback feature provided by Oracle is to flashback a database to a point it time without having to go through the hassles for a PITR.
It uses flashback logs to recover past versions of changed blocks. This along with information from ARCHIVE_LOGS can easily recover a database to a point in time in the past. The end result being similar to a point in time recovery, the advantage is that Flashback Database is much faster and less disruptive. This is particularly applicable for test and development environments where there is very high chances of data corruption and you want to revert the database to a preivous state.

For oracle flashback database, following should be considered 
Database should be in ARCHIVELOG mode.
db_recovery_file_dest to be set to an appropriate location for the flashback recovery files.
db_recovery_file_dest_size to be set to an appropriate size for the amount and size of the testing required.
db_flashback_retention_target to be set to an appropriate time, in mins, to retain flashbackability.

Run  'Alter database flashback on' only if there is a requirement to flashback the database to Any previous point in time.

To determine if flashback is enabled for the database you can run the following
select flashback_on from v$database;

Detailes steps for oracle flashback database will be provided in a later post.

Thank You!!!

Thought for the Day

Take risks in you life. If you win you can lead. If you lose you can guide.
Swami Vivekanand