oracle 11g Flashback Data Archive(闪回数据归档)

ゞ浴缸里的玫瑰 2022-06-09 02:20 211阅读 0赞

UNDO表空间记录的回滚信息虽然可以提供回闪查询,但时间久了,这些信息会被覆盖掉,其实只要事务一提交,他们就变成可覆盖的对象了,所以经常在做回闪查询时,我们会因为找不到undo block而收到1555错误,[11G][]里面引入了Flashback Data Archive ,他用于存储数据的所有改变,时间由你自己设定,消耗的是更多的磁盘空间,现在来看下这个特性。

一、创建闪回数据归档  
1、为了创建闪回数据归档，必须拥有DBA角色或拥有系统权限flashback archive administer  
SQL> select \* from dba\_sys\_privs where privilege like '%FLASH%';

GRANTEE          PRIVILEGE    ADM  
\------------------------------ ---------------------------------------- ---  
SYS          FLASHBACK ANY TABLE   NO  
DBA          FLASHBACK ANY TABLE   YES  
SYS          FLASHBACK ARCHIVE ADMINISTER  NO  
DBA          FLASHBACK ARCHIVE ADMINISTER  YES  
MDSYS          FLASHBACK ANY TABLE   NO  
SQL> grant flashback archive administer to mytest;

Grant succeeded.  
 2、创建表空间  
Flashback archive只能在ASSM的tablespace上创建.  
SQL> create tablespace flash\_tbs1 datafile '/opt/oracle/oradata/orcl/flash\_tbs1.dbf' size 50M autoextend on next 32M maxsize unlimited;

Tablespace created.

3、创建闪回归档  
SQL> create flashback archive flash1 tablespace flash\_tbs1  quota 1024M  retention 1 year;

Flashback archive created.

注意：这里只给分配了1024M的空间，如果想让空间不受限的话，执行下面的语句即可：  
create flashback archive flash1 tablespace flash\_tbs1 retention 1 year;  
二、更改闪回数据归档  
SQL> alter flashback archive flash1 set default;

Flashback archive altered.  
SQL> alter flashback archive flash1 add tablespace example; --添加表空间

Flashback archive altered.

SQL> alter flashback archive flash1 remove tablespace example; --删除表空间

Flashback archive altered

SQL> alter flashback archive flash1 modify tablespace flash\_tbs1 quota 2048M;  -添加配额

Flashback archive altered.

SQL> alter flashback archive flash1 modify retention 3 year; --修改保留年限

Flashback archive altered.

SQL> alter flashback archive flash1 purge all;  --清除所有

Flashback archive altered.

SQL> alter flashback archive flash1 purge before timestamp(systimestamp-interval '2' day); -清除2天前的

Flashback archive altered

SQL> alter flashback archive flash1 purge before scn 123344; --指定scn清除

Flashback archive altered.

三、启用和禁用闪回数据归档  
 1、在建表的同时就启用表的闪回日志  
SQL> create table t1(id int,name varchar2(10)) flashback archive flash1;

Table created.

2、也可以在建表后，再启用表的闪回日志

SQL> alter table t2 flashback archive;  --为表启用闪回数据归档,没指定表示使用数据库默认的

Table altered.

SQL> alter table t3 flashback archive flash1; --为表启用闪回数据归档,指定在特定的闪回数据归档中存储表的变化

Table altered.

3、数据库将把T1表的数据归档到默认的闪回数据归档中  
SQL> select \* from dba\_flashback\_archive\_tables;

TABLE\_NAME      STATUS OWNER\_NAME FLASHBACK\_ARCHIVE\_NAME  ARCHIVE\_TABLE\_NAME   
\---------------------------------------------------------------------------------------------------  
T1  ENABLED        MYTEST FLASH1     SYS\_FBA\_HIST\_75754         
T2  ENABLED        MYTEST LASH1     SYS\_FBA\_HIST\_75755

4、在使用闪回数据归档前，必须设置默认闪回数据归档  
SQL> select flashback\_archive\_name,status from dba\_flashback\_archive;

FLASHBACK\_ARCHIVE\_NAME     STATUS  
\---------------------------------------  
FLASH1      DEFAULT

5、禁用闪回数据归档

SQL> alter table t2 no flashback archive;

Table altered.

四、闪回数据归档的限制  
在使用闪回归档的过程中有某些限制。对于已经启用闪回的表，不能使用DDL命令drop column（11r2可以drop column）,但可以add column命令。删除属于一个启用了闪回数据归档的表列的唯一方法是首先关闭闪回归档功能。但是，这样会删除所有闪回归档数据。  
1、ALTER TABLE：  
Drops, renames, or modifies a column (11GR2是可以的)  
Performs partition or subpartition operations  
Converts a LONG column to a LOB column  
Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause  
2、 DROP TABLE  
3、TRUNCATE TABLE (11GR2是可以的)  
4、RENAME TABLE (11GR2也是可以的)

orcl:/home/oracle@oralce>sqlplus mytest/test

SQL\*Plus: Release 11.2.0.1.0 Production on Fri Aug 25 17:33:11 2017

Connected to:  
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter table t1 drop column name;

Table altered.

SQL> alter table t1 add(name varchar2(10));

Table altered.

SQL> alter table t1 rename to t10;

Table altered.

SQL> truncate table t10;

Table truncated.

SQL> drop table t10;  
drop table t10  
           \*  
ERROR at line 1:  
ORA-55610: Invalid DDL statement on history-tracked table

SQL> alter table t10 no flashback archive;

Table altered.

SQL> drop table t10;

Table dropped.

五、监控认回数据归档  
1、查哪些表已经启用了闪回数据归档  
SQL> select \* from dba\_flashback\_archive\_tables;

2、查数据库中所有的闪回数据归档

SQL> select flashback\_archive\_name,tablespace\_name,quota\_in\_mb from dba\_flashback\_archive\_ts;

FLASHBACK\_ARCHIVE\_NAME  TABLESPACE\_NAME  QUOTA\_IN\_MB                          
\----------------------------------------------------------------------------------------------  
FLASH1    FLASH\_TBS1     2048                       
六、使用闪回数据归档：例子  
SQL> create table mytest(id int,name varchar2(10));

Table created.

SQL> alter table mytest flashback archive flash1;

Table altered.

SQL> begin  
for i in 1..100 loop  
 insert into mytest values(i,'shiyu'||i);  
 commit;  
end loop;  
end;  
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> select count(\*) from mytest;

COUNT(\*)  
\----------  
       100

SQL> col FLASHBACK\_ARCHIVE\_NAME for a10  
SQL> col TABLE\_NAME for a10  
SQL> col ARCHIVE\_TABLE\_NAME for a20  
SQL> col OWNER\_NAME for a5  
SQL> select \* from dba\_flashback\_archive\_tables;

TABLE\_NAME OWNER FLASHBACK\_ ARCHIVE\_TABLE\_NAME  STATUS  
\---------- ----- ---------- -------------------- --------  
T1    MYTES FLASH1     SYS\_FBA\_HIST\_75767  ENABLED  
    T

MYTEST    MYTES FLASH1     SYS\_FBA\_HIST\_75768  ENABLED

T  
SQL> select count(\*) from SYS\_FBA\_HIST\_75768;

COUNT(\*)  
\----------  
  0

SQL> select current\_scn from v$database;

CURRENT\_SCN  
\-----------  
    1779772

SQL> delete from mytest;

100 rows deleted.

SQL> commit;

Commit complete.

SQL> select current\_scn from v$database;

CURRENT\_SCN  
\-----------  
    1779810

SQL> select count(\*) from mytest as of scn 1779772;