闪回数据归档（十二）

最新推荐文章于 2022-07-17 19:43:49 发布

cujingqian3552

最新推荐文章于 2022-07-17 19:43:49 发布

阅读量83

点赞数

文章标签：数据库运维

我们都知道闪回查询是依赖undo_retention参数的，也就是说，闪回查询最长的时间等于undo_retention的值，如果想查询大于undo_retention的时间的数据，是不可能的，但是闪回归档数据解决了这个问题。

一、语法：

1：创建归档数据

CREATE FLASHBACK ARCHIVE [DEFAULT] flashback_archive
  TABLESPACE tablespace tablespace_name
  [QUOTA integer {K| M| G| T| P| E}]
  RETENTION integer {YEAR | MONTH | DAY};
注：default与非default的flashback archive在使用上是有区别的，如下：
非default falshback archive：create table test(x int) flashback archive flashback_archive;
default flashback archive:create table test(x int) flashback archive;

2：删除归档数据

DROP FLASHBACK ARCHIVE flashback_archive;

3：修改归档数据

ALTER FLASHBACK ARCHIVE flashback_archive

  { SET DEFAULT
  | { ADD | MODIFY } TABLESPACE tablespace [flashback_archive_quota]
  | REMOVE TABLESPACE tablespace_name>
  | MODIFY RETENTION flashback_archive_retention
  | PURGE { ALL
          | BEFORE { SCN expr | TIMESTAMP expr}
          }
  };
 QUOTA integer {K| M| G| T| P| E};
 RETENTION integer {YEAR | MONTH | DAY};

二、测试：

SYS@ORA11GR2>select * from v$version where rownum=1;

BANNER
————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
SYS@ORA11GR2>

1:创建测试表空间及用户，分配较为基本的角色

SYS@ORA11GR2>create tablespace ts_users datafile ‘/u01/app/oracle/oradata/ORA11GR2/ts_users.dbf’ size 50m;
Tablespace created.
SYS@ORA11GR2>create user xxf identified by xxf default tablespace ts_users;
User created.
SYS@ORA11GR2>grant connect,resource to xxf;
Grant succeeded.
SYS@ORA11GR2>

2：创建flashback archive（注：创建、删除需要有“flashback archive administer”系统权限）

SYS@ORA11GR2>create flashback archive fbad_ts_users tablespace ts_users retention 1 day;
Flashback archive created.
SYS@ORA11GR2>

3：创建表t1，启用闪回归档数据（注：这个动作需要有“flashback archive”权限），

SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>create table t1(x int) flashback archive fbad_ts_users;
create table t1(x int) flashback archive fbad_ts_users
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive

XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>grant flashback archive on fbad_ts_users to xxf;
Grant succeeded.
SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>create table t1(x int) flashback archive fbad_ts_users;
Table created.
XXF@ORA11GR2>

4：已存在的表启用闪回归档数据

XXF@ORA11GR2>create table t2(x int);
Table created.
XXF@ORA11GR2>alter table t2 flashback archive fbad_ts_users;
Table altered.
XXF@ORA11GR2>

5：查看已启用闪回归档数据的表（其中：ARCHIVE_TABLE_NAME为记录闪回数据的表，我们无法查看它）

XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
———- ———- ———————- —————— ——-
T1 XXF FBAD_TS_USERS SYS_FBA_HIST_75350 ENABLED
T2 XXF FBAD_TS_USERS SYS_FBA_HIST_75351 ENABLED
SYS@ORA11GR2>

6：使用“no flashback archive”子句可以disable flashback archive （注：需要有“flashback archive administer”系统权限）

SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>alter table t1 no flashback archive;
alter table t1 no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive

XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>alter table xxf.t1 no flashback archive;
Table altered.
SYS@ORA11GR2>select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
———- ———- ———————- —————— ——–
T2 XXF FBAD_TS_USERS SYS_FBA_HIST_75351 ENABLED
SYS@ORA11GR2>

7：查看超出undo保存期限的数据（测试环境的undo_retention为15分钟）

SYS@ORA11GR2>show parameter undo_retention;
NAME TYPE VALUE
———————————— ———– ———————–
undo_retention integer 900
SYS@ORA11GR2>

8：t2表删除5条记录，超出15分钟以后，进行闪回查询

SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>set time on
21:19:02XXF@ORA11GR2>select count(*) from t2;
COUNT(*)
———-
10
21:19:48 XXF@ORA11GR2>delete t2 where x>=6;
5 rows deleted.
21:20:03 XXF@ORA11GR2>commit;
Commit complete.
21:20:06 XXF@ORA11GR2>select count(*) from t2;
COUNT(*)
———-
5
21:20:14 XXF@ORA11GR2>

9：下面的闪回查询已经超出了15分钟，我们发现，通过闪回查询，已经查到删除前的数据，此时利用的就是闪回数据归档中的数据

