TRANSACTION BACKOUT in Oracle 11g

Transaction backout without OEM using PL/SQL

As of  10g, data could be read as a point in time in the past,  and undo sql was viewed and manually applied to a segment level using flashback table. Undoing all changes to a point in time in the past consisted of using OEM, Grid Control or Database control to flashback database on a database level. However, it was not possible to apply undo for an entire transaction, including all dependent transactions. As of 11g however, flashing back transactions is now possible.  This includes all dependencies and writing after write operations.

The functionality used to flashback transactions is called TRANSACTION BACKOUT. 

There are two interfaces for this feature:

• the new built in package DBMS_FLASHBACK

• Oracle Enterprise Manager, including  OEM, Grid Control and Database Control

Before this feature can be used, the database has to be prepared. Because the LOGMINER functionality is being used to flashback transactions,  all necessary information should be recorded in the redo log files. In other words, supplemental logging should be enabled, and it should be ensured that Oracle adds additional information into the redo stream.  This warrants two important things:

1.      Oracle must be able to group and merge information for DML operations for objects like Index Organized Tables (OITs), clustered tables, and chained blocks. This is enabled by adding minimal supplemental logging data to the redo logs. Oracle will then be able to store the before image of the modified columns into the redo log files  necessary for transaction recovery or instance recovery. Furthermore, Oracle will also store additional information about other columns in the row used as examples to reconstruct a full row from the redo independent of the physical address of the row. 

• The before image consists of all the logical information needed to undo a change applied to a block. This is used to perform any kind of recovery, transaction recovery, or instance recovery. It is stored in the undo segments and redo logs. This being, redo is needed for the undo, in case an instance crash occurs to reconstruct undo information which was lost in the cache and was not on disk. 

This must be enabled for a transaction backout operation, which can be done using the following statement:

SYS AS SYSDBA @ orcl SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;           

2.      When a row with a primary key is updated, all the columns of primary keys should be placed into the redo logs. The updated row can then be identified by the primary key as well as the rowid.

• It is also possible that supplemental uses other information to reconstruct and identify a row.  For example, if a table has no primary key but one or more non-nullable columns with unique constraints, one of the unique index keys  is used to identify a row by adding this value to the redo logs for a DML operation.  Also, if a table does not have a non-null unique constraint, all of the row data, except for LONG and LOB datatypes, are supplementally logged.

This process of updating the primary key and updating a row to have the primary key and rowid identification is called identification key logging at database level and can be enabled with:

SYS AS SYSDBA @ orcl SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

To see if supplemental logging is enabled, the following query can be used:

SYS AS SYSDBA @ orcl SQL> SELECT supplemental_log_data_pk AS pk_sup,       
                                   supplemental_log_data_min AS min_sup
                            FROM v$database;

PK_SUP MIN_SUP
------ --------
YES    YES

Only after making these two changes to the database can the logminer  functionality  be used to backout transactions.