Using Total Recall

Purpose

Note: The Flashback Data Archive feature is part of the Total Recall option.

This tutorial shows you how to create, monitor and maintain a flashback data archive which tracks and stores all transactional changes to rows in a table. You will create some "wrong transactions" and then recover the original row by using UPDATE commands.

Time to Complete

Approximately 20 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Creating and Using a Flashback Data Archive
 Viewing Data Dictionary Information about Flashback Data Archives
 Modifying a Flashback Data Archive
 Cleaning Up
 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

Flashback Data Archive provides the ability to automatically track and store all transactional changes to a record for the duration of its lifetime. This feature also provides seamless access to historical data with "as of" queries. It provides flashback functionality for longer time periods than your undo data. You can use Flashback Data Archive for compliance reporting, audit reports, data analysis and decision support.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install the database by completing the Installing Oracle Database 11g on Linux tutorial.

2.

Download and unzip the flada.zip file into your working directory (i.e.wkdir).

Note: the files for this OBE are intended for a Linux environment. Two files: flada_setup.sql and flada_tbs2.sql must be modified to work in a Windows environment. In both files change the datafile specification to the Windows file name format in the CREATE TABLESPACE command.

Back to Topic List

Creating and Using a Flashback Data Archive

In this section, you are acting in two different roles:

To track and store all transactional changes to the HR.EMPLOYEES table, you create, configure and use a flashback data archive. Perform the following steps:

1.

Using SQL*Plus, connect to the database as the SYS user and run the flada_setup.sql script from the directory when the files are located.

sqlplus / as sysdba
@flada_setup.sql


The setup script creates a small tablespace and unlocks the HR user with the HR password. The password is case sensitive by default.

 

2.

To give the HR user administrative privileges for creating, maintaining and dropping flashback data archives, execute the following command as the SYS user:

GRANT FLASHBACK ARCHIVE ADMINISTER TO hr;

 

3.

In SQL*Plus, connect as the hr user with the HR password. Note: the password needs to be in upper case.

connect hr/HR

 

4.

To create a flashback data archive, execute the following command:

CREATE FLASHBACK ARCHIVE fla1
TABLESPACE fla_tbs1
QUOTA 10M
RETENTION 1 YEAR
/

Space requirements depend on your chosen retention period and the activity of the tracked table.

 

5.

To enable this flashback data archive for the EMPLOYEES table, execute the following command:

ALTER TABLE hr.employees
FLASHBACK ARCHIVE fla1
/

 

6.

To view and increase the salary of Mr. Fox three times by 1000, execute the flada_dml.sql script. This produces activity in the flashback data archive.

@flada_dml.sql

Press [Enter] to continue .

On the previous screenshot you see the original salary. Press [Enter] to continue to the next step in the script.

On the previous screenshot you see committed updates. Press [Enter] to continue to the next step in the script.

Press [Enter] to continue .

On the previous screenshot you see the current, erroneous salary of Mr. Fox. Press [Enter] to exit the script.

 

7.

You can view records as of any point in time, since the flashback data archive was created. See the "Viewing Data Dictionary Information about Flashback Data Archives" lesson for an example on how to query the "created" time.

Choose a time after the creation of the flashback data archive and before you executed the erroneous DML. To view Mr. Fox's employee record as of that time, execute the following query ( replace '10' MINUTE with your chosen historic date, format examples: '50' SECOND, '10' DAY, '5' MONTH ):

SELECT employee_id, last_name, salary
FROM hr.employees
AS OF TIMESTAMP
(SYSTIMESTAMP - INTERVAL '10' MINUTE)
WHERE last_name = 'Fox'
/

Note: You may get an error message similar to the one below, if the the time you specified is before the time the flashback data archive was started. Reduce the time to a smaller interval and try again.

ORA-01466: unable to read data - table definition has changed

 

8.

You can check that the flashback archive is being utilized by looking at the explain plan. Execute the following script:

                               
@flada_explainplan
                            

The flada_explainplan.sql script contains the following:

set echo on
explain plan for
SELECT employee_id, last_name, salary
FROM hr.employees
AS OF TIMESTAMP
(SYSTIMESTAMP - INTERVAL '10' MINUTE)
WHERE last_name = 'Fox'
/
set echo off
@$ORACLE_HOME/rdbms/admin/utlxpls

 

