FLASHBACK TECHNOLOGY IN LIEU OF COMPLEX DATABASE RECOVERY

Oracle11g Flashback
Executive Summary
Oracle Flashback technology is best suited to attain readiness and preparedness in scenarios such as human errors, and point-in-time database state restore without the need for conventional point-in-time recovery. Most importantly, it can be used to discriminate among backup and recovery planned strategies, including real-time mirroring through which a full database snapshot can actually be restored at any time, with nearly no downtime. Similarly, there is the convenience that this is an Oracle Enterprise Edition provided feature rather than an expensive and more comprehensive mirroring technology with both full snapshot and thing provisioning capabilities, in general, mor customizable and granular than those technologies. Besides, the fact that Oracle Flashback relies significantly on Automatic Undo Management (AUM), as discussed, implies the understanding of that concept as balance between the space provided in UNDO tablespace and the time provided through the UNDO_RETENTION setting in comparison to the actual need of undo resources determined by the effect of transactional workload and relevant duration involved, which is quite different from the traditional MANUAL undo management model which uses a round-robin algorithm instead.

Introduction

Oracle Flashback Technology is a set of features (resources and capabilities) that allow DBAs to reach past states of database objects, including bringing the database objects back to a previous state, without using point-in-time media recovery. Flashback recovery is quite cost effective and convenient in comparison to conventional backup and recovery procedures and operations.

With flashback features, it is possible:

• Perform queries that return metadata exhibiting a detailed history of changes to the database
  
• Perform queries that return past data
  
• Recover tables or rows to a previous point in time
  
• Automatically track and archive transactional data changes
  
• Roll back a transaction and its dependent transactions while the database remains online
  

Flashback features utilize the Automatic Undo Management (AUM) to get metadata and historical data for transactions. For instance, if a user runs an UPDATE statement to change a retail_price from 2000 to 5000, then Oracle Database stores the value 2000 as undo data, which is persistent in database beyond shutdown.

In addition to this, Oracle Database uses undo data to perform these actions:

• Roll back active transactions
• Recover terminated transactions by using database or process recovery
• Provide read consistency for SQL queries

Application Development Features

The key application development features, include:

Oracle Flashback Query

A developer or DBA would use this feature to retrieve data for an earlier time that he or she specified with the AS OF clause of the SELECT statement.

Oracle Flashback Version Query

A DBA or developer could use this feature to retrieve metadata and historical data for a specific time interval (for example, to view the set of rows in a table that ever existed for a given period of time). Metadata for each row version includes start and end time, type of change operation, and identity of the transaction that created the row version. To create an Oracle Flashback Version Query, they would need to use the VERSIONS BETWEEN clause of the SELECT statement.

Oracle Flashback Transaction Query

A developer or DBA can use this feature to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval; in order to perform an Oracle Flashback Transaction Query, they need to select from the static data dictionary view FLASHBACK_TRANSACTION_QUERY.

DBMS_FLASHBACK Package

Normally, a developer DBA could use this feature to set the internal Oracle Database clock to an earlier time so that the DBA can examine data that was current at that time, or to roll back a transaction and its dependent transactions while the database remains online.

Flashback Transaction

Development DBAs use Flashback Transaction to roll back a transaction and its dependent transactions while the database remains online. This recovery operation uses undo data to create and run the corresponding compensating transactions that return the affected data to its original state.

Flashback Data Archive (Oracle Total Recall)

Development DBAs can utilize the Flashback Data Archive to automatically track and archive both regular queries and Oracle Flashback Query, providing SQL-level access to the versions of database objects without getting the ORA-001555 snapshot-too-old error.

Database Administration Features

These flashback features are mainly for data recovery and used normally by a DBA.

Oracle Flashback Table

A DBA uses this capability to restore a table to its state at a previous point in time. The DBA can restore a table while the database is on line, undoing changes solely to the specified table.

Oracle Flashback Drop

The DBA utilizes this feature to recover a dropped table. This feature can reverse the effects of a DROP TABLE statement.

Oracle Flashback Database

The DBA can use this feature to quickly return the database to an earlier point in time, by undoing all of the changes that have taken place since then. This occurs rather fast, since the DBA needs not to have database backups restored.

Database Configuration for Oracle Flashback Technology

Planning Flashback configuration is quite important. So, a DBA must first perform the set of configuration tasks for:

• Configuring the Database for Automatic Undo Management
• Configuring the Database for Oracle Flashback Transaction Query
• Configuring the Database for Flashback Transaction
• Enabling Oracle Flashback Operations on Specific LOB Columns
• Granting Necessary Privileges

Database Configuration for Automatic Undo Management

In order to configure the database for Automatic Undo Management (AUM), the DBA has to:

• Create an undo tablespace (or use an existing one) with enough space to keep the required data for flashback operations, and planning on capacity and growth accordingly.
  
• Enable Automatic Undo Management, setting the following initialization parameters accordingly:
  
  • UNDO_MANAGEMENT
  • UNDO_TABLESPACE
  • UNDO_RETENTION
  
  
  If the undo tablespace is fixed in size, Oracle Database automatically tunes the system to give the undo tablespace the best possible undo retention. Otherwise, if extensible, Oracle Database retains undo data longer than the longest query duration and the low threshold of undo retention specified by the UNDO_RETENTION parameter.
  The DBA can query the V$UNDOSTAT.TUNED_UNDORETENTION view to verify the amount of time for which undo is retained for the current undo tablespace. Further information can also be obtained from the V$UNDOSTAT view.
  It is important to understand that setting UNDO_RETENTION does not guarantee that unexpired undo data is not discarded. If the system needs more space, Oracle Database is able to overwrite unexpired undo with more recently generated undo data.
  
• Specify the RETENTION GUARANTEE clause for the undo tablespace to ensure that unexpired undo data is not discarded.

Database Configuration for Oracle Flashback Transaction Query

In order to configure a database for the Oracle Flashback Transaction Query feature, the DBA needs to:

• Ensure that Oracle Database is running with version 10.0 compatibility.
• Enable supplemental logging using the statement:

  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database Configuration for Flashback Transaction

To configure the database for the Flashback Transaction feature, the database administrator needs to:

• With the database mounted but not open, enable ARCHIVELOG:
  

  ALTER DATABASE ARCHIVELOG;

• Open at least one archive log:
  

  ALTER SYSTEM ARCHIVE LOG CURRENT;

• If not done, enable minimal and primary key supplemental logging:
  

  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
  
  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

• When tracking foreign key dependencies, enable foreign key supplemental logging:
  

  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

If the database contains too many foreign key constraints, enabling foreign key supplemental logging may not be worth the performance overhead.

Oracle Flashback Operations on Specific LOB Columns

In order to enable flashback operations on specific LOB columns of a table, the DBA must use the ALTER TABLE statement with the RETENTION option.

Because undo data for LOB columns can be voluminous, the DBA has to preselect the LOB columns to use with flashback operations.

Necessary Privilege Grants

The DBA needs to grant privileges to users, roles, or applications that must use these flashback features.

For Oracle Flashback Query and Oracle Flashback Version Query

In order for a DBA to allow access to specific objects during queries, he needs to grant FLASHBACK and SELECT privileges on those objects; while to allow queries on all tables, he has to grant the FLASHBACK ANY TABLE privilege.

For Oracle Flashback Transaction Query

Grant the SELECT ANY TRANSACTION privilege.

To allow execution of undo SQL code retrieved by an Oracle Flashback Transaction Query, grant SELECT, UPDATE, DELETE, and INSERT privileges for specific tables.

For DBMS_FLASHBACK Package

To use the DBMS_FLASHBACK package, a grant to EXECUTE privilege on DBMS_FLASHBACK is required.

For Flashback Data Archive (Oracle Total Recall)

In order to permit for a specific user to enable Flashback Data Archive on tables, using a specific Flashback Data Archive, the DBA must grant the FLASHBACK ARCHIVE object privilege on that Flashback Data Archive to that user. Likewise, to grant the FLASHBACK ARCHIVE object privilege, the DBA needs to either be logged on as SYSDBA or have FLASHBACK ARCHIVE ADMINISTER system privilege.

