TECHNOLOGY: Inside OCP
New Ways to Flash Back
By Aradhana Puri
More Oracle Flashback concepts and sample questions
Oracle Database 10g introduces significant advances in the area of Oracle Flashback technology. In my last column, I discussed sample questions related to Oracle Flashback Drop and Oracle Flashback Version Query. In this column, I continue my discussion of Oracle Flashback technology, with information on Oracle Flashback Transaction Query and Oracle Flashback Database.
This column presents questions of the type you may encounter when taking the Oracle Database 10g Administration Workshop II exam. (Note that the sample question format has been adjusted for presentation in this article.) The Oracle Database 10g Administration Workshop II exam is one of the two exams you must take to achieve the Oracle Certified Professional (OCP) level of certification.
Oracle Flashback Transaction Query
Oracle Flashback Transaction Query lets you view the changes made by a transaction (or by all transactions) to the rows of one or more tables during a period of time. It allows you to examine changes to the database at the transaction level, so you can diagnose problems, perform analysis, and audit transactions.
You ran a batch job by mistake (transaction identifier=0400B00322B000), and you need to identify the changes made to the database tables by the batch job.
Which view would you query?
The correct answer is B. The FLASHBACK_TRANSACTION_QUERY view lists all the changes made by a transaction. The FLASHBACK_TRANSACTION_QUERY view also provides the SQL statements that can be used to undo the changes made by the transaction. (Note that a single transaction may contain statements that modify multiple rows.)
You are informed that the record for an employee with employee ID 100 is missing from the HR.EMP table. You need to identify the following:
Which would you use?
A. Oracle Flashback Drop only
The correct answer is C. Oracle Flashback Version Query and Oracle Flashback Transaction Query are complementary features. Oracle Flashback Version Query provides a history of the changes made to a row, along with the corresponding identifiers of the transactions that made the changes. You use the transaction identifier provided by the flashback version query in the flashback transaction query.
Answer A is incorrect because you use the Oracle Flashback Drop feature to undo the effects of the DROP TABLE statement. Answers B and D are incorrect because Oracle Flashback Version Query and the RMAN REPORT command do not provide undo SQL statements and information regarding the user who executed the transaction.
To identify the information in the preceding question, first use the following flashback version query to identify the transaction identifier for the transaction that deleted employee_id =100:
SQL> SELECT versions_xid, versions_operation 2 FROM hr.emp 3 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE 4 WHERE employee_id=100; VERSIONS_XID VERSIONS_OPERATION --------------------- --------------------- 040022004E2B0000 D
Next, use the transaction identifier ( 040022004E2B0000 ) in the following flashback transaction query to find out which operations were performed by the transaction, the SQL statements for undoing those changes, and the user who executed the transaction:
SELECT operation, undo_sql, logon_user FROM FLASHBACK_TRANSACTION_QUERY WHERE xid= HEXTORAW('040022004E2B0000');
You can also perform flashback version query and flashback transaction query by using Oracle Enterprise Manager 10g. Figure 1 shows the result of a flashback transaction query in Oracle Enterprise Manager 10g.
Oracle Flashback Database
The Oracle Flashback Database feature is similar to conventional point-in-time recovery in its results, allowing you to return a database to its state at a time in the recent past. It is, however, much faster than point-in-time recovery, because it does not require restoration of datafiles from a backup and it requires application of fewer changes from the archived redo logs. Oracle Flashback Database uses Oracle Flashback Database logs. At regular intervals, the database copies images of each altered block in every datafile into flashback logs. You can use the Oracle Recovery Manager (RMAN) FLASHBACK DATABASE command or the SQL FLASHBACK DATABASE command to execute the Oracle Flashback Database operation. For example, you can use either of the following commands to flash back the database to SCN 54678:
RMAN> FLASHBACK DATABASE TO SCN=54678 SQL> FLASHBACK DATABASE TO SCN 54678
Your Oracle 10g database is running in ARCHIVELOG mode, and a flash recovery area is configured. The database is in the MOUNT EXCLUSIVE state. Which steps would you follow to configure Oracle Flashback Database? (Choose all that apply.)
A. Open the database in read-only mode.
The correct answers are B and C. You use the DB_FLASHBACK_RETENTION_TARGET initialization parameter to specify an upper limit, in minutes, on how far back you want to be able to flash back the database. You use the ALTER DATABASE FLASHBACK ON command to enable the Oracle Flashback Database feature. (You can use the ALTER DATABASE FLASHBACK OFF command to disable the Oracle Flashback Database feature.)
Answer A is incorrect because you can enable Oracle Flashback Database only when the database is mounted in exclusive mode, not when it is open. Answer D is incorrect because you don't have to enable block change tracking to configure the Oracle Flashback Database feature.
You have configured the Oracle Flashback Database feature for your database. Which statements are correct in this scenario? (Choose all that apply.)
A. Flashback logs will be generated for all the tablespaces by default.
The correct answers are A and B. Flashback logs are generated for all the tablespaces by default and are automatically deleted when you disable the Oracle Flashback Database feature.
Answer C is incorrect because you can use the Oracle Flashback Database feature to recover from logical data corruptions or user errors, but in the case of physical corruption of your database, you must use traditional recovery methods. Answer D is incorrect because flashback logs are not archived.
You are required to flash back your database. You need to find out the approximate time and the oldest SCN to which you can flash it back. How would you get this information?
A. Query the V$FLASHBACK_DATABASE_LOG view.
The correct answer is A.
Answer B is incorrect because the V$FLASHBACK_DATABASE_STAT view provides information regarding the overhead of logging flashback data in the flashback logs. Answer C is incorrect because the time period specified by the DB_FLASHBACK_RETENTION_TARGET initialization parameter is not an absolute guarantee that flashback will be available. Flashback logs are stored in the flash recovery area. If the space is needed in the flash recovery area, flashback logs may be deleted automatically. Answer D is incorrect because the V$RECOVERY_FILE_DEST view provides information regarding the flash recovery area.
Note that you can use the following query to find the oldest SCN to which you can flash back your database:
SELECT oldest_flashback_scn FROM V$FLASHBACK_DATABASE_LOG;
This column has discussed sample exam questions associated with Oracle Flashback technology, specifically with Oracle Flashback Transaction Query and Oracle Flashback Database. Note that in addition to these flashback features and the Oracle Flashback Version Query and Oracle Flashback Drop features described in my last column, you can use the Oracle Flashback Table feature to return a table to its state at a previous point in time.
Aradhana Puri (firstname.lastname@example.org) is a principal Oracle Certification Exam developer at Oracle.