9.

You realize that the recent updates were mistakes. To revert to the original values for your chosen historic date (for example, ten minutes ago), execute the following command ( replace '10' MINUTE with your chosen historic date ):

UPDATE hr.employees
SET salary =
(SELECT salary FROM hr.employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE)
WHERE last_name = 'Fox')

WHERE last_name = 'Fox'
/

On the previous screenshot you see the UPDATE command, which uses a historic row to revert to the value from ten minutes ago. (In this test environment, you are most likely working with minutes. However, in a production environment, you might use DAY, MONTH or YEAR.)

 

Back to Topic List

Viewing Data Dictionary Information about Flashback Data Archives

In this section, you are again acting in two different roles:

To view meta data about your flashback data archive, perform the following steps:

 

Back to Topic List

Modifying a Flashback Data Archive

To practice additional flashback data archive maintenance tasks, perform the following steps:

1.

From your SQL*Plus session, connect to the database as the SYS user.

connect / as sysdba

 

2.

To list the data dictionary views available to you, execute the flada_list1.sql file:

@flada_list1.sql

Press [Enter] to continue .

Press [Enter] to continue with the next step in the script.

 

3.

Use the DBA_FLASHBACK_ARCHIVE data dictionary view to query the time when the flashback data archive(s) have been created. Press [Enter] to continue with the next step in the script.

Press [Enter] to continue .

 

4.

Press [Enter] to describe the DBA_FLASHBACK_ARCHIVE_TS data dictionary view.

Press [Enter] to list the tablespace(s), which are used for flashback data archives.

Press [Enter] to continue .

 

5.

Press [Enter] to describe the DBA_FLASHBACK_ARCHIVE data dictionary view.

Press [Enter] to query the table name(s), the owner name(s), and the internal "history" table name(s) of the flashback data archive.

Press [Enter] to continue .

 

6.

Press [Enter] to connect to the database as the HR user and list the data dictionary views available to this user.

Press [Enter] to continue .

Press [Enter] to exit the script .

1.

Using SQL*Plus, connect to the database as the HR user.

sqlplus hr/HR

 

2.

Data in the flashback data archive is automatically purged, when the retention time is expired. However, you can also explicitly purge data with the following command:

ALTER FLASHBACK ARCHIVE fla1
PURGE BEFORE TIMESTAMP
(SYSTIMESTAMP - INTERVAL '2' MINUTE)
/

Data, older than two minutes, is purged.

 

3.

Execute the flada_tbs2.sql script to create an additional 10 MB tablespace.

@flada_tbs2.sql

 

4.

As the HR user, add 5 MB of the FLA_TBS2 tablespace to the FLA1 flashback data archive. Execute the following command:

connect hr/HR

ALTER FLASHBACK ARCHIVE fla1
ADD TABLESPACE fla_tbs2
QUOTA 5M
/

 

5.

All tables in one specific flashback data archive have the same retention time. To change the retention time of the FLA1 flashback data archive to two years, execute the following command:

ALTER FLASHBACK ARCHIVE fla1
MODIFY RETENTION 2 YEAR
/

 

6.

To disable the flashback data archive for the HR.EMPLOYEES table, execute the following command:

ALTER TABLE hr.employees NO FLASHBACK ARCHIVE
/

 

7.

To drop the FLA1 flashback data archive, execute the following command:

DROP FLASHBACK ARCHIVE fla1
/

Dropping a flashback data archive includes dropping the internal tamper-proofed history table. This internal table cannot be directly dropped by you due to auditing and security requirements.

Dropping a flashback data archive does not drop the tablespaces in which they are stored, because the tablespaces might contain other data.

 

Back to Topic List

Cleaning Up

When you are finished with this OBE or when you wish to start again with Creating and Using a Flashback Data Archive, perform the following steps:

1.

From your SQL*Plus session, connect to the database as the SYS user.

connect / as sysdba

 

2.

To drop the tablespaces and lock the HR user, execute the flada_cleanup.sql script.

@flada_cleanup.sql

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Create and use a flashback data archive
 View data dictionary information about flashback data archives
 Modify a flashback data archive

Back to Topic List

 Place the cursor over this icon to hide all screenshots.