Using Flashback Data Archive (Oracle Total Recall)
A Flashback Data Archive provides the ability to track and store transactional changes to a table over its lifetime. A Flashback Data Archive is useful for compliance with record stage policies and audit reports
You can specify a default Flashback Data Archive for the system. A Flashback Data Archive is configured with retention time. Data archived in the Flashback Data Archive is retained for the retention time.
Conditions for Data Archive:
- You have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table.
- The table is neither nested, clustered, temporary, remote, or external.
- The table contains neither LONG nor nested columns.
To disable:
- FLASHBACK ARCHIVE ADMINISTER system privilege or
- As SYSDBA
Creating a Flashback Data Archive
Creating a default Flashback Data Archive that uses up to 1 G of tablespace tbs1, whose data are retained for 100 years:
SHAIKDB>create flashback archive default flash_arch_default tablespace tbs1 quota 1g retention 100 year;
Flashback archive created.
SHAIKDB>create flashback archive flash_days tablespace tbs2 quota 100m retention 10 day;
Flashback archive created.
SHAIKDB>alter flashback archive flash_days set default;
Flashback archive altered.
SHAIKDB>create flashback archive flash_months tablespace tbs3 quota 1g retention 1 month;
Flashback archive created.
Add space:
SHAIKDB>alter flashback archive flash_days add tablespace tbs3;
Flashback archive altered.
SHAIKDB>alter flashback archive flash_days modify retention 45 day;
Flashback archive altered.
SHAIKDB>alter flashback archive flash_days modify tablespace tbs3 quota 10g;
Flashback archive altered.
SHAIKDB>col FLASHBACK_ARCHIVE_NAME for a20
SHAIKDB>select * from dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
-------------------- ------------------ ------------------------------ ----------------------------------------
FLASH_ARCH_DEFAULT 1 TBS1 1024
FLASH_DAYS 2 TBS2 100
FLASH_MONTHS 3 TBS3 1024
FLASH_DAYS 2 TBS3 10240
SHAIKDB>select * from dba_flashback_Archive;
OWNER_NAME FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS
------------------------------ -------------------- ------------------ ----------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- -------
SYS FLASH_ARCH_DEFAULT 1 36500 28-SEP-15 09.24.57.000000000 PM 28-SEP-15 09.24.57.000000000 PM
SYS FLASH_DAYS 2 45 28-SEP-15 09.29.39.000000000 PM 28-SEP-15 09.29.39.000000000 PM DEFAULT
SYS FLASH_MONTHS 3 30 28-SEP-15 09.30.28.000000000 PM 28-SEP-15 09.30.28.000000000 PM
undo_management string AUTO
SHAIKDB>alter flashback archive flash_months add tablespace tbs2;
Flashback archive altered.
SHAIKDB>alter flashback archive flash_months remove tablespace tbs3;
alter flashback archive flash_months remove tablespace tbs3
*
ERROR at line 1:
ORA-55626: Cannot remove the Flashback Archive's primary tablespace
If it is not the default tablespace that you created the archive with then you can remove the tablespace using the remove command from flashback archive.
SHAIKDB>alter flashback archive flash_months remove tablespace tbs2;
Flashback archive altered.
SHAIKDB>alter flashback archive flash_months set default;
Flashback archive altered.
SHAIKDB>create user flash identified by flash default tablespace tbs1;
User created.
SHAIKDB>grant create session,create table to flash;
Grant succeeded.
SHAIKDB>grant flashback archive on flash_months to flash;
Grant succeeded.
SHAIKDB>grant flashback archive on flash_arch_default to flash;
Grant succeeded.
SHAIKDB>alter user flash quota unlimited on tbs1;
User altered.
SHAIKDB>alter user flash quota unlimited on tbs2;
User altered.
SHAIKDB>alter user flash quota unlimited on tbs3;
User altered.
Create table flash and store the historical data in the default Flashback Data Archive:
SHAIKDB>show user
USER is "FLASH"
SHAIKDB>create table flash1 (id number,name varchar2(10)) flashback archive;
Table created.
SHAIKDB>begin
for i in 1..100 loop
insert into flash1 values(i,'LOVE');
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
SHAIKDB>select * from flash1 where rownum <=10;
ID NAME
---------- ----------
1 LOVE
2 LOVE
3 LOVE
4 LOVE
5 LOVE
6 LOVE
7 LOVE
8 LOVE
9 LOVE
10 LOVE
10 rows selected.
SHAIKDB>col ARCHIVE_TABLE_NAME for a20
SHAIKDB>col OWNER_NAME for a15
SHAIKDB>col table_name for a10
SHAIKDB>select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME STATUS
---------- --------------- -------------------- -------------------- --------
FLASH1 FLASH FLASH_MONTHS SYS_FBA_HIST_74868 ENABLED
SHAIKDB>show user
USER is "SYS"
SHAIKDB>create flashback archive flash_days tablespace tbs2 quota 1g retention 1 day;
Flashback archive created.
SHAIKDB>grant flashback archive on flash_days to flash;
Grant succeeded.
Create table flash2 and store the historical data in the default_flash_arch Flashback Data Archive:
SHAIKDB>create table flash2 (id number,name2 varchar2(10)) flashback archive flash_days;
Table created.
SHAIKDB>begin
for i in 1..100 loop
insert into flash2 values(i,'FLASH2');
commit;
end loop;
end;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SHAIKDB>select * from flash2 where rownum <=10;
ID NAME2
---------- ----------
1 FLASH2
2 FLASH2
3 FLASH2
4 FLASH2
5 FLASH2
6 FLASH2
7 FLASH2
8 FLASH2
9 FLASH2
10 FLASH2
10 rows selected.
SHAIKDB>select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME STATUS
---------- --------------- -------------------- -------------------- --------
FLASH1 FLASH FLASH_MONTHS SYS_FBA_HIST_74868 ENABLED
FLASH2 FLASH FLASH_DAYS SYS_FBA_HIST_74878 ENABLED
SHAIKDB>alter table flash1 no flashback archive;
alter table flash1 no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
SHAIKDB>show user
USER is "SYS"
or grant flashback archive administer privilege to manage the flashback archives.
SHAIKDB>alter table flash.flash1 no flashback archive;
Table altered.
SHAIKDB>select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME STATUS
---------- --------------- -------------------- -------------------- --------
FLASH2 FLASH FLASH_DAYS SYS_FBA_HIST_74878 ENABLED
SHAIKDB>alter table flash1 flashback archive flash_days;
Table altered
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME STATUS
---------- --------------- -------------------- -------------------- --------
FLASH1 FLASH FLASH_DAYS SYS_FBA_HIST_74868 ENABLED
FLASH2 FLASH FLASH_DAYS SYS_FBA_HIST_74878 ENABLED
SHAIKDB>alter flashback archive flash_days modify retention 7 day;
Flashback archive altered.
19:20:19 SHAIKDB>alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss';
Session altered.
19:20:40 SHAIKDB>select sysdate from dual;
SYSDATE
-------------------
09/29/2015 19:20:47
SHAIKDB>insert into flash1 select * from flash2;
200 rows created.
SHAIKDB>commit;
Commit complete.
SHAIKDB>insert into flash2 select * from flash1;
300 rows created.
SHAIKDB>commit;
Commit complete.
19:20:47 SHAIKDB>select count(*) from flash1;
COUNT(*)
----------
300
19:29:54 SHAIKDB>select count(*) from flash1 as of timestamp to_timestamp('09-29-2015 10:00:00','mm-dd-yyyy hh24:mi:ss');
COUNT(*)
----------
100
19:32:11 SHAIKDB>select count(*) from flash1 as of timestamp to_timestamp('09-29-2015 19:30:00','mm-dd-yyyy hh24:mi:ss');
COUNT(*)
----------
300
19:47:18 SHAIKDB>select distinct name from flash1;
NAME
----------
FLASH2
LOVE
19:47:25 SHAIKDB>delete flash1 where name='LOVE';
200 rows deleted.
19:47:45 SHAIKDB>commit;
Commit complete.
19:47:48 SHAIKDB>select count(*) from flash1;
COUNT(*)
----------
100
19:47:54 SHAIKDB>select count(*) from flash1 as of timestamp ( systimestamp - interval '5' minute);
COUNT(*)
----------
300
19:48:44 SHAIKDB>select count(*) from flash1 as of timestamp ( systimestamp - interval '1' hour);
COUNT(*)
----------
100
Lets get the data back:
19:48:55 SHAIKDB>select count(*) from flash1 as of timestamp ( systimestamp - interval '10' minute) where name='LOVE';
COUNT(*)
----------
200
19:50:44 SHAIKDB>insert into flash1 select * from flash1 as of timestamp(systimestamp - interval '10' minute) where name='LOVE';
200 rows created.
19:51:21 SHAIKDB>commit;
Commit complete.
19:51:23 SHAIKDB>select count(*) from flash1;
COUNT(*)
----------
300
If you must use unsupported DDL statements on a table enabled for Flashback Data Archive, use the DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA procedure to disassociate the base table from its Flashback Data Archive. To reassociate the Flashback Data Archive with the base table afterward, use the DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA procedure.
View
|
Description
|
*_FLASHBACK_ARCHIVE
|
Displays information about Flashback Data Archive files.
|
*_FLASHBACK_ARCHIVE_TS
|
Displays tablespaces of Flashback Data Archive files.
|
*_FLASHBACK_ARCHIVE_TABLES
|
Displays information about tables that are enabled for Data Flashback Archive files.
|
Documentation:
Oracle® Database Advanced Application Developer's Guide 11g Release 2 (11.2) → 12 Using Oracle Flashback Technology → Using Flashback Data Archive (Oracle Total Recall)
