As Published In May/June 2004

Flash Back to a Better Time
By Arup Nanda

Restore a table or an entire database to a point in the past, using a simple SQL statement.

It's New Year's Eve. John, the DBA at Acme Bank, is reveling with his friends, counting down the arrival of the new year. Just when the clock strikes midnight and the crowd roars, his pager goes off. At the bank's data center, during the end-of-the-year bookkeeping process in the interest accumulation batch run, something has gone wrong and all the interest calculations are incorrect. The good news is that the development team has identified the bug and developed an emergency fix, but the fix can't undo the damage already done. The operations manager asks John if he can somehow turn back the hands of time and place the database back to its state before the batch process started, which was around 11:00 p.m.

Does that sound familiar to you DBAs? What are John's options?

Prior to Oracle Database 10g, John could have done a point-in-time recovery to reinstate the database to the desired point. Unfortunately, the bank's regular daily backup starts around that time, meaning he would have to apply almost 24 hours' worth of archived logs to the restored database.

Another option available in Oracle9i Database is to use the flashback query feature to reconstruct the table rows as of 11:00 p.m. and create a different set of tables manually. This approach, although possible, becomes impractical if the number of tables is high.

Fortunately, John's current database is Oracle Database 10g, so he has more options.

Flashback Table

Let's take a look at the above scenario. The problematic year-end batch run might have affected only a few tables. For example, it may have updated only the table ACCOUNTS with new account balances. If this is the case, John can use the flashback table feature, which reinstates a table to a point in the past.

There is no special setup necessary to perform the flashback table operation. The only requirement is that the table must have row movement enabled—either at table creation time or later using the ALTER TABLE ACCOUNTS ENABLE ROW MOVEMENT statement. The FLASHBACK TABLE statement reads the past images of the table from the undo segments and reconstructs the table rows using the flashback queries introduced in Oracle9i.

If a non-DBA user other than the schema owner performs a flashback table operation, she needs SELECT, DELETE, INSERT, ALTER, and FLASHBACK privileges on that table or the equivalent ANY TABLE system privileges.

For John, the table ACCOUNTS looks like this:

ACCOUNT_NO   NUMBER(12),
BALANCE      NUMBER(15,2)
STATUS       CHAR(1)

The column value for STATUS is regularly "A" (active), but when the interest application starts, all the accounts are frozen and the status is updated to "F" (frozen). After the interest is applied to each account, the status is updated to "I" (interest applied).

Here are the steps John takes to use the flashback table feature:

1. He asks for an approximate point in time to which the database has to be retraced; the answer is about 11:00 p.m.
2. He defines the desired logical reference point to go back to. Here's what he sees when he queries the table now:

   select status, count(*) 
   from ACCOUNTS 
   group by status;
   
        STATUS   COUNT(*)
        ------  --------
        I          27088
        F          19999
   

3. The output shows that 27,088 accounts have been processed so far (status = I). The desired logical reference point should be where all the accounts have status "F." The status prior to that is "A," which is not what John wants to go back to. He must reinstate the state where all accounts have status "F," which occurred at about 11:00 p.m.
4. He checks one current sample account with status = 'I' to set a baseline for a future verification test:

   select account_no, balance
   from ACCOUNTS
   where status = 'I'
   and rownum < 2;
    ACCOUNT_NO    BALANCE
   -----------   --------
        21633    3913.49
   

5. He issues this statement to restore the table ACCOUNTS to that time:

   flashback table ACCOUNTS to timestamp
   to_timestamp ('12/31/2003 23:00:00','mm/dd/yyyy hh24:mi:ss');
   

   Voilà! The entire table is reconstructed as of that time stamp. John can flash back to a point as far in the past as the remaining undo data in undo segments allow. Instead of the time stamp, John can also use the system change number (SCN) as follows:

   flashback table ACCOUNTS 
   to SCN 9988653338;
   

   The table was reinstated to 11:00 p.m., but is that the desired point? John checks the status again:

   select status, count(*) 
   from ACCOUNTS 
   group by status;
   
   STATUS   COUNT(*)
   ------   --------
   I             88
   F          46999
   

6. There are still 88 records with status "I." So, 11 p.m. was not early enough; John has to go back even earlier. He flashes back the table even farther—to 10:30 p.m.—and then rechecks the status:

   flashback table ACCOUNTS to timestamp
   to_timestamp ('12/31/2003 22:30:00','mm/dd/yyyy hh24:mi:ss');
   
   select status, count(*) 
   from ACCOUNTS 
   group by status;
   
   STATUS   COUNT(*)
   ------   --------
   A          47087
   

