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 


 

 

 


 

 

 

 

 

11g FLASHBACK Compensating Transaction

Oracle 11g New Features Tips by Donald BurlesonJune 29, 2015

Oracle 11g New Features Tips

When the procedure completes successfully, it has created a compensating transaction, having backed out the original transaction(s). This compensating transaction does not include a commit.

Here is an example in which two tables, t and t_d, have been used. Table t has a primary key on col1 and table t_d has a foreign key on col1 which references table t (col1):

LUTZ  @ orcl SQL> SELECT versions_xid , versions_operation , col1
                FROM t
                     VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

VERSIONS_XID     V       COL1
---------------- - ----------
020017004B020000 I          4
020017004B020000 I          5
020017004B020000 I          6
020017004B020000 I          7
020017004B020000 I          8
020017004B020000 I          9
020017004B020000 I         10
03001300F8010000 D         10
03001300F8010000 D          9
03001300F8010000 D          8
03001300F8010000 D          7
03001300F8010000 D          6
03001300F8010000 D          5
03001300F8010000 D          4
                            1
                            2
                            3
                            4
                            5
                            6
                            7
                            8
                            9
                           10

24 rows selected.

LUTZ  @ orcl SQL> SELECT versions_xid , versions_operation , col2
                FROM t_d
                     VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

VERSIONS_XID     V       COL2
---------------- - ----------
020017004B020000 U         11
03001300F8010000 U         14
090007005B020000 U         11
                           10

With id 03001300F8010000, one row has updated (the only row in the table) in table t_d, and deleted 7 rows in table t.

One could even have a look at the sql statements which would undo the transaction:

LUTZ  @ orcl SQL> SELECT undo_sql
                    FROM flashback_transaction_query
                      WHERE xid=HEXTORAW('03001300F8010000');

UNDO_SQL
-------------------------------------------------------------------------------
insert into "LUTZ"."T"("COL1") values ('10');
insert into "LUTZ"."T"("COL1") values ('9');
insert into "LUTZ"."T"("COL1") values ('8');
insert into "LUTZ"."T"("COL1") values ('7');
insert into "LUTZ"."T"("COL1") values ('6');
insert into "LUTZ"."T"("COL1") values ('5');
insert into "LUTZ"."T"("COL1") values ('4');
update "LUTZ"."T_D" set "COL2" = '11' where ROWID = 'AAAO2xAAEAAAAAXAAA';

Next, the PL/SQL block is called with the value 03001300F8010000 for the exchange variable at the prompt.

After a while, a message is received with the information that the procedure has successfully completed.

% The status of the transaction and the dependent and compensating transactions             can be reviewed  with the *_FLASHBACK_TXN_STATE views

LUTZ  @ orcl SQL> SELECT * FROM user_flashback_txn_state;

COMPENSATING_XID XID              DEPENDENT_XID    BACKOUT_MODE
---------------- ---------------- ---------------- ----------------
03001300F7010000 09000C0058020000                  CASCADE
03001300F7010000 0100180013020000 09000C0058020000 CASCADE
020017004B020000 03001300F8010000                  CASCADE
0400140049020000 05001E001D020000                  CASCADE

% The compensating transaction is not committed automatically!

% The rows are locked by the compensating transaction.

Here it is possible to see multiple rows for the same transaction; one for each dependent transaction.

At this point,  it is possible to check the results of the compensating transaction to see if they are satisfactory. The DBA can also choose to commit, or respectively rollback, the compensating transaction if the results are unsatisfactory.

The results of executed, but not yet committed or rolled back, compensating transactions can be viewed in two dynamic performance views:

1.      v$flashback_txn_mods shows all modifications of all compensating transactions in memory:

SYS AS SYSDBA @ orcl SQL> desc V$FLASHBACK_TXN_MODS

Name                          Null?    Type
---------------------------   -----    ------
COMPENSATING_XID                       RAW(8)
COMPENSATING_TXN_NAME                  VARCHAR2(255)
XID                                    RAW(8)
TXN_NAME                               VARCHAR2(255)
PARENT_XID                             RAW(8)
INTERESTING                            NUMBER
ORIGINAL                               NUMBER
BACKOUT_SEQ                            NUMBER
UNDO_SQL                               VARCHAR2(4000)
UNDO_SQL_SQN                           NUMBER
UNDO_SQL_SUB_SQN                       NUMBER
BACKOUT_SQL_ID                         NUMBER
OPERATION                              VARCHAR2(30)
BACKEDOUT                              NUMBER
CONFLICT_MOD                           NUMBER
MODS_PER_LCR                           NUMBER
 

2.      v$flashback_txn_graph shows the dependencies and conflicting operations after a compensating transaction has been started but are neither committed or rolled back yet:

SYS AS SYSDBA @ orcl SQL> desc V$FLASHBACK_TXN_GRAPH

 Name               Null?    Type
 ------------------ -------- -------------
COMPENSATING_XID             RAW(8)
COMPENSATING_TXN_NAME        VARCHAR2(255)
XID                          RAW(8)
TXN_NAME                     VARCHAR2(255)
PARENT_XID                   RAW(8)
INTERESTING                  NUMBER
ORIGINAL                     NUMBER
BACKOUT_SEQ                  NUMBER
NUM_PREDS                    NUMBER
NUM_SUCCS                    NUMBER
DEP_XID                      RAW(8)
DEP_TXN_NAME                 VARCHAR2(255)
TXN_CONF_SQL_ID              NUMBER
DEP_TXN_CONF_SQL_ID          NUMBER

% Detailed information about all compensating transactions in the database,             including all dependencies, can be read from the data dictionary with the data            dictionary views

  • flashback_txn_report (committed transactions!)

  • flashback_txn_state (current state with dependency report)

LUTZ  @ orcl SQL> desc  user_flashback_txn_report;

 Name                       Null?   Type
 -------------------------- ------  ----
 COMPENSATING_XID           NOT NULL RAW(8)
 COMPENSATING_TXN_NAME               VARCHAR2(256)
 COMMIT_TIME                         DATE
 XID_REPORT                          CLOB

LUTZ  @ orcl SQL>  SELECT xid_report
                    FROM user_flashback_txn_report
                     WHERE COMPENSATING_XID=HEXTORAW('020017004B020000'); 

XID_REPORT
----------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="020017004B0200

Internally, the server has used logminer functionality and retrieved all information needed for the backout operation from the redo log files. The server has also found all dependencies and created a list of statements. After this, it has applied the compensating statements ordered in the correct sequence.

The dependency handling can be managed with the options parameter of the transcation_backout procedure. There are several possibilities:

  • NOCASCADE (default) - fails if there are dependencies.

  • nonconflicting_only - flashes back only those rows which do not have any conflicting rows.

  • NOCASCADE_FORCE - only the given particular transactions from the array, are flashed back with no respect to any other dependent transaction.  Note that the transactions are flashed back in reverse order of their commit times

  • CASCADE - all dependent transactions are flashed back  in a reversed order.

It is possible to back out not only the specified transaction but also all dependent transactions with CASCADE option:

% DEFAULT for the OPTIONS parameter is NOCASCADE

% Using NOCASCADE throws an ORA-55504 if there are dependent                          transactions

% The TRANSACTION_BACKOUT procedure automatically checks            dependencies:

  • dependencies produced by write after write operations

  • dependencies of primary key foreign key relationships

The privileges needed for a transaction backout are:

  • EXECUTE on dbms_flashback

  • SELECT ANY TRANSACTION (in OEM and for flashback_transcation_query view)

  • SELECT, FLASHBACK and DML privileges on all tables involved in the transactions need to be backed out.

The limitations for transaction backout are:

  • Only committed transactions can be flashed back.

  • Transactions which have dependent transactions can only be flashed back with either the cascade, nonconflict_only, or nocascade_force options.

  • DDL statements are not allowed to be flashed back in transactions.

  • Only transactions which do not make any changes which are not supported by the logminer can be flashed back.

  • All dependent transactions must be committed before the backout operation.

o        There can not be any dependent transactions which have been aborted more than one minute after the beginning of the backout operation.

o        A backout operation can only be started if there is no active ongoing logminer session.

% Best practices for transaction backout:

  • run dbms_flashback.transaction_backout as soon as possible after you found out that it needs to be done.

  • The longer you wait the worse the performance of the back out operation will be.

 

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