Oracle | DBMS_FLASHBACK

Oracle DBMS_FLASHBACK_ARCHIVE Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Support user applications' schema evolution during application upgrade and other table maintenance tasks beyond DDL statements supported by Flashback Data Archive.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
NODROP	BINARY_INTEGER	1
NOCOMMIT	BINARY_INTEGER	2
NODELETE	BINARY_INTEGER	4

Dependencies

DBMS_FDA_LIB	SYS_FBA_CONTEXT_AUD	SYS_FBA_TSFA
SQLOBJ$AUXDATA	SYS_FBA_CONTEXT_LIST	SYS_FBA_USERS
SYS_FBA_APP	SYS_FBA_DL	SYS_MFBA_NCHANGE
SYS_FBA_APP_TABLES	SYS_FBA_FA	SYS_MFBA_NROW
SYS_FBA_BARRIERSCN	SYS_FBA_PARTITIONS	SYS_MFBA_NTCRV
SYS_FBA_COLS	SYS_FBA_PERIOD	SYS_MFBA_STAGE_RID
SYS_FBA_CONTEXT	SYS_FBA_TRACKEDTABLES	SYS_MFBA_TRACKED_TXN

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-06512	Unable to disassociate or re-associate Flashback Data Archive table <schema_name>.<table_name>
ORA-55602	The table "<schema_name"."<table_name>" is not enabled for Flashback Archive
ORA-55603	Invalid flashback archive or valid time period command
ORA-55634	Flashback Data Archive enabled table "string"."string" has different definition from its history table
ORA-55636	Flashback Data Archive enabled table "string"."string" has different definition from its history table
ORA-55637	Flashback Data Archive enabled table <schema_name>.<table_name> is not in the correct compliance

First Available 11.2

Related System Privileges FLASHBACK ARCHIVE ADMINISTER is required to import user generated history, set context level, and tamper-proof tables.

Security Model Owned by SYS with EXECUTE granted to the DBA role

Source {ORACLE_HOME}/rdbms/admin/dbmstran.sql

Subprograms

ADD_TABLE_TO_APPLICATION	ENABLE_APPLICATION	PURGE_CONTEXT
CREATE_TEMP_HISTORY_TABLE	ENABLE_AT_VALID_TIME	REASSOCIATE_FBA
DISABLE_APPLICATION	EXTEND_MAPPINGS	REGISTER_APPLICATION
DISABLE_ASOF_VALID_TIME	GET_SYS_CONTEXT	REMOVE_TABLE_FROM_APPLICATION
DISASSOCIATE_FBA	IMPORT_HISTORY	SET_CONTEXT_LEVEL
DROP_APPLICATION	LOCK_DOWN_APPLICATION	UNLOCK_APPLICATION

ADD_TABLE_TO_APPLICATION

Takes an application name and marks a table in it as a security table dbms_flashback_archive.add_table_to_application( application_name IN VARCHAR2, table_name IN VARCHAR2, schema_name IN VARCHAR2 DEFAULT '');

See REGISTER_APPLICATION Demo Below

CREATE_TEMP_HISTORY_TABLE

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

conn sys@pdbdev as sysdba

      

      CREATE FLASHBACK ARCHIVE uw_archive

      TABLESPACE uwdata

      QUOTA 15G

      RETENTION 30 DAY;

      CREATE FLASHBACK ARCHIVE uw_archive

      *

      ERROR at line 1:

      ORA-65131: The feature Flashback Data Archive is not supported in a pluggable database.

DISABLE_APPLICATION

Takes an application name and marks a table in it as a security table dbms_flashback_archive.disable_application(application_name IN VARCHAR2);

See REGISTER_APPLICATION Demo Below

DISABLE_ASOF_VALID_TIME

Disables session level valid-time flashback dbms_flashback_archive.disable_asof_valid_time;

exec dbms_flashback_archive.disable_asof_valid_time;

DISASSOCIATE_FBA

Disassociates the given table from the flashback data archive dbms_flashback_archive.open_fba( owner_name IN VARCHAR2, table_name IN VARCHAR2);

See REASSOCIATE_FBA Demo Below

DROP_APPLICATION

emoves an application from the applications list dbms_flashback_archive.drop_application(application_name IN VARCHAR2);

See REGISTER_APPLICATION Demo Below

ENABLE_APPLICATION

Takes an application name and enables flashback archive on all the security tables for this application dbms_flashback_archive.enable_application( application_name IN VARCHAR2, flashback_archive_name IN VARCHAR2 DEFAULT '');

See REGISTER_APPLICATION Demo Below

ENABLE_AT_VALID_TIME

This procedure enables session level valid time flashback dbms_flashback_archive.enable_at_valid_time( level IN VARCHAR2, query_time IN TIMESTAMP DEFAULT SYSTIMESTAMP);

exec dbms_flashback_archive.enable_at_valid_time('CURRENT');

EXTEND_MAPPINGS

Extends time mappings to times in the past dbms_flashback_archive.extend_mappings;

exec dbms_flashback_archive.extend_mappings;

GET_SYS_CONTEXT

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;

TBD

IMPORT_HISTORY

Imports history from a table called TEMP_HISTORY in the given schema 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);

TBD

LOCK_DOWN_APPLICATION

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

See REGISTER_APPLICATION Demo Below

PURGE_CONTEXT

Purges the context to be saved selected by SET_CONTEXT_LEVEL dbms_flashback_archive.purge_context;

See SET_CONTEXT_LEVEL Demo Below

REASSOCIATE_FBA

Reassociates the history table with the base table which will fail if the user has not performed corresponding structural modifications (through DDL statements) to the history table. dbms_flashback_archive.reassociate_fba( owner_name IN VARCHAR2, table_name IN VARCHAR2);

ALTER SYSTEM SET undo_retention = 1 SCOPE=MEMORY;

      

      CREATE FLASHBACK ARCHIVE uw_archive

      TABLESPACE uwdata

      QUOTA 10M

      RETENTION 30 DAY;

      

      ALTER TABLE servers FLASHBACK ARCHIVE uw_archive;

      

      -- perform DML on the servers table until flashback archive tables are created

      SELECT table_name

      FROM user_all_tables

      WHERE table_name LIKE 'SYS_FBA%';

      

      DELETE FROM SYS_FBA_HIST_73776

      WHERE srvr_id = 515;

      

      exec dbms_flashback_archive.disassociate_fba('UWCLASS', 'SERVERS');

      

      DELETE FROM SYS_FBA_HIST_73776

      WHERE srvr_id = 515;

      

      exec dbms_flashback_archive.reassociate_fba('UWCLASS', 'SERVERS');

      

      -- make the corresponding change to the fba table

      exec dbms_flashback_archive.reassociate_fba('UWCLASS', 'SERVERS');

REGISTER_APPLICATION

Takes an application name and optionally a flashback archive, and registers an application for database hardening dbms_flashback_archive.register_application( application_name IN VARCHAR2, flashback_archive_name IN VARCHAR2 DEFAULT '');

conn / as sysdba

      

      ALTER SESSION SET CONTAINER = PDBDEV;

      

      exec dbms_flashback_archive.register_application('UWAPP', 'UW_ARCHIVE');

      

      SELECT *

      FROM sys_fba_app;

      

      SELECT *

      FROM sys_fba_app_tables;

      

      exec dbms_flashback_archive.add_table_to_application('UWAPP', 'SERVERS', 'UWCLASS');

      

      SELECT *

      FROM sys_fba_app_tables;

      

      exec dbms_flashback_archive.lock_down_application('UWAPP');

      

      exec dbms_flashback_archive.unlock_application('UWAPP');

      

      exec dbms_flashback_archive.disable_application('UWAPP');

      

      exec dbms_flashback_archive.enable_application('UWAPP', 'SERVERS', 'UWCLASS');

      

      exec dbms_flashback_archive.remove_table_from_application('UWAPP', 'SERVERS', 'UWCLASS');

      

      SELECT *

      FROM sys_fba_app_tables;

REMOVE_TABLE_FROM_APPLICATION

Takes an application name and marks a table in it as no longer being a security table dbms_flashback_archive.remove_table_from_application ( application_name IN VARCHAR2, table_name IN VARCHAR2, schema_name IN VARCHAR2 DEFAULT '');

See REGISTER_APPLICATION Demo Above

SET_CONTEXT_LEVEL

Defines how much of the user context is to be saved dbms_flashback_archive.set_context_level(level IN VARCHAR2); -- valid values are ALL, TYPICAL, and NONE

exec dbms_flashback_archive.set_context_level('ALL');

      

      exec dbms_flashback_archive.purge_context;

      

      -- I have not yet found where any of this is saved in the data dictionary ... it is not in the SYS_FBA_CONTEXT tables

UNLOCK_APPLICATION

Reverses a lock down performed with LOCK_DOWN_APPLICATION dbms_flashback_archive.unlock_application(application_name IN VARCHAR2);

See REGISTER_APPLICATION Demo Above