Flashback Data Archive
11g的新功能，有如下特性。
1.在有效期内保存事务改变的信息。
2.FDA包含一个或多个表空间，可以创建多个FDA,以sysdba登陆时，可以指定默认的FDA.
3.FDA默认关闭，当启动后，只有Flashback Archive Admnistrator权限用户或sysdba禁用FDA.
4.创建FDA时，可以指定四个参数，即闪回归档名称，闪回归档第一个表空间名称，闪回数据量（默认是unlimited，除非在第一个表空间的配额是unlimited，否则不能默认。会出现ORA_55621错误）,保留时间。
5.以sysdba登录，可以指定默认的闪回归档，如果没有指定，也可以使用alter flashback archive命令指定。
在11g之前的闪回功能中，只有Flashback Database 依赖于闪回日志，其他Flashback Query, Flashback Version Query,
Flashback Transaction Query,Flashback Table,Flashback Drop使用了undo表空间数据。
Flashback Data Archive 通过将改变的数据存储到单独创建的闪回归档区，设置单独的存储策略，就可以闪回到指定时间而不影响undo策略。
Flashback Data Archive 不记录数据库的所有变化，只对指定表进行记录。因此对于不需要用到undo，又对审计或数据安全性要求很高的数据库中，
是一个很好的选择。
闪回数据归档区可以有一个或多个，每一个归档区都可以有唯一名称，并设置单独的保留策略。
FBDA是专门为11g引入的一个后台进程，用于追踪表的变化转储到闪回归档区。
Flashback Data Archive 使用限制：
1.只能在自动段空间管理（ASSM）的表空间上创建。
2.要求使用自动的undo管理，undo_management=auto.
SQL> select name,description from v$bgprocess where name='FBDA';
NAME DESCRIPTION
---------- --------------------------------------------------------------------------------------------------------------------------------
FBDA Flashback Data Archiver Process
测试：
一、创建用户并授权
SQL> show user;
USER is "SYS"
SQL> create user fda identified by fda default tablespace users temporary tablespace temp01;
User created.
SQL> grant resource,connect to fda;
Grant succeeded.
SQL> grant flashback archive administer to fda;
Grant succeeded.
SQL> set linesize 800
SQL> select * from dba_sys_privs where grantee='FDA';
GRANTEE PRIVILEGE ADMIN_
------------------------------------------------------------ -------------------------------------------------------------------------------- ------
FDA FLASHBACK ARCHIVE ADMINISTER NO
FDA UNLIMITED TABLESPACE NO
二、查看与闪回有关的视图
SQL> select d.owner,d.view_name,d.text_length From dba_views d where d.view_name like '%FLASHBACK%';
OWNER VIEW_NAME TEXT_LENGTH
------------------------------------------------------------ ------------------------------------------------------------ -----------
SYS GV_$FLASHBACK_DATABASE_LOG 157
SYS GV_$FLASHBACK_DATABASE_LOGFILE 124
SYS GV_$FLASHBACK_DATABASE_STAT 138
SYS V_$FLASHBACK_DATABASE_LOG 146
SYS V_$FLASHBACK_DATABASE_LOGFILE 113
SYS V_$FLASHBACK_DATABASE_STAT 127
SYS V_$FLASHBACK_TXN_GRAPH 250
SYS V_$FLASHBACK_TXN_MODS 260
SYS FLASHBACK_TRANSACTION_QUERY 276
SYS DBA_FLASHBACK_ARCHIVE 304
SYS DBA_FLASHBACK_ARCHIVE_TABLES 336
OWNER VIEW_NAME TEXT_LENGTH
------------------------------------------------------------ ------------------------------------------------------------ -----------
SYS DBA_FLASHBACK_ARCHIVE_TS 263
SYS DBA_FLASHBACK_TXN_REPORT 209
SYS DBA_FLASHBACK_TXN_STATE 358
SYS USER_FLASHBACK_ARCHIVE 532
SYS USER_FLASHBACK_ARCHIVE_TABLES 759
SYS USER_FLASHBACK_TXN_REPORT 146
SYS USER_FLASHBACK_TXN_STATE 321
三、创建测试表空间
SQL> create tablespace fda1 datafile '/u01/app/oracle/oradata/orcl/fda1' size 100m;
Tablespace created.
SQL> create tablespace fda2 datafile '/u01/app/oracle/oradata/orcl/fda2' size 100m;
Tablespace created.
SQL> create tablespace fda3 datafile '/u01/app/oracle/oradata/orcl/fda3' size 100m;
Tablespace created.
SQL> create tablespace fda4 datafile '/u01/app/oracle/oradata/orcl/fda4' size 100m;
Tablespace created.
四、创建一个Flashback Archive，配额20M，有效期一年。
SQL> create flashback archive default archive1 tablespace fda1 quota 20m retention 1 year;
Flashback archive created.
默认的闪回归档只有一个。
SQL> create flashback archive default archive2 tablespace fda2 quota 20m retention 1 year;
create flashback archive default archive2 tablespace fda2 quota 20m retention 1 year
*
ERROR at line 1:
ORA-55609: Attempt to create duplicate default Flashback Archive
五、创建一个非默认归档
SQL> create flashback archive archive2 tablespace fda2 quota 20m retention 3 year;
Flashback archive created.
SQL> revoke unlimited tablespace from fda;
Revoke succeeded.
SQL> alter user fda quota 10m on fda3;
User altered.
SQL> conn fda/fda
Connected.
SQL> create flashback archive archive3 tablespace fda3 quota 20m retention 3 year;
create flashback archive archive3 tablespace fda3 quota 20m retention 3 year
*
ERROR at line 1:
ORA-55621: User quota on tablespace "FDA3" is not enough for Flashback Archive
此种情况下，用户配额必须大于等于归档空间大小，否则会报ORA-55621
SQL> conn / as sysdba;
Connected.
SQL> grant unlimited tablespace to fda;
Grant succeeded.
SQL> conn fda/fda
Connected.
SQL> create flashback archive archive3 tablespace fda3 quota 20m retention 3 year;
Flashback archive created.
SQL> DROP FLASHBACK ARCHIVE archive3;
Flashback archive dropped.
SQL> conn / as sysdba;
Connected.
SQL> show user;
USER is "SYS"
SQL> alter user fda quota 10m on fda3;
User altered.
SQL> create flashback archive archive3 tablespace fda3 quota 20m retention 3 year;
create flashback archive archive3 tablespace fda3 quota 20m retention 3 year
*
ERROR at line 1:
ORA-55621: User quota on tablespace "FDA3" is not enough for Flashback Archive
SQL> create flashback archive archive3 tablespace fda3 quota 10m retention 3 year;
Flashback archive created.
可以使用alter flashback archive 修改如下内容：
1.改变归档有效期
2.清空部分或全部数据
3.增加，修改，移除表空间
不能移除Flashback Data Archive里面所有的表空间。
只能使用sysdba修改默认表空间，只能有一个默认表空间
SQL> show user;
USER is "FDA"
SQL> alter flashback archive archive3 set default;
alter flashback archive archive3 set default
*
ERROR at line 1:
ORA-55611: No privilege to manage default Flashback Archive
SQL> conn / as sysdba;
Connected.
SQL> alter flashback archive archive3 set default;
Flashback archive altered.
SQL> select t.owner_name,t.flashback_archive_name,t.status from dba_flashback_archive t ;
OWNER_NAME FLASHBACK_ARCHIVE_NAME STATUS
------------------------------------------------------------ -------------------------------------------------------------------------------- --------------
SYS DATAARCHIVE
SYS ARCHIVE2
SYS ARCHIVE1
FDA ARCHIVE3 DEFAULT
SQL> alter flashback archive archive2 set default;
Flashback archive altered.
SQL> select t.owner_name,t.flashback_archive_name,t.status from dba_flashback_archive t ;
OWNER_NAME FLASHBACK_ARCHIVE_NAME STATUS
------------------------------------------------------------ -------------------------------------------------------------------------------- --------------
SYS DATAARCHIVE
SYS ARCHIVE2 DEFAULT
SYS ARCHIVE1
FDA ARCHIVE3
六、操作
6.1为存在的Flashback Archive 添加表空间，分配限额(如果不分配quota，则表示无限使用)
SQL> alter flashback archive archive1 add tablespace fda4 quota 20m;
Flashback archive altered.
SQL> select * from DBA_FLASHBACK_ARCHIVE_TS;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
------------------------------------------------------------ ------------------ ------------------------------------------------------------ ------------------------------
DATAARCHIVE 1 FBRA
ARCHIVE1 2 FDA1 20
ARCHIVE1 2 FDA4 20
ARCHIVE2 3 FDA2 20
ARCHIVE3 4 FDA3 10
6.2 修改配额
SQL> alter flashback archive archive1 modify tablespace fda4 quota 15m;
Flashback archive altered.
SQL> select * from DBA_FLASHBACK_ARCHIVE_TS;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
------------------------------------------------------------ ------------------ ------------------------------------------------------------ ------------------------------
DATAARCHIVE 1 FBRA
ARCHIVE1 2 FDA1 20
ARCHIVE1 2 FDA4 15
ARCHIVE2 3 FDA2 20
ARCHIVE3 4 FDA3 10
6.3 修改配额，不限。
SQL> alter flashback archive archive1 modify tablespace fda4 ;
Flashback archive altered.
SQL> select * from DBA_FLASHBACK_ARCHIVE_TS;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
------------------------------------------------------------ ------------------ ------------------------------------------------------------ ------------------------------
DATAARCHIVE 1 FBRA
ARCHIVE1 2 FDA1 20
ARCHIVE1 2 FDA4
ARCHIVE2 3 FDA2 20
ARCHIVE3 4 FDA3 10
6.4 修改有效期
SQL> alter flashback archive archive1 modify retention 5 year;
Flashback archive altered.
SQL> select t.owner_name,t.flashback_archive_name,t.retention_in_days,t.status from DBA_FLASHBACK_ARCHIVE t ;
OWNER_NAME FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS STATUS
------------------------------------------------------------ ------------------------------------------------------------ ----------------- --------------
SYS DATAARCHIVE 30
SYS ARCHIVE2 1095 DEFAULT
SYS ARCHIVE1 1825
FDA ARCHIVE3 1095
6.5 将表空间移除
SQL> alter flashback archive archive1 remove tablespace fda4;
Flashback archive altered.
SQL> select * from DBA_FLASHBACK_ARCHIVE_TS;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
------------------------------------------------------------ ------------------ ------------------------------------------------------------ ------------------------------
DATAARCHIVE 1 FBRA
ARCHIVE1 2 FDA1 20
ARCHIVE2 3 FDA2 20
ARCHIVE3 4 FDA3
仅移除Flashback Archive中的信息，表空间不会删除。
6.6 清空所有的记录数据
SQL> alter flashback archive archive1 purge all;
Flashback archive altered.
6.6 清空超过3天的数据
SQL> alter flashback archive archive1 purge before timestamp(systimestamp - interval'2'day);
Flashback archive altered.
6.7清除指定SCN之前的数据
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
9831847
SQL> alter flashback archive archive1 purge before scn 9831847;
Flashback archive altered.
6.8 删除Flashback Archive,即使是默认的，也可以删除。
SQL> drop flashback archive archive2;
Flashback archive dropped.
SQL> select t.owner_name,t.flashback_archive_name,t.status from dba_flashback_archive t ;
OWNER_NAME FLASHBACK_ARCHIVE_NAME STATUS
------------------------------------------------------------ ------------------------------------------------------------ --------------
SYS DATAARCHIVE
SYS ARCHIVE1
FDA ARCHIVE3
七、使用示例
SQL> create table fdatest(id number) flashback archive;
create table fdatest(id number) flashback archive
*
ERROR at line 1:
ORA-55608: Default Flashback Archive does not exist
SQL> alter flashback archive archive3 set default;
Flashback archive altered.
SQL> create table fdatest(id number) flashback archive;
Table created.
7.2 对表启用或禁用flashback archive.
SQL> alter table fdatest no flashback archive;
Table altered.
SQL> alter table fdatest flashback archive;
Table altered.
7.3 启用并指定闪回区
SQL> alter table fdatest flashback archive
2 archive1;
alter table fdatest flashback archive
*
ERROR at line 1:
ORA-55600: The table "SYS"."FDATEST" is already enabled for Flashback Archive
SQL> alter table fdatest no flashback archive;
Table altered.
SQL> alter table fdatest flashback archive archive1;
Table altered.
7.4 启用flashback archive的表支持以下操作：
1.添加，删除，重新命名或修改一列。
2.添加，删除，重命名约束。
3.drop 或 truncate一个分区或子分区。
4.truncate table操作。
5.rename table;
可能引发错误的操作：
1.alter table statement that includes an ungrade table clause,with or without an including data clause.
2.alter table 移动或交换分区或子分区。
3.drop table 命令。
7.5 DBA_FLASHBACK_ARCHIVE_TABLES表记录着启动flashback archive的操作。
SQL> select * from DBA_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------
FDATEST SYS ARCHIVE1 SYS_FBA_HIST_78792 ENABLED
7.6 删除启用了flashback archive的表报错
SQL> drop table fdatest;
drop table fdatest
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
SQL> exec dbms_flashback_archive.disassociate_fba('SYS','FDATEST');
PL/SQL procedure successfully completed.
SQL> drop table FDATEST;
drop table FDATEST
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
SQL> exec dbms_flashback_archive.reassociate_fba('SYS','FDATEST');
PL/SQL procedure successfully completed.
SQL> exec dbms_flashback_archive.disassociate_fba('SYS','FDATEST');
PL/SQL procedure successfully completed.
SQL> drop table FDATEST;
drop table FDATEST
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
SQL> alter table FDATEST no flashback archive;
alter table FDATEST no flashback archive
*
ERROR at line 1:
ORA-55602: The table "SYS"."FDATEST" is not enabled for Flashback Archive
SQL> show user;
USER is "SYS"
SQL> alter table FDATEST flashback archive;
alter table FDATEST flashback archive
*
ERROR at line 1:
ORA-55624: The table "SYS"."FDATEST" cannot be enabled for Flashback Archive at this point
SQL> exec dbms_flashback_archive.disassociate_fba('SYS','FDATEST');
BEGIN dbms_flashback_archive.disassociate_fba('SYS','FDATEST'); END;
*
ERROR at line 1:
ORA-55633: Cannot do DDL on Flashback Data Archive enabled table
ORA-06512: at "SYS.DBMS_FLASHBACK_ARCHIVE", line 3
ORA-06512: at "SYS.DBMS_FLASHBACK_ARCHIVE", line 30
ORA-06512: at line 1
QL> alter table fdatest flashback archive
2 ;
alter table fdatest flashback archive
*
ERROR at line 1:
ORA-55624: The table "SYS"."FDATEST" cannot be enabled for Flashback Archive at this point
纠结。。。。
八、用flashback data archive 恢复数据
8.1创建测试表
SQL> create table fa(id number) flashback archive;
Table created.
8.2插入数据
SQL> begin
2 for i in 1..100 loop
3 insert into fa values(i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
8.3更新操作
SQL> update fa set id=150 where id <50
2 ;
49 rows updated.
SQL> commit;
Commit complete.
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYYMMDDHH24:MI:S
----------------------------------
20140208 11:16:38
8.4查询刚才数据
SQL> select count(*) from fa as of timestamp(systimestamp-interval '1' minute);
COUNT(*)
----------
100
SQL> select count(*) from fa as of timestamp(systimestamp-interval '10' minute);
select count(*) from fa as of timestamp(systimestamp-interval '10' minute)
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
说明那时还没有建表。
8.5删除操作
SQL> delete from fa;
100 rows deleted.
SQL> commit;
Commit complete.
SQL> select t.archive_table_name from DBA_FLASHBACK_ARCHIVE_TABLES t where t.table_name='FA';
ARCHIVE_TABLE_NAME
----------------------------------------------------------------------------------------------------------
SYS_FBA_HIST_78804
SQL> select count(*) from SYS_FBA_HIST_78804;
COUNT(*)
----------
49
说明只记录了更新的那些记录。
阅读(1779) | 评论(0) | 转发(0) |
