Oracle 11g Flashback Transaction Backout(原创)

Flashback Transaction Backout
Oracle Database 11g introduces the flashback transaction backout feature, which lets you perform logical recovery by undoing changes made by a transaction as well as its dependent transactions. It is easy to maintain data consistency because you can back out transactions that include a sequence of insert, update, and delete statements with a single execution of the TRANSACTION_BACKOUT procedure belonging to the DBMS_FLASHBACK package. You can do the same thing through the Enterprise Manager, which uses the TRANSACTION_BACKOUT procedure as well, to back out the changes made by a transaction or set of transactions with just a single click on your part.
A dependent transaction can have either a write-after-write or a primary key constraint relationship with the parent transaction:

• In a write-after-write relationship, the dependent transaction modifies the data that was previously modified by the parent transaction.
• Under a primary key constraint relationship, the dependent transaction reinserts the primary key deleted by the parent transaction.

In order to undo the changes brought about by a transaction, the database executes appropriate compensating transactions to return the data to its original state. Because the flashback transaction backout feature needs both the undo as well as the redo data generated for the undo blocks to execute the compensating transactions, you’ll need the necessary undo data and the archived redo logs to undo a transaction.

Prerequisites for Flashback Transaction Backout
You must enable supplemental logging in the database to enable the flashback transaction backout feature. So, first issue the following statements to turn supplemental logging on in the database:

SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key) columns;
You must also grant the following privileges to any user that wants to use the flashback transaction backout feature. The following statements grant the necessary privileges to the user HR:
SQL> grant execute on dbms_flashback to hr;
SQL> grant select any transaction to hr;

The first privilege grants the user HR the flashback system privilege and the second, the select any transaction privilege. If a user wants to perform a transaction backout operation in another user’s schema, the first user must also have the necessary DML privileges on the table or tables in the second user’s schema.

Using the DBMS_FLASHBACK.TRANSACTION_BACKOUT Procedure
You can use the new DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure to back out transactions. Here’s the structure of the DBMS_ FLASHBACK.TRANSACTION_BACKOUT procedure:
PROCEDURE TRANSACTION_BACKOUT
 Argument Name        Type           In/Out     Default?
 ----------------   --------------   ---------   ----------
NUMBEROFXIDS        NUMBER           IN
XIDS                XID_ARRAY        IN
OPTIONS             BINARY_INTEGER   IN           DEFAULT
SCNHINT             TIMESTAMP        IN
Here’s a brief explanation of the four key parameters in the DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure:

• numberofxids is the number of transactions you want to back out in this operation.
• xids  A list of transaction identifiers that are passed as an array.
• options  Enables you to specify the order in which to back out the parent and the child transactions. You can use the following four values for the options parameter:

1. The nocascade value is the default and you use it when you don’t expect a transaction to have any dependent transactions.If you use the default value of nocascade for the options parameter, it means that you’re expecting the parent transaction doesn’t have any dependent transactions.
   
2. The cascade value backs out the dependent transactions before backing out the parent transaction.
   
3. The nocascade_force value backs out only the parent transactions. It ignores any dependent transactions.
   
4. The noconflict_only option backs out only those rows in the parent transaction that don’t have any conflicts.

• scnhint  You use the scnhint parameter to specify the SCN at the start of the transaction. The SCN must be before the start of the first transaction in the transaction set to be backed out.Similarly, you can replace the scnhint parameter with the timehint parameter, which enables you to provide a time hint on the start of the transaction. You must provide a timehint parameter if you’re using transaction names instead of transaction identifiers. The length of time for which the DBMS_FLASHBACK.TRANSACTION_ BACKOUT operation executes depends directly on the amount of redo generated by the transactions being backed out.

Once you execute the DBMS_FLASHBACK. TRANSACTION_BACKOUT procedure, the transactions you name aren’t automatically backed out by the database. The procedure checks the dependencies among transactions and performs the DML operations, but doesn’t commit them. Instead, it provides you with a report of its work. In the meantime, it holds locks on the rows and the tables in order to keep new transactions from affecting the backout operation. In order for the transactions to be backed out for good, you must issue a commit statement.

Using the TRANSACTION_BACKOUT Procedure

The following exercise shows you how to use the DBMS_FLASHBACK. TRANSACTION_BACKOUT procedure to back out a transaction along with its dependent transactions. Before you can execute the DBMS_FLASHBACK .TRANSACTION_BACKOUT procedure, you must first create a variable of an XID_ARRAY type. This array will hold a set of transaction identifiers as the starting point of the dependency search. Alternately, you can use a set of transaction names to identify the transactions.
declare
   trans_arr xid_array;
begin
   trans_arr := xid_array('030003000D02540','D10001000D02550');
   dbms_flashback.transaction_backout (
        numtxns         => 1,
        xids            => trans_arr,
        options         => dbms_flashback.nocascade
   );
end;
The column XIDS passes an array of transactions as input to the procedure. The default value for the options parameter is cascade, but I chose nocascade in this example. When you execute this procedure, the primary transaction and its dependent transaction are rolled back in one step. Although the database names the backout operation, for auditing purposes, Oracle recommends that you name your backout operation. Successful execution of the TRANSACTION_BACKOUT procedure means that the database backed out a single parent transaction.
TRANSACTION_BACKOUT Reports

We use this with the TRANSACTION_BACKOUT procedure to flashback the transaction ID of "060015009D030000".

BEGIN
  DBMS_FLASHBACK.transaction_backout (numtxns => 1,
                                      xids    => xid_array('060015009D030000'),
                                      options => DBMS_FLASHBACK.cascade);
END;
/
Querying the test table shows that the row has been removed.
SQL> SELECT * FROM test_user.test_tab;
no rows selected
We can use the transaction ID to query the DBA_FLASHBACK_TXN_STATE view.
SELECT *
FROM   dba_flashback_txn_state
WHERE  xid = '060015009D030000';
COMPENSATING_XID XID              DEPENDENT_XID    BACKOUT_MODE     USERNAME
---------------- ---------------- ---------------- ---------------- ------------------------------
05001800A0030000 060015009D030000 02000B00DB030000 CASCADE          SYS
1 row selected.
The COMPENSATING_XID returned from this query is used to query the DBA_FLASHBACK_TXN_REPORT view.
COLUMN xid_report FORMAT A80
SET LONG 100000
SELECT xid_report
FROM   dba_flashback_txn_report
WHERE  compensating_xid = '05001800A0030000';
XID_REPORT
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="05001800A0030000">
        <TRANSACTION XID="060015009D030000">
        <CHARACTERISTICS>
        </CHARACTERISTICS>
        <UNDO_SQL>
                <USQL exec="yes">
                 delete from "TEST_USER"."TEST_TAB" where "ID" = '2' and "DESCRIPTION" = 'Desc
ription for 2'
                </USQL>
        </UNDO_SQL>
XID_REPORT
--------------------------------------------------------------------------------
        <DEPENDENT_XIDS>
                <TRANSACTION XID="02000B00DB030000">
                <CHARACTERISTICS>
                </CHARACTERISTICS>
                <UNDO_SQL>
                        <USQL exec="yes">
                         update "TEST_USER"."TEST_TAB" set "DESCRIPTION" = 'Description for 2' where
"ID" = '2' and "DESCRIPTION" = 'Field'
                        </USQL>
                </UNDO_SQL>
                <DEPENDENT_XIDS>

XID_REPORT
--------------------------------------------------------------------------------
                </DEPENDENT_XIDS>
                </TRANSACTION>
        </DEPENDENT_XIDS>
        </TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>update "TEST_USER"."TEST_TAB" set "DESCRIPTION" = 'Description for 2'
 where "ID" = '2' and "DESCRIPTION" = 'Field'
</EXEC_USQL>
<EXEC_USQL>delete from "TEST_USER"."TEST_TAB" where "ID" = '2' and "DESCRIPTION"
 = 'Description for 2'
</EXEC_USQL>

XID_REPORT
--------------------------------------------------------------------------------
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>
1 row selected.

 

参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》

       http://www.oracle-base.com/articles/11g/flashback-and-logminer-enhancements-11gr1.php#flashback_data_archive

本文原创，转载请注明出处、作者

如有错误，欢迎指正

邮箱:[e-mail]