11G OCM Configure Flashback Data Archive

References

Overview

The objective from the 11G OCM Upgrade Exam is:

Configure Flashback Data Archive

Reading Notes

Definition

" Oracle Flashback Technology " says that:

A Flashback Data Archive provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

" Oracle Flashback Technology " says that Flashback Data Archive is:

An archive that is stored in a tablespace and contains transactional changes to every record in a table for the duration of the record's lifetime. The archived data can be retained for a much longer duration than the retention period offered by an undo tablespace.

" Oracle Flashback Technology " says that:

A flashback data archive enables you to use some of the logical flashback features to access data from far back in the past. A flashback data archive consists of one or more tablespaces or parts of tablespaces. When you create a flashback data archive, you specify the name, retention period, and tablespace. You can also specify a default flashback data archive. The database automatically purges old historical data the day after the retention period expires.

Procedure

Create Tablespace

Although this step is not necessary, I am going to use a separate tablespace for the Flashback Data Archive:

CREATE TABLESPACE flashback;

Creating a Flashback Data Archive

Following the procedure in Creating a Flashback Data Archive , I created a Flashback Data Archive for use with the OCMPART schema:

CREATE FLASHBACK ARCHIVE ocmpart_flash TABLESPACE flashback RETENTION 1 YEAR;

Altering a Flashback Data Archive

Following the procedure in Altering a Flashback Data Archive , I now make the Flashback Data Archive called OCMPART_FLASH the default:

ALTER FLASHBACK ARCHIVE ocmpart_flash SET DEFAULT;

Dropping a Flashback Data Archive

Following the procedure in Dropping a Flashback Data Archive , I now drop the Flashback Data Archive called OCMPART_FLASH :

DROP FLASHBACK ARCHIVE ocmpart_flash;

Specifying the Default Flashback Data Archive

Following the procedure in Specifying the Default Flashback Data Archive , I now create the Flashback Data Archive called OCMPART_FLASH as the default:

CREATE FLASHBACK ARCHIVE DEFAULT ocmpart_flash TABLESPACE flashback RETENTION 1 YEAR;

Enabling and Disabling Flashback Data Archive

Following the procedure in Enabling and Disabling Flashback Data Archive , I will now enable Flashback Data Archiving on OCMPART.SALES :

ALTER TABLE ocmpart.sales FLASHBACK ARCHIVE;

No name is specified for the Flashback Data Archive as the default one is sufficient.

DDL Statements Not Allowed on Tables Enabled for Flashback Data Archive

Following the procedure in DDL Statements Not Allowed on Tables Enabled for Flashback Data Archive , I attempt to truncate the table:

TRUNCATE TABLE ocmpart.sales;

And I get the following error message:

ORA-55610: Invalid DDL statement on history-tracked table

An explanation of the error message is:

55610, 00000, "Invalid DDL statement on history-tracked table"
// *Cause: An attempt was made to perform certain DDL statement that is
//         disallowed on tables that are enabled for Flashback Archive.
// *Action: No action required.

Viewing Flashback Data Archive Data

Following the procedure in Viewing Flashback Data Archive Data , I did queries on the static views:

SELECT * FROM dba_flashback_archive_tables ;

The result is:

TABLE_NAME	OWNER_NAME	FLASHBACK_ARCHIVE_NAME	ARCHIVE_TABLE_NAME
SALES	OCMPART	OCMPART_FLASH	SYS_FBA_HIST_71727

SELECT * FROM DBA_FLASHBACK_ARCHIVE ;

The result is:

FLASHBACK_ARCHIVE_NAME	FLASHBACK_ARCHIVE#	RETENTION_IN_DAYS	CREATE_TIME	LAST_PURGE_TIME	STATUS
OCMPART_FLASH	1	365	28/FEB/12 11:37:55.000000000 PM	28/FEB/12 11:37:55.000000000 PM	DEFAULT

SELECT * FROM DBA_FLASHBACK_ARCHIVE_TS ;

The result is:

FLASHBACK_ARCHIVE_NAME	FLASHBACK_ARCHIVE#	TABLESPACE_NAME	QUOTA_IN_MB
OCMPART_FLASH	1	FLASHBACK