ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 11g 新特性 Flashback Data Archive 说明

oracle 11g 新特性 Flashback Data Archive 说明

原创 Linux操作系统 作者:roominess 时间:2012-05-08 14:02:34 0 删除 编辑

Oracle 11g里又出了一个新特性:Oracle Flashback Data Archive.  11g的官方文档里搜到了相关内容说明,参考:

            Using Oracle Flashback Technology

                        --Using Flashback Data Archive (Oracle Total Recall)

            http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_flashback.htm#BJFIEJGG

 

 

 

.  Flashback Data Archive 说明

 

官网的定义如下:

            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.

            --Flashback Data Archive 在它的有效期内将保存事务改变的信息。

            A Flashback Data Archive consists of one or more tablespaces or parts thereof. You can have multiple Flashback Data Archives. If you are logged on as SYSDBA, 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.

            -- FDA 包含一个或者多个表空间,我们可以创建多个FDA 当以sysdba 登陆时,可以指定default FDA

            By default, flashback archiving is off for any table. You can enable flashback archiving for a table if all of these conditions are true:

            -- 默认情况下,FDA 是关闭的,当具备一下条件时,我们可以启用FDA

 

(1).  You have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive to use for that table.

(2).  The table is neither nested, clustered, temporary, remote, or external.

(3).  The table contains neither LONG nor nested columns.

 

            After flashback archiving is enabled for a table, you can disable it only if you either have the FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA.

            --FDA 启动以后,只有具有FLASHBACK ARCHIVE ADMINISTER 权限的用户或者用SYSDBA登陆的用户才可以禁用FDA

 

            When choosing a Flashback Data Archive for a specific table, consider the data retention requirements for the table and the retention times of the Flashback Data Archives on which you have the FLASHBACK ARCHIVE object privilege.

 

给用户赋:

SQL> create user dvd identified by dvd default tablespace users temporary tablespace temp;

User created.

 

SQL> grant resource,connect to dvd;

Grant succeeded.

 

SQL> grant flashback archive administer to dvd;

Grant succeeded.

 

SQL> select * from dba_sys_privs where grantee='DVD';

 

GRANTEE        PRIVILEGE                            ADM

----------------------- ---------------------------------------- ---

DVD            FLASHBACK ARCHIVE ADMINISTER       NO

DVD            UNLIMITED TABLESPACE                NO

 

 

          Oracle 10g中的lashback 包括: flashback version queryflashback transaction queryflashback databaseflashback tableflashback drop等特性。

            在这些闪回技术当中,除了Flashback Database(依赖于闪回日志)之外,其他的闪回技术都是依赖于Undo撤销数据,都与数据库初始化参数UNDO_RETENTION密切相关。

            它们是从撤销数据中读取信息来构造旧数据的。这样就有一个限制,就是undo中的信息不能被覆盖。而undo段是循环使用的,只要事务提交,之前的undo信息就可能被覆盖,虽然可以通过 undo_retention等参数来延长undo的存活期,但这个参数会影响所有的事务,设置过大,可能导致undo tablespace快速膨胀。

 

            Oracle 11gflashback增加了:Flashback Data Archive 特性。该技术与之前的Flashback的实现机制不同,通过将变化数据另外存储到创建的闪回归档区(Flashback Archive)中,以和undo区别开来,这样就可以为闪回归档区单独设置存储策略,使之可以闪回到指定时间之前的旧数据而不影响undo策略。并且可以根据需要指定哪些数据库对象需要保存历史变化数据,而不是将数据库中所有对象的变化数据都保存下来,这样可以极大地减少空间需求。

            Flashback Data Archive并不是记录数据库的所有变化,而只是记录了指定表的数据变化。所以,Flashback Data Archive是针对对象的保护,是Flashback Database的有力补充。

 

            通过Flashback Data Archive,可以查询指定对象的任何时间点(只要满足保护策略)的数据,而且不需要用到undo,这在有审计需要的环境,或者是安全性特别重要的高可用数据库中,是一个非常好的特性。缺点就是如果该表变化很频繁,对空间的要求可能很高。

 

闪回数据归档区

            闪回数据归档区是闪回数据归档的历史数据存储区域,在一个系统中,可以有一个默认的闪回数据归档区,也可以创建其他许多的闪回数据归档区域。

            每一个闪回数据归档区都可以有一个唯一的名称。同时,每一个闪回数据归档区都对应了一定的数据保留策略。

            例如可以配置归档区FLASHBACK_DATA_ARCHIVE_1中的数据保留期为1年,而归档区FLASHBACK_DATA_ARCHIVE_2的数据保留期为2天或者更短。 以后如果将表放到对应的闪回数据归档区,则就按照该归档区的保留策略来保存历史数据。

            闪回数据归档区是一个逻辑概念,是从一个或者多个表空间中拿出一定的空间,来保存表的修改历史,这样就摆脱了对Undo撤销数据的依赖,不利用undo就可以闪回到归档策略内的任何一个时间点上。

 

Flashback archive相关数据字典

*_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.

 

代表DBA 或者User

 

Flashback archive的后台进程

            Oracle11gFlashback data archive特性专门引入了一个新的后台进程FBDA,用于将追踪表(traced table,也就是将指定使用flashback data archivetable)的历史变化数据转存到闪回归档区。


SQL> select name,description from v$bgprocess where name='FBDA';

NAME  DESCRIPTION

----- -----------------------------------------------------------

FBDA  Flashback Data Archiver Process

 

 

 

Flashback archive 的限制条件

            1Flashback data archive只能在ASSMtablespace上创建
            
2Flashback data archive要求必须使用自动undo管理,

                         undo_management 参数为auto

 

.  Flashback Data Archive 的相关操作

2.1 Creating a Flashback Data Archive

            Create a Flashback Data Archive with the CREATE FLASHBACK ARCHIVE statement, specifying:

            1Name of the Flashback Data Archive

2Name of the first tablespace of the Flashback Data Archive

3(Optional) Maximum amount of space that the Flashback Data Archive can use in the first tablespaceThe default is unlimited. Unless your space quota on the first tablespace is also unlimited, you must specify this value; otherwise, error ORA-55621 occurs.

4Retention time (number of days that Flashback Data Archive data for the table is guaranteed to be stored)

 

--  创建FDA 时,可以指定以上4个参数,没有没有执行Flashback Archive 的配额,默认为 unlimited 这里的配额,只的是用户对表空间的配额。

 

            If you are logged on as SYSDBA, you can also specify that this is the default Flashback Data Archive for the system. If you omit this option, you can still make this Flashback Data Archive the default later .

            -- 如果以SYSDBA 登陆,还可以指定default Flashback Data Archive 如果没有指定,也可以通过alter flashback archive 命令来指定。

 

示例:

 

1先创建几个测试的表空间

 

SQL> create tablespace FDA1 datafile 'D:/APP/ADMINISTRATOR/ORADATA/NEWCCS/FDA01.dbf' size 100M;

Tablespace created.

 

SQL> create tablespace FDA2 datafile 'D:/APP/ADMINISTRATOR/ORADATA/NEWCCS/FDA02.dbf' size 100M;

Tablespace created.

 

SQL> create tablespace FDA3 datafile 'D:/APP/ADMINISTRATOR/ORADATA/NEWCCS/FDA03.dbf' size 100M;

Tablespace created.

 

SQL> create tablespace FDA4 datafile 'D:/APP/ADMINISTRATOR/ORADATA/NEWCCS/FDA04.dbf' size 100M;

Tablespace created.

 

2创建一个默认的Flashback Archive, 配额为10M,数据保留期为1

 

SQL> create flashback archive default fla1 tablespace fda1 quota 10M retention 1 year;

 

默认的Flashback Archive 只能有一个:

SQL> create flashback archive default fla3 tablespace fda1 quota 10M retention 1 year;

