从 Oracle 11g 开始,Oracle 提供了一个这样的功能:闪回数据归档(Flashback Data Archive)。通过这一功能 Oracle 数据库可将 UNDO 数据进行归档,从而提供全面的历史数据查询,也因此引入了一个新的概念 Oracle Totoal Recall,也即 Oracle 全面回忆功能。
在 Oracle 11g 中,新增的后台进程 FBDA(Flashback Data Archive Process)用于对闪回数据进行归档写出。
[oracle@jwlnx1 ~]$ ps -ef | grep fbda | grep -v grep
oracle 6713 1 0 Sep01 ? 00:00:01 ora_fbda_orcl11g
闪回数据归档需要独立的存储,所以在使用该特性之前需要创建独立的 ASSM 表空间。
CREATE FLASHBACK ARCHIVE fda TABLESPACE fda1 QUOTA 10G RETENTION 5 YEAR; -- 配额可选
ALTER FLASHBACK ARCHIVE fda SET DEFAULT; -- 设置默认归档
ALTER FLASHBACK ARCHIVE fda ADD TABLESPACE fda2 QUOTA 10G; -- 将归档扩展到另一个表空间中
ALTER FLASHBACK ARCHIVE fda MODIFY RETENTION 7 YEAR; -- 调整保留时间
FLASHBACK ARCHIVE ADMINISTER 系统权限能够创建、修改或删除归档以及控制归档的保留和清除。
GRANT FLASHBACK ARCHIVE ADMINISTER TO hr; -- 授予用户 FLASHBACK ARCHIVE ADMINISTER 权限
GRANT FLASHBACK ARCHIVE ON fda TO hr; -- 授予用户归档权限
ALTER TABLE hr.employees FLASHBACK ARCHIVE fda; -- 启用对表的归档保护
创建闪回数据归档
1. 创建一个供闪回数据归档使用的表空间:
SQL> CREATE TABLESPACE fda DATAFILE 'fda1.dbf' SIZE 10M;
Tablespace created.
2. 在表空间创建一个保留时间为 1 年的闪回数据归档:
SQL> CREATE FLASHBACK ARCHIVE fda TABLESPACE fda RETENTION 1 YEAR;
Flashback archive created.
3. 创建用于此练习的模式,并授予它 DBA 角色:
SQL> GRANT dba TO test IDENTIFIED BY test;
Grant succeeded.
4. 授予用户操作归档的必要权限
SQL> GRANT FLASHBACK ARCHIVE ON fda TO test;
Grant succeeded.
5. 作为 test 进行连接,创建一个表并为此表启用闪回数据归档:
SQL> CONNECT test/test
Connected.
SQL> CREATE TABLE t1 AS SELECT * FROM all_users;
Table created.
SQL> ALTER TABLE t1 FLASHBACK ARCHIVE fda;
Table altered.
6. 运行这些查询来确定归档创建的对象。可能需要等待几分钟,因为对象不是立即创建的。
SQL> SELECT object_name,object_type FROM user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------ ---------------
T1 TABLE
SYS_FBA_DDL_COLMAP_74594 TABLE
SYS_FBA_HIST_74594 TABLE PARTITION
SYS_FBA_HIST_74594 TABLE
SYS_FBA_TCRV_74594 TABLE
SYS_FBA_TCRV_IDX_74594 INDEX
6 rows selected.
SQL> SELECT segment_name,segment_type FROM dba_segments
2 WHERE tablespace_name='FDA';
SEGMENT_NAME SEGMENT_TYPE
------------------------ ---------------
SYS_FBA_DDL_COLMAP_74594 TABLE
SYS_FBA_TCRV_74594 TABLE
SYS_FBA_HIST_74594 TABLE PARTITION
SYS_FBA_TCRV_IDX_74594 INDEX
7. 对保护表执行一些 DML:
SQL> DELETE FROM t1;
37 rows deleted.
SQL> COMMIT;
Commit complete.
8. 使用标准的闪回查询语法对保护的表执行闪回查询
SQL> SELECT COUNT(*) FROM t1;
COUNT(*)
----------
0
SQL> SELECT COUNT(*) FROM t1 AS OF TIMESTAMP(SYSDATE-20/1440);
COUNT(*)
----------
37
9. 尝试对保护的表执行一些 DDL 命令
SQL> DROP TABLE t1;
DROP TABLE t1
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
作为 SYSDBA 连接,并尝试执行如下命令:
SQL> CONNECT / AS SYSDBA;
Connected.
SQL> DROP USER test CASCADE;
DROP USER test CASCADE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on
table "TEST"."SYS_FBA_TCRV_74594"
SQL> DROP TABLESPACE fda INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE fda INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on
table "TEST"."SYS_FBA_TCRV_74594"
10. 删除表的归档保护
SQL> ALTER TABLE test.t1 NO FLASHBACK ARCHIVE;
Table altered.
11. 删除闪回数据归档
SQL> DROP FLASHBACK ARCHIVE fda;
Flashback archive dropped.
12. 重新运行步骤 9 中的所有命令
SQL> DROP USER test CASCADE;
User dropped.
SQL> DROP TABLESPACE fda INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
有三个数据字典视图来记录闪回数据归档配置:
- DBA_FLASHBACK_ARCHIVE 描述配置的归档
- DBA_FLASHBACK_ARCHIVE_TS 显示每个表空间的每个归档分配的配额
- DBA_FLASHBACK_ARCHIVE_TABLES 列出启用了归档的表
- ALTER TABLE
- 删除、重命名或修改列
- 执行分区或子分区操作
- 将 LONG 列转换为 LOB 列
- 包括 UPGRADE TABLE 子句(不管有无 INCLUDING DATA 子句)
- DROP TABLE
- TRUNCATE TABLE