71 DBMS_FLASHBACK_ARCHIVE

Overview

Flashback Data Archive (FDA) provides strict protection on the internal history tables that it creates and maintains for users.

The read-only semantics prohibit users, including a DBA, from doing updates, deletes, and inserts on the Flashback Data Archive internal history tables. Users are also prevented from issuing any DDL statements on these tables. This strict security enforcement helps meet the requirements of applications in regulatory / compliance environments. Flashback Data Archive supports most common DDL statements, including those that alter the table definition or incur data movement. However, some DDL statements are not supported on Flashback Data Archive-enabled tables. Since most application schemas are modified during application software upgrades, the ability to perform DDL operations on tracked tables is critical.

To support schema evolution during application upgrades and other table maintenance tasks that require use of DDL statements not supported by Flashback Data Archive, the DBMS_FLASHBACK_ARCHIVE package provides a set of simple-to-use PL/SQL procedures:

• To disassociate a Flashback Data Archive enabled base table from the underlying FDA

• To reassociate a temporarily disassociated base table with its underlying FDA

After a user has disassociated the base table from its FDA, it's possible to issue any DDL statements on the base table or the history tables in the FDA. Having finished with the schema changes, the user can then reassociate the base table with its FDA so that Flashback Data Archive protection is in operation and automatic tracking and archiving is resumed.

Security Model

Users need the FLASHBACK_ARCHIVE_ADMINISTER privilege to import user-generated history, to set context level, and to tamper-proof tables. After a table is disassociated, users can perform DDL and DML statements on the table if they have the necessary privileges. Enabling and disabling session-level Valid Time Temporal flashback needs no additional privileges.

Constants

The DBMS_FLASHBACK_ARCHIVE package uses the constants shown in Table 71-1.

Examples

Normally, users cannot perform any modification to the history table:

SQL> DELETE FROM scott.SYS_FBA_HIST_61527;

ERROR at line 1: 
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "SCOTT"."SYS_FBA_HIST_61527"

Users also cannot issue DDL statements on history tables:

SQL> ALTER TABLE scott.SYS_FBA_HIST_61527 DROP COLUMN comm;

ERROR at line 1: 
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "SCOTT"."SYS_FBA_HIST_61527"

Use the DISASSOCIATE_FBA Procedure to disassociate the scott.emp_test table:

SQL> EXEC DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA('scott','emp_test');

PL/SQL procedure successfully completed. 

Now a user can perform table structural modifications (through DDL statements) to the user base table:

SQL> ALTER TABLE scott.emp_test RENAME COLUMN sal TO salary;

Table altered.

Users can also modify the contents in the history table that they couldn't modify previously.

SQL> DELETE FROM scott.SYS_FBA_HIST_61527 WHERE empno=3968;

2 rows deleted. 

If a user tries to reassociate the history table with the base table, this will fail as the user has not performed corresponding structural modifications (through DDL statements) to the history table:

SQL> EXEC DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA('scott','emp_test');
BEGIN DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA('scott','emp_test');
END;

ERROR at line 1:
ORA-55636: Flashback Data Archive enabled table "SCOTT"."EMP_TEST" has different definition from its history table
ORA-06512: at "SYS.DBMS_FLASHBACK_ARCHIVE", line 17
ORA-06512: at line 1

Disassociate the table and fix the table definition problem:

SQL> EXEC DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA('scott','emp_test');

PL/SQL procedure successfully completed.

Perform the same rename column DDL on the history table to make its definition conform to its base table scott.emp_test:

SQL> ALTER TABLE scott.SYS_FBA_HIST_61527 RENAME COLUMN sal TO salary;

Table altered.

Finally, reassociate the base table with its history table successfully:

SQL> EXEC DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA('scott','emp_test')

PL/SQL procedure successfully completed.

The above example is used as an illustration. RENAME COLUMN is available as a supported DDL and you do not need to invoke disassociate/reassociate as a matter of course. You need to disasociate/reassociate only if the operation is not supported by flashback archive and produces a 55610 error message.

SQL> create table emp_test as select empno, sal from emp;

Table created.

SQL> alter table emp_test flashback archive;

Table altered.

Create a temporary history table with the same form as the base table but with the additional metadata columns.

SQL>  EXEC DBMS_FLASHBACK_ARCHIVE.CREATE_TEMP_HISTORY_TABLE('scott','emp_test');

PL/SQL procedure successfully completed.

SQL> describe scott.temp_history
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RID                                                VARCHAR2(4000)
 STARTSCN                                           NUMBER
 ENDSCN                                             NUMBER
 XID                                                RAW(8)
 OPERATION                                          VARCHAR2(1)
 EMPNO                                              NUMBER(4)
 SAL                                                NUMBER(7,2)

Do the following once per database. This will extend mappings to the past so that import of old history can be done. Goes back to 01-JAN-88.

SQL> EXEC DBMS_FLASHBACK_ARCHIVE.extend_mappings();

PL/SQL procedure successfully completed.

SQL> insert into scott.temp_history values (NULL, timestamp_to_scn(to_date('01-JAN-06')), timestamp_to_scn(to_date('31-DEC-06')), NULL, 'U', 1, 1000);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_FLASHBACK_ARCHIVE.IMPORT_HISTORY('scott','emp_test');

PL/SQL procedure successfully completed.

Now flashback queries of the imported data are possible.

