oracle flashback archive 测试步骤分享

flashback archive 测试：

undo管理是AOTU
表空间必须是ASSM

1.创建flashback archive 表空间
create tablespace fba datafile '+DATADG' size 10g;

2.创建flashback archive 区域
create flashback archive default fba1 tablespace fba retention 2 day; //创建时指定数据库默认闪回区域



查询：
select flashback_archive_name name, status from dba_flashback_archive;
select OWNER_NAME,FLASHBACK_ARCHIVE_NAME,FLASHBACK_ARCHIVE#,RETENTION_IN_DAYS,CREATE_TIME,LAST_PURGE_TIME,STATUS from dba_flashback_archive;

3.尝试修改保留时间：
alter flashback archive fba1 modify retention 1 day;

4.清空flashback archive中的全部信息：
alter flashback archive fba1 purge all;
清空1天前的信息：
alter flashback archive fba1 purge before timestamp (systimestamp - interval '1' day);

5.创建测试数据
test1 2936192 384M
test2 2936192 384M

6.把test2 放到flashback archive 里面去
alter table test2 flashback archive fba1;
查询：
SELECT table_name,archive_table_name,status from dba_flashback_archive_tables;

7.都delete 18：07 左右
delete from test1;
delete from test2; 占用了1000m的表空间

8.做些别的操作确保清空undo

9.查询尝试：
select /*+ parallel(16) */ count(*) from test1 as of timestamp to_timestamp('2019-09-18 18:01:00','YYYY-MM-DD HH24:MI:SS');


select /*+ parallel(16) */ count(*) from test2 as of timestamp to_timestamp('2019-09-19 10:10:00','YYYY-MM-DD HH24:MI:SS');

如果查询的时间太靠前,会报错：

SQL> select /*+ parallel(16) */ count(*) from test2 as of timestamp to_timestamp('2019-09-18 18:01:00','YYYY-MM-DD HH24:MI:SS');
select /*+ parallel(16) */ count(*) from test2 as of timestamp to_timestamp('2019-09-18 18:01:00','YYYY-MM-DD HH24:MI:SS')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed



test1已经无法查询：
select count(*) from test1 as of timestamp to_timestamp('2019-09-18 18:01:00','YYYY-MM-DD HH24:MI:SS')
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_1251597811$" too small


test2 可以在开启后查询任意时间的数据：
SQL> select /*+ parallel(16) */ count(*) from test2 as of timestamp to_timestamp('2019-09-18 18:20:00','YYYY-MM-DD HH24:MI:SS');

COUNT(*)
----------
2936192