【备份恢复】 闪回技术之闪回数据归档

UNDO表空间记录的回滚信息虽然可以提供回闪查询,但时间久了,这些信息会被覆盖掉,其实只要事务一提交,他们就变成可覆盖的对象了,
所以经常在做回闪查询时,我们会因为找不到undo block而收到ORA-01555错误,11G里面引入了Flashback Data Archive。

1.创建测试表空间及用户，分配较为基本的角色
SYS@PROD1>create tablespace ts_users datafile '/u01/app/oracle/oradata/PROD1/ts_users.dbf' size 10m;

Tablespace created.

SYS@PROD1>create user ts_fra identified by oracle default tablespace ts_users;

User created.

SYS@PROD1>grant connect,resource to ts_fra;

Grant succeeded.

2.创建flashback archive并设置保留策略
SYS@PROD1>create flashback archive fra1 tablespace ts_users retention 1 day;

Flashback archive created.

3.创建表t1,启用闪回归档数据
SYS@PROD1>conn ts_fra/oracle;
Connected.
TS_FRA@PROD1>create table t1(x int) flashback archive fra1;
create table t1(x int) flashback archive fra1
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive

SYS@PROD1>grant flashback archive on fra1 to ts_fra;

Grant succeeded.

对新建的表启动闪回数据归档
TS_FRA@PROD1>create table t1(x int) flashback archive fra1;

Table created.

对已存在的表启动闪回归档数据
TS_FRA@PROD1>create table t2 (x int);

Table created.

TS_FRA@PROD1>alter table t2 flashback archive fra1;

Table altered.

查看已启用闪回数据归档的表
TS_FRA@PROD1>col table_name for a10;
TS_FRA@PROD1>col owner_name for a10;
TS_FRA@PROD1>col FLASHBACK_ARCHIVE_NAME for a30;
TS_FRA@PROD1>col ARCHIVE_TABLE_NAME for a20;
TS_FRA@PROD1>set linesize 100;

TS_FRA@PROD1>select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME   STATUS
---------- ---------- ------------------------------ -------------------- -------------
T1         TS_FRA     FRA1                           SYS_FBA_HIST_88690   ENABLED
T2         TS_FRA     FRA1                           SYS_FBA_HIST_88691   ENABLED

4.禁用闪回归档数据
TS_FRA@PROD1>alter table t2 no flashback archive;
alter table t2 no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive

SYS@PROD1>alter table ts_fra.t2 no flashback archive;

Table altered.

SYS@PROD1>col table_name for a10;
SYS@PROD1>col owner_name for a10;
SYS@PROD1>col FLASHBACK_ARCHIVE_NAME for a30;
SYS@PROD1>col ARCHIVE_TABLE_NAME for a20;
SYS@PROD1>set linesize 100;

用户ts_fra下的t2表已经禁用了闪回归档
SYS@PROD1>select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME   STATUS
---------- ---------- ------------------------------ -------------------- -------------
T1         TS_FRA     FRA1                           SYS_FBA_HIST_88690   ENABLED

插入数据
TS_FRA@PROD1>insert into t1 values (1);

1 row created.

TS_FRA@PROD1>insert into t1 values (2);

1 row created.

TS_FRA@PROD1>insert into t1 values (3);

1 row created.

TS_FRA@PROD1>commit;

Commit complete.

TS_FRA@PROD1>select * from t1;

        X
----------
        1
        2
        3

用户ts_fra无权限
TS_FRA@PROD1>select dbms_flashback.get_system_change_number as scn from dual;
select dbms_flashback.get_system_change_number as scn from dual
      *
ERROR at line 1:
ORA-00904: : invalid identifier

SYS@PROD1>select dbms_flashback.get_system_change_number as scn from dual;

      SCN
----------
  1203810

SYS@PROD1>delete from ts_fra.t1 where x<=2;

2 rows deleted.

SYS@PROD1>commit;

Commit complete.

SYS@PROD1>select * from ts_fra.t1 as of scn 1203810;

        X
----------
        1
        2
        3

5.删除当前undo表空间（为了验证前面操作的闪回信息是从闪回归档里来的，而不是从undo表空间）
SYS@PROD1>show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SYS@PROD1>create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/PROD1/undotbs02.dbf'      
 2  size 100m autoextend on next 10m;

Tablespace created.

SYS@PROD1>alter system set undo_tablespace=undotbs2;

System altered.

SYS@PROD1>show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2

SYS@PROD1>drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

等了一下会儿，成功删除。。。
SYS@PROD1>drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

6.再次通过闪回查询查看删除的数据
SYS@PROD1>select * from ts_fra.t1 as of scn 1203810;

        X
----------
        1
        2
        3

7.清除归档fra1中5分钟之前的数据（闪回归档中的数据时可以删除的），再次利用刚才的闪回查询的SCN，
此时我们发现，结果与表中实际情况相同。
SYS@PROD1>alter flashback archive fra1 purge before timestamp (sysdate-5/1440);

Flashback archive altered.

SYS@PROD1>select * from ts_fra.t1 as of scn 1203810;

        X
----------
        1
        2
        3

如果：结果与实际情况不相同，则需先清空buffer cache里的内存（alter system flush buffer cache）
同理：alter system flush shared pool

11.删除表中所有数据(执行报错，未解决)

SYS@PROD1>select dbms_flashback.get_system_change_number as scn from dual;

      SCN
----------
  1205995

TS_FRA@PROD1>delete from ts_fra.t1;
delete from ts_fra.t1
                  *
ERROR at line 1:
ORA-55617: Flashback Archive "FRA1" runs out of space and tracking on "T1" is suspended

12.清除闪回归档 FBAD_TS_USERS 中所有数据
SYS@PROD1>alter flashback archive fra1 purge all;

Flashback archive altered.

13.再次执行闪回查询,因为可以通过undo得到指定SCN的数据,测试时,undo数据并未被覆盖(没懂)
SYS@PROD1>select * from ts_fra.t1 as of scn 1203810;

        X
----------
        3

因为闪回归档里数据清空了，所以不能再对表进行闪回查询了，所以查到的数据只是DML操作commit之后保存的数据。

14.收尾
SYS@PROD1>alter table ts_fra.t1 no flashback archive;

Table altered.

SYS@PROD1>drop flashback archive fra1;

Flashback archive dropped.

小结：
- 启用了闪回归档功能的表不允许 drop
- 启用了闪回归档功能的表允许 truncate
- 允许添加、删除列
- 不允许删除表所在用户
- 将表归档数据禁用后则可以正常删除

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

转载于:http://blog.itpub.net/31400681/viewspace-2131291/