Oracle 10g Availability Enhancements, Part 3: FLASHBACK Enhancements

Thursday Jan 27th 2005 by Jim Czuprynski

Oracle 10g offers significant enhancements that help insure the high availability of any Oracle database, as well as improvements in the database disaster recovery arena. This article - part three of a series - concentrates on the expanded capabilities of the logical Flashback command set.

Synopsis. Oracle 10g offers significant enhancements that help insure the high availability of any Oracle database, as well as improvements in the database disaster recovery arena. This article - part three of a series - concentrates on the expanded capabilities of the logical Flashback command set.

The previous article in this series discussed how two new Oracle 10g features - the Flash Recovery Area and the Flashback Database command -- expand a DBA's flexibility during a point-in-time incomplete recovery of a database. However, when those features are used in conjunction with the new logical FLASHBACK command set enhancements, a DBA now has an extensive set of tools for recovering data with more granularity than ever before.

A Quick Review: Flashback Query

Oracle 9i provided the ability to "flash back" to a prior view of the database via queries performed against specific logical entities. For example, if a user had accidentally added, modified or deleted a large number of rows erroneously, it was now possible to view the state of the logical entity just before the operation had taken place. Of course, this capability is limited by the amount of UNDO data retained in the database's UNDO segments, and that is bounded by the time frame specified by the UNDO_RETENTION initialization parameter.

For example, let's assume that late one Friday afternoon a junior DBA, in his zest to perform emergency maintenance requested by a developer against the DEPARTMENTS table, inadvertently set all of the values for the Department Managers to NULL values. If the senior DBA knew the approximate time at which this had occurred, then she could issue the following query to return the state of that table at that time:

  FROM hr.departments
TO_TIMESTAMP('12/05/2004 11:55:00', 'MM/DD/YYYY HH24:MI:SS');

Moreover, to restore the data back to its prior state, she could issue this UPDATE statement:

