Recovering Accidentally Lost Data Using Oracle’s Flashback Query

by Sreeram Surapaneni

Extract

Recent studies show that nearly 50% of
system outages are due to human errors. The ability to recover from these user
errors using a simple, easy to use interface is challenging. Most database
products today do not offer any solutions, thus administrators have no option but
to recover the database using a previous backup. This not only requires the
database to be unavailable during the period of recovery but also leads loss of
valid transactions if the database to be restored to a point in time in the
past. Oracle 9i’s new feature, Flashback Query allows the users to view the old
image of data that was modified and committed by DML statement, without
requiring any structural changes to the database.

Introduction

Oracle database uses a version based read
consistency mechanism by saving an image of the data prior to making any
modifications. These changes are stored in the undo tablespace. If the users
decide not to commit the transaction, Oracle uses the data saved in the undo
tablespace to recreate the original data. The Flashback Query uses the same
mechanism to construct an image of the data, as it existed at a time in the
past. Oracle professionals can specify how long they wish to retain the undo
data using the UNDO_RETENTION parameter in the init.ora file and thereby can
control how far back a Flashback Query can go.

The Flashback query feature in an
application can be utilized by using either of the following two techniques.
Using the AS OF clause in SQL queries to specify a past time is the simplest
way to implement the Flashback query. This method allows you to perform
DDL operations such as creating and truncating tables, and DML operations such
as inserts and deletes, in the same session as queries using the AS OF clause. Alternatively, it can be
implemented using the DBMS_FLASHBACK
package but we must open a cursor before making a call to disable the Flashback
mode. We can fetch results from past data from the cursor, then issue INSERT or UPDATE statements against
the current state of the Database. This is illustrated in the next section with
examples.

Before ordinary users can take advantage of
this functionality, the Database Administrators must perform the following
actions.

  • Configure for Automatic undo management: set
    initialization parameter undo_management=auto and undo_tablespace= <undo
    tablespace name> in the init.ora file.

  • Specify undo retention time by setting
    undo_reention = <time in seconds> in the init.ora.

    For eg:


    undo_management=auto
    undo_retention=1200
    undo_tablespace=UNDOTBS

  • Grant execute privilege on dbms_flashback
    package to the users that need to perform Flashback Query.

  • Grant ‘FLASHBACK’ privilege on the table’s that
    users need to perform flashback query. Alternatively, ‘FLASHBACK ANY TABLE’
    system privilege to allow the user to use this feature on any table except data
    dictionary tables.

Please note that the flashback query is
turned off when the session ends, and if there is no explicit call to the
disable procedure.

Notes:

  • Flashback
    Query is supported by the export utility to export data
    which is consistent as of a specified point in
    time.

  • PL/SQL cursors opened in Flashback mode area
    available for DML after Flashback mode is disabled.

Latest Articles