Oracle 11g Flashback Data Archive (Oracle Total Recall)

Flashback Data Archive (Oracle Total Recall)

Most flashback features work at the logical level, in that they don't directly allow you to recover an object. They simply give you a view of how the data was in the past, which in turn can be used to recover from logical corruption, such as accidental deletions. Typically, this view of the past is constructed using undo segments, which are retained for a period of time indicated by the UNDO_RETENTION parameter. Once the undo information is lost, the view of the past is lost also. At least that's how it was before Oracle 11g introduced the Flashback Data Archive functionality.

A flashback data archive is essentially an extended store of undo information, allowing some logical flashback operations to extend way back into the past with no difference in how they are coded. An individual flashback archives consists of one or more tablespaces, or parts of tablespace. Each flashback archive has a name, retention period and a quota on each associated tablespace. The database can have multiple flashback data archives, but only a single default archive. When a DML transaction commits an operation on a flashback archive enabled table, the Flashback Data Archiver (FBDA) process stores the pre-image of the rows into a flashback archive, along with metadata of the current rows. The FBDA process is also responsible for managing the data within the flashback archives, such as purging data beyond the retention period.

The following script creates a new tablespace, then creates two flashback data archives using the CREATE FLASHBACK ARCHIVE command. The first is limited in size to 10Gig with a retention period of 1 year, while the second has an unlimited quota and a retention period of 2 years.

CREATE TABLESPACE fda_ts
DATAFILE '/u01/app/oracle/oradata/DB11G/fda1_01.dbf'
SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts
QUOTA 10G RETENTION 1 YEAR;

CREATE FLASHBACK ARCHIVE fda_2year TABLESPACE fda_ts
RETENTION 2 YEAR;Management of flashback archives falls into three distinct categories:
Tablespace management.

-- Set as default FBA
ALTER FLASHBACK ARCHIVE fba_name SET DEFAULT;

-- Add up to 10G of the specified tablespace to the specified flashback archive.
ALTER FLASHBACK ARCHIVE fba_name ADD TABLESPACE ts_name QUOTA 10G;

-- Add an unlimited quota of the specified tablespace to the specified flashback archive.
ALTER FLASHBACK ARCHIVE fba_name ADD TABLESPACE ts_name;

-- Change the tablespace quota to 20G.
ALTER FLASHBACK ARCHIVE fba_name MODIFY TABLESPACE ts_name QUOTA 20G;

-- Change the tablespace quota to unlimited.ALTER FLASHBACK ARCHIVE fba_name MODIFY TABLESPACE ts_name;

-- Remove the specified tablespace from the archive.
ALTER FLASHBACK ARCHIVE fba_name REMOVE TABLESPACE ts_name;Modifying the retention period.

ALTER FLASHBACK ARCHIVE fba_name MODIFY RETENTION 2 YEAR;

Purging data.
-- Remove all historical data.
ALTER FLASHBACK ARCHIVE fba_name PURGE ALL;

-- Remove all data before the specified time.
ALTER FLASHBACK ARCHIVE fba_name PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);

-- Remove all data before the specified SCN.
ALTER FLASHBACK ARCHIVE fba_name PURGE BEFORE SCN 728969;Archives are removed using the DROP FLASHBACK ARCHIVE command, which drops the archive and all its historical data, but doesn't drop the associated tablespace.

DROP FLASHBACK ARCHIVE fba_name;

To enable flashback archiving on a specific table the user must have the FLASHBACK ARCHIVE object privilege on the specific flashback archive used. To try this out create a test user and grant the object privilege on the first flashback archive created earlier.

CONN sys/password AS SYSDBA

CREATE USER fda_test_user IDENTIFIED BY fda_test_user QUOTA UNLIMITED ON users;

GRANT CONNECT, CREATE TABLE TO fda_test_user;
GRANT FLASHBACK ARCHIVE ON fda_1year TO fda_test_user;

If we connect to the test user we are able to create a table and associate it with the default flashback archive as follows.

CONN fda_test_user/fda_test_user

CREATE TABLE test_tab_1 (
id NUMBER,
desription VARCHAR2(50),
CONSTRAINT test_tab_1_pk PRIMARY KEY (id)
)

FLASHBACK ARCHIVE;If we try to create a similar table, but point it at the second archive it fails, as we have no privileges on it.

CONN fda_test_user/fda_test_user

CREATE TABLE test_tab_2 (
id NUMBER,
desription VARCHAR2(50),
CONSTRAINT test_tab_2_pk PRIMARY KEY (id)
)
FLASHBACK ARCHIVE fda_2year;
CREATE TABLE test_tab_2 (
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


SQL>The ALTER TABLE command allows existing tables to have flashback archiving switched on or off.

-- Enable using the default FBDA.
ALTER TABLE table_name FLASHBACK ARCHIVE;

-- Enable using specific FBDA.
ALTER TABLE table_name FLASHBACK ARCHIVE fda_name;

-- Disable flashback archiving.
ALTER TABLE table_name NO FLASHBACK ARCHIVE;

As with the CREATE TABLE statement, the FLASHBACK ARCHIVE object privilege must be granted on the flashback archive being used.

Once flashback archiving is enabled the table is protected, so it cannot be switched off unless you have the FLASHBACK ARCHIVE ADMINISTER system privilege, or are logged on as SYSDBA.

SQL> ALTER TABLE test_tab_1 NO FLASHBACK ARCHIVE;
ALTER TABLE test_tab_1 NO FLASHBACK ARCHIVE
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


SQL>In addition, there are certain DDL restrictions associated with having flashback archiving enabled. The following operations result in a ORA-55610 error:

ALTER TABLE statements that drop, rename or modify columns.
ALTER TABLE statements that performs partition or subpartition operations.
ALTER TABLE statements that converts a LONG column to a LOB column.
ALTER TABLE statements that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause.
DROP TABLE statements.
RENAME TABLE statements.
TRUNCATE TABLE statements.

Information about flashback data archives is displayed using the %_FLASHBACK_ARCHIVE view.

CONN sys/password AS SYSDBA

COLUMN flashback_archive_name FORMAT A20

SELECT flashback_archive_name, retention_in_days, status
FROM dba_flashback_archive;

FLASHBACK_ARCHIVE_NA RETENTION_IN_DAYS STATUS
-------------------- ----------------- -------
FDA_2YEAR 730
FDA_1YEAR 365 DEFAULT

2 rows selected.

SQL>The %_FLASHBACK_ARCHIVE_TS view displays the tablespaces and quotas associated with each flashback archive.

COLUMN flashback_archive_name FORMAT A20
COLUMN quota_in_mb FORMAT A10

SELECT flashback_archive_name, tablespace_name, quota_in_mb
FROM dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NA TABLESPACE_NAME QUOTA_IN_M
-------------------- ------------------------------ ----------
FDA_2YEAR FDA_TS
FDA_1YEAR FDA_TS 10240

2 rows selected.

SQL>

The %_FLASHBACK_ARCHIVE_TABLES view displays tables associated with each flashback archive, along with the name of the table holding the historical information.

COLUMN table_name FORMAT A15
COLUMN owner_name FORMAT A15
COLUMN flashback_archive_name FORMAT A20
COLUMN archive_table_name FORMAT A20

SELECT table_name, owner_name, flashback_archive_name, archive_table_name
FROM dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME
--------------- --------------- -------------------- --------------------
TEST_TAB_1 FDA_TEST_USER FDA_1YEAR SYS_FBA_HIST_72023

1 row selected.

SQL>