Oracle Flashback Archive / Total Recall
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.
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 cannot 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 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx