Flashback Data Archive 闪回归档测试

DML闪回特性，使用UNDO表空间，由于UNDO表空间的信息总会丢失（undo_retention不可能设置无限大），所以Oracle database 11g开始，Oracle提供了闪回数据归档(Flashback Data Archive)功能。通过此通过，可以使数据库对UNDO数据进行归档，从而提供长时间历史数据查询功能 。Oracle database 11g新增的后台进程FBDA(Flashback Data Archiver Process)用于对闪回数据进行归档写出操作。闪回归档需要独立存储空间，在使用该特性之前启动ASSM。


一、创建闪回空间
SQL> create tablespace fbra datafile '/u01/app/oracle/oradata/orcl/fbra01.dbf' size 100m autoextend on segment space management auto;


Tablespace created.


二、创建闪回归档


SQL> create flashback archive dataarchive tablespace fbra retention 1 month;


Flashback archive created.


三、为测试闪回归档，减小UNDO表空间，关闭自动扩展属性
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' resize 100m;
  
Database altered.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' autoextend off;

Database altered.

四、连接到测试用户，并在相关表上启用FLASHBACK ARCHIVE对象权限（ create table jerry_copy as select * from dba_objects）

SQL> conn jerry/jerry;
Connected.

SQL> alter table jerry_copy flashback archive dataarchive;

Table altered.

五、获得系统当前SCN：

SQL> select dbms_flashback.get_system_change_number from dual;


GET_SYSTEM_CHANGE_NUMBER
------------------------
                 9104236


六、查询前期创建的表


SQL> select count(*) from jerry_copy;


  COUNT(*)
----------
     72828


SQL> delete from jerry_copy where rownum < 10000;


9999 rows deleted.


SQL> commit;


Commit complete.


七、执行删除语句，使undo表空间数据写入归档。


SQL> begin 
  2     for i in 1..10000 loop
  3             delete from jerry_copy where rownum < 1;
  4             commit;
  5     end loop;
  6  end;
  7  /


PL/SQL procedure successfully completed.


八、启用跟踪


SQL> set autotrace on;


九、查看执行计划，查询表


SQL> select count(*) from jerry_copy as of scn 9104236;


  COUNT(*)
----------
     72828




Execution Plan
----------------------------------------------------------
Plan hash value: 1326699257


----------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    |     1 |       |  1172   (1)| 00:00:15 |       |       |
|   1 |  SORT AGGREGATE           |                    |     1 |       |            |          |       |       |
|   2 |   VIEW                    |                    | 14223 |       |  1172   (1)| 00:00:15 |       |       |
|   3 |    UNION-ALL              |                    |       |       |            |          |       |       |
|   4 |     PARTITION RANGE SINGLE|                    | 10582 |   268K|    25   (0)| 00:00:01 |     1 |     1 |
|*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_76492 | 10582 |   268K|    25   (0)| 00:00:01 |     1 |     1 |
|*  6 |     FILTER                |                    |       |       |            |          |       |       |
|*  7 |      HASH JOIN OUTER      |                    |  3641 |  7253K|  1147   (1)| 00:00:14 |       |       |
|*  8 |       TABLE ACCESS FULL   | JERRY_COPY         |  3641 | 43692 |  1128   (1)| 00:00:14 |       |       |
|*  9 |       TABLE ACCESS FULL   | SYS_FBA_TCRV_76492 | 10139 |    19M|    19   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   5 - filter("ENDSCN">9104236 AND "ENDSCN"<=9108963 AND ("STARTSCN" IS NULL OR "STARTSCN"<=9104236))
   6 - filter("STARTSCN"<=9104236 OR "STARTSCN" IS NULL)
   7 - access("T".ROWID=CHARTOROWID("RID"(+)))
   8 - filter("T"."VERSIONS_STARTSCN" IS NULL)
   9 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>9108963) AND ("STARTSCN"(+) IS NULL OR
              "STARTSCN"(+)<9108963))


Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
        327  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> col TABLE_NAME for a40;
SQL> col OWNER_NAME for a50;
SQL> col FLASHBACK_ARCHIVE_NAME for a40;
SQL> col ARCHIVE_TABLE_NAME for a30;
SQL> set autotrace off;

十、查看数据字典查询关于闪回归档表的记录

SQL> select * from user_flashback_archive_tables;

TABLE_NAME                               OWNER_NAME                                         FLASHBACK_ARCHIVE_NAME                   ARCHIVE_TABLE_NAME             STATUS
---------------------------------------- -------------------------------------------------- ---------------------------------------- ------------------------------ ----------------
JERRY_COPY                               JERRY                                              DATAARCHIVE                              SYS_FBA_HIST_76492             ENABLED


十一、查看闪回功能生成的字典对象


SQL>  select u.table_name, u.tablespace_name  from user_tables u  where u.table_name like '%FBA%';


TABLE_NAME                               TABLESPACE_NAME
---------------------------------------- ------------------------------------------------------------
SYS_FBA_DDL_COLMAP_76492                 FBRA
SYS_FBA_TCRV_76492                       FBRA
SYS_FBA_HIST_76492


十二、查询和闪回归档相关的数据字典表
SQL> col COMMENTS for a80;
SQL> select * from dict where table_name like '%FLASHBACK_ARCHIVE%';


TABLE_NAME                               COMMENTS
---------------------------------------- --------------------------------------------------------------------------------
DBA_FLASHBACK_ARCHIVE                    Description of the flashback archives available in the system
DBA_FLASHBACK_ARCHIVE_TABLES             Information about the tables that are enabled for Flashback Archive
DBA_FLASHBACK_ARCHIVE_TS                 Description of tablespaces in the flashback archives available in the system
USER_FLASHBACK_ARCHIVE                   Description of the flashback archives available to the user
USER_FLASHBACK_ARCHIVE_TABLES            Information about the user tables that are enabled for Flashback Archive


十二、测试完成，取消在表上的闪回归档
SQL> alter table jerry_copy no flashback archive;


Table altered.


十三、恢复UNDO表空间自动扩展属性


SQL> conn / as sysdba;


SQL>  alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' autoextend on;


Database altered.