SQL> select * from scott.emp_test as of timestamp to_date('06-JUN-06') where empno=1;
 
     EMPNO        SAL                                                          
---------- ----------                                                          
         1       1000 

ADD_TABLE_TO_APPLICATION Procedure

This procedure takes an application name and adds a table to the application as a security table. If the application is enabled for Flashback Data Archive, then this table will also be enabled for Flashback Data Archive.

DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION (
   application_name           IN   VARCHAR2,
   table_name                 IN   VARCHAR2,
   schema_name                IN   VARCHAR2 := NULL);

CREATE_TEMP_HISTORY_TABLE Procedure

This procedure creates a table called TEMP_HISTORY with the correct definition in schema.

DBMS_FLASHBACK_ARCHIVE.CREATE_TEMP_HISTORY_TABLE (
   owner_name1      IN   VARCHAR2, 
   table_name1      IN   VARCHAR2);

DISABLE_APPLICATION Procedure

This procedure takes an application name and disables Flashback Data Archive on all of its security tables.

DBMS_FLASHBACK_ARCHIVE.DISABLE_APPLICATION (
   application_name           IN   VARCHAR2);

DISABLE_ASOF_VALID_TIME Procedure

This procedure disables session level valid-time flashback.

DBMS_FLASHBACK_ARCHIVE.DISABLE_ASOF_VALID_TIME;

DISASSOCIATE_FBA Procedure

This procedure disassociates the given table from the flashback data archive.

DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA (
   owner_name     IN    VARCHAR2, 
   table_name     IN    VARCHAR2);

DROP_APPLICATION Procedure

This procedure takes an application name and removes it from the list of applications. As part of this procedure, Flashback Data Archive will be disabled on all security-enabled tables and all history data will be lost. The group called SYSTEM cannot be dropped.

DBMS_FLASHBACK_ARCHIVE.DROP_APPLICATION (
   application_name           IN   VARCHAR2);

ENABLE_APPLICATION Procedure

This procedure takes an application name and enables Flashback Data Archive on all the security tables for this application. Once an application is enabled, every change to an FDA enabled table will be tracked.

DBMS_FLASHBACK_ARCHIVE.ENABLE_APPLICATION (
   application_name           IN   VARCHAR2);

ENABLE_AT_VALID_TIME Procedure

This procedure enables session level valid time flashback.

DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME (
   level          IN    VARCHAR2, 
   query_time     IN    TIMESTAMP DEFAULT SYSTIMESTAMP);

EXTEND_MAPPINGS Procedure

This procedure extends time mappings to times in the past.

DBMS_FLASHBACK_ARCHIVE.EXTEND_MAPPINGS;

GET_SYS_CONTEXT Function

This function gets the context previously selected by the SET_CONTEXT_LEVEL Procedure.

DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT (
   xid            IN   RAW, 
   namespace      IN   VARCHAR2, 
   parameter      IN   VARCHAR2)
 RETURN VARCHAR2;

IMPORT_HISTORY Procedure

This procedure is called after invoking the CREATE_TEMP_HISTORY_TABLE Procedure procedure, and after the TEMP_HISTORY table is populated with user-generated history data

DBMS_FLASHBACK_ARCHIVE.IMPORT_HISTORY (
   owner_name1         IN   VARCHAR2, 
   table_name1         IN   VARCHAR2 
   temp_history_name   IN   VARCHAR2 DEFAULT 'TEMP_HISTORY',
   options             IN   BINARY_INTEGER DEFAULT 0);

LOCK_DOWN_APPLICATION Procedure

This procedure takes an application name and makes all the security tables read-only. The group called SYSTEM cannot be locked.

DBMS_FLASHBACK_ARCHIVE.LOCK_DOWN_APPLICATION (
   application_name           IN   VARCHAR2);

PURGE_CONTEXT Procedure

This procedure purges the context to be saved selected by the SET_CONTEXT_LEVEL Procedure.

DBMS_FLASHBACK_ARCHIVE.PURGE_CONTEXT;

REASSOCIATE_FBA Procedure

This procedure reassociates the given table with the flashback data archive.

DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA (
   owner_name      VARCHAR2, 
   table_name      VARCHAR2);

REGISTER_APPLICATION Procedure

This procedure takes an application name and optionally a Flashback Data Archive, and registers an application for database hardening. When database hardening is enabled, then all the security tables for that application are enabled for Flashback Data Archive using the given Flashback Data Archive. If no Flashback Data Archive is specified, the default Flashback Data Archive is used.

DBMS_FLASHBACK_ARCHIVE.REGISTER_APPLICATION (
   application_name           IN   VARCHAR2,
   flashback_archive_name     IN    VARCHAR2 := NULL);

REMOVE_TABLE_FROM_APPLICATION Procedure

This procedure takes an application name and marks a table in it as no longer being a security table. If the application is already enabled for Flashback Data Archive, Flashback Data Archive will be disabled for this table.

DBMS_FLASHBACK_ARCHIVE.REMOVE_TABLE_TO_APPLICATION (
   application_name           IN   VARCHAR2,
   table_name                 IN   VARCHAR2,
   schema_name                IN   VARCHAR2 := NULL);

SET_CONTEXT_LEVEL Procedure

This procedure defines how much of the user context is to be saved.

DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL (
   level          VARCHAR2);