As Published In
Oracle Magazine
September/October 2008

TECHNOLOGY: OCP


More Ways to Flash Back

By Sushma Jagannath

Query history and recover from corruptions with Oracle Flashback technology.

Oracle Flashback technology comprises a group of Oracle Database 11g features with which you can view data back and forth in time and recover data without using point-in-time media recovery. These features are built in to Oracle Database and exposed via a set of simple SQL commands.

The Oracle Flashback technology uses Oracle’s Automatic Undo Management system to obtain metadata and historical data for transactions. Using this information, it can roll back a transaction and its dependent transactions while the database remains online.

Oracle Flashback technology can do the following:

 

  • Query data as it existed in the past
  • Identify changes done by an erroneous query and request the undo SQL to reverse those changes
  • Recover a table to a previous point in time
  • Recover a database to a previous point in time


This column focuses on some of the Oracle Flashback features that help you query historical data, perform change analysis, and perform self-service repair to recover from logical corruptions while the database is online. It presents sample questions of the type you may encounter when taking the Oracle Database 11g Administration Workshop II exam. Passing this exam is a requirement for achieving the Oracle Certified Professional level of certification.

Oracle Flashback Query

By default, database operations use the most-recent committed data available. If you want to query the database as it was at some time in the past, however, you can do so with Oracle Flashback Query, which queries all data as it existed at a specific point in time or at a specific user-specified system change number (SCN). Users can set the date and time, and then any SQL query they execute will operate on the data as it existed at that time.

John is a DBA at LMN Inc. and manages a 200GB database. One of the developers reports to John that he just accidentally deleted 100,000 rows of data from the SALES table and that the data needs to be recovered. John needs to recover the data as quickly as possible without any data loss. Considering that the undo information is available, which of the following is the best method for recovering the lost data and satisfying the recovery requirements?

A. Restore the entire database
B. Use the Import utility
C. Use Oracle Flashback Query
D. Use Oracle Flashback Drop

The correct answer is C. Oracle Flashback Query uses undo segments, provides a snapshot of the data at a specific time, and enables the data to be reinserted. John can issue the following statement to restore the data:

 

INSERT INTO SALES 
(SELECT * FROM 
SALES AS OF TIMESTAMP 
TO_TIMESTAMP('<required point in time>',
'DD-MON-YY HH24:MI:SS') MINUS 
SELECT * FROM SALES); 


Answer A is incorrect because the restoration of the entire database takes a lot of time compared to reinserting the data based on information provided by Oracle Flashback Query. Answer B is incorrect because there may be data loss since the last export. Answer D is incorrect because Oracle Flashback Drop is used to recover the entire table from an erroneous DROP statement and not to restore specific data in the table.

Oracle Flashback Transaction Query

Oracle Flashback Transaction Query lets you view changes made to the database at the transaction level. This enables you to diagnose problems in your database and perform analysis and audits of transactions. Given a transaction ID, Oracle Flashback Transaction Query retrieves all changes that were performed by that transaction and the corresponding undo SQL. The undo SQL can then be used to quickly revert changes across affected rows, to rectify a bad transaction, or for testing purposes.

Which database transactions can you view with Oracle Flashback Transaction Query?

A. All the transactions committed on the same day
B. All the transactions from the time the database was created
C. All the transactions for which you have undo information retained in the undo tablespace to satisfy the specified time stamp or SCN
D. All the transactions committed within the same session

The correct answer is C. As long as you have the required undo information available in the undo tablespace that corresponds to the required point in time and SCN, you will be able to use Oracle Flashback Transaction Query to view the database transaction.

Oracle Flashback Table

