Oracle Flashback Archive

Oracle Flashback Archive / Total Recall Version 11.2.0.3

General Information

Noterow

Data Dictionary Objects

DBA_FLASHBACK_ARCHIVE	DBA_FLASHBACK_ARCHIVE_TABLES	SYS_FBA_FA
DBA_FLASHBACK_ARCHIVE_TS	SYS_FBA_BARRIERSCN	SYS_FBA_TRACKEDTABLES

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

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

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 user_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 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 user_flashback_archive_tables;

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