create flashback archive default fla3 tablespace fda1 quota 10M retention 1 year

                                                 *

ERROR at line 1:

ORA-55609: Attempt to create duplicate default Flashback Archive

 

这里报错了,我们可以是使用alter flashback 来修改默认的Flashback Archive.

 

3  创建一个Flashback Archive fla2,使用默认配额unlimited retention 年。

 

SQL> create flashback archive fla2 tablespace fda2 retention 2 year;

Flashback archive created.

 

            根据官网的说法,这种情况下,用户对该表空间的配额也必须为ulimited 否则就会报错ORA-55621

 

测试一下:

 

SQL> conn / as sysdba;

Connected.

 

SQL> revoke unlimited tablespace from dvd;

Revoke succeeded.

 

SQL> alter user dvd quota 10m on fda4;

User altered.

 

SQL> conn dvd/dvd;

Connected.

 

SQL> create flashback archive fla5 tablespace fda4 retention 1 day;

create flashback archive fla5 tablespace fda4 retention 1 day

                                         *

ERROR at line 1:

ORA-55621: User quota on tablespace "FDA4" is not enough for Flashback Archive

 

报错。

 

修改用户的配合,在创建,成功:

 

SQL> conn / as sysdba;

Connected.

 

SQL> grant unlimited tablespace to dvd;

Grant succeeded.

 

SQL> conn dvd/dvd;

Connected.

 

SQL> create flashback archive fla5 tablespace fda4 retention 1 day;

Flashback archive created.

 

2.2  Altering a Flashback Data Archive

With the ALTER FLASHBACK ARCHIVE statement, you can:

-- 使用alter flashback archive 可以修改如下内容:

            1Change the retention time of a Flashback Data Archive

            2Purge some or all of its data

            3Add, modify, and remove tablespaces

 

Note:

            Removing all tablespaces of a Flashback Data Archive causes an error.

            If you are logged on as SYSDBA, you can also use the ALTER FLASHBACK ARCHIVE statement to make a specific file the default Flashback Data Archive for the system.

            -- 不能移除Flashback Data Archive里的所有表空间。 否则报错。 如果用sysdba 登陆,可以修改默认的Flashback archive

 

示例:

2.2.1  Flashback Data Archive 修改为default FA

 

先用我们具有flashback archive administer 权限的用户试试:

SQL> conn dvd/dvd;

Connected.

SQL> alter flashback archive fla1 set default;

alter flashback archive fla1 set default

*

ERROR at line 1:

ORA-55611: No privilege to manage default Flashback Archive

 

报错,没有权限,用sysdba 测试成功:

SQL> conn / as sysdba;

Connected.

SQL> alter flashback archive fla1 set default;

 

Flashback archive altered.

 

注意一点,只能有一个默认的Flashback archive.

 

SQL> select flashback_archive_name name, status  from dba_flashback_archive;

NAME       STATUS

---------- -------

FLA1       DEFAULT

FLA2

 

当前默认的Flashback Archive FLA1,我们将默认改成FLA2,在查看:

SQL> alter flashback archive fla2 set default;

Flashback archive altered.

 

SQL>  select flashback_archive_name name, status  from dba_flashback_archive;

NAME       STATUS

---------- -------

FLA1

FLA2       DEFAULT

 

2.2.2  为已经存在的Flashback Archive 添加表空间,并指定配额

 

SQL> alter flashback archive fla1 add tablespace fda3 quota 20M;

Flashback archive altered.

 

2.2.3 为已经存在的Flashback Archive 添加表空间,不指定配额,即需要多少用多少空间

 

SQL>  alter flashback archive fla1 add tablespace fda4;

Flashback archive altered.

 

2.2.4  修改已经存在的Flashback Archive的配额

SQL> alter flashback archive fla1 modify tablespace fda1 quota 20m;

Flashback archive altered.

 

2.2.5  修改配额不受限制

