OBE Home > Database 10g > Single Instance > Availability > Flashback

Recovering from Human Error using Flashback

Purpose

This lesson describes the Oracle Database 10g Flashback features that support self-service error correction.

Topics

This lesson discusses the following:

	Overview
	Prerequisites
	Filling the Recycle Bin
	Querying Tables in the Recycle Bin
	Recovering Tables from the Recycle Bin
	Emptying Tables from the Recycle Bin
	Using Flashback Table to Recover a Table

Viewing Screenshots

Move your mouse over this icon to show all screenshots. You can also move your mouse over each individual icon to see only the screenshot associated with it.

Overview

Back to List of Topics

In Oracle Database 10g, flashback functionality has been extended. It is now possible to flash back an entire table and quickly recover a table that has been inadvertently dropped.

What is the Recycle Bin?

In previous releases of the Oracle server, the DROP command permanently removed objects from the database. Now when you drop a table and its dependent objects it is placed in the recycle bin. It is not removed from the database until you empty the recycle bin. The extents allocated to the segment are not deallocated until you purge the object. You can restore these dropped objects from the recycle bin at any time.

Prerequisites

Back to List

Before starting this module, you should have performed the following:

Filling the Recycle Bin

Back to List of Topics

First, you will need to add an object to the Recycle Bin. Perform the following steps:

connect system/<password>
@create_job_hist

drop table hr.jobs_hist;

Querying Tables in the Recycle Bin

Back to List of Topics

You can still query tables after you have dropped them. You can issue a SELECT statement against the objects in the recycle bin using the name of the object in the recycle bin. You can also issue a Flashback Query against objects in the recycle bin to query past data, as long as the undo information required to satisfy the query is available. You can only issue these queries if you are the original owner of the object. Perform the following

http://<hostname>:5500/em

Recovering Tables From the Recycle Bin

Back to List of Topics

You can recover the table from the recycle bin by performing the following:

Emptying the Recycle Bin

Back to List of Topics

At some point, you may want to permanently remove the object from the database and release the space allocated to it after you have dropped the object. To remove an object from the recycle bin, perform the following:

Using Flashback Versions Query to Recover a Table to a Point in Time

Back to List of Topics

In Oracle9i, Flashback Query was introduced. This feature provides a way for you to view and repair historical data. You can perform queries on the database as of a certain wall clock time or user-specified system change number (SCN).

In Oracle Database 10G, Flashback functionality has been expanded. You can use the VERSIONS BETWEEN clause to retrieve all of the versions of the rows that exist or ever existed between the time the query was issued and a point back in time. This type of query is referred to as a Flashback Versions Query.

The rows returned by the Flashback Versions Query are a history of changes of the rows across transactions.

The Flashback Versions Query retrieves all committed occurrences of the rows. Changes made by the current active transaction are not returned. In other words, uncommitted row versions within a transaction will not be shown.

You can use Flashback Versions Query to retrieve row history. It provides you with a way to audit the rows of a table and retrieve information about the transactions that changed the rows. You can use the transaction ID obtained from Flashback Versions Query to perform transaction mining using LogMiner or Flashback Transaction Query to obtain additional information about the transaction.

You will perform the following tasks:

	Update the JOBS Table
	Enable Row Movement on the JOBS Table
	Recover the JOBS Table to a Particular Point in Time

Update the JOBS Table

Back to List

To show how flashback works, you need to make a change to a table. Perform the following:

updatejobs.sql

UPDATE hr.jobs
SET max_salary = max_salary + 2000 
WHERE job_id = 'IT_PROG'
/
COMMIT
/

connect hr/hr
select * from jobs where job_id='IT_PROG';

Enable Row Movement on the JOBS Table

Back to List

You must first enable row movement on the table you want to perform flashback table on.Enabling row movement lets you specify whether Oracle can move a table row. It is possible for a row to move, for example, during table compression or an update operation on partitioned data. Perform the following:

Recover the JOBS Table to a Particular Point in Time

Back to List

Now you are ready to recover the JOBS table to a particular point in time. Perform the following:

select * from jobs where job_id='IT_PROG';

Move your mouse over this icon to hide all screenshots.