Flashback-Related New Features - Oracle 11g

Oracle 9i Data base and Oracle Data base 10g introduced and developed several flashback-related features that enhanced the ability of DBAs and developers to perform the logical repair of data, besides providing means to produce “as-if” reports using historical data.In Oracle Database 11g, Oracle takes the flashback concept further by giving you two very useful features.

The flashback data archive,which is the underlying technology behind the new Oracle Total Recall option,lets you track, store, and manage historical changes in data (that is,all versions of a table’s rows over time) and lets you easily query the historical data.

The flashback transaction backout feature lets you back out unwanted trans actions effortlessly with a one-step transaction backout operation.We’ll review the flashback data archive feature in the following section, followed by a discussion of the flash back transaction backout feature.

Total Recall and the Flashback Data Archive

Oracle Database 10g release introduced several flashback-related features that helped you perform various logical data correction and recovery tasks, such as recovering an accidentally dropped table or rewinding the database or transaction to a previous point in time.To perform their magic,all flashback features(except for flash back data base,which depends on flash backlogs) rely on the undo data stored in the undo tablespace. During every transaction that involves a change in a table’s data, the Oracle server copies “old” pre-change data to the undo segments in the undo tablespace.This undo data is useful in recovering databases, in providing data consistency, and, more important for this discussion, in help ing roll back un wanted transactions before you commit the changes.

Although the undo data is stored in a tablespace and thus is of a per sistent nature, there’s a limit on how far back you can go in terms of ret rieving older data—the undo tablespace can’t serve as a historical archive of all the changes that were made to a table’s data.Although the undo tablespace in an Oracle database performs several fun ctions,the most important of which is the maintenance of transaction consis tency,the undo management’s main function isn’t to serve as an archival record of all changes made to data over time.

In addition to the problem of a space-restricted amount of stored undo data, another limitation of undo data is that you can’t collect undo data for a limited set of tables or transactions.Let’s say you are interested in tracking changes in only two tables over time. Oracle’s undo management feature doesn’t let you specify that only the undo of these two important tables be saved—it’s an all-or-nothing deal when it comes to storing undo data.

Oracle Database 11g takes flashback technology further by introducing a major new flashback-related feature, called the flashback data archive. Oracle actually calls the new feature Total Recall, with flash back data archive as the underlying technology.The flash back data archive feature provides a new time dimension to your data, enabling you to automatically track and record all changes made to a table row securely and effortlessly.

In some types of applications,such as applications dealing with compliance reporting and audit reports, sometimes you’d need to examine the entire history of a piece of data.That is, you’d need to have access to the entire history of a table row, from its inception to the present time,showing all the changes that row went through.

If you aren’t using the flash back data archive feature, you can use a flashback query when you want to see some historical information, but the SCN or the timestamp you specify in the flashback query must be less than the duration of your undo retention setting for the data base.

Total Recall and the Flashback Data Archive

Flash back data archiving removes this limitation on data retention in herent in undo mana gement as currently implemented by Oracle—you can retain historical data for as long as you want,without regard for the length of undo retention or the amount of flashback logs.

Regulatory oversight and compliance requirements due to regulations such as HIPAA and Sarbanes-Oxley have led to a serious need for retaining data over long periods of time. Long retention periods that span several years of time mean you need an efficient and securelong-term history management system to assure compliance with the new regulations.Current methods include implementing a his torical datamanagement system at the application level or using triggers to archive data.Using business logic to implement his torical data management leads to greater application complexity, making it hard to deal with changes in retention requirements and database upgrades, besides lacking a tamper-proof method of secur ing the data. Triggers involve a performance overhead and lack a central manage ment interface.

The flashback data archive feature overcomes these limitations of current techniques to maintain historical data.Using this feature,you can have the data base automatically track all changes to a specified table or a set of tables and store and maintain an archive of historical data.You can implement archiving both for new and existing tables.Since all the historical data pertaining to any given row is stored in the flash back data archive,you can easily use “as-if” historical flashback SQL queries on the data,either to track the changes in a record or to perform decision support tasks.By storing changes to tracked tables over long periods of time, you can always perform a query to check the data as of a time that’s way in the past, without worrying about the dreaded “snapshot too old” error.In addition, you can employ the flashback data narchive feature to enforce digital shredding policies—all unwanted data will simply disappear after the specified retention period is up.

You can profitably employ the flashback data archive feature for the following purposes:

    • Change tracking
    • Integrated lifecycle management (ILM)
    • Auditing
    • Generating reports
    • Compliance and reporting

The database server automatically stores changes made to any selected table in the flashback data archive. You can retain data long term,for many years,or forever,if you want.Once you save the data,you can’t modify it in any way since the flash back data archive allows only a read-only access to the historical data it stores.You can, however,purge some or all of the data in the flash back data archive according to your guidelines.

Total Recall and the Flashback Data Archive

What Is the Flashback Data Archive?

A flashback data archive is a new database object that holds historical data for one or several tables,along with the storage retention and purging polices for that data.The archive is simply one or more tablespaces that you dedicate for saving all transact ional changes for one or more tables in the data base.The database writes all original data in the buffer cache to the undo table space as undo data.A new background process named Flashback Data Archiver Process (FBDA) will collect and write the original data to a flash back data archive, thus creating a history ofall table data.To enable flashback archiving,you must either create a table with the flash back data archive clause or use the alter table statement to enable archiving for existing tables.

Note the following key facts about the flashback data archives:

    • There’s a one-to-many relationship between a flashback data archive and tables.
    • You can have multiple flash back data archives in your data base,say,one for very long term durations such as five years and the others for shorter time retention periods such as six months or a year.
    • You can denote a particular tablespace as the default flash back data archive.
    • The new Oracle background process (FBDA) collects the original data from the buffer cache and records it in the tablespace designated for a flashback data archive.
    • Oracle will automatically purge the flashback data archive the day after the retention period expires.

Here’s a summary of the architecture of the flash back data archive:once you enable a table for archiving,the FDAP creates an internal history table for that table.The history table will have all the columns of the original table plus some time stamp columns to track the trans action changes.

When you update or delete a row in the tracked table,the FDAP process marks the trans actions and the corresponding undo records for archival.The history table will have the before image of the row before you committed the delete or update transaction.An insert transaction won’t cause any records to be added to the history table, since that new row doesn’t have a prior history.

The FDAP process wakes up at system-determined intervals (the default is five minutes) and copies the undo data for the marked transactions to the history table.So when you make a change to a table, that change won’t immediately show up in the history table. It’ll take a few minutes for you to see the changes in the history table.If the database is generating a high amount of undo data, it adjusts the sleep time of the FDAP background process so it can read undo data from the buffer cache faster.

Until the FDAP process completes recording the pre-transaction (undo) data in the history table, the database won’t reuse the undo records that are marked for archival.Once the FDAP process completes the history generation, these undo records become candi dates for recycling again.

Here’s an example that shows how the history table archives data following transactions that update or delete data from a table that’s being currently tracked in the FDAP.The first transaction inserts a new row into the depart ments table:

The preceding transaction,since it involves an insertion of a new row,will be not add any history records to the history table hr.SYS_FBA_HIST_70308. The following update statement will add a record, however:

The next transaction deletes the updated row Again,a new row is written to the history table:

If you query the history table hr.SYS_FBA_HIST_70308 now, you’ll find all the pre-change table data that the FBDA background process has captured from the undo records:

As you can see, only the update and delete statements led to the addition of a history record to the history table—the insert statement didn’t.

Benefits of Using a Flashback Data Archive

The flashback data archive feature offers the following key benefits:

    • You can use a centralized management interface that lets you easily implement administrative tasks such as setting common retention policies for related groups of tables and automatic purging of aged-out data from the archive.
    • You can view the archived data from any past point in time easily using the as of clause of the flashback query feature.
    • You can capture historical data without any changes in the underlying application at all.

    • It’s easy to implement without any application changes and has a low performance overhead because of an efficient strategy for archiving data,as we explain later.
    • You incur low storage requirements for storing the historical data because the data is stored in compressed format.The database also automatically partitions the internal history tables using a range- partitioned scheme,without any work on your part whatsoever.
    • You can store critical data extremely securely because users, including DBAs, can’t directly update the historical data.

Managing the flashback data archive includes performing tasks such as creating a new flashback data archive,altering the characteristics of a flashback data archive,and turning flashback logging on and off.We explain how to manage flashback data archives in the following sections.

Creating a Flashback Data Archive

To create a flashback data archive, you must either have the DBA role or have the system privilege flashback archive administer.Here’s a query that shows how to query the DBA_SYS_PRIVS view to check who has been granted this privilege:

Grant the flashback archive administer privilege to the user hr so that user can manage a flashback data archive.Here’s the example:

You create a flashback data archive by using the create flashback state ment.You must first create the tablespace that’ll host the flashback data archive.You can specify the following things while creating a new flashback data archive:

    • A name for the flashback data archive
    • Whether the flashback data archive is the default archive for the database
    • The name of a tablespace you’ve created earlier
    • A quota for this flashback data archive in the tablespace you choose as the host for the archive
    • A retention period for the flashback data archive

The two key parameters are quota,which determines the amount of space in a tablespace that the flashback data archive can use, and retention, which determines how long the historical data is retained in the archive before the database automatically purges it (you can also manually purge the historical data).Here’s an example that shows how to create a flash back data archive in the tablespace flash_tbs1:

The retention 4 year clause means the flashback data archive flash1 will retain data up for a period of four years.Note that there is no quota clause in the create flashback data archive statement,meaning the archive can take up all the space available in the tablespace flash_tbs1.You can limit the amount of space the flashback data archive takes up in a tablespace by specifying a value for the quota clause, as shown in the following example:

The amount of space you must allocate for a flashback data archive will depend on the amount of transactions in the tables you’re archiving and the duration of the archiving retention period.Before the flashback data archive uses up its allotted quota or,in the absence of a quota clause,before the archive uses up the entire tablespace,Oracle issues an out-of-space alert for the flashback data archive.

This gives you time to purge older data in the archive or increase the tablespace quota or add more datafiles to the tablespace housing the flashback data archive, depending on the case.

Creating a Flashback Data Archive

You can drop a flashback data archive by using the drop flashback archive command:

SQL> drop flashback archive flash1;

Of course,once you drop a flashback data archive,the data you archived disappears,although the tablespace that housed the flash back data archive will remain intact,since it may contain other data besides the flashback data archive.You can’t drop or even modify the contents of the tracking table that stores the actual data changes, since that defeats the purpose of creating tamper-proof historical data archives for auditing and security purposes.

Altering a Flashback Data Archive

Use the alter flashback command to change things such as the length of the retention time,making a flashback data archive the default flashback data archive for the database,adding space to the flashback data archive,or purging data from it.The following are examples of the several ways you can use the alter flashback command to manage the flashback data archive:

The following two commands show how to add a table space to a flashback data archive and remove the tablespace when you want it:

Altering a Flashback Data Archive

Although the data in a flashback data archive is automatically purged upon the expiry of the retention time period,you can always remove unwanted data from a flash back data archive.If you want to empty the flashbackdata archive you can do so by specifying the purge all clause.

You can alternatively use the before time stamp or before scn clause toselectively purge data from the flashback data archive. Here are someexamples showing how to purge data from a flashback data archive:

It’s important to remember that the data base will automatically purge the archived data a day after the retention period expires.

Enabling and Disabling Flashback Data Archiving

Once you create a flash back area,you must enable flashback logging for those tables whose transactional changes you want to archive.You can’t turn flashback archiving on at the data base or tablespace level.You must explicitly turn flashback archiving on for each table whose changes you want to track.

To enable flash back data archive logging, you must have the flash backarchive object privilege on the flash back archive you want to use as well as the create tablespace system privilege.Of course,the DBA role automatically contains these privileges.

By default,flashback logging is turned off for every table in the data base.After you create one or more flashback data archives in your data base, any table you create with the flash back data archive clause or any table you alter with the flash back data archive clause will have flash back logging enabledautomatically.

To enable flashback logging for a table from its inception, modify your create table statement to include the flashback archive clause,as shown here:

If you’ve designated a certain flashback data archive as the default flash back data archive for the database,you can omit the name of the flashback data archive (fla4 in this example) and instead simply use the clause flash back archive.If you don’t specify a flash back data archive by name,the database will use the default flash back data archive for all create table and alter table statements that specify a flashback archive clause.

One of the wonderful features of the flashback data archive capability is that you can enable archiving for already existing tables.This saves you the bother of having to re-create existing tables in order to take advantage of theflashback data archive feature.Use the flashback archive clause to enableflash back archiving for an existing table,as shown here:

SQL> alter table employees
flashback archive;

Since we didn’t specify a specific flashback data archive, the database will archive the data from the employees table to the default flashback data archive.The following query shows this:

In this example,employees is the name of the table whose changes you want the database to track.The history table for recording the changes is given a system-generated name,SYS_FBA_HIST_70313 in this case,and its owner is the user HR, who is also the owner of the original table that’s being tracked. You can’t directly update the history table, but you can use the as of SQL construct to query the historical data.

You must set your default flashback data archive (using the set default clause,as shown earlier) before you can use a flashback data archive. The following query shows you’ve set a default flashback data archive:

If you want to explicitly specify storing the table changes in a specific flashback data archive,you can do so in the following manner:

SQL> alter table employees
flashback archive flash1;

You can turn off the flashback logging to the flashback data archive for a specific table anytime by executing the following command:

SQL> alter table employees
no flashback archive;

If you’ve enabled flashback logging for any other tables, the changes made to those tables will continue to be stored in the flashback data archive.

Flashback Data Archive Limitations

There are certain limitations on using the flashback archive. For any flashback-enabled table,you can’t use the drop column DDL command (you can, however,use the add column command).

The only way to drop a column belonging to a table that’s flashback data archive enabled is to turn flash back archiving off first.Un fortunately,this has the severe side effect of erasing all flash back archive data from the flashback data archive.

Monitoring Flashback Data Archives

There are some new views associated with the flashback data archive feature, which we summarize here:

Using Flashback Data Archives: Examples

You can use flashback data archives for a wide range of tasks,includingaccessing historical data, auditing,and recovering data.In the following sections,we provide brief examples that demon strate the versatility of the flashback data archive feature.Make sure you create one or more flashback data archives first.You must also use the alter table command to enable the flash back data archive feature for a table, if you didn’t specify archiving when you created the table.

Accessing Older Data

You can retrieve data from a specified point in time by specifying the as of clause,as shown by the following query:

The as_of formulation is especially useful for pinpointing the state of the pertinent data at a specific point in time in the past.Look at the following examplewhich helps you correct errone ously entered table data when you aren’t sure about the exact point in time.First check the current salaryinformation for an employee from the hr.employees table:

Now,user hr wrongly updates the salary column for employee Zlotkey,giving the employee a raise of 50,000 instead of 5,000,and then commits the update:

To set matters right,you must update employee Zlotkey’s salary to its value before the erroneous up date made by user hr.Just make sure you specify a time that falls before the erroneous up date.If you know that the update was made about an hour ago (but not quite an hour), you can revert the salary column’s value to what it was an hour ago by running the following SQL:

The previous update statement uses the flashback data archive to update the salary information for Zlotkey.In this example, we use the expression syst imestamp–interval '60' minute to specify that the database needs to retrieve the necessary values that prevailed an hour ago.You can also use values such as the following:

Generating Reports

You can use the historical data in a flashback data archive to create reports that range over a long period of time. Here’s an example thatuses the versions_between clause to retrieve data between two time periods:

The preceding query generates a report of all changes made to the patient_info table for the name ALAPATI from January 1, 2007, until the present time.

Information Lifecycle Management

Information lifecycle management (ILM) applications often require that you be able to retrieve a specific version of a row in a table.In the absence of a flashback data archive,you have the onerous burden of managing and storing multiple versions for each row in a table.

Flashback data archiving works as a boon for ILM applications. Simply use the versions between clause to retrieve all available versions of a table row that fall between two specified time periods.The following example shows how to do this:

The preceding SQL statement will fetch all versions of the requested row that fall in the sixmonth time period specified by the version between clause.

Flashback Transaction Backout

In Oracle Database 10g, Oracle introduced the twin flashback features flashback version query and flashback transaction query to allow you to undo erroneous changes to data following logical corruptions in the database.Following a suspected data error, you’d first employ the flashback version query to identify the versions of the rows in a table that pertain to the erroneous transaction. Once you identify the culprit transaction(s), you’d then use the flashback transaction query to audit all the changes made by that transaction.Using the SQL code provided by the UNDO_SQL column of the flashback trans action query,you’d then undo the changes made by the erroneous transaction. Thus, the flash back transaction query offered a powerful means to undo logical corruption in your database.

In Oracle Database 11g, you can use the new flashback transaction backout feature to perform the tasks you had to perform by using both the flashback version query and the flashback transaction query together.Often, a data failure could cause other dependent transactions to be executed,using the corrupted data.Flashback transaction backout is a new logical recovery feature that lets you return a target transaction as well its dependent transactions to their original state.The flashback transaction backout feature identifies and fixes the initial transaction as well as the dependent transactions, thus completely undoing the effects of a logical data corruption. Un doing an entire set of inserts,updates, and deletes in one fell swoop ensures that the transaction atomicity and consistency principles are maintained.You can thus perform a logical recovery of the database while the database is online by executing a single backout command (a single execution of the transaction_backout procedure).If you’re using Database Control,you can back out a transaction with a single click.A dependent transaction can have the one of the following types of relationships to the target transaction:

    • A write-after-write (WAW) relationship where a dependent transaction modifies the same data that was modified by the target transaction
    • A primary key constraint relationship, where a dependent transaction inserts the same primary key that was deleted by the target transaction

The database undoes the transaction changes by executing what are called compensating transactions,which revert the data affected by the un wanted transactions to their original states. Flash back transaction relies on undodata(and the redo generated for the undo blocks) to create the compensating transactions.Thus,you must have both the necessary undo data and thearchived redo logs to undo a set of unwanted transactions.

Prerequisites for Flashback Transaction Backout

You must first enable supplemental logging in the database and then grant certain special privileges to users who want to use the flash back transaction backout feature.To enable supplemental logging in your database,use the following commands:

SQL>alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key) columns;

In addition to turning supplemental logging on,grant the following privileges to the users who need to use the flashback transaction backout feature:

SQL> grant execute on dbms_flashback to hr;
SQL> grant select any transaction to hr;;

A user must have the flashback privilege,which you can grant by granting the execute privilege on the DBMS_FLASHBACK table.In addition,the user needs the select any transaction privilege.

If users want to back out transactions in their own schema, no additional privileges are necessary.If a user wants to back out transactions in another schema,however,you must also grant DML privileges to that user on all tables that’ll be affected by the transaction back out operation.


The idea of a compensating transaction is crucial to the trans action backout feature.A compensating transaction backs out one or more transactions by using undo data.Use the DBMS_FLASHBACK package’s transaction_backout procedure to back out unwanted transactions easily. Here’s the structure of the transaction_backout procedure:

There are four parameters in the transaction_backout procedure:

    Numtxns: This is the number of transactions to be backed out.
    Names: This is the list of transactions to be backed out (by name).
    Timehint: If you identify transactions by name, you can provide a time hint, such as a time that’s before the start of any transactions.
    Options: This specifies the order in which specified transactions and their dependent transactions are backed out.

The transaction_backout procedure merely analyzes the dependencies among the transactions,performs the DML operations, and provides a report.

The procedure doesn’t automatically commit these DML operations,however.All the procedure does is keep other transactional dependencies from affecting the backout operation by holding necessary locks on the affected table rows as well as on the tables themselves.You must explicitly issue a commit statement for the backout to become permanent.

The database will automatically provide a transaction name for the backout operation,but explicit naming of the operation by you facilitates auditing later. If your execution of the transaction_backout procedure completes successfully,it means the single transaction has been backed out and there are no dependent transactions.How long a back out operation takes will depend on the amount of redo generated by the transaction that’s being out—the larger the amount of redo logs,the longer it takes to complete the transaction back out operation.

You can run the transaction_backout procedure with four options,as explained here:

    cascade:Backs out all transactions,including the dependenttransactions,which are backed out first, before backing out their parent (target) transactions.
    nocascade:Expects the specified transaction not to have any dependents.This is the default value.
    nocascade_force:Backs out only the target transaction, ignoring dependent transactions.
    nonconflict_only:Backs out only those rows in the target transaction that don’t conflict.

Note that the default value of nocascade expects that the transaction pending a backout has no dependent transactions.


TRANSACTION_ REPORT views for details about thetransaction backout operation.

The transaction_backout procedure populates both of these views.If a transaction appears in the DBA_ FLASHBACK_ TRANSACTION_ STATE view, itmeans the transaction has been successfully backed out of the database. For each backed-out transaction, the DBA_ FLASHBACK_ TRANSACTION_REPORT view provides a detailed reportYou can also use Database Control to perform a transaction backout operation if you don’t want to use the DBMS_FLASHBACK package directly.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics