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
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;
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;
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 */