21:54:52XXF@ORA11GR2>select count(*) from t2 as of timestamp(sysdate – 37/1440);
COUNT(*)
———-
10
21:55:49 XXF@ORA11GR2>select * from t2 as of timestamp(sysdate – 37/1440);
X
———-
6
7
8
9
10
1
2
3
4
5
10 rows selected.
21:56:21 XXF@ORA11GR2>
22:03:30 XXF@ORA11GR2>select * from xxf.t2 as of timestamp(to_date(’2012-11-19 21:19:00′,’yyyy-mm-dd hh24:mi:ss’));
X
———-
6
7
8
9
10
1
2
3
4
5
10 rows selected.
22:03:36 XXF@ORA11GR2>

10：清除归档FBAD_TS_USERS中5分钟之前的数据（闪回归档中的数据时可以删除的），再次利用刚才的闪回查询的时间，此时我们发现，数据已经不见了，注意，执行时间和要闪回的时间，间隔超出了

undo_retention

22:06:04 SYS@ORA11GR2>alter flashback archive fbad_ts_users purge before timestamp (sysdate-5/1440);
Flashback archive altered.
22:06:06 SYS@ORA11GR2>select * from xxf.t2 as of timestamp(to_date(’2012-11-19 21:19:00′,’yyyy-mm-dd hh24:mi:ss’));
X
———-
1
2
3
4
5
22:06:11 SYS@ORA11GR2>

10：再次删除t2中的部分数据

22:08:48 SYS@ORA11GR2>delete xxf.t2 where x>=3;
3 rows deleted.
22:08:58 SYS@ORA11GR2>commit;
Commit complete.
22:09:00 SYS@ORA11GR2>select * from xxf.t2;
X
———-
1
2

22:09:07 SYS@ORA11GR2>
22:09:25 SYS@ORA11GR2>select * from xxf.t2 as of timestamp(sysdate – 5/1440);
X
———-
1
2
3
4
5

22:09:33 SYS@ORA11GR2>

11：清除归档FBAD_TS_USERS中所有数据

22:09:35 SYS@ORA11GR2>alter flashback archive fbad_ts_users purge all;
Flashback archive altered.
22:09:45 SYS@ORA11GR2>

12：闪回查询5分钟之前的数据，虽然我们清空了闪回归档，但是，我们依旧能查看到5分钟之前的状态。

为什么呢？

因为，此时的闪回查询查询的是undo中的数据（删除时间为22:08:48，查询时间为22:09:46）

22:09:46 SYS@ORA11GR2>select * from xxf.t2 as of timestamp(sysdate – 5/1440);
X
———-
1
2
3
4
5
22:09:50 SYS@ORA11GR2>

13：清除某个SCN之前的历史数据

alter flashback archive fbad_ts_users purge before scn xxxxxxx;

三、启用了闪回数据归档功能的表则禁用了部分ddl命令，如：

1：启用了闪回归档功能的表不允许drop了

SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>drop table t2;
drop table t2
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
XXF@ORA11GR2>drop table t2 purge;
drop table t2 purge
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
XXF@ORA11GR2>

2：启用了闪回归档功能的表允许truncate

XXF@ORA11GR2>truncate table t2;
Table truncated.
XXF@ORA11GR2>
XXF@ORA11GR2>select * from xxf.t2 as of timestamp(sysdate – 5/1440);
X
———-
1
2
XXF@ORA11GR2>

3：允许添加、删除列

XXF@ORA11GR2>alter table t2 add y int;
Table altered.
XXF@ORA11GR2>desc t2;
Name Null? Type
—————————————– ——– —————
X NUMBER(38)
Y NUMBER(38)
XXF@ORA11GR2>alter table t2 drop column x;
Table altered.
XXF@ORA11GR2>

3：不允许删除用户

XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>drop user xxf cascade;
drop user xxf 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 “XXF”.”SYS_FBA_TCRV_75351″

SYS@ORA11GR2>

4：将表归档数据禁用后则可以正常删除

SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>
XXF@ORA11GR2>alter table t2 no flashback archive;
alter table t2 no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive

XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>grant flashback archive administer to xxf;
Grant succeeded.
SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>alter table t2 no flashback archive;
Table altered.
XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>drop user xxf cascade;
User dropped.
SYS@ORA11GR2>

四、删除闪回归档

SYS@ORA11GR2>drop flashback archive fbad_ts_users ;
Flashback archive dropped.
SYS@ORA11GR2>

五、小结

这个功能还是非常实用的，在空间允许且有这方面需求的情况下，可以考虑使用，虽然多多少少给运维带来一点点麻烦，但是哪怕用到了一次，那点儿麻烦也是值得的。

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

转载于:http://blog.itpub.net/23009281/viewspace-758028/

cujingqian3552

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
闪回数据归档（十二）

我们都知道闪回查询是依赖undo_retention参数的，也就是说，闪回查询最长的时间等于undo_retention的值，如果想查询大于undo_retention的时间的数据，是不可能的，但是闪回归档数据解决了这个...
复制链接

扫一扫