Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

TRANSACTION BACKOUT in Oracle 11g

SQL Tips by Donald Burleson

Oracle 11g New Features Tips

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. 

% TRANSACTION BACKOUT utilizes LOGMINER functionalities.

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.

 
This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr.

You can buy it direct from the publisher for 30% off.

 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational