1.什么是flashback data archive
Flashback data archive是oracle 11g中引入的一个新特性。Flashback archive是一个新的数据库对象,用于存储一个或多表的历史数据。Flashback archive是一个逻辑对象,概念上类似于表空间。实际上flashback archive可以看作是存储一个或多个表的所有事务变化的逻辑空间。
2.FDA实现原理
从9i开始,Oracle就提供了表的flashback query功能,使用户可以查询表上过去某一时刻的数据或一段时间内数据的变化情况。flashback query实际上是利用UNDO信息来获得过去的数据。由于UNDO空间是循环使用的,事务的前映像不可能永久保存,因此flashback query只能支持过去一段时间内的数据查询,这个时间与UNDO_RETENTION相关。
Flashback data archive相比flashback query则更进了一步。Flashback data archive不再直接使用UNDO信息获得历史数据,而是把历史数据保存在一个track table里。Oracle新增加了一个后台进程fbda,此进程每隔一段时间(通常是5分钟)把UNDO中的历史数据信息加到track table里。如果表上开启了flashback archive功能,则Oracle先给事务产生的UNDO信息做标记,直到被标记的UNDO信息加到track table后,此UNDO空间才能被重用。
Flashback data archive采用压缩方式存储历史数据,以减小空间占用。同时flashback data archive采用range分区方式对track table表进行分区。track table表上没有索引,用户可根据需要对track table增加索引。
3.FDA(闪回数据归档)实验
3.1 环境准备
----创建表空间
sys@ORCL>create tablespace shall_fda1 datafile '/home/oracle/shall_fda1.dbf' size 2g;
Tablespace created.
----创建闪回归档
sys@ORCL>create flashback archive flash_fda1 tablespace shall_fda1 retention 1 year;
Flashback archive created.
----查看闪回信息
sys@ORCL>select * from dba_flashback_archive;
OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
----------------------------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
------------------ -----------------
CREATE_TIME
---------------------------------------------------------------------------
LAST_PURGE_TIME STATUS
--------------------------------------------------------------------------- -------
SYS
FLASH_FDA1
1 365
25-APR-16 07.28.39.000000000 PM
25-APR-16 07.28.39.000000000 PM
----查看闪回使用表空间
sys@ORCL>select * from dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NAME
----------------------------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
------------------ ------------------------------ ----------------------------------------
FLASH_FDA1
1 SHALL_FDA1
3.2 创建测试用户
sys@ORCL>create user shall identified by shall default tablespace users;
User created.
sys@ORCL>grant connect,resource to shall;
Grant succeeded.
----创建测试表
shall@ORCL>create table test(id int,name varchar2(8));
Table created.
shall@ORCL>begin
2 for i in 1..10000 loop
3 insert into test values(i,'shall');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
shall@ORCL>commit;
Commit complete.
shall@ORCL>select count(*) from test;
COUNT(*)
----------
10000
3.3 将重要表加入FDA
shall@ORCL>alter table test flashback archive flash_fda1;
alter table test flashback archive flash_fda1
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
----授予权限
sys@ORCL>grant flashback archive on flash_fda1 to shall;
Grant succeeded.
shall@ORCL>alter table test flashback archive flash_fda1;
Table altered.
3.4 查看开启FDA表
shall@ORCL>select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME
----------------------------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME STATUS
----------------------------------------------------- -------------
TEST SHALL
FLASH_FDA1
SYS_FBA_HIST_88619 ENABLED
3.5 删除数据并提交
sys@ORCL>select count(*) from shall.test;
COUNT(*)
----------
10000
sys@ORCL>select sysdate from dual;
SYSDATE
-------------------
2016-04-25 19:45:37
sys@ORCL>delete from shall.test;
10000 rows deleted.
sys@ORCL>commit;
Commit complete.
3.6 插入新的数据
sys@ORCL>begin
2 for i in 1..999 loop
3 insert into shall.test values(3,'ffff');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
sys@ORCL>select count(*) from shall.test;
COUNT(*)
----------
999
3.7 闪回查询任意时间点
sys@ORCL>select count(*) from shall.test as of timestamp to_timestamp('2016-04-25 19:45:37','yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
10000
sys@ORCL>select current_scn from v$database;
CURRENT_SCN
-----------
1013709
3.8 truncate表
sys@ORCL>truncate table shall.test;
Table truncated.
3.9 再次闪回查询任意时间点
sys@ORCL>select count(*) from shall.test as of timestamp to_timestamp('2016-04-25 19:45:37','yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
10000
sys@ORCL>select count(*) from shall.test as of scn 1013709;
COUNT(*)
----------
999
3.10 恢复truncate之前的数据
shall@ORCL>insert into test select * from test as of scn 1013709;
999 rows created.
shall@ORCL>commit;
Commit complete.
shall@ORCL>select count(*) from test;
COUNT(*)
----------
999
shall@ORCL>truncate table test;
Table truncated.
shall@ORCL>select * from test;
no rows selected
shall@ORCL>select count(*) from shall.test as of scn 1013709;
COUNT(*)
----------
999
shall@ORCL>select count(*) from shall.test as of timestamp to_timestamp('2016-04-25 19:45:37','yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
10000
3.11 drop表报错
shall@ORCL>drop table test purge;
drop table test purge
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
----需要改变取消fra
shall@ORCL>alter table test no flashback archive;
alter table test no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
----授予shall操作权限
sys@ORCL>grant flashback archive administer to shall;
Grant succeeded.
shall@ORCL>alter table test no flashback archive;
Table altered.
shall@ORCL>drop table test;
Table dropped.
shall@ORCL>show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$MU8Rfr2Fvq3gUxYLqMAQ0Q==$0 TABLE 2016-04-25:20:10:54
----利用flashback drop恢复表
shall@ORCL>flashback table test to before drop;
Flashback complete.
4.FDA修改操作
----创建表空间
sys@ORCL>create tablespace shall_fda20 datafile '/home/oracle/shall_fda20.dbf' size 200m;
Tablespace created.
----创建FDA
sys@ORCL>create flashback archive flash_fda20 tablespace shall_fda20 retention 1 year;
Flashback archive created.
----查询
sys@ORCL>col FLASHBACK_ARCHIVE_NAME for a40
sys@ORCL>col LASHBACK_ARCHIVE# for a40
sys@ORCL>col TABLESPACE_NAME for a40
sys@ORCL>select * from dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
---------------------------------------- ------------------ ---------------------------------------- ----------------------------------------
FLASH_FDA1 1 SHALL_FDA1
FLASH_FDA20 2 SHALL_FDA20
sys@ORCL>select * from dba_flashback_archive;
OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS
------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------ ----------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- -------
SYS FLASH_FDA1 365 25-APR-16 07.28.39.000000000 PM 25-APR-16 07.28.39.000000000 PM
SYS FLASH_FDA20 365 25-APR-16 08.42.42.000000000 PM 25-APR-16 08.42.42.000000000 PM
----设置默认FDA
sys@ORCL>alter flashback archive flash_fda20 set default;
Flashback archive altered.
sys@ORCL>select * from dba_flashback_archive;
OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS
------------------------------ ---------------------------------------- ------------------ ----------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- -------
SYS FLASH_FDA1 1 365 25-APR-16 07.28.39.000000000 PM 25-APR-16 07.28.39.000000000 PM
SYS FLASH_FDA20 2 365 25-APR-16 08.42.42.000000000 PM 25-APR-16 08.42.42.000000000 PM DEFAULT
----FDA增加表空间
sys@ORCL>alter flashback archive flash_fda20 add tablespace shall_fda1;
Flashback archive altered.
sys@ORCL>select * from dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
---------------------------------------- ------------------ ---------------------------------------- ----------------------------------------
FLASH_FDA1 1 SHALL_FDA1
FLASH_FDA20 2 SHALL_FDA1
FLASH_FDA20 2 SHALL_FDA20
----FDA移除表空间
sys@ORCL>alter flashback archive flash_fda20 remove tablespace shall_fda1;
Flashback archive altered.
----FDA限额
sys@ORCL>alter flashback archive flash_fda1 modify tablespace shall_fda1 quota 100m;
Flashback archive altered.
sys@ORCL>select * from dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
---------------------------------------- ------------------ ---------------------------------------- ----------------------------------------
FLASH_FDA1 1 SHALL_FDA1 100
FLASH_FDA20 2 SHALL_FDA20
----修改FDA保留时间
sys@ORCL>alter flashback archive flash_fda20 modify retention 3 year;
Flashback archive altered.
sys@ORCL>select * from dba_flashback_archive;
OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS
------------------------------ ---------------------------------------- ------------------ ----------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- -------
SYS FLASH_FDA1 1 365 25-APR-16 07.28.39.000000000 PM 25-APR-16 07.28.39.000000000 PM
SYS FLASH_FDA20 2 1095 25-APR-16 08.42.42.000000000 PM 25-APR-16 08.42.42.000000000 PM DEFAULT
----清除flash_fda1的所有保存数据
sys@ORCL>alter flashback archive flash_fda1 purge all;
Flashback archive altered.
----手工删除flash_fda1中一个月之前的数据
sys@ORCL>alter flashback archive flash_fda1 purge before timestamp (systimestamp - interval '1' month);
Flashback archive altered.
----手工删除flash_fda1中5天之前的数据
sys@ORCL>alter flashback archive flash_fda1 purge before timestamp (systimestamp - interval '5' day);
Flashback archive altered.
----手工删除flash_fda1中scn为1019200之前的数据
sys@ORCL>alter flashback archive flash_fda1 purge before scn 1019200;
Flashback archive altered.
----创建表时添加到FDA
shall@ORCL>create table t1(id int) flashback archive flash_fda1;
Table created.
----表关闭、启用FDA
shall@ORCL>alter table t1 no flashback archive;
Table altered.
shall@ORCL>alter table t1 flashback archive; ----没有指定FDA就使用默认的
shall@ORCL>alter table t1 no flashback archive;
Table altered.
shall@ORCL>alter table t1 flashback archive flash_fda20;
Table altered.
----查看使用FDA的表
shall@ORCL>select * from dba_flashback_archive_tables;
----查看FDA对应的哪些表空间
shall@ORCL>select * from dba_flashback_archive_ts;
----查看FDA设置
shall@ORCL>select * from dba_flashback_archive;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30130773/viewspace-2103944/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2103944/