UPDATE hr.departments D1
   SET D1.manager_id = (
       SELECT manager_id 
         FROM hr.departments
         TO_TIMESTAMP('12/05/2004 11:55:00', 'MM/DD/YYYY HH24:MI:SS') D
        WHERE manager_id IS NOT NULL
          AND d1.department_id = D.department_id


(See Listing 3.1 for a complete set of DML statements that simulate this scenario.)

The ability to flash back to a different version of the data is an obvious time-saver in this situation, as it prevents having to resort to other brute-force methods to restore data integrity. However, what if more than one table's data had been affected by this transaction? For example, what will happen if a user error causes data to be modified in another table based on a change in values in the DEPARTMENTS table, perhaps through a trigger?

Or - even worse - what if significant amounts of data had been deleted instead of just being updated erroneously? Or what if the junior DBA had dropped a whole table instead of just truncating the data stored within? Now the restoration and recovery of these data requires some difficult decisions for the DBA:

  • She could perform an incomplete recovery of the database using the traditional method of restoring backups of all datafiles and then applying the appropriate archived redo logs to roll the database forward until the database reached a point in time just prior to the time that the user error occurred.
  • Alternatively, if the new Oracle 10g Flashback Database feature had been enabled, she could use that new method to "flash" the database back to a particular point in time. (See the prior article in this series for complete details on this method.)
  • Another option would be to perform a brute-force recovery via manual means, perhaps using database exports (providing, of course, that the exports were current enough). However, unless the DBA is intimately aware of the inter-relation of the affected data, this may not be practical, and could even be more destructive.

Unfortunately, in all these cases, the state of the data that has been added, modified or deleted after the recovery point in time would most likely also be lost. Moreover, that probably means that her users are going to spend their weekend re-entering significant amounts of lost data.

Wouldn't it be great if Oracle provided a way to reverse the effects of a particular DML statement completely, or (even better!) a dropped table? Here is the good news: Oracle 10g has significantly improved the existing set of logical FLASHBACK features to handle many of these not-quite-a-disaster data recovery operations.

Flashback Version Query

Flashback Version Query improves upon the existing Flashback Query feature: It allows a DBA to see all the different versions of a particular row within a specified time frame, as long as those versions are still available within the UNDO tablespace's rollback segments. This time frame can be defined based on either a beginning and an ending timestamp value, or based on a range of starting and ending System Change Numbers (SCNs).

Flashback Version Query: An Illustration. I will use the Human Resources demonstration schema to illustrate the new capabilities of Flashback Version Query. First, I will establish a new baseline of "correct" data by adding four new Job Titles, a new Department, and five new Employees to the database via a series of DML statements (see Listing 3.2).

Next, I will issue a series of DML statements to simulate a set of "mistaken" operations against the database (see Listing 3.3). Note that the changes against the HR.EMPLOYEES table within these transactions also mean that new rows will be added automatically to the HR.JOB_HISTORY table via the NNNN trigger.

Now, I will use the query in Listing 3.4 to show what Flashback Version Query can tell me about the versions of the rows in the database as a result of these DML statements. Here is a sample of the results:

            Current FLASHBACK VERSION Results For Selected Employees

                       Vsn       Vsn
                     Start       End
XID                    SCN       SCN Operatio Last Name     Dept     Salary
---------------- --------- --------- -------- ------------ ----- ----------
04002900E7000000   2150721           Insert   Campbell       280  110000.00
04000C00E7000000   2150749           Update   Asimov         280    5250.00
04002900E7000000   2150721   2150749 Insert   Asimov         280    5000.00
04000C00E7000000   2150749           Update   Heinlein       280   26250.00
04002900E7000000   2150721   2150749 Insert   Heinlein       280   25000.00
04000C00E7000000   2150749           Update   Bradbury       280   50925.00
04002900E7000000   2150721   2150749 Insert   Bradbury       280   48500.00
04001500E7000000   2150751           Update   Ellison        270   34125.00
04000C00E7000000   2150749   2150751 Update   Ellison        280   34125.00
04002900E7000000   2150721   2150749 Insert   Ellison        280   32500.00
04001B00E7000000   2150753           Delete   Brin           280   39375.00
04000C00E7000000   2150749   2150753 Update   Brin           280   39375.00
04001E00E7000000   2150747   2150749 Insert   Brin           280   37500.00

A New PseudoColumn: ORA_ROWSCN. While the ROWID pseudocolumn uniquely identifies a row's block location within the database, the new pseudocolumn ORA_ROWSCN identifies the System Change Number (SCN) of the most recently committed change to a row. This pseudocolumn can therefore be used to establish the upper limit of SCNs that I might want to search through. See Listing 3.4 for a sample query that utilized this new pseudocolumn.

Another intriguing potential use of ORA_ROWSCN is the capability to control transaction concurrency within applications. For example, if one user retrieves a row from HR.EMPLOYEES for eventual update, but has not yet applied the change, the value of ORA_ROWSCN will remain unchanged until those modifications are committed to the database. In the meantime, if another user modifies that same row and commits the changes, I can have my application check if the value of ORA_ROWSCN is still equal to its original value.

If the value has changed, I can notify the original user that the row has changed since it was originally retrieved for update, and request whether to continue the transaction or roll it back. Prior to ORA_ROWSCN, my application would have to check every value of every column for that row to determine if any value had changed. Alternatively, I add a separate numeric column that would be incremented whenever a change to a row was committed for each table that needs this level of transaction control. However, ORA_ROWSCN makes accurate transaction control almost trivial.

Flashback Transaction Query

Like its cousin Flashback Version Query, Flashback Transaction Query gives me even more flexibility: It allows me to see all changed rows within a particular set of transactions that occurred within a range of timestamps or SCNs.

Flashback Transaction Query uses the FLASHBACK_TRANSACTION_QUERY view as a window into the database's UNDO segments. I can use this view's transaction ID column (XID) to identify what changes have been recorded during a specific transaction. Reminiscent of Oracle's LogMiner toolset, Flashback Transaction Query can display the actual DML statements to issue, to reverse the original transaction.

Listing 3.5 shows how to utilize a Flashback Version Query SELECT statement to drive the retrieval of all transactions that have occurred during a specific range off SCNs. Here is the result of that query:

Current FLASHBACK_TRANSACTION_QUERY Contents For Selected Employees

                            User   Table                  Commit
XID#             Operation  Logon  Owner  Table Name         SCN
---------------- ---------- ------ ------ ------------ ---------
04000C00E7000000 UPDATE     SYS    HR     EMPLOYEES      2150749
update "HR"."EMPLOYEES" set "SALARY" = '37500' where ROWID = 'AAAGMsAAEAAAABWAAA';

04000C00E7000000 UPDATE     SYS    HR     EMPLOYEES      2150749
update "HR"."EMPLOYEES" set "SALARY" = '32500' where ROWID = 'AAAGMsAAEAAAABYAAE';

04000C00E7000000 UPDATE     SYS    HR     EMPLOYEES      2150749
update "HR"."EMPLOYEES" set "SALARY" = '48500' where ROWID = 'AAAGMsAAEAAAABYAAD';

04000C00E7000000 UPDATE     SYS    HR     EMPLOYEES      2150749
update "HR"."EMPLOYEES" set "SALARY" = '25000' where ROWID = 'AAAGMsAAEAAAABYAAC';

04000C00E7000000 UPDATE     SYS    HR     EMPLOYEES      2150749
update "HR"."EMPLOYEES" set "SALARY" = '5000' where ROWID = 'AAAGMsAAEAAAABYAAB';

04000C00E7000000 BEGIN      SYS                          2150749

04001500E7000000 INSERT     SYS    HR     JOB_HISTORY    2150751
delete from "HR"."JOB_HISTORY" where ROWID = 'AAAGMwAAEAAAABtAAG';

04001500E7000000 UPDATE     SYS    HR     EMPLOYEES      2150751

04001500E7000000 BEGIN      SYS                          2150751

04001B00E7000000 DELETE     SYS    HR     EMPLOYEES      2150753
	"DEPARTMENT_ID") values ('906','David','Brin','dbrin@astounding.com',
	'212-555-1616',TO_DATE('10/31/1987 00:00:00', 'mm/dd/yyyy hh24:mi:ss'),

04001B00E7000000 BEGIN      SYS                          2150753

04001E00E7000000 INSERT     SYS    HR     EMPLOYEES      2150747

04001E00E7000000 BEGIN      SYS                          2150747

04002900E7000000 INSERT     SYS    HR     EMPLOYEES      2150721

04002900E7000000 INSERT     SYS    HR     EMPLOYEES      2150721

04002900E7000000 INSERT     SYS    HR     EMPLOYEES      2150721

04002900E7000000 INSERT     SYS    HR     EMPLOYEES      2150721

04002900E7000000 INSERT     SYS    HR     EMPLOYEES      2150721

04002900E7000000 BEGIN      SYS                          2150721

19 rows selected.

Using SCNs vs. TIMESTAMPs. As you might expect, using an SCN to identify a transaction or range of row versions is more accurate than using a TIMESTAMP. Oracle recommends using SCNs over TIMESTAMPs when an extremely accurate Logical Flashback operation needs to be performed; in fact, the documentation states that a TIMESTAMP can be as much as three minutes ahead in time than an SCN.

Effect of UNDO_RETENTION Setting. The length of time the row versions are available obviously depends on the setting of the UNDO_RETENTION initialization parameter. By default, this setting is 900 seconds (15 minutes); in some cases, I have set UNDO_RETENTION as high as 10800 (3 hours) for some databases that I knew needed longer UNDO retention durations. For the sake of these examples, I have set it to 1800 (30 minutes) in my demonstration database, so that I can more easily illustrate these two new features without recreating examples every 15 minutes.

Rewinding Tables with FLASHBACK TABLE

While Flashback Version Query and Flashback Transaction Query offer the capability to retrieve the state of a table's rows at a prior point in time, Oracle 10g also offers the ability to restore an entire table to an earlier state within the boundaries of available UNDO data via the FLASHBACK TABLE command.

To illustrate this, I will create a new table in the HR demo schema called APPLICANTS that I will use to record information about each person applying for a job. I will use a row-level trigger and a sequence to automatically increment the primary key column, APPLICANTS.APPLICANT_ID, whenever a new entry is added to the table.

Listing 3.6 shows the DDL and DML statements necessary to create and populate this table initially. Once the new table was populated, I recorded the maximum SCN (2177093) just before I issued a series of additional INSERT statements shown in Listing 3.7.

I will issue the FLASHBACK TABLE command shown in Listing 3.8 to bring the table back to its initial state. Note that I set the table's ENABLE ROW MOVEMENT parameter to TRUE before attempting to "rewind" the table.

Prerequisities. Before I can execute a FLASHBACK TABLE command, there are some precursors:

  • The UNDO segments that hold the statements needed to "rewind" the table(s) back to its prior state must still be available.
  • The user account from which I am issuing the FLASHBACK TABLE command must have been granted the FLASHBACK TABLE object privilege for the tables that I wish to "rewind," or the user account must have been granted the FLASHBACK ANY TABLE privilege.
  • Also, the user account that is performing the FLASHBACK TABLE operation must have been granted SELECT, INSERT, UPDATE, and DELETE rights.
  • Finally, the table(s) to be "rewound" must have the ENABLE ROW MOVEMENT directive enabled. This directive allows Oracle to move rows into or out of the selected table(s).

Caveats. Even though FLASHBACK TABLE offers some slick new capabilities, some warnings are in order:

  • It is important to remember that once the FLASHBACK TABLE operation is completed, it only rolls back the transactions applied to the table or tables specified in the command. However, the state of any other database objects is unchanged. If I now reissue the second set of INSERTs, the sequence upon which the APPLICANT table's BEFORE INSERT trigger has not been reset, and the next set of applicants will use the most current value of the sequence for the APPLICANT_ID value.
  • A FLASHBACK TABLE operation cannot be rolled back, as an implicit COMMIT is issued once it is complete. However, another FLASHBACK TABLE statement can be issued to restore the table to a different point in time (providing, of course, that sufficient UNDO is available for the successive operation).
  • Also, FLASHBACK TABLE cannot be used to recover to a point in time prior to the issuance of DDL statements that have modified the table's structure.

Restoring a Dropped Table: FLASHBACK DROP and the Recycle Bin

Rounding out the new logical flashback features, Oracle 10g offers the capability to recover from one of the most destructive "accidental" operations that can happen to any Oracle database: the complete removal of a table via the DROP TABLE command. To facilitate this new feature, Oracle 10g has added a new storage area to the database called the Recycle Bin where dropped objects are retained until the object is either recovered via a Flashback Drop operation, or until the object is purged from the Recycle Bin.

Peering Into the Recycle Bin. Every time a table is dropped, it is assigned a unique object identifier in the Recycle Bin. This 30-character-long object identifier is in the format BIN$$globalUID$version, where globalUID is a 24-character globally unique identifier for the dropped object, and version is assigned for each version of the dropped object.

Oracle 10g provides several methods to view the Recycle Bin's contents and identify which tables have been dropped:

  • A new column, DROPPED, has been added to the DBA_TABLES data dictionary view to allow screening for tables that have been dropped from the database but are now present in the Recycle Bin instead.
  • The SHOW RECYCLEBIN; command shows all dropped tables and their dependent objects when issued from within a SQL*Plus session.
  • The RECYCLEBIN data dictionary view shows the contents of the Recycle Bin for the current user.
  • Finally, the DBA_RECYCLEBIN data dictionary view shows the complete contents of the Recycle Bin.

Viewing Different Versions of Dropped Tables. Even if a table is created and dropped several times, all of the different iterations of the dropped table and its dependent objects are retained in the Recycle Bin until they are purged. Using the dropped table's object identifier, I can query directly against the dropped table's data in the Recycle Bin, thus allowing me to determine exactly which version of the table should be recovered. I will demonstrate this feature in an upcoming recovery example.

Listing 3.9 displays several examples of querying the Recycle Bin for its current status.

Recycle Bin Space Pressure and Automatic Purging. Oracle 10g automatically manages the contents of the Recycle Bin to insure there is enough space to store any dropped tables and their related objects. Unfortunately, this also means that there is no way to predict when Oracle may need to purge objects from the Recycle Bin.

Oracle will keep objects in the Recycle Bin until it can no longer allocate new extents in the tablespace where the dropped objects originally resided without expanding the tablespace. This situation is known as space pressure. When space pressure demands that Recycle Bin space be reclaimed, Oracle will purge the oldest objects first (i.e., first-in, first-out basis), and it will purge a dropped table's dependent objects first (e.g. indexes, triggers) before it purges the table itself.

Manually Purging Recycle Bin Objects. Versions of objects that no longer need to be retained can also be purged manually via the following commands, in order of increasing destructiveness to the Recycle Bin:

  • Purging A Single Index. The PURGE INDEX <index name>; command purges the most recent incarnation of the specified index from the Recycle Bin. Note that the index cannot have been used to enforce a constraint for its supported table; otherwise, Oracle will return an error.
  • Purging A Single Table. Issuing the PURGE TABLE <table name>; purges only the most recent incarnation of the dropped table and its dependent objects (e.g. indexes and triggers).
  • Purging All Objects in a Tablespace. The PURGE TABLESPACE <tablespace_name>; purges all objects in the specified tablespace from the Recycle Bin.
  • Purging All Schema Objects. The PURGE RECYCLEBIN; command will purge all schema objects for the current user account from the Recycle Bin.
  • Purging All Objects. Finally, the PURGE DBA_RECYCLEBIN; command purges all database objects in the Recycle Bin. Note that this command must be issued from a user account with DBA privileges.

See Listing 3.10 for examples of these commands.

Example: Restoring a Dropped Table. As long as the table and its dependent objects are still present in the Recycle Bin, the table can be recovered using the FLASHBACK TABLE <table name> TO BEFORE DROP; command. To illustrate the power of this new feature, I have dropped the HR.APPLICANTS table created in the previous FLASHBACK TABLE example and then purged the entire Recycle Bin via the PURGE DBA_RECYCLEBIN; command.

Next, I recreated the table via the code in Listing 3.6, and loaded with the first set of applicants. I dropped it again, created it again and loaded it with the first set as well as the second set of applicants (Listing 3.7). Finally, I dropped the table a third time, reloaded it with the first and second set of applicants, added a third set of applicants (Listing 3.11), and dropped it once again. This left three distinct iterations of the table to experiment against. Here are the results stored in the Recycle Bin after these operations:

SQL> TTITLE 'Current Recycle Bin Contents'
SQL> COL object_name         FORMAT A30      HEADING 'Object Name'
SQL> COL type                FORMAT A8       HEADING 'Object|Type'
SQL> COL original_name       FORMAT A20      HEADING 'Original Name'
SQL> COL droptime            FORMAT A20      HEADING 'Dropped On'
  2       object_name
  3      ,type
  4      ,original_name
  5      ,droptime
  6    FROM dba_recyclebin
  7   WHERE owner = 'HR'
  8  ;

                     Current Recycle Bin Contents

Object Name                    Type     Original Name        Dropped On
------------------------------ -------- -------------------- --------------------
BIN$GXKs4x3zS+6aEyHIbjIO0g==$0 INDEX    APPLICANTS_LAST_NAME 2005-01-3:19:04:25

BIN$0YQGF9xpTgOPRqsqfuHtNA==$0 INDEX    APPLICANTS_LAST_NAME 2005-01-3:19:03:36

BIN$lpBfdfPSQfai8dZoa/DHUw==$0 INDEX    APPLICANTS_PK_IDX    2005-01-3:19:03:36
BIN$TdmwJaPjSIu5XTGn2vmweQ==$0 INDEX    APPLICANTS_PK_IDX    2005-01-3:19:04:25
BIN$eUzM3ZWMTQefYskd+7kAqw==$0 TRIGGER  TR_BRIU_APPLICANTS   2005-01-3:19:04:25
BIN$ldrmjTN0R8K8qyRRCmSsxw==$0 TABLE    APPLICANTS           2005-01-3:19:04:25
BIN$RSEdFMhCRcqCv5g7lYss6A==$0 TRIGGER  TR_BRIU_APPLICANTS   2005-01-3:19:03:36
BIN$992SjQhHRlqHZHB4Aa/dWQ==$0 TABLE    APPLICANTS           2005-01-3:19:03:36
BIN$tqINHzsMRT6EfbsgiD8eFQ==$0 INDEX    APPLICANTS_LAST_NAME 2005-01-3:19:06:56

BIN$877/hfooRKuuiVAKjsE7Jg==$0 INDEX    APPLICANTS_PK_IDX    2005-01-3:19:06:56
BIN$vLf00KzHSpGbMjkhnJETUw==$0 TRIGGER  TR_BRIU_APPLICANTS   2005-01-3:19:06:56
BIN$xJfp8JRWQ9KalGzPUw9Ygg==$0 TABLE    APPLICANTS           2005-01-3:19:06:56

12 rows selected.

By querying directly against the Recycle Bin using the dropped table's object identifier, I can confirm that the second iteration of the HR.APPLICANTS table has exactly 30 rows:

SQL> -- Most recent iteration of HR.APPLICANTS


SQL> -- Second-most recent iteration of HR.APPLICANTS
SQL> SELECT COUNT(*) FROM "BIN$ldrmjTN0R8K8qyRRCmSsxw==$0";


SQL> -- Third-most recent iteration of HR.APPLICANTS


Oracle retrieves the most recently-dropped table first, so if I issued a FLASHBACK TABLE hr.applicants TO BEFORE DROP; Oracle would restore the iteration with 45 rows. Since I want to restore only the iteration with 30 rows, I will issue a FLASHBACK TABLE <object_identifier> TO BEFORE DROP; command to insure that I have restored the desired copy of the table:


Alternatively, I can restore a different iteration of the table as with a different object name:

RENAME TO applicants_1;


Oracle 10g's new Logical Flashback features significantly expand an Oracle DBA's abilities to recover data, transactions and database objects that have been lost with a minimum of effort. When these new features are used in conjunction with each other and with the FLASHBACK DATABASE features described in the previous article, just about any data loss situation can be forestalled. The next article the final one in this series -- will concentrate on additional availability enhancements implemented as part of DataGuard and Logminer.

References and Additional Reading

While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 10g documentation for the deeper technical details of this article:

B10734-01 Oracle Database Backup and Recovery Advanced User's Guide

B10735-01 Oracle Database Backup and Recovery Basics

B10750-01 Oracle Database New Features Guide

B10759-01 Oracle Database SQL Reference

B10770-01 Oracle Database Recovery Manager Reference

» See All Articles by Columnist Jim Czuprynski

Mobile Site | Full Site