Oracle Flashback Technology

Oracle Flashback Features Explained

Oracle Flashback Technology provides a set of powerful features that allow users to view past states of data or rewind the database (or specific objects) to a previous point in time. These features offer efficient alternatives to traditional point-in-time recovery, significantly reducing recovery times and simplifying data retrieval.

1. Oracle Flashback Database

Oracle Flashback Database offers a more efficient alternative to traditional database point-in-time recovery (DBPITR). It allows you to revert your entire database (all datafiles) to its state at a past time. This process is much faster than conventional media recovery because it primarily uses "flashback logs" to undo changes, rather than restoring full datafiles from backup and applying extensive redo logs.

• Reliance: Flashback Database relies on Flashback logs, which are generated in the Flashback Recovery Area (FRA).

• Key Benefit: No need to restore datafiles from backup; fewer individual changes need to be reapplied from redo logs compared to conventional media recovery.

Managing Restore Points for Flashback Database:

Restore points are crucial for Flashback Database, allowing you to rewind to a specific, named point in time.

• View Existing Restore Points:

  set linesize 132 pages 80
  col name format a30
  col time format a32
  SELECT name,to_char(scn),time,guarantee_flashback_database FROM v$restore_point order by name;
  
  

• Creating Guaranteed Restore Points (Example in Data Guard): This example shows how to create guaranteed restore points on both primary and standby in a Data Guard setup.

  -- On Standby (DGMGRL CLI)
  DGMGRL> EDIT DATABASE 'Stadnby' SET STATE = 'APPLY-OFF';
  
  -- On Primary (DGMGRL CLI)
  DGMGRL> EDIT DATABASE 'Primary' SET STATE = 'TRANSPORT-OFF';
  
  -- On Standby (SQL*Plus)
  SQL> CREATE RESTORE POINT Test GUARANTEE FLASHBACK DATABASE;
  
  -- On Primary (SQL*Plus)
  SQL> CREATE RESTORE POINT Test GUARANTEE FLASHBACK DATABASE;
  
  -- On Primary (DGMGRL CLI)
  DGMGRL> EDIT DATABASE 'Primary' SET STATE = 'TRANSPORT-ON';
  
  -- On Standby (DGMGRL CLI)
  DGMGRL> EDIT DATABASE 'Stadnby' SET STATE = 'APPLY-ON' WITH APPLY INSTANCE = '';
  
  

• Performing Flashback Database to a Restore Point (Example in Data Guard):

  -- On Standby (DGMGRL CLI)
  DGMGRL> EDIT DATABASE 'Stadnby' SET STATE = 'APPLY-OFF';
  
  -- On Primary (DGMGRL CLI)
  DGMGRL> EDIT DATABASE 'Primary' SET STATE = 'TRANSPORT-OFF';
  
  -- Stop Databases on Primary and Standby
  -- (Standard shutdown commands for each instance)
  
  -- Start one of the instances in MOUNT mode (e.g., primary)
  -- SQL> STARTUP MOUNT;
  
  -- On Primary (SQL*Plus)
  SQL> FLASHBACK DATABASE TO RESTORE POINT Test;
  
  -- On Standby (SQL*Plus)
  SQL> FLASHBACK DATABASE TO RESTORE POINT Test;
  
  -- Start Databases on Primary and Standby (in normal mode)
  -- (Standard startup commands for each instance)
  
  -- On Primary (DGMGRL CLI)
  DGMGRL> EDIT DATABASE 'Primary' SET STATE = 'TRANSPORT-ON';
  
  -- On Standby (DGMGRL CLI)
  DGMGRL> EDIT DATABASE 'Stadnby' SET STATE = 'APPLY-ON' WITH APPLY INSTANCE = '';
  
  

2. Oracle Flashback Query

Oracle Flashback Query allows you to specify a target time (or SCN) and then run SQL queries against your database, viewing the results as they would have appeared at that past time. This is invaluable for recovering from accidental data modifications.

• Purpose: To retrieve the contents of lost or erroneously changed rows by querying data as it existed at a past point in time.

• Reliance: Relies on undo data.

• Example:

  SELECT * FROM EMPLOYEE AS OF TIMESTAMP TO_TIMESTAMP('2009-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = 'Adwin';
  
  

3. Oracle Flashback Version Query

Flashback Version Query lets you view all the versions of all the rows that ever existed in one or more tables within a specified time interval. It also provides metadata about each version, such as start/end SCN/time, operation type (INSERT, UPDATE, DELETE), and transaction ID.

• Purpose: To recover lost data values, audit changes to tables, and understand the history of row modifications.

• Reliance: Relies on undo data.

• Example:

  SELECT versions_startscn, versions_starttime,
         versions_endscn, versions_endtime,
         versions_xid, versions_operation,
         last_name, salary
  FROM employees
  VERSIONS BETWEEN TIMESTAMP
  TO_TIMESTAMP('2013-02-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND TO_TIMESTAMP('2014-12-18 18:00:00', 'YYYY-MM-DD HH24:MI:SS')
  WHERE first_name = 'Adwin';
  
  

4. Oracle Flashback Transaction Query

Flashback Transaction Query allows you to retrieve metadata and historical data for a specific transaction or for all transactions within a given time interval. A unique feature is the UNDO_SQL column, which shows the SQL code that is the logical opposite of the DML operation performed by the transaction, making it useful for reversing changes.

• Purpose: To audit transaction history, analyze changes made by specific transactions, and generate undo SQL.

• Reliance: Relies on undo data.

• Example:

  SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
  FROM flashback_transaction_query
  WHERE xid = HEXTORAW('000200030000002D'); -- Replace with your transaction ID
  
  

5. Oracle Flashback Table

Oracle Flashback Table enables you to return a table to its state at a previous point in time. This operation can be performed while the database remains online, undoing changes only to the specified table.

• Purpose: To quickly recover a table from logical corruption or accidental changes without performing a full database restore.

• Reliance: Relies on undo data.

• Example:

  FLASHBACK TABLE employee TO TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH24:MI:SS');
  
  

6. Oracle Flashback Drop

Flashback Drop reverses the effects of a DROP TABLE statement. When a table is dropped, Oracle does not immediately remove its associated space. Instead, the table is renamed (to a system-generated name) and, along with its associated objects (indexes, constraints, etc.), is placed in the database's Recycle Bin. The Flashback Drop operation recovers the table from this Recycle Bin.

• Purpose: To recover accidentally dropped tables.

• Examples:

  • Recover using Recycle Bin name:

    FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP;
    
    

  • Recover using original table name (if unique in Recycle Bin):

    FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;
    
    

  • Recover and rename:

    FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP RENAME TO HR.int2_admin_emp;
    
    

Underlying Mechanism: Flashback Table, Flashback Query, Flashback Transaction Query, and Flashback Version Query all fundamentally rely on undo data. Undo records store the information necessary to reconstruct data as it stood at a past time and to examine the history of changes. These undo records are primarily used for read consistency and transaction rollback, but they also serve as the foundation for these powerful flashback capabilities.