7. John flashed back too far into the past; all the accounts have status "A," the one prior to "F." Hence, he has to roll forward to the future, to 10:45 p.m.

   flashback table ACCOUNTS to timestamp
   to_timestamp ('12/31/2003 22:45:00','mm/dd/yyyy hh24:mi:ss');
   
   select status, count(*) 
   from ACCOUNTS 
   group by status;
   
   STATUS   COUNT(*)
   ------   --------
   F          47087
   

8. This is exactly where he wants to be. He makes sure by selecting the account he queried earlier.

   select balance 
   from ACCOUNTS 
   where account_no = 21633;
   
   BALANCE
   -------
   3836.75
   

9. The balance used to be $3,913.49; it was rolled back to its state at 10:45 p.m. The operation is complete. The flashback can be applied in forward and backward directions as many times as desired to arrive at the exact point.

Since the table is never dropped, all the dependent objects—such as indexes, constraints, triggers, and so on—remain intact. All independent objects referencing this table, such as procedures, also remain valid. Even global indexes on partitioned tables are maintained and remain valid.

If John wanted to flash back the table TXN in addition to the table ACCOUNTS, he could have used many table names separated by a comma as follows:

flashback table BANK.ACCOUNTS, bank.txn to scn 1234567;

The entire flashback table operation is done through a single, powerful SQL statement.

Let's examine another situation. Suppose that Laura accidentally drops a key lookup table, GL_MASTER. Realizing the mistake, she asks John if he can reinstate the table. In prior versions of Oracle Database, this would have required a point-in-time recovery. In Oracle Database 10g, however, dropping a table renames the table and places it in a logical container known as the Recycle Bin.

To recover the table, John simply issues the following command:

flashback table gl_master to before drop; 

The table reappears instantly, without needing any kind of recovery. Note that unlike the flashback operation described earlier, this does not require data reconstruction through the undo segments; rather the table is merely moved back from the recycle bin.

Flashback Database

When logical corruption is not limited to a few tables, it can be faster to recover by performing a database point-in-time recovery using Flashback Database, available in Oracle 10g. Flashback Database does not use the undo space but rather another area on the disk known as a flash recovery area, independent of the undo operations. Flashback Database lets you do database point-in-time recovery without requiring you to first restore a backup of your database.

To flash back the entire database, John has to do the following to prepare the database for flashback capability:

1. Configure a flashback area of 2GB by setting the following two parameters:

   db_recovery_file_dest = /usr/users/oracle/10.1/recovery_area
   db_recovery_file_dest_size = 2G
   

2. Configure the maximum flashback time in minutes using the following parameter:

   db_flashback_retention_target = 1440
   

   This value reflects the maximum time to flash back to; the actual possible time is determined by how much space is available in the flash recovery area. All three of these parameters are dynamic and can also be set using the ALTER SYSTEM command.

3. Enable the database for flashback. It must be in archivelog mode to do so. In the MOUNT stage, before OPEN, John issues

   alter database flashback on;
   

When Flashback Database is enabled, it periodically writes the changed blocks to a special type of log file known as a flashback log. These logs are not written by the traditional Log Writer (LGWR) process but by a new process known as Recovery Writer (RVWR). Unlike the regular redo logs, flashback logs are not created nor do they need to be maintained by the DBA; they are automatically created as Oracle Managed Files (OMF) in the directory specified by the flash recovery area. These files are not archived; hence, recovery after a media failure in that directory is not possible.

Flashback Operation

1. In the scenario described for Acme Bank, John determines that flashing back tables is not feasible in his case. He has to roll back the entire database to a point in time in the past. Again, he chooses the time of 11:00 p.m. as a starting point and issues the following:

   flashback database to timestamp
   to_timestamp ('12/31/2003 
   23:00:00','mm/dd/yyyy hh24:mi:ss');
   

   This operation performs all the necessary tasks, such as recovering the datafiles using the information from the flash recovery area and applying the changes. Now the entire database is rolled back to its 11:00 p.m. state. There is no need for John to perform any manual recovery. Instead of the time stamp, John could have used the SCN to flash back. The alert log, parts of which are shown in Listing 1, shows some pertinent information about the flashback operation. It clearly shows the steps the statement took to reinstate the datafiles to the SCN number specified.

2. Although the database is reinstated to 11:00 p.m., the question remains—is it the correct time? John uses the same reference point approach mentioned in the flashback table section. He opens the database in READ ONLY mode and selects the status as follows:

   alter database open read only;
   
   select status, count(*) 
   from BANK.ACCOUNTS 
   group by status;
   
   STATUS 	 COUNT(*)
   ------ 	 --------
   I             255
   F          46832
   

3. No, the 255 records with status "I" means he still has to go back further into the past—to 10:30 p.m.—as follows:

   shutdown immediate
   startup mount
   
   flashback database to timestamp
   to_timestamp ('12/31/2003 22:30:00,
   'mm/dd/yyyy hh24:mi:ss');
   
   alter database open read only;
   

4. After issuing the same query from ACCOUNTS—

   select status, count(*) 
   from BANK.ACCOUNTS 
   group by status;
   

   —he sees that the status for all accounts is "A," which means he went back too far. He has to go forward from there—to 10:45 p.m.—as follows:

   shutdown immediate
   startup mount
   
   flashback database to timestamp
   to_timestamp ('12/31/2003 22:45:00,
   'mm/dd/yyyy hh24:mi:ss');
   
   alter database open read only;
   

5. Now the status query—

   select status, count(*) 
   from bank.accounts 
   group by status;
   

   —shows "F" for all accounts: the exact place he needs to be. John can go backward and forward to get to the exact spot, as long as the recovery area has the necessary flashback logs and the database is opened in read-only mode. Now that John has flashed back to the exact moment he needed, he can open the database for update as follows:

   shutdown immediate
   startup mount
   alter database open resetlogs;
   

   Note that flashbacks can also be done through Oracle Recovery Manager (RMAN), while the database is in mount exclusive (or read-only) mode, as follows:

   RMAN> flashback database to scn 2203167700;
   
   Starting flashback at 01-JAN-04
   allocated channel: ORA_DISK_1
   channel ORA_DISK_1: sid=10 devtype=DISK
   starting media recovery
   media recovery complete
   Finished flashback at 01-JAN-04
   

There are limits to Flashback Database operation. A database can't be flashed back

• To a point prior to when it was opened with the RESETLOGS option.
• If the control file has been restored or re-created, if a tablespace has been dropped, or if a datafile has been shrunk. The expansion of the data is handled by flashback, however, so automatic extension of files due to the autoextend option is not an issue.

Estimation of Recovery Area

When John is mulling over his options, one question that comes up is how far back in the past he can flash back the database. He finds the answer in two views. The first view, V$FLASHBACK_ DATABASE_LOG, shows how much flashback has been generated and placed in the recovery area. The following queries the view and displays the columns in a vertical format:

select * 
from v$flashback_database_log;
OLDEST_FLASHBACK_SCN      :2203146511
OLDEST_FLASHBACK_TIME     :30-DEC-2003 18:37:09
RETENTION_TARGET          :10000
FLASHBACK_SIZE            :16384000
ESTIMATED_FLASHBACK_SIZE  :10857984000

This shows that John can flash back to a maximum of either SCN 2203146511 or 6:37:09 p.m. on December 30, 2003. The current expected flashback retention is 10,000 minutes as specified by the initialization parameter value db_flashback_retention_target, but the size of the recovery area to hold that much flashback log has to be at least the value of the column ESTIMATED_FLASHBACK_SIZE. The recovery area currently holds 16,384,000 bytes of flashback information (FLASHBACK_SIZE). The estimated value helps John to allocate the space for the recovery area to achieve depth of flashback capability.

Next Steps READ more about Flashback Flashback SQL Reference Guide "Get a Movie, Not a Picture: Flashback Versions Query" Oracle Database 10g: Flashback Technology

The other question to come up eventually is how much space is required for the logging operation while the database is in flashback mode. Another view, V$FLASHBACK_DATABASE_STAT, shows how much flashback information has been collected so far, as in Listing 2. The view shows the amount of logged information in one-hour intervals specified by the begin and end times. The column FLASHBACK DATA shows how much flashback log has been generated in that interval in bytes. The redo log entries generated and the data blocks changed are recorded in the columns REDO_DATA and DB_DATA, respectively. This helps in understanding the nature of the flashback log generation and estimating the size of the recovery area required.

If flashback is not necessary anymore, John can turn it off with the following:

alter database flashback off;

All the OMF files created in the recovery area are deleted automatically when flashback is turned off. John verifies the flashback status of the database by querying the column FLASHBACK_ON in the view V$DATABASE.

SQL> select flashback_on 
from v$database;

FLASHBACK_ON
------------
NO

Conclusion

Flashback operations in Oracle Database 10g have evolved from their introduction in Oracle9i to include flashback at the table level, as well as more powerful operations such as Flashback Database, where an entire database can be reinstated to a point in time in the past. With flashback, the DBA becomes a precise time traveler, controlling exactly how much time to trace back and at what granularity.