0) { obj.className = "imgborder_on"; } } } function hideImage(obj) { if (obj.className.substr(0, 10) == "imgborder_") { obj.src = eyeglass.src; obj.className = "imgborder_off"; } } function showAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { showImage(imgs[i]); } } function hideAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { hideImage(imgs[i]); } } //-->

OBE Home > 10gR2 Single > Availability

Recovering from Human Error Using Flashback

Purpose

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

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Setup
 Filling the Recycle Bin

Querying Tables in the Recycle Bin

Recovering Tables from the Recycle Bin

Emptying the Recycle Bin

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

 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

In Oracle Database 10g, the 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.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

1.

Perform the Installing Oracle Database 10g on Windows tutorial.

2.

Download and unzip the flashback.zip file into your working directory (i.e. c:\wkdir).

Back to Topic List

Setup

The flash recovery area is a unified storage location for all recovery related files and activities in an Oracle database. All files that are needed to completely recover a database from a media failure are part of the flash recovery area. The recovery related files that can be created in the flash recovery area include: archived redo log files, control files, backups created by Recovery Manager (RMAN), and flashback logs.

By allocating a storage location and unifying related recovery files within a specific area, the Oracle database server relieves the database administrator from having to manage the disk files created by these components.

Follow the steps below to configure the flash recovery area, put your database is in ARCHIVELOG mode, and enable Flashback Database.

1.

Log in to Enterprise Manager Database Console by opening your browser and entering the following URL:

                               
http://<your host name>:1158/em
                            

Enter sys in the User Name field and oracle in the Password field. Select SYSDBA from the Connect As drop-down list. Click Login.

Move your mouse over this icon to see the image

 

2.

The Oracle Database Home page allows you to view the current state of the database by displaying a series of metrics that portray the overall health of the database. The Oracle Database Home page provides a launch point for the database status and administration and configuration of the database environment. It contains four pages via subtabs with each page displaying subsections. Click the Maintenance tab.

Move your mouse over this icon to see the image

 

3.

Under Backup/Recovery Settings, click Recovery Settings.

Move your mouse over this icon to see the image

 

4.

Scroll down to the Flash Recovery Area section. Confirm that the Flash Recovery Area is enabled and that the Flash Recovery Area Size is set to 2 GB.

Move your mouse over this icon to see the image

 

5.

Select the Enable Flashback Database check box.

Move your mouse over this icon to see the image

 

6.

Now you need to put the database in ARCHIVELOG mode. Scroll up to the Media Recovery section. Select the ARCHIVELOG Mode check box. Click Apply.

Move your mouse over this icon to see the image

 

7.

Click Yes to restart the database.

Move your mouse over this icon to see the image

 

8.

Enter your Host Credentials and sys/oracle as SYSDBA for your Database Credentials. Click OK.

Move your mouse over this icon to see the image

 

9.

Click Yes to confirm the restart of your database.

Move your mouse over this icon to see the image

 

10.

Your database is being restarted. Wait a few minutes and click Refresh.

Move your mouse over this icon to see the image

 

Filling the Recycle Bin

Add an object to the recycle bin by performing the following steps:

1.

Log in to SQL*Plus as system/oracle . Execute the create_job_hist.sql script to create a new table:

@create_job_hist

 

2.

Now enter the following SQL statement to drop the table you just created:

                               
                                 
drop table hr.jobs_hist;
                              
                            

The table has been dropped. You have just added this table to the recycle bin.

 

Back to Topic List

Querying Tables in the Recycle Bin

You can query tables even after you have dropped them. You can issue a SELECT statement against the objects in the recycle bin by 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 issue these queries only if you are the original owner of the object. Using Enterprise Manager, perform the following steps:

1.

Click the Administration tab.

 

2.

Scroll down to the Schema section. Click Tables.

 

3.

Enter HR in the Schema field and click Go.

 

4.

Scroll to the bottom of the page and click the Recycle Bin button.

 

5.

Enter HR in the Schema Name field and click Go.

 

6.

Notice that the table you dropped is listed. Click View Content.

 

7.

You can see the contents of the table you dropped. Click OK.

 

Back to Topic List

Recovering Tables from the Recycle Bin

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

1.

On the Recycle Bin page, select the check box next to the JOBS_HIST object and click Flashback Drop.

 

2.

On the Perform Object Level Recovery: Rename page you can supply a new name for the table or accept the original name. Accept the original table name of JOBS_HIST and click Next.

 

3.

Before you submit the job to recover the table, click Show SQL to see the SQL command that will be executed by Enterprise Manager.

 

4.

Review the SQL statement that will be executed. When done, click OK.

 

5.

Click Submit.

 

6.

You receive a message indicating the flash back from the recycle bin was successful. Click OK.

 

7.

The JOBS_HIST table no longer appears in the recycle bin. You want to make sure that the table now appears in the tables list. Click the Tables breadcrumb.

 

8.

Enter HR in the Schema Name field and click Go.

 

9.

You now see the JOBS_HIST table back in the tables list.

 

Back to Topic List

Emptying the Recycle Bin

At some point, you may want to permanently remove an 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 steps:

1.

On the Tables page, select the JOBS_HIST table and click Delete With Options.

 

2.

Click Yes to confirm the deletion.

 

3.

Scroll to the bottom of the page and click the Recycle Bin button.

 

4.

Enter HR in the Schema Name field and click Go.

 

5.

On the Recycle Bin page, select the check box next to the JOBS_HIST object and click Purge.

 

6.

Click Yes to confirm the deletion.

 

7.

The table has been purged from the recycle bin. Click the Tables breadcrumb.

 

Back to Topic List

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

In Oracle9 i , 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, the flashback functionality has been expanded. You can use the VERSIONS BETWEEN clause to retrieve all 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 currently 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 with LogMiner. You can also use Flashback Transaction Query to obtain additional information about the transaction.

In this topic 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

Back to Topic List

Update the JOBS Table

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

1.

Login in to SQL*Plus as the system user and execute the updatejobs.sql script :

                               
                                 
@c:\wkdir\updatejobs.sql
                              
                            

The updatejobs.sql script updates the salary for the IT_PROG job. The script contains the following commands:

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

 

2.

To see the updated value of MAX_SALARY for the IT_PROG job, execute the following query:

                               
                                 
select * from hr.jobs where job_id='IT_PROG';
                              
                            

You see that the current value after your update is 12000.

 

Back to List

Enable Row Movement on the JOBS Table

You must first enable row movement on the table that you want to perform flashback table on. By enabling row movement, you can specify whether the Oracle database 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 steps:

1.

Switch to your Enterprise Manager browser session. Select the JOBS table and click Edit.

 

2.

Select the Options tab.

 

3.

Select Yes for Enable Row Movement and click Apply.

 

4.

Now you can perform a flashback on the table. Click the Tables breadcrumb.

 

Back to List

Recover the JOBS Table to a Particular Point in Time

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

1.

Select the JOBS table. Select Flashback Versions Query from the Actions list and click Go.

 

2.

Select JOB_ID and MAX_SALARY and click Move. Then enter WHERE job_id = 'IT_PROG' in the Bind The Row Value field and click Next.

 

3.

The update you made previously is in the list. Click Next.

 

4.

Click Next.

 

5.

You may want to see what will change. Click Show Row Changes.

 

6.

You will see the objects that will change as a result of the flashback operation. Click OK.

 

7.

You are now ready to perform the flashback operation. Click Submit.

 

8.

The flashback operation has completed successfully. Click OK.

 

9.

Return to your SQL*Plus session again and enter the following SQL statement to confirm that the MAX_SALARY value has changed back to the original value:

                               
                                 
select * from hr.jobs where job_id='IT_PROG';
                              
                            

You see that the value has been changed back to 10000.

 

Back to List

Summary

In this tutorial, you learned how to:

 Fill the recycle bin
 Query tables in the recycle bin
 Recover tables from the recycle bin
 Empty tables from the recycle bin
 Use Flashback Table to recover a table

Back to Topic List

 Place your cursor on this icon to hide all screenshots.

 

Left Curve
Popular Downloads
Right Curve
Untitled Document