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

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.

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.

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;

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

connect hr/HR

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.

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

ALTER TABLE hr.employees
FLASHBACK ARCHIVE fla1
/

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.

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

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

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.)

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

connect / as sysdba

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.

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.

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.

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.

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 .

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

sqlplus hr/HR

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.

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

@flada_tbs2.sql

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
/

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
/

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

ALTER TABLE hr.employees NO FLASHBACK ARCHIVE
/

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.

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

connect / as sysdba

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

@flada_cleanup.sql