ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 闪回数据归档(十二)

闪回数据归档(十二)

原创 Linux操作系统 作者:dzq0371 时间:2013-04-09 11:35:44 0 删除 编辑
    我们都知道闪回查询是依赖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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-07-14

  • 博文量
    52
  • 访问量
    182575