Oracle Flashback Archive / Total Recall
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Note ORA-65131: The feature Flashback Data Archive is not supported in a pluggable database. This means in both CDB$ROOT and in any PDB. Expect this to change with the release of 12.1.0.2.
Data Dictionary Objects
CDB_FLASHBACK_ARCHIVE SYS_FBA_APP_TABLES SYS_FBA_PARTITIONS
CDB_FLASHBACK_ARCHIVE_TABLES SYS_FBA_BARRIERSCN SYS_FBA_PERIOD
CDB_FLASHBACK_ARCHIVE_TS SYS_FBA_COLS SYS_FBA_TRACKEDTABLES
DBA_FLASHBACK_ARCHIVE SYS_FBA_CONTEXT SYS_FBA_TSFA
DBA_FLASHBACK_ARCHIVE_TABLES SYS_FBA_CONTEXT_AUD SYS_FBA_USERS
DBA_FLASHBACK_ARCHIVE_TS SYS_FBA_CONTEXT_LIST USER_FLASHBACK_ARCHIVE
DBMS_FLASHBACK_ARCHIVE SYS_FBA_DL USER_FLASHBACK_ARCHIVE_TABLES
SYS_FBA_APP SYS_FBA_FA  
Related Privileges FLASHBACK ARCHIVE ADMINISTER
GRANT flashback archive administer TO uwclass;
This change should be made for the demonstration purposes only. It should not be done in a production environment. set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%retention%';

ALTER SYSTEM SET undo_retention = 1 SCOPE=MEMORY;

-- after the demo return to the default value
ALTER SYSTEM SET undo_retention = 900 SCOPE=MEMORY;
 
Create Flashback Archive
Flashback Archive Creation CREATE FLASHBACK ARCHIVE [DEFAULT] <flashback_archive_name>
TABLESPACE <tablespace_name>
[QUOTA <integer_value <M | G | T | P | E>]
[[NO] OPTIMIZE DATA]
RETENTION <retention_value> <YEAR | MONTH | DAY>;
See demo at page bottom
 
Alter Flashback Archive
Set an archive as the default archive for the database ALTER FLASHBACK ARCHIVE <archive_name> SET DEFAULT;
ALTER FLASHBACK ARCHIVE uw_archive SET DEFAULT;
Add a tablespace to an existing archive ALTER FLASHBACK ARCHIVE <archive_name> ADD TABLESPACE <tablespace_name>;
SELECT tablespace_name
FROM user_tablespaces;

ALTER FLASHBACK ARCHIVE uw_archive ADD TABLESPACE examples;
Modify a tablespace quota on an existing archive tablespace ALTER FLASHBACK ARCHIVE <archive_name>
MODIFY TABLESPACE <tablespace_name>
[QUOTA <integer_value <M | G | T | P E>];
SELECT *
FROM dba_flashback_archive_ts;

ALTER FLASHBACK ARCHIVE uw_archive ADD TABLESPACE examples QUOTA 10M;

SELECT *
FROM dba_flashback_archive_ts;
Remove a tablespace from a flashback archive ALTER FLASHBACK ARCHIVE <archive_name>
REMOVE TABLESPACE <tablespace_name>;
SELECT *
FROM dba_flashback_archive_ts;

ALTER FLASHBACK ARCHIVE uw_archive REMOVE TABLESPACE examples;

SELECT *
FROM dba_flashback_archive_ts;
Change the archive's retention policy ALTER FLASHBACK ARCHIVE <archive_name>
MODIFY RETENTION <retention_value> <YEAR | MONTH | DAY>;
SELECT *
FROM dba_flashback_archive;

ALTER FLASHBACK ARCHIVE uw_archive MODIFY RETENTION 1 MONTH;

SELECT *
FROM dba_flashback_archive;
Purge a flashback archive based on SCN ALTER FLASHBACK ARCHIVE <archive_name> PURGE BEFORE SCN <scn_value>;
desc sys_fba_hist_70439

SELECT DISTINCT startscn AS SCN
FROM sys_fba_hist_70439
UNION
SELECT DISTINCT endscn AS SCN
FROM sys_fba_hist_70439
ORDER BY 1;

ALTER FLASHBACK ARCHIVE uw_archive PURGE BEFORE SCN 8872847;

SELECT DISTINCT startscn AS SCN
FROM sys_fba_hist_70439
UNION
SELECT DISTINCT endscn AS SCN
FROM sys_fba_hist_70439
ORDER BY 1;
Purge a flashback archive based on TIMESTAMP ALTER FLASHBACK ARCHIVE <archive_name>
PURGE BEFORE TIMESTAMP <timestamp_value>;
desc sys_fba_hist_70439

SELECT DISTINCT SCN_TO_TIMESTAMP(startscn) AS SCNTS
FROM sys_fba_hist_70439
UNION
SELECT DISTINCT SCN_TO_TIMESTAMP(endscn) AS SCNTS
FROM sys_fba_hist_70439
ORDER BY 1;

ALTER FLASHBACK ARCHIVE uw_archive
PURGE BEFORE TIMESTAMP TO_TIMESTAMP('29-JUN-07 11.19.39.000000000 PM');

SELECT DISTINCT SCN_TO_TIMESTAMP(startscn) AS SCNTS
FROM sys_fba_hist_70439
UNION
SELECT DISTINCT SCN_TO_TIMESTAMP(endscn) AS SCNTS
FROM sys_fba_hist_70439
ORDER BY 1;
Purge a flashback archive of all contents ALTER FLASHBACK ARCHIVE <archive_name> PURGE ALL;
SELECT COUNT(*)
FROM sys_fba_hist_70439;

ALTER FLASHBACK ARCHIVE uw_archive PURGE ALL;

SELECT COUNT(*)
FROM sys_fba_hist_70439;
 
Drop Flashback Archive
Drop a flashback archive DROP FLASHBACK ARCHIVE <archive_name>;
SELECT COUNT(*)
FROM dba_flashback_archive_ts;

SELECT COUNT(*)
FROM dba_flashback_archive;

DROP FLASHBACK ARCHIVE uw_archive;

SELECT COUNT(*)
FROM dba_flashback_archive_ts;

SELECT COUNT(*)
FROM dba_flashback_archive;
 
Demo
Technology Demo conn uwclass/uwclass@pdbdev

desc dba_flashback_archive_ts

set linesize 121
col flashback_archive_name format a25
col flashback_archive# format 999
col tablespace_name format a20
col quota_in_mb format a15

SELECT COUNT(*)
FROM dba_flashback_archive_ts;

desc user_flashback_archive_tables

SELECT COUNT(*)
FROM dba_flashback_archive_tables;

CREATE FLASHBACK ARCHIVE uw_archive
TABLESPACE uwdata
QUOTA 10M
RETENTION 30 DAY;

SELECT *
FROM dba_flashback_archive_ts;

col create_time format a31
col last_purge_time format a31

SELECT flashback_archive_name, retention_in_days,
create_time, last_purge_time
FROM dba_flashback_archive;

ALTER FLASHBACK ARCHIVE uw_archive MODIFY RETENTION 2 MONTH;

SELECT flashback_archive_name, retention_in_days,
create_time, last_purge_time
FROM dba_flashback_archive;

ALTER TABLE uwclass.servers FLASHBACK ARCHIVE uw_archive;

col table_name format a30
col owner_name format a10
col flashback_archive_name format a30
col archive_table_name format a30

SELECT *
FROM dba_flashback_archive_tables;

SELECT COUNT(*)
FROM dba_objects
WHERE object_name LIKE 'SYS_FBA_HIST%';

SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE table_name LIKE 'SYS_FBA%';

UPDATE servers
SET srvr_id = srvr_id+1;

COMMIT;

UPDATE servers
SET srvr_id = srvr_id-3;

COMMIT;

DELETE FROM servers
WHERE srvr_id > 599;

COMMIT;

-- need to wait for undo to age out and then ...
conn / as sysdba

desc sys_fba_ddl_colmap_70439

Name                         Null?    Type
---------------------------- -------- -------------
STARTSCN                              NUMBER
ENDSCN                                NUMBER
XID                                   RAW(8)
OPERATION                             VARCHAR2(1)
COLUMN_NAME                           VARCHAR2(255)
TYPE                                  VARCHAR2(255)
HISTORICAL_COLUMN_NAME                VARCHAR2(255)

desc sys_fba_tcrv_70439

Name                         Null?    Type
---------------------------- -------- -----------
RID                                   ROWID
STARTSCN                              NUMBER
ENDSCN                                NUMBER
XID                                   RAW(8)
OP                                    VARCHAR2(1)

desc sys_fba_hist_70439

Name                         Null?    Type
---------------------------- -------- --------------
RID                                   VARCHAR2(4000)
STARTSCN                              NUMBER
ENDSCN                                NUMBER
XID                                   RAW(8)
OPERATION                             VARCHAR2(1)
SRVR_ID                               NUMBER(10)
NETWORK_ID                            NUMBER(10)
STATUS                                VARCHAR2(1)
LATITUDE                              FLOAT(20)
LONGITUDE                             FLOAT(20)
NETADDRESS                            VARCHAR2(15)

SELECT COUNT(*)
FROM sys_fba_hist_70439;

SELECT *
FROM user_flashback_archive_tables;

DELETE FROM sys_fba_hist_70439;

UPDATE sys_fba_hist_70439
SET netaddress = 'Oops';

DROP TABLE sys_fba_hist_70439;

ALTER TABLE servers NO FLASHBACK ARCHIVE;

SELECT *
FROM user_flashback_archive_tables;

SELECT flashback_archive_name, status
FROM dba_flashback_archive;

DROP FLASHBACK ARCHIVE uw_archive;

SELECT flashback_archive_name, status
FROM dba_flashback_archive;
Warning /* Should a table created by sys to support Flashback Archive fail to drop when the table is converted to NO FLASHBACK ARCHIVE the table can not be dropped with normal SQL. */

-- the solution is to do the following:

SELECT o.object_id, o. owner, o.object_name, t.property
FROM dba_objects_ae o, tab$ t
WHERE o.object_type = 'TABLE'
AND o.object_id = t.obj#
AND t.property = 9126805504;

-- if the object returned is the one you are trying to drop then:

UPDATE tab$
SET property = 536870912
WHERE property = 9126805504;

COMMIT;

-- you will then be able to drop the table. For example:

DROP TABLE SYS_FBA_TCRV_73674;

/* and as you remember your Oracle support agreement clearly states you should not do this consider the implications and ask Oracle Support to authorize it before acting */

Related Topics
DBMS_FLASHBACK_ARCHIVE
Flashback Query
Flashback Table
Flashback Transaction Backout
Flashback Transaction Query
Flashback Version Query
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx