Backing Out Transactions with Flashback

Purpose

This tutorial shows you how to back out transactions in your database using Flashback Transaction.

Time to Complete

Approximately 20 minutes

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Creating a Test Environment
	Backing Out transactions with a WAW Dependency
	Cleanup
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

What Is Flashback Transaction Back-Out?

Flashback Transaction Back-Out is a logical recovery option to roll back a target transaction and its dependent transactions while the database remains online. A dependent transaction is related by either a write-after-write (WAW) relationship, in which a transaction modifies the same data that was changed by the target transaction, or a primary key constraint relationship, in which a transaction re-inserts the same primary key value that was deleted by the target transaction. Flashback Transaction utilizes undo and the redo generated for undo blocks, to create and execute a compensating transaction for reverting the affected data back to its original state.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.	Install the database by completing the Installing Oracle Database 11g in Linux tutorial.
2.	Download and unzip the flatxn.zip file into your working directory (i.e wkdir).

Back to Topic List

Creating a Test Environment

To setup a test environment and create transactions for this OBE, perform the following steps:

1.	Navigate to your working directory and execute the flatxn_archivelog.sh script as the SYS user. ./flatxn_archivelog.sh This script unlocks the HR user and enables ARCHIVELOG mode.
2.	Using SQLPlus, connect to the database as the SYS user and run the flatxn_setup1.sql* script. sqlplus / as sysdba @flatxn_setup1.sql This setup script sets up the database and the HR user for backing out transactions with flashback. Notice the ALTER DATABASE and GRANT commands. Press [Enter] to continue to the next step in the script.
3.	The script connects to the database as HR user and creates the first test transaction, which consists of inserting five rows. Press [Enter] to continue to the next step in the script.
4.	The script continues to create the second test transaction, which consists of updating two rows that you just inserted. This creates a write-after-write (WAW) dependency. A WAW dependency exists when a transaction updates or deletes a row, that has been inserted or updated by a dependent transaction. Press [Enter] to continue to the next step in the script.
5.	The script continues to create the third transaction, which consists of updating three rows, one of which was also updated in the previous transaction. This action creates another WAW dependency. Press [Enter] to continue to the next step in the script.
5.	The script continues to create a fourth transaction, which consists of updating a row that was inserted in transaction 1. This action creates another WAW dependency. Press [Enter] to continue to the next step in the script.
6.	Lastly, the script connects to the database as SYS user and switches archive logs. Flashback Transaction requires at least one archived log to start analyzing redo. Press [Enter] to exit the script.

Back to Topic List

Backing Out a Transaction with a WAW Dependency

A transaction can have a write-after-write (WAW) dependency, meaning that a transaction updates or deletes row data, that has been inserted or updated by a previous transaction. In this section, you want to backout a bad transaction, in addition to its WAW dependents which also caused incorrect changes. P erform the following steps:

1.	Enter the following URL to log in to Enterprise Manager Database Control (change the <hostname> to localhost or your specific hostname). https://<hostname>:1158/em
2.	To log in to Enterprise Manager, enter the following details, and click Login. User Name: sys Password: oracle Connect as: SYSDBA
3.	You suspect that the HR.REGIONS table contains changes from the bad transaction, and want to view the complete table data. Click the Schema tabbed page. Then click Tables.
4.	To view all tables that belong to the HR schema user, enter HR into the Schema field and click the Go button.
5.	The HR tables are displayed. First, select the HR.REGIONS tables, then select View Data from the Actions drop-down list. Click the Go button behind the Actions drop-down, to view the data in the HR.REGIONS table.
6.	While viewing the content of the table, you realize that it should not contain the rows in the red box. Click OK.
7.	You return to the list of tables. Select the HR.REGIONS table, then select Flashback Transaction from the Actions drop-down list and click the Actions Go button.
8.	Review your query conditions. You can specify a time period in which you believe the erroneous changes were made. For this example, leave the default time period of the last hour. Then click Next. The Processing: Mining Transactions window appears, while redo logs are processed for the given time period.
9.	On the Flashback Transaction: Select Transaction page, select the Transaction ID link of the transaction which updates 2 regions.
10.	On the Select Transaction: Show Details page, review the erroneous UPDATE commands and click OK.
11.	Click Next to identify any dependent transactions and backout all needed changes. The Processing: Flashback Transaction window appears.
12.	On the Flashback Transaction: Show Dependencies page, click the first Transaction ID link.
13.	On the Show Dependencies: Details page, review the compensating transactions. Notice that none of the compensating transactions have been executed. Notice that the update of region #10 is marked "[exec=no]" meaning it will not be executed because it has dependent transactions. You will fix this in the next step by requesting all dependent transactions are also undone. Click OK.
14.	On the Flashback Transaction: Show Dependencies page, click Change Recovery Option.
15.	On the Show Dependencies: Change Recovery Option page, select Cascade, which will also backout the erroneous dependent transaction changes, and click OK. The Processing: Flashback Transaction window appears.
16.	From the Flashback Transaction: Show Dependencies window, click Next.
17.	Review the information and click Finish.
18.	The Flashback Transaction executed successfully. Click OK.
19.	You can view what is now contained in the HR.REGIONS table. Select the HR.REGIONS tables, then select View Data from the Actions drop-down list and click Go.
20.	Transactions 2, 3 were backed out but transaction 4 remains (region name is still set to "Still called Venus") since there was no WAW dependency between transaction 2 and 4.

Back to Topic List

Cleanup

You can cleanup your environment by performing the following steps:

From a SQL*Plus session, execute the flatxn_cleanup1.sql script:

Now you want to run flatxn_cleanup.sh to turn archivelog off. Execute the following command:

./flatxn_cleanup.sh

Back to Topic List

Summary

In this tutorial, you learned how to back out transactions with a WAW Dependency:

Back to Topic List