Besides, in order to support the execution of these statements, the DBA must isssue a grant of the FLASHBACK ARCHIVE ADMINISTER system privilege:

• CREATE FLASHBACK ARCHIVE
• ALTER FLASHBACK ARCHIVE
• DROP FLASHBACK ARCHIVE

Also, to grant the FLASHBACK ARCHIVE ADMINISTER system privilege, the database administrator must be logged on as SYSDBA.

To create a default Flashback Data Archive, using either the CREATE FLASHBACK ARCHIVE or ALTER FLASHBACK ARCHIVE statement, the DBA must be logged on as SYSDBA.

To disable Flashback Data Archive for a table that has been enabled for Flashback Data Archive, the user must either be logged on as SYSDBA or have the FLASHBACK ARCHIVE ADMINISTER system privilege.

Oracle Flashback Query (Using SELECT AS OF)

To begin with, use Oracle Flashback Query, use a SELECT statement with an AS OF clause. Oracle Flashback Query retrieves data as it existed at an earlier time. The query explicitly references a past time through a time stamp or System Change Number (SCN). It returns committed data that was current at that point in time.

Uses of Oracle Flashback Query include:

• Simplifying application design by removing the need to store some kinds of temporal data.
  Oracle Flashback Query lets DBAs retrieve past data directly from the database.
  
• Applying packaged applications, such as report generation tools, to past data.
  
• Recovering lost data or undoing incorrect, committed changes.
  For instance, if a user mistakenly deletes or updates certain rows, and then commits them, the DBA can immediately undo this human error.
  
• Comparing current data with the corresponding data at an earlier time.
  For instance, the DBA can run a daily report that shows the change in data from yesterday. Then, the DBA can compare individual rows of table data or find intersections or unions of sets of rows.
  
• Providing self-service error correction for an application, therefore enabling users to undo and correct their errors.
  
• Checking the state of transactional data at a particular time.

Case Study on Examining and Restoring Past Data

Assume that the DBA discovered at 4:30 AM that the row for an employee named Smith was deleted from the personnel table, and he knows that the previous night at 7:30PM the data for Smith was correctly stored in the database. You can use Oracle Flashback Query to examine the contents of the table at 7:30 PM to find out what data was lost. If appropriate, you can restore the lost data.

The employee record at 7:30PM March 21, 2007

Retrieving a Lost Row with Oracle Flashback Query

SELECT * 
  FROM personnel
  AS OF TIMESTAMP
  TO_TIMESTAMP('2012-03-21 07:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE UPPER(last_name) = 'SMITH';

Restoring a Lost Row After Oracle Flashback Query

INSERT INTO personnel 
 (
  SELECT * 
    FROM personnel
    AS OF TIMESTAMP
    TO_TIMESTAMP('2012-03-21 07:30:00', 'YYYY-MM-DD HH:MI:SS')
   WHERE UPPER(last_name) = 'SMITH'
 );
Oracle Flashback Query Guidelines 
A DBA can perform the following tasks, namely:

• Specify or omit the AS OF clause for each table and specify different times for different tables.
  
• Use the AS OF clause in queries to perform data definition language (DDL) operations (such as creating and truncating tables) or data manipulation language (DML) statements (such as INSERT and DELETE) in the same session as Oracle Flashback Query.
  
• Use the result of Oracle Flashback Query in a DDL or DML statement that affects the current state of the database, use an AS OF clause inside an INSERT or CREATE TABLE AS SELECT statement.
  
• If a possible 3-second error (maximum) is important to Oracle Flashback Query in your application, use an SCN instead of a time stamp.
  
• Create a view that refers to past data by using the AS OF clause in the SELECT statement that defines the view.
  If the DBA specifies a relative time by subtracting from the current time on the database host, the past time is recalculated for each query. For instance:
  

  CREATE VIEW v_personnel_past_hour 
    AS
    SELECT * 
      FROM personnel
      AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);  

• The DBA can use the AS OF clause in self-joins, or in set operations such as INTERSECT and MINUS, to extract or compare data from two different times.
  You can store the results by preceding Oracle Flashback Query with a CREATE TABLE AS SELECT or INSERT INTO TABLE SELECT statement. For instance, this query reinserts into table personnel the rows that existed an hour ago:
  

  INSERT INTO personnel
      (
        ( SELECT * FROM personnel
          AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE)
         )
        MINUS 
      SELECT * FROM personnel
      );

Oracle Flashback Version Query

DBAs can use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a COMMIT statement is executed.

Specify Oracle Flashback Version Query using the VERSIONS BETWEEN clause of the SELECT statement. The syntax is:

VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}

where start and end are expressions representing the start and end, respectively, of the time interval to be queried. The time interval includes (start and end).

Oracle Flashback Version Query Row Data Pseudocolumns

Pseudocolumn Name	Description
VERSIONS_STARTSCN VERSIONS_STARTTIME	It describes the starting System Change Number (SCN) or TIMESTAMP when the row version was created. This pseudocolumn identifies the time when the data first had the values reflected in the row version. Use this pseudocolumn to identify the past target time for Oracle Flashback Table or Oracle Flashback Query. Indeed, if this pseudocolumn is NULL, then the row version was created before start.
VERSIONS_ENDSCN VERSIONS_ENDTIME	The SCN or TIMESTAMP when the row version expired. If this pseudocolumn is NULL, then either the row version was current at the time of the query or the row corresponds to a DELETE operation.
VERSIONS_XID	This is the identifier of the transaction that created the row version.
VERSIONS_OPERATION	The operation performed by the transaction: I for insertion, D for deletion, or U for update, denoting the row version.

Here is a typical use of Oracle Flashback Version Query:

SELECT versions_startscn, 
       versions_starttime,
       versions_endscn, 
       versions_endtime,
       versions_xid, 
       versions_operation,
       last_name, salary
  FROM personnel
  VERSIONS BETWEEN TIMESTAMP
      TO_TIMESTAMP('2007-03-21 19:30:00', 'YYYY-MM-DD HH24:MI:SS')
  AND TO_TIMESTAMP('2007-03-22 04:30:00', 'YYYY-MM-DD HH24:MI:SS')
  WHERE first_name = 'Anthony';

Oracle Flashback Transaction Query

DBA can use Oracle Flashback Transaction Query to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval. Oracle Flashback Transaction Query queries the static data dictionary view FLASHBACK_TRANSACTION_QUERY, whose columns are described in Oracle Database Reference. The column UNDO_SQL shows the SQL code that is the is the logical opposite of the DML operation performed by the transaction. The following statement queries the FLASHBACK_TRANSACTION_QUERY view for transaction information, including the transaction ID, the operation, the operation start and end SCNs, the user responsible for the operation, and the SQL code that shows the logical opposite of the operation:

SELECT xid, 
       operation, 
       start_scn, 
       commit_scn, 
       logon_user, 
       undo_sql
FROM  flashback_transaction_query
WHERE xid = HEXTORAW('000400070000004F');

This statement uses Oracle Flashback Version Query as a subquery to associate each row version with the LOGON_USER responsible for the row data change:

SELECT xid,
               logon_user
FROM flashback_transaction_query
WHERE xid IN ( SELECT versions_xid
                                FROM personnel
                               VERSIONS BETWEEN
                              TIMESTAMP TO_TIMESTAMP('2007-03-21 19:30:00', 'YYYY-MM-DD HH24:MI:SS')
                AND TO_TIMESTAMP('2007-03-22 04:30:00', 'YYYY-MM-DD HH24:MI:SS') );
Concluding Remarks

It is important to understand the relevance of appropriate settings for Automatic Undo Management (AUM) to attain preparedness and readiness in the event that an Oracle Flashback can take place accordingly without any issues, from which some possible scenarios will be discussed in the next edition of this blog series. Understanding Oracle Flashback Technology allows DBAs to attain good log Flashback mining skills practical in a dynamic recovery or business process strategy.