SQL> alter flashback archive fla1 modify tablespace fda1;

Flashback archive altered.

 

2.2.6 修改Flashback Archive retention time

SQL> alter flashback archive fla1 modify retention 2 year;

Flashback archive altered.

 

SQL> alter flashback archive fla1 modify retention 1 month;

Flashback archive altered.

 

SQL> alter flashback archive fla1 modify retention 2 month;

Flashback archive altered.

 

SQL> alter flashback archive fla1 modify retention 2 day;

Flashback archive altered.

 

SQL> alter flashback archive fla1 modify retention 1 day;

Flashback archive altered.

 

2.2.7  将表空间从Flashback Archive中移除

 

SQL> alter flashback archive fla1 remove tablespace fda4;

Flashback archive altered.

 

-- 注意,这里移除的仅仅是Flashback Archive中的信息,表空间不会被删除。

 

2.2.8  清空Flashback Archive中的所有历史记录

 

SQL> alter flashback archive fla1 purge all;

Flashback archive altered.

 

2.2.9 清空Flashback Archive 中超过1天的历史数据

SQL> alter flashback archive fla1 purge before timestamp (systimestamp - interval '1' day);

Flashback archive altered.

 

2.2.10  清空Flashback Archive 中指定SCN 之前的所有历史数据

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

 1315755078

 

SQL> alter flashback archive fla1 purge before scn  1315755078;

Flashback archive altered.

 

我这里只是演示一个SCN。 具体要结合自己的情况。

 

2.3  Dropping a Flashback Data Archive

            Drop a Flashback Data Archive with the DROP FLASHBACK ARCHIVE statement. Dropping a Flashback Data Archive deletes its historical data, but does not drop its tablespaces.

--  删除Flashback Archive 不会删除相应的表空间

 

示例:

SQL> DROP FLASHBACK ARCHIVE fla2;

Flashback archive dropped.

 

SQL>  select flashback_archive_name name, status  from dba_flashback_archive;

NAME       STATUS

---------- -------

FLA1

 

2.4  Specifying the Default Flashback Data Archive

            By default, the system has no default Flashback Data Archive. If you are logged on as SYSDBA, you can specify default Flashback Data Archive in either of these ways:

            默认情况下,没有default Flashback Data Archive. 当以sysdba 登陆之后,就可以指定它。

 

2.4.1 修改已经存在的Flashback Archive default

SQL> alter flashback archive fla1 set default;

Flashback archive altered.

 

SQL> alter flashback archive fla10 set default;

alter flashback archive fla10 set default

*

ERROR at line 1:

ORA-55605: Incorrect Flashback Archive is specified

 

如果指定的Flashback 不存在,就报错。

 

2.4.2 在创建Flashback Data Archive 时,指定default

SQL>create flashback archive default fla2 tablespace tbs1 quota 10m retention 1 year;

 

            The default Flashback Data Archive for the system is the default Flashback Data Archive for every user who does not have his or her own default Flashback Data Archive.

 

 

2.5  Enabling and Disabling Flashback Data Archive

            By default, flashback archiving is disabled for any table. You can enable flashback archiving for a table if you have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive to use for that table.

            默认情况下,所有表都没有启动flashback archive

 

            To enable flashback archiving for a table, include the FLASHBACK ARCHIVE clause in either the CREATE TABLE or ALTER TABLE statement.

            In the FLASHBACK ARCHIVE clause, you can specify the Flashback Data Archive where the historical data for the table are stored. The default is the default Flashback Data Archive for the system. If you specify a nonexistent Flashback Data Archive, an error occurs.

 

            If you enable flashback archiving for a table, but AUM(automatic undo managed) is disabled, error ORA-55614 occurs when you try to modify the table.

 

            If a table has flashback archiving enabled, and you try to enable it again with a different Flashback Data Archive, an error occurs

 

            After flashback archiving is enabled for a table, you can disable it only if you either have the FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA.

            To disable flashback archiving for a table, specify NO FLASHBACK ARCHIVE in the ALTER TABLE statement. (It is unnecessary to specify NO FLASHBACK ARCHIVE in the CREATE TABLE statement, because that is the default.)

 

 

示例:

2.5.1 创建table,使用默认的Flashback Data Archive      来存储历史数据

SQL> create table 安庆 (id number) flashback archive;

Table created.

 

2.5.2 创建table,使用指定的Flashback Data Archive 来存储历史数据

 

SQL> create table 怀宁 (id number) flashback archive fla1;

Table created.

 

2.5.3 对表启用Flashback archive,并使用默认的Flashback archive

 

SQL> alter table dave flashback archive;

Table altered.

 

2.5.4 禁用表的Flashback Archive

SQL> alter table dave no flashback archive;

Table altered.

 

2.5.5 table 启用Flashback archive,并指定Flashaback Archive 区。

SQL> alter table dave flashback archive fla1;

Table altered.

 

 

2.6 DDL Statements on Tables Enabled for Flashback Data Archive

            Flashback Data Archive supports many DDL statements, including some that alter the table definition or move data. For example:

            --启动Flashback Data Archive的表支持以下的DDL 操作

            1ALTER TABLE statement that does any of the following:

                                    1Adds, drops, renames, or modifies a column

                                    2Adds, drops, or renames a constraint

                                    3Drops or truncates a partition or subpartition operation

            2TRUNCATE TABLE statement

            3RENAME statement that renames a table

 

            Some DDL statements cause error ORA-55610 when used on a table enabled for Flashback Data Archive. For example:

            -- 启动Flashback Data Archive的表上的一些DDL 操作可能触发ORA-55610的错误,这些DDL 如下:

            1ALTER TABLE statement that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause

            3ALTER TABLE statement that moves or exchanges a partition or subpartition operation

            3DROP TABLE statement

 

 

            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.

 

            -- 如果必须在已经启用Flashback Archive的表上执行这些不支持的DDL 操作,可以用DBMS_FLASHBACK_ARCHIVE 包将表从Flashback Data Archive 分离出来,待操作结束后在添加进去。

 

            The DBMS_FLASHBACK_ARCHIVE package contains two simple procedures for disassociation and reassociation of a Flashback Data Archive (FDA) enabled table from/with its underlying FDA respectively.

 

            Flashback Area中,会有一张历史表记录着我们启动FA表的所有操作。 我们可以通过如下SQL 来查看他们之间的映射关系。

 

SQL> SELECT table_name,archive_table_name,status from dba_flashback_archive_tables;

 

TABLE_NAME ARCHIVE_TABLE_NAME   STATUS

---------- -------------------- --------

ANQING     SYS_FBA_HIST_78429   ENABLED

怀宁       SYS_FBA_HIST_78431   ENABLED

ORA        SYS_FBA_HIST_78448   ENABLED

DVD        SYS_FBA_HIST_78456   ENABLED

HUAINING   SYS_FBA_HIST_78464   ENABLED

QS         SYS_FBA_HIST_78472   ENABLED

FA         SYS_FBA_HIST_78484   ENABLED

 

7 rows selected.

 

            我们要执行那些不支持的DDL,就需要用dbms_flashback_archive禁用他们之间的映射关系,在操作,操作完在用该包启用他们。

 

关于dbms_flashback_archive包的使用,参考官网:

          DBMS_FLASHBACK_ARCHIVE

            http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_flashb_archive.htm#ARPLS72464

 

示例:

 

SQL> drop table 怀宁;

drop table 怀宁

           *

ERROR at line 1:

ORA-55610: Invalid DDL statement on history-tracked table

 

这个表使我们之前创建的,并启用了Flashback Archive.

 

表的分离和重新结合:

SQL> exec dbms_flashback_archive.disassociate_fba('SYS','怀宁');

PL/SQL procedure successfully completed.

 

SQL> exec dbms_flashback_archive.reassociate_fba('SYS','怀宁');

