As Published In November/December 2008

TECHNOLOGY: OCP

New Ways to Flash Back

Reverse transactions and track historical changes in Oracle Database 11g.

First introduced in Oracle9i Database, Oracle Flashback technology comprises a group of features with which you can view data at different points in time and recover data without using point-in-time media recovery.

Oracle Database 11g offers two key new flashback technology features that continue to provide DBAs with a powerful set of data protection tools:

• Oracle Flashback Transaction rolls back a transaction and its dependent transactions while the database remains online.
• Oracle Flashback Data Archive provides solutions for retaining transactional changes to data for a long period.

This column focuses on these two new flashback features of Oracle Database 11g and presents sample questions of the kind 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 Transaction

Oracle Flashback Transaction can roll back a specific transaction and its dependent transactions while the database remains online. It uses undo data and the archived redo logs for the specified transactions to create and execute the compensating transactions that return the affected data to its original state. Oracle Flashback Transaction requires that supplemental logging is enabled, and the user must have SELECT, FLASHBACK, and DML privileges on all affected tables. This operation can be performed with either a PL/SQL procedure (DBMS_FLASHBACK .TRANSACTION_BACKOUT) or an Oracle Enterprise Manager wizard.

Examine the following commands issued by the DBA:

SQL> alter database add supplemental 
log data;
Database altered.

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

For which of these are the above commands prerequisites?

A. Oracle Flashback Transaction
B. Fine-grained audit
C. Oracle Flashback Query
D. Oracle Flashback Database

The correct answer is A. These commands are required before you start Oracle Flashback Transaction; they enable the log miner to extract transactions from redo logs. Answer B is incorrect because fine-grained audit does not need redo logs. Answer C is incorrect because Oracle Flashback Query uses undo data. Answer D is incorrect because Oracle Flashback Database uses flashback log data from the flash recovery area.

Examine the following transactions:

SQL> INSERT INTO hr.regions 
VALUES (5, ’Pole’);
SQL> COMMIT;

SQL> UPDATE hr.regions SET 
region_name=’Poles’ WHERE region_id = 5;
SQL> UPDATE hr.regions SET 
region_name=’North and South Poles’ WHERE region_id = 5;
SQL> COMMIT;

SQL> INSERT INTO hr.countries 
VALUES (’TT’ , ’Test Country’ , 5);
SQL> COMMIT;

The referential integrity is set between the COUNTRIES and REGIONS tables. Which statement describes the result when the first transaction—INSERT INTO hr.regions VALUES (5, ’Pole’);—is flashed back with NOCASCADE FORCE as the recovery option?

A. The respective undo SQL statements are executed for the first, second, and third transactions.
B. The undo SQL is executed only for the first and third transactions, because of the referential integrity constraint.
C. The undo SQL is executed only for the first transaction.
D. The undo SQL statements are not executed for any of the transactions, because of a conflict among them.

The correct answer is C. NOCASCADE FORCE backs out only the transaction on which the flashback transaction operation is applied, irrespective of the dependency. Answers A and B are incorrect because there is no dependency between the second and third transactions or between the first and third transactions. Answer D is incorrect because undo SQL is not executed for any transactions when NO CONFLICT is used as the recovery option.

Oracle Flashback Data Archive

Organizations often need to track and store all transactional changes to a record for the duration of its lifetime. Oracle Flashback Data Archive satisfies long-retention requirements securely, by enabling the database to automatically track and store all transactional changes to a table for its entire lifetime. Thus, you do not need to build this functionality into database applications.

A flashback data archive consists of one or more tablespaces or parts of tablespaces. When you create a flashback data archive, you specify the name, the retention period, and the tablespace that is to be tracked. The database automatically purges historical flashback data the day after the retention period expires.

You can turn Oracle Flashback Data Archive on and off for individual tables. By default, Oracle Flashback Data Archive is turned off for every table. Examine the tablespace information in Listing 1. The database has the standard block size of 8K. Why does the following command fail to create the default flashback data archive?

Code Listing 1: Tablespace information for failed CREATE FLASHBACK ARCHIVE

SELECT TABLESPACE_NAME, BLOCK_SIZE, STATUS, CONTENTS, EXTENT_MANAGEMENT, 
ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT, BIGFILE 
FROM DBA_TABLESPACES;

TABLESPACE_NAME   BLOCK_SIZE    STATUS    CONTENTS    EXTENT_MAN    ALLOCATIO   SEGMEN    BIG
---------------   ----------    -------   ---------   -----------   ----------  -------   ---
SYSTEM            8192          ONLINE    PERMANENT   LOCAL           SYSTEM     MANUAL   NO
SYSAUX            8192          ONLINE    PERMANENT   LOCAL           SYSTEM     AUTO     NO
UNDOTBS1          8192          ONLINE    UNDO        LOCAL           SYSTEM     MANUAL   NO
TEMP              8192          ONLINE    TEMPORARY   LOCAL           UNIFORM    MANUAL   NO
USERS             8192          ONLINE    PERMANENT   LOCAL           SYSTEM     AUTO     NO
EXAMPLE           8192          ONLINE    PERMANENT   LOCAL           SYSTEM     AUTO     NO
SOEINDEX          8192          ONLINE    PERMANENT   LOCAL           UNIFORM    AUTO     NO
SOE               8192          ONLINE    PERMANENT   LOCAL           UNIFORM    AUTO     NO
ORCLFLASH         4096          ONLINE    PERMANENT   LOCAL           SYSTEM     MANUAL   NO

SQL>CREATE FLASHBACK ARCHIVE 
DEFAULT fla1 TABLESPACE orclflash RETENTION 1 YEAR;

A. The ORCLFLASH tablespace is not using automatic segment space management (ASSM).
B. The content of the ORCLFLASH tablespace is not of the UNDO type.
C. The ORCLFLASH tablespace is not a bigfile tablespace.
D. The ORCLFLASH tablespace has a nonstandard block size.

The correct answer is A. This CREATE FLASHBACK ARCHIVE command generates the following error message:

ERROR at line 1:
ORA-55627: Flashback Archive tablespace must be ASSM tablespace

Answers B and C are incorrect because you can enable Oracle Flashback Data Archive on a nonundo tablespace and also on tablespaces that are not of the bigfile type. Answer D is incorrect because the tablespace can contain nonstandard block sizes.

A user receives the following error message while truncating a table:

ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

What do you conclude from the above error message?

A. Oracle Flashback Data Archive has been enabled for the table.
B. Auditing has been applied on the table.
C. One or more automatic workload repository (AWR) baselines exist that contain performance information from transactions on the table.
D. Pending statistics exist for the table that are yet to be published.

The correct answer is A. Table-related operations such as DROP, TRUNCATE, and RENAME and certain ALTER TABLE operations cannot be performed when the table is enabled to track the changes in a flashback data archive. Answer B is incorrect because auditing tracks only the operations that are performed on the table and does not stop the TRUNCATE operation. Answer C is incorrect because AWR baselines are collected and stored for performance comparisons of the database instance and are not bound to a table. Answer D is incorrect because the collection of statistics may affect performance but does not restrict the TRUNCATE operation.

Which statements are true about Oracle Flashback Data Archive?

A. It provides access to data at any point in time without changing the current data.
B. It stores data in compressed form.
C. It provides the option of setting different retention requirements.
D. It enables access to historical data alongside current data.
E. It is enabled at a database level.
F. It archives the original data along with its associated indexes.

The correct answers are A, B, C, and D. Oracle Flashback Data Archive does not change the current data but offers the ability to access the data as of any point in time. It stores data in compressed form to minimize storage requirements. You can group historical data by retention requirements and create different data archives based on those requirements. Oracle Flashback Data Archive is an online operation—historical access can coexist with current access. Answer E is incorrect because Oracle Flashback Data Archive stores historical information in one or more tablespaces and tracks the history of one or more tables. Answer F is incorrect because Oracle Flashback Data Archive archives the original data but does not include the indexes, because the retrieval pattern of historical information might be different from that of the current information.

Conclusion

This column focused on two new flashback features introduced in Oracle Database 11g:

Oracle Flashback Transaction enables you to reverse a transaction and its dependent transactions. The database rewinds to a pretransaction state.

Oracle Flashback Data Archive enables you to track historical changes to the data in a highly secure and efficient manner, thus eliminating the need to build this functionality into database applications.
 

Send us your comments