When logical errors span a table or a set of tables, traditional tablespace point-in-time recovery requires restoration of the tablespace to an auxiliary database instance; a media recovery to a point in time before the logical error; and finally, export or import of the dictionary metadata for the recovered tablespace. However, with Oracle Flashback Table, the DBA can recover a table or a set of tables to a specified point in time quickly, while the database is online. Oracle Flashback Table restores the tables while maintaining associated attributes such as indexes, triggers, and constraints. All dependent objects and referential integrity as specified by constraints are also preserved. The following SQL statement demonstrates how to use this feature to recover a table to a specified point in time:

 

FLASHBACK TABLE 
                               
table_name
TO TIMESTAMP TO_TIMESTAMP('2008-005-10 14:00:00', 'YYYY-MM-DD HH24:MI:SS');


At 7:00 a.m., you noticed that an erroneous update transaction at 2:00 p.m. the previous day had changed the LASTNAME column for all employees in the EMP table to an empty string. Several transactions have taken place since then on EMP and other tables. You need to return the original data to the LASTNAME column and reinstate the EMP table as it was just before the erroneous update. You must also ensure that

 

  • No other table is affected
  • The operation takes the least-possible amount of time


Which option must you choose?

A. Oracle Flashback Drop
B. Oracle Flashback Transaction Query along with Oracle Flashback Drop
C. Oracle Flashback Table
D. Oracle Flashback Database

The correct answer is C. Answer A is incorrect because Oracle Flashback Drop is used to undo the effects of the DROP TABLE statement. Answer B is incorrect because Oracle Flashback Transaction Query is a diagnostic tool for viewing changes made to the database at the transaction level. Although it enables you to perform analyses and audits of transactions, Oracle Flashback Transaction Query alone or with Oracle Flashback Drop cannot restore the data. Answer D is incorrect because Oracle Flashback Database restores the entire database to an earlier point in time, undoing all the changes that have taken place since then. This would undo all transactions in other tables as well.

Oracle Flashback Database

Databasewide logical corruptions or errors can include a combination of missing tables or rows, truncated tables, invalid PL/SQL procedures, and many other data manipulation language and data definition language mistakes. Because of the variety and widespread nature of the errors, the only option when you encounter such a problem is to restore the last good backup and recover the database to a "clean" point in time, potentially entailing major system downtime.

Oracle Flashback Database provides database point-in-time recovery by returning the database to a past time or SCN—without requiring a backup of the database to be restored. The database maintains an additional piece of information, flashback logging, which captures block changes to the database. Before you enable Oracle Flashback Database, a flash recovery area must be defined.

You have configured the flash recovery area and have put the database in FLASHBACK mode. What are the other prerequisites for using Oracle Flashback Database?

A. The database must be opened.
B. The database must run in ARCHIVELOG mode.
C. The database must not contain any online tablespaces for which the flashback functionality was disabled.
D. The control file must be restored from the backup.

Next Steps



LEARN more about
Oracle Flashback Technology
Oracle Certification Program

 READ Inside OCP columns


The correct answers to this question are B and C. Answer A is incorrect because the database must be in MOUNT state and should not be opened. Answer D is incorrect because the control file must be the current control file and cannot be a backed-up or re-created file. When the database control file is restored from backup or re-created, all existing flashback log information is discarded.

Conclusion

This column has focused on some of the Oracle Flashback features that provide easy-to-use options for self-service repair:

Oracle Flashback Query enables the user to query data at some point in time to reconstruct lost data that may have been deleted or changed by accident.

Oracle Flashback Transaction Query provides a mechanism for viewing changes made to the database at the transaction level.

Oracle Flashback Table enables users to quickly recover a table or a set of tables (including all dependent objects and referential integrity) to a previous specified point.

Oracle Flashback Database provides a way to quickly rewind an Oracle Database instance, with a single SQL command, to a previous time to correct any problems caused by logical data corruption or user error.

Table 1 lists key Oracle Flashback technologies and their object levels, suggested uses, and dependencies.

 

figure 1
Table 1: Oracle Flashback technologies

 



Sushma Jagannath
is a Certification Exam Development manager at Oracle. She has been with the company since 2000.

Send us your comments