测一把ORACLE 11G Flashback Data Archive

by

Flasback Data Archive 提供了UNDO 的归档类似REDO的ARCHIVELOG. 指定保留周期，实现指定时间内的闪回。

With flashback features, you can:
 Perform queries that return past data
 Perform queries that return metadata that shows a detailed history of changes to the database
 Recover tables or rows to a previous point in time
 Automatically track and archive transactional data changes
 Roll back a transaction and its dependent transactions while the database remains online

闪回数据存档提供了跟踪和存储表上交易更改的功能一生。 闪回数据存档对于遵守记录阶段策略和审核报告很有用。Oracle数据库中的闪回数据存档功能 Oracle Database 11g）提供了一种机制，该机制可以安全地跟踪生产数据库的更改，高效，易于使用且应用程序透明。
By default, Flashback Data Archive is not enabled for any tables. You can enable Flashback Data Archive
for a table if all of these conditions are true:
 You have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive to use for that table.
 The table is not a nested table, temporary table, external table, materialized view, Advanced Query (AQ) table, or non-table object.
 The table contains neither LONG nor nested columns
C:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on 星期四 6月 9 14:06:56 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant flashback archive administer to anbob;

授权成功。

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
D:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORACLE\ORADATA\ORCL\USERS01.DBF
D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF

SQL> create tablespace flasharch datafile 'd:\oracle\oradata\orcl\flasharch.dbf' size 10m;

表空间已创建。

SQL> conn anbob/anbob
已连接。
SQL> create flashback archive arch_area tablespace flasharch quota 9m retention 1 year;

闪回档案已创建。

SQL> create table testfbk(id int) flashback archive arch_area;

表已创建。

SQL> alter table testfbk add moddt date;

表已更改。

SQL> insert into testfbk values(1,sysdate);

已创建 1 行。

SQL> insert into testfbk values(2,sysdate);

已创建 1 行。

SQL> insert into testfbk values(3,sysdate);

已创建 1 行。

SQL> commit;

提交完成。

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
2  ;

会话已更改。

SQL> select * from testfbk;

ID MODDT
---------- -------------------
1 2011-06-09 14:30:52
2 2011-06-09 14:30:58
3 2011-06-09 14:31:03

SQL> set time on
14:35:28 SQL> update testfbk set id=1000+id;

已更新3行。

14:35:45 SQL> commit;

提交完成。

14:35:47 SQL> select * from testfbk
14:52:36   2  ;

ID MODDT
---------- -------------------
1001 2011-06-09 14:30:52
1002 2011-06-09 14:30:58
1003 2011-06-09 14:31:03

14:52:37 SQL> delete testfbk where id=1003
14:52:58   2  ;

已删除 1 行。

14:53:01 SQL> commit;

提交完成。

14:55:28 SQL> select table_name from user_tables where tablespace_name='FLASHARCH';

TABLE_NAME
------------------------------
SYS_FBA_TCRV_71032
SYS_FBA_DDL_COLMAP_71032

14:55:38 SQL>

14:55:28 SQL> select table_name from user_tables where tablespace_name='FLASHARCH';

TABLE_NAME
------------------------------
SYS_FBA_TCRV_71032
SYS_FBA_DDL_COLMAP_71032

14:55:38 SQL> desc SYS_FBA_TCRV_71032
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
RID                                                VARCHAR2(4000)
STARTSCN                                           NUMBER
ENDSCN                                             NUMBER
XID                                                RAW(8)
OP                                                 VARCHAR2(1)

15:06:31 SQL> desc SYS_FBA_DDL_COLMAP_71032
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
STARTSCN                                           NUMBER
ENDSCN                                             NUMBER
XID                                                RAW(8)
OPERATION                                          VARCHAR2(1)
COLUMN_NAME                                        VARCHAR2(255)
TYPE                                               VARCHAR2(255)
HISTORICAL_COLUMN_NAME                             VARCHAR2(255)

SQL> select * from testfbk as of timestamp to_timestamp('2011-6-9 14:52:37','yyyy-mm-dd hh24:mi:ss');

ID MODDT
---------- -------------------
1003 2011-06-09 14:31:03
1001 2011-06-09 14:30:52
1002 2011-06-09 14:30:58

SQL> select * from testfbk;

ID MODDT
---------- -------------------
1001 2011-06-09 14:30:52
1002 2011-06-09 14:30:58

SQL> select * from testfbk as of timestamp to_timestamp('2011-6-9 14:35:28','yyyy-mm-dd hh24:mi:ss');

ID MODDT
---------- -------------------
1 2011-06-09 14:30:52
2 2011-06-09 14:30:58
3 2011-06-09 14:31:03
SQL> alter table testfbk add remark varchar2(20);

表已更改。

SQL> select * from testfbk as of timestamp to_timestamp('2011-6-9 14:35:28','yyyy-mm-dd hh24:mi:ss');

ID MODDT               REMARK
---------- ------------------- --------------------
1 2011-06-09 14:30:52
2 2011-06-09 14:30:58
3 2011-06-09 14:31:03

SQL> set time on
15:47:52 SQL> alter table testfbk drop column id;
alter table testfbk drop column id
*
第 1 行出现错误:
ORA-55610: 针对历史记录跟踪表的 DDL 语句无效

15:48:57 SQL> alter table testfbk rename column id to id2;
alter table testfbk rename column id to id2
*
第 1 行出现错误:
ORA-55610: 针对历史记录跟踪表的 DDL 语句无效

禁用FDA

# Disable Flashback Data Archeive for tablespace
select * from DBA_FLASHBACK_ARCHIVE_TS;
alter flashback archive fla1 remove tablespace fda4;

# Disable Flashback Data Archeive for fda
select flashback_archive_name name, status  from dba_flashback_archive;
DROP FLASHBACK ARCHIVE fla2;

# Disable Flashback Data Archive for the table employee:
alter table dave employee flashback archive;

# puge all FDA ‘fla1’
alter flashback archive fla1 purge all;

oracle 9i的闪回查询到10G的 flashback version query、flashback transaction query、flashback database、flashback table和flashback drop等特性，大大简化了Flashback Query的使用

以前的闪回查询是基于undo 日志 ，undo又是循环使用的，如果undo log被覆盖就无能为力，undo_retention等参数来延长undo的存活期，但是如果时间过常undo 表空间就迅速增长。Oracle 11g则为又引入Flashback Data Archive。该技术与以上所说的诸多闪回技术的实现机制不同，通过将变化数据另外存储到创建的闪回归档区（Flashback Archive）中，以和undo区别开来，又可以针对单个对象开启跟踪节约空间，这样就可以查询一个表几年前的数据快照了

如果归档满了会报ORA-55617: Flashback Archive “ xxx″ runs out of space and tracking on,当前的dml 停止.

oracle 19c FDA new feature https://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/4421812.pdf

2 thoughts on “测一把ORACLE 11G Flashback Data Archive”

Comments are closed.