PL/SQL procedure successfully completed.

 

最后我们在分离,在drop table

SQL> exec dbms_flashback_archive.disassociate_fba('SYS','怀宁');

PL/SQL procedure successfully completed.

 

SQL> drop table 怀宁;

drop table 怀宁

           *

ERROR at line 1:

ORA-55610: Invalid DDL statement on history-tracked table

 

drop 失败。

 

表情: - -

猜测:灵异事件。

 

google 一下,说是bug9650074

 

9650074 ORA-55633 in Flashback data archive DDL support area

 

结果metalink 又不读开,这个网络啊。 先不研究了。

 

 

.  一个用Flashback Data Archive 恢复数据的测试

 

这个测试使用之前的Flashback Archive: fla1.

 

创建测试表:

SQL> create table fa(id number) flashback archive;

Table created.

 

插入数据:

SQL> declare

  2  i number;

  3  begin

  4  for i in 1..100 loop

  5  insert into fa values(i);

  6  end loop;

  7  commit;

  8  end;

  9  /

PL/SQL procedure successfully completed.

 

SQL> select count(*) from fa;

  COUNT(*)

----------

  100

 

查询时间:

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') tm from dual;

TM

-------------------

2011-05-11 15:33:35

 

update 一次数据:

SQL> update fa set id=200 where id <50;

49 rows updated.

SQL>commit;

 

在查询一次时间:

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') tm from dual;

 

TM

-------------------

2011-05-11 15:35:23

 

 

 

使用Flashback Archive查询1分钟之前的数据:

SQL> select count(*) from fa as of timestamp (systimestamp - interval '1'minute);

 

  COUNT(*)

----------

       100

 

使用Flashback Archive查询10分钟之前的数据:

SQL> select count(*) from fa as of timestamp (systimestamp - interval '10'minute);

 

  COUNT(*)

----------

     0

 

这里显示为0. 因为我们还没有做DML 操作。

 

或者使用时间来查:

SQL> select count(*) from fa as of timestamp to_timestamp('2011-05-11 15:35:23','yyyy-mm-dd hh24:mi:ss');

  COUNT(*)

----------

 100

 

SQL> delete from fa;

100 rows deleted

SQL> commit;

Commit complete.

 

 

SQL>  select count(*) from fa as of timestamp (systimestamp - interval '1'minute);

 

  COUNT(*)

----------

       100

 

根据时间的不同,查询的结果也不一样。 下面我们来确认下这个问题:

 

SQL> SELECT * from dba_flashback_archive_tables;

 

TABLE_NAME OWNER_NAME FLASHBACK_ARCHI ARCHIVE_TABLE_NAME   STATUS

---------- ---------- --------------- -------------------- --------

FA         SYS        FLA1            SYS_FBA_HIST_78484   ENABLED

 

从这个结果,可以看出,在Flashback archive对应的FA表的历史表是SYS_FBA_HIST_78484

 

该表保存了FA表的所有的操作记录:

SQL> select count(*) from SYS_FBA_HIST_78484;

  COUNT(*)

----------

    149

 

SQL> desc SYS_FBA_HIST_78484

 Name                                      Null?    Type

 ----------------------------------------- -------- ---------------

 RID                                                VARCHAR2(4000)

 STARTSCN                                           NUMBER

 ENDSCN                                             NUMBER

 XID                                                RAW(8)

 OPERATION                                          VARCHAR2(1)

 ID                                                 NUMBER

 

注意一点:我们不能对这些历史表做任何修改操作,只能查询。

 

            如果想对这些历史表进行相关的修改操作,和之前的操作一样:使用dbms_flashback_archive分离2个表之间的关系。

 

如:

sql> exec dbms_flashback_archive.disassociate_fba('scott','emp_test');

sql> exec dbms_flashback_archive.reassociate_fba('scott','emp_test');

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15880878/viewspace-723015/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-24

  • 博文量
    118
  • 访问量
    186190