http://space.itpub.net/?uid-17203031-action-viewspace-itemid-683706
1、行级闪回
我们在开发和运维过程中,经常遇到数据被误删除的情况。无论是在应用开发中的Bug,还是修改数据的时候,如果提交了错误数据修改结果,会带来很多问题。一般来说,一旦提交Commit事务,我们是不能获取到之前的数据情况,除非使用较复杂的数据恢复手段,利用备份数据恢复。
但是在Oracle中,可以使用其闪回FlashBack特性来解决这个问题。首先,声明一点,闪回Flashback的范围很大,包括数据库、表、数据均是可以Flashback的,但是机制差别很大。本文说的是简单的数据flashback,用来快速的挽救回我们的数据。
首先构建实验环境,和版本信息。
SQL>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database11gEnterpriseEdition Release11.2.0.1.0 - Production
PL/SQL Release11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version11.2.0.1.0 - Production
NLSRTL Version11.2.0.1.0 – Production
构建一张简单的数据表,定位时间信息。
SQL>create table t as select owner,object_id,object_name from dba_objects where rownum<3;
Table created
SQL> select sysdate from dual;
SYSDATE
-----------
2011-1-12 8
SQL> select * from t;
OWNER OBJECT_ID OBJECT_NAME
----------- ---------- ------------------
SYS 20 ICOL$
SYS 46 I_USER1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2011-01-12 08:23:59 //注意这个时间点。
如果我们此时误删除了数据,并且将删除结果提交。
SQL> delete t;
2 rows deleted
SQL> commit;
Commit complete
SQL> select count(*) from t;
COUNT(*)
----------
0
这时,虽然我们已经commit了删除事务,但是仍可以指定一个时间点,获取到那个时间点的数据。
SQL>select * from t as of timestamp to_timestamp('2011-01-12 08:23:59','yyyy-mm-dd hh24:mi:ss');
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- --------------------------------------------------------------------------------
SYS 20 ICOL$
SYS 46 I_USER1
删除的数据集合又可以查询到。这样恢复数据的思路有了,可以将结果集合直接插入回数据表。
SQL>insert into t select * from t as of timestamp to_timestamp('2011-01-12 08:23:59','yyyy-mm-dd hh24:mi:ss');
2 rows inserted
SQL> commit;
Commit complete
SQL> select * from t;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- --------------------------------------------------------------------------------
SYS 20 ICOL$
SYS 46 I_USER1
数据恢复了,利用的是闪回特性。
结论:Oracle在进行处理的时候,对过去提交过的数据,是保存过一个镜像的,并且与一个SCN相对应。所谓SCN(System Commit Number),就相当于Oracle系统中的时钟,每次进程会话commit一次,相当于推进一次scn值。相对于时钟,SCN对Oracle数据库至关重要。
对数据,Oracle是可以保存多个版本的。每个版本是和对应的SCN相关联。我们利用闪回,可以一定程度的查找回过去一个时间SCN的数据版本。而SCN是一个绝对整数,如:
// Oracle 9i以上版本中,获取到当前系统SCN的方法;
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1169694
闪回标准的做法应为:
SQL> select * from t as of scn 1169694;
但是这样做,存在不方便的问题。我们就需要不断的试算合适的SCN取值,相对而言,时间日期较容易理解。使用 as of timestamp 更加容易。
所谓“No free lunch”,使用闪回时是受到一些限制的。受到系统参数的限制。闪回是一个系统配置,需要系统参数的支持。
//Undo相关的参数
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
在自动管理Undo的情况下,undo_retention表示支持闪回的秒数,默认为15分钟。但是,要注意,这只是一个近似值,实际上要根据系统繁忙程度和其他很多因素来决定闪回的时间。实际上,如果超过了闪回15分钟,可以获得结果。
如果闪回的时间过长,保存的SCN版本已经消失,系统会报错。
SQL> select * from emp as of timestamp to_timestamp('2008-01-1208:23:59','yyyy-mm-dd hh24:mi:ss');
select * from emp as of timestamp to_timestamp('2008-01-12 08:23:59','yyyy-mm-dd hh24:mi:ss')
ORA-08180:未找到基于指定时间的快照
提到闪回flashback,要注意Oracle的闪回技术是由几个相对独立的技术实现的。
ü 数据库级别闪回:flashback database。将整个数据库快速恢复到一个时间点;
ü 数据表级别闪回:将数据表全部恢复到过去的一个时间点上,或者对已经删除掉的数据表恢复;
ü 行级别闪回:设置指定的时间点,可以查询到该时间点的特定数据行;
ü 事务闪回:可以将按照事务的单位,将数据库变更闪回;
2、表级闪回
我们已经在本系列的第一篇中,介绍过行级别的闪回,可以将制定时间的数据行导出。在本篇中,我们介绍一下数据表级别的闪回。
在实际开发和维护中,我们有时候会遇到把数据表drop掉的情况。过去这种情况,我们只能通过之前保留的备份,进行不完全的备份。这样的工作量很大也很麻烦。从Oracle10g起,引入了回收站的机制,将drop掉的数据表保存在回收站中。当发现误删除的时候,可以通过回收站回收数据表。
回收站机制类似于我们在Windows上的回收站。在windows中,当我们选择删除一个文件时,本质上并没有将文件从硬盘上删除,只是将文件以一种形式改名,这样就能从回收站中看到。
Oracle的回收站也是采用同样的原理。下面我们做一个简单的实验。
首先,确定系统参数。在Oracle10g中,有一个参数recyclebin,控制数据表回收站机制的启动和关闭。
//用sys帐号登录,确定recyclebin参数
SQL>conn sys/sys@orcl as sysdba;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------
recyclebin string on //当取值为on的时候,表示开启回收站功能;
之后,我们以scott用户登录进去,演示删除一张数据表。——不知为何,我用sys用户演示的时候show recyclebin 不出结果。
SQL>conn scott/tiger@orcl;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
//建立数据表
SQL> create table t as select * from all_objects;
Table created
SQL>select count(*) from t;
COUNT(*)
----------
40712
之后删除数据表。
SQL>drop table t;
Table dropped
SQL>select * from t;
select * from t
ORA-00942: 表或视图不存在
在sqlplus(注意:只能在sqlplus/sqlplusw中查看到)中,使用show recyclebin命令,可以看到当前回收站的情况信息。
SQL>show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------- ------------------------------ ------------ -------------------
T BIN$jJvR2eVETmKH1JE6yBQ2og==$0 TABLE 2011-01-25:22:30:52
记录的内容显而易见,有一个对象原名为T,在回收站中的名称为“BIN$…”。原来的对象类型为数据表,是在XXX时间被删除。
如果这个时候确定说删除是一个错误,需要恢复,简单的使用flashback命令,就可以了。
//使用闪回命令,将数据表t闪回到删除之前的状态去
SQL>flashback table t to before drop;
闪回完成。
SQL>select count(*) from t;
COUNT(*)
----------
40712 ——数据恢复
一个简单的命令,就可以挽回我们误操作的结果了。
那么,如果我们不需要这个对象了,需要完全的删除。我们需要怎么做呢?而且,在windows的回收站里,文件在清空回收站之前是还会占用硬盘空间的。Oracle的回收站里面既然保存数据,那么必然要消耗数据库空间。
我们可以使用purge命令,对回收站的信息进行清除。
SQL>drop table t;
表已删除。
SQL>purge table t; //清除表
表已清除。
SQL>show recyclebin;
SQL>flashback table t to before drop;//尝试闪回,失败
flashback table t to before drop
*
第1 行出现错误:
ORA-38305: 对象不在回收站中
同时,purge命令还提供了不同的操作粒度,如下:
ü purge table <t>;对指定的数据表进行清除purge;
ü purge tablespace <tbsname>; 对指定的表空间回收站进行purge;
ü purge tablespace <tbsname> user <username>;对指定表空间回收站中指定用户schema对象进行purge操作;
那么,如果我们不想通过回收站进行删除操作,希望在drop数据表的时候完全删除,怎么做呢?
SQL>select count(*) from t;
COUNT(*)
----------
40712
SQL>drop table t purge; //使用purge关键字,相当是说“不希望转入回收站” ——不能进行rollback,drop隐含提交。
表已删除。
SQL>show recyclebin; ——没有数据了。
谈到drop一个数据表,就不能不说truncate table操作。truncate操作具有操作快的特点,那么truncate命令是否支持闪回呢?
关于truncate与drop的区别详见:http://blog.csdn.net/changyanmanman/article/details/7605118
SQL>select count(*) from t;
COUNT(*)
----------
40712
SQL>truncate table t; //截断数据表
表被截断。
SQL>show recyclebin;
SQL> flashback table t to before drop; //没有在回收站中,也就必然不能支持闪回了;
flashback table t to before drop
*
第1 行出现错误:
ORA-38305: 对象不在回收站中
SYS用户对象不能闪回
SYS用户是Oracle系统中的超级用户,肩负着执行数据库启动、关闭、备份等管理职责。在对象控制上,SYS用户可以访问所有对象和数据。所以,一般都建议不要直接使用SYS进行实际日常DBA工作。
在闪回这个问题上,SYS用户是受到限制的。下面我们使用SYS用户实验闪回特性。
SQL> conn / as sysdba;
已连接。
SQL> show user;
USER 为 "SYS" //确定是以sys用户登录的
SQL> create table t as select * from dba_objects;
表已创建。
SQL> select count(*) from t;
COUNT(*)
----------
50331
SQL> show parameter recyclebin; //当前开启的是闪回模式
NAME TYPE VALUE
------------------------- ----------- -----------
recyclebin string on
SQL> drop table t;
表已删除。
SQL> show recyclebin;//没有闪回记录
SQL> select * from user_recyclebin;
未选定行
看来,当我们使用sys用户进行默认表空间(sys用户的默认表空间为system)数据表drop的时候,是不会被闪回的。Oracle内部也不支持这种操作。
那么,这种特点是针对SYS的呢?还是针对system表空间的呢?我们继续下面的实验,建立一张数据表在users表空间
-- Create table
create table t
(
id number(10) not null
)
tablespace USERS
storage
(
initial 64K
minextents 1
maxextents unlimited
);
//准备数据
SQL> insert into t select object_id from dba_objects;
50331 rows inserted
SQL> commit;
Commit complete
//确认数据表所在的表空间
SQL> select table_name,tablespace_name from all_tables where wner='SYS' and table_name='T';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T USERS
//删除数据表
SQL> drop table t;
表已删除。
//显示回收站
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$kLlt43leRJGNWtQMB/yTaQ==$0 TABLE 2011-01-28:00:14:18
显然,闪回机制对system表空间数据表是不进行闪回的。
结论:使用sys用户的闪回要注意,当建立数据表是在system表空间下的时候,是不支持闪回特性的。
闪回表回收站——两个视图
使用方面,闪回特性还要关注两个回收站视图。all_recyclebin、dba_recyclebin。
all/user/dba三层结构,我们熟悉Oracle的朋友一定不会陌生。Oracle中大部分对象都提供了以这三个作为前缀的命名视图。三层的视图表示的都是一种类型对象,都是对元数据表的映射。最低一个层次是user_视图,表示当前用户所属的schema下的对象。其次是all_视图是当前用户所能访问、具有访问权限的对象信息。对象的owner可能不是当前用户,但是因为具有访问权限,也是可能被访问到。最高的是dba_视图,通常只有DBA用户才能访问到该层面视图,常用来作为全局对象。
recyclebin系列视图也是类似的作用。视图中可以查询到回收站中的对象信息。
SQL> desc dba_recyclebin;
Name Type Nullable Default Comments
-------------- ------------ -------- ------- --------------------------------------------------------
OWNER VARCHAR2(30) Name of the original owner of the object
OBJECT_NAME VARCHAR2(30) New name of the object
ORIGINAL_NAME VARCHAR2(32) Y Original name of the object OPERATION VARCHAR2(9) Y Operation carried out on the object
TYPE VARCHAR2(25) Y Type of the object
TS_NAME VARCHAR2(30) Y Tablespace Name to which object belongs
CREATETIME VARCHAR2(19) Y Timestamp for the creating of the object
DROPTIME VARCHAR2(19) Y Timestamp for the dropping of the object
DROPSCN NUMBER Y SCN of the transaction which moved object to Recycle Bin
PARTITION_NAME VARCHAR2(32) Y Partition Name which was dropped
CAN_UNDROP VARCHAR2(3) Y User can undrop this object
CAN_PURGE VARCHAR2(3) Y User can purge this object
RELATED NUMBER Parent objects Obj#
BASE_OBJECT NUMBER Base objects Obj#
PURGE_OBJECT NUMBER Obj# for object which gets purged
SPACE NUMBER Y Number of blocks used by this object
注意其中几个字段:包括原对象名、现在对象名称、删除时间等。这些可以帮助我们了解到对象的回收机制。注意其中的canpurge和canundrop两个标志,表示当前这个回收站对象时候可以进行操作。
同时,我们注意到回收站视图中是没有all_视图的。这其实也比较好理解:回收站是属于对象所有者的回收站。建立回收站的目的是为了将删除的对象可以闪回,将对象闪回的权限,还是控制在对象原有所有者或者DBA用户手中比较好。
在回收站空间管理上,也要关注purge命令的使用。我们是可以直接对recyclebin使用purge命令的。相对于上面的命令格式,这种方式其实更加简单直接。
ü purge user_recyclebin;
ü purge dba_recyclebin;
SQL> select * from dba_recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------ ----------------
BIN$kLlt43leRJGNWtQMB/yTaQ==$0 T
(结果数据段有省略…)
SQL> purge user_recyclebin;
Done
SQL> purge dba_recyclebin;
Done
SQL> select object_name, original_name from dba_recyclebin;
OBJECT_NAME ORIGINAL_NAME
--------------- --------------------------------
//对象被清理
结论:使用dba_recyclebin和user_recyclebin可以比较容易的看到整个数据库和当前用户对象的回收站。
闪回机制探讨
下面我们一起来详细研究下,drop闪回的原理。首先,我们构建一个适当的实验环境。
SQL> connscott/tiger@orcl;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL> col object_name format a25;
SQL> select object_name,object_id,object_type from user_objects;
OBJECT_NAME OBJECT_ID OBJECT_TYPE
------------------ ---------- -------------------
PK_DEPT 51150 INDEX
……(篇幅原因,有删节)
PCK_MYTEST_WORK 52607 PACKAGE BODY
T 53218 TABLE
SYS_C005520 53186 INDEX
IND_T_TEST_NAME 53187 INDEX
T_TEST 53185 TABLE
14 rows selected
实验环境下,我们存在对象表T,对象编号为53218。对应的段信息为:
SQL> select segment_name, segment_type, tablespace_name, bytes from user_segments where segment_name='T';
SEGMENT_NA SEGMENT_TYPE TABLESPACE_NAME BYTES
---------- ------------------ -------------------- ----------
T TABLE USERS 65536
下面,我们尝试删除数据表T。
SQL> drop table t;
Table dropped
SQL> select * from t;
select * from t
ORA-00942: 表或视图不存在
我们重新查找对象字段视图。
SQL> select object_name,object_id,object_type from user_objects;
OBJECT_NAME OBJECT_ID OBJECT_TYPE
------------------------- ---------- -------------------
PK_DEPT 51150 INDEX
DEPT 51149 TABLE
EMP 51151 TABLE
PK_EMP 51152 INDEX
BONUS 51153 TABLE
SALGRADE 51154 TABLE
P_ACCA_LOG_USER_COUNT 52786 PROCEDURE
PCK_MYTEST_WORK 52606 PACKAGE
MY_SEQ 52578 SEQUENCE
PCK_MYTEST_WORK 52607 PACKAGE BODY
SYS_C005520 53186 INDEX
IND_T_TEST_NAME 53187 INDEX
T_TEST 53185 TABLE
BIN$/hWq4qC8ScioKlJiEL1jA 53218 TABLE
w==$0
14 rows selected
该结果没有删节。首先,发现对象T的信息已经不存在了。其次,一个以BIN$开头的数据表对象出现在视图中,而且使用了object_id为53218,与之前T所占用的object_id相同。
接着让我们查看段信息。
SQL> col segment_name format a30;
SQL> select segment_name, segment_type, tablespace_name, bytes from user_segments;
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES
------------------------------ --------------------------------
IND_T_TEST_NAME INDEX USERS 65536
SYS_C005520 INDEX USERS 65536
T_TEST TABLE USERS 65536
BIN$/hWq4qC8ScioKlJiEL1jAw==$0 TABLE USERS 65536
SALGRADE TABLE USERS 65536
BONUS TABLE USERS 65536
EMP TABLE USERS 65536
PK_DEPT INDEX USERS 65536
DEPT TABLE USERS 65536
PK_EMP INDEX USERS 65536
10 rows selected
数据段segment代表了存储。在数据段中,我们已经找不到数据表T的信息了。而新添加的BIN$却还是占据了一个位置。
最后,我们检查数据表视图。
SQL> select table_name, tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT USERS
EMP USERS
BONUS USERS
SALGRADE USERS
T_TEST USERS
数据表T信息被删除了,同时那个BIN$对象并没有被识别为数据表。
那么,回收站视图的情况呢?
SQL> select object_name, original_name, operation, type ,ts_name from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPETS_NAME
------------------------- --------------------------------
BIN$/hWq4qC8ScioKlJiEL1jA T DROP TABLE USERS
w==$0
user_recyclebin帮助我们揭示了BIN$对象的本来面目。这个对象是记录原数据库表T的信息。
到此处,我们的结论也就不难获得了。所谓的闪回drop,就是一种对象假删除技术。当系统参数recyclebin被设置为on的时候,Oracle是开启闪回drop功能的。当对数据表使用drop的时候,Oracle并不是将对象直接删除,而是采用了对象改名。将删除的数据表进行改名(逻辑上),改为BIN$开头的一个编码。这个编码占据了原有数据表的所有资源(包括对象信息和存储信息),但是不能算成为原对象的等价体。也就是说,如果我们直接操作这个修改名,系统会报错,因为Oracle不认为这个对象是一个数据表。数据表T的数据字典信息被删除,而改名的对象信息没有加入其中。
SQL> select * from BIN$/hWq4qC8ScioKlJiEL1jAw==$0;
select * from BIN$/hWq4qC8ScioKlJiEL1jAw==$0
ORA-00933: SQL 命令未正确结束
当我们进行flashback table XXX to before drop的时候,Oracle只是将原来的数据表T信息重新改回,原有空间和数据并没有改变。
那么,这部分被假删除的数据是不是要占用原有对象表空间呢?答案是肯定的。就如同Windows回收站在没有清空的情况下,是同样消耗资源一样。Oracle的回收站也有同样的特性。
当我们删除了一个对象,这个对象信息隐藏在数据表空间中,对象是占用空间的。但是,这部分空间的消耗并不计入到表空间容量之中。当表空间分配不足的时候,这部分闪回所用的硬盘空间是可以自动的被回收。作为一般开发人员和管理人员,这部分的操作是Oracle自动完成,相当于透明的操作。
那么,有没有可能因为闪回drop表空间对象引起一些故障问题呢?我们说是可能的。笔者曾经读到过一篇博文,文章中阐述了这样的场景(这里对文章作者表示敬意):数据库操作程序中,向一个数据表中插入大量的数据,之后报错:说用户没有drop对象权限。为什么进行的insert操作,而提示说没有drop权限呢?程序用户也的确是没有drop权限。
最后发现是表空间中,非recyclebin可用空间使用耗尽,需要回收recyclebin中的空间。在这个过程中,Oracle不是使用直接覆盖的方法,而是自动生成了drop语句命令(一种递归调用),删除那些BIN$对象。在这个过程中,使用到了用户的drop权限。
解决的方法也很简单:管理员直接purge空间既可以。
闪回与安全
看了闪回功能,对我们的drop命令也有了一层新的认识。那么,可能有些应用是需要绝对安全删除(如PCI中的一项要求!),不希望一些被drop掉的信息还保留在数据库中,存在不安全因素。
对这种情况,可以使用两种方法。一个是直接关闭recyclebin参数,设置为off。这样就关闭了闪回drop的特性了。也就不会出现安全问题。这种方法笔者认为很实用,因为在生产环境下,drop数据表的情况还是比较少的,特别是应用层面。
第二个方法就是加参数的drop语句。使用drop table XXX purge,就不会将数据表保存在回收站中了。
SQL> select count(*) from t;
COUNT(*)
----------
0
SQL> drop table t purge;
Table dropped
SQL> select count(*) from user_recyclebin;
COUNT(*)
----------
0 //对象没有经过回收站,直接被删除;
同时,下面两个命令比较有用。
ü drop tablespace XXX including contents;删除表空间的时候,不使用回收站,并连带清除回收站;
ü drop user XXX cascade;删除用户和对应用户对象的时候,不使用回收站并连带清除回收站;
3、闪回归档(针对11g)
在Oracle11g中,Oracle推出了flashback archive(闪回归档)的新特性。Flashback Archive实现了有针对性数据表的可控时间闪回功能。我们可以设置一块存储空间区域,保留一个特定数据表在不同时间的数据镜像。而且可以设置保留时间,没有达到保留时间的时候,数据都会进行保留。
下面我们通过一系列的实验来说明这些特性。
1、环境准备
我们选择Oracle11g进行测试,同时建立一个表空间mytest。表空间mytest选择ASSM方式进行segment space management。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database11gEnterpriseEdition Release11.2.0.1.0 - Production
PL/SQL Release11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version11.2.0.1.0 - Production
NLSRTL Version11.2.0.1.0 - Production
SQL> create tablespace mytest datafile size100m extent management local uniform. size1m
segment space management auto;
Tablespace created
2、创建Flashback Archive空间
Flashback的其他特性,如flashback drop、flashback database,都是数据库的默认选择项目,基本不需要用户进行指定和配置。如果不希望有这些配置项目,则可以通过参数配置开关进行关闭。
Flashback Archive是一种比较特殊的类型,需要我们进行一系列的配置工作。首先,我们在sys用户下创建一个flashback archive存储对象。
SQL> show user;
User is "SYS"
SQL> create flashback archive flar1 tablespace mytest retention 1 year;
Done
创建flar1对象作为使用的flashback archive,存储使用的表空间为mytest,设置的保留年限为1年。
在创建flar1之后,我们可以在dba_flashback_archive、dba_flashback_archive_ts(dba_前缀可以使用user等进行替换)查询到闪回信息。
SQL> select * from dba_flashback_archive;
OWNER_NAME FLASHBACK_ARCHI FLASHBACK_ RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS
---------- --------------- ---------- ----------------- -------------------- -------------------- -------
SYS FLAR1 1 365 05-9月-1111.07.43. 05-9月-11 11.07.43.
000000000上午 000000000上午
SQL> select * from dba_flashback_archive_ts;
FLASHBACK_ARCHI FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
--------------- ---------- --------------- ----------------------------------------
FLAR1 1 MYTEST
3、设置具有闪回归档功能的数据表
我们转移到scott用户下,进行闪回归档演示。
SQL> conn scott/tiger@ora11g;
Connected to Oracle Database11gEnterpriseEdition Release11.2.0.1.0
Connected as scott
SQL> desc t;
Object t does not exist.
SQL> create table t as select * from dba_objects;
Table created
SQL> select count(*) from t;
COUNT(*)
----------
72226
之后,我们尝试使用alter table xx flashback archive yyy的方法,将数据表与设置的闪回归档区关联上。
SQL> alter table t flashback archive flar1;
alter table t flashback archive flar1
ORA-55620:无权使用闪回归档
在scott普通用户下,直接调用该命令是被拒绝的。使用设置闪回归档,需要拥有flashback archive系统权限。
--在sys用户下
SQL> grant flashback archive on flar1 to scott;
Grant succeeded
--在scott用户下
SQL> alter table t flashback archive flar1;
Table altered
此时,可以观察到dba_flashback_archive_tables视图的结果,建立了数据表T与flar1的关系。
SQL> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
-------------------------- ----------------------------------------------- --------
T SCOTT FLAR1 SYS_FBA_HIST_88294 ENABLED
4、闪回归档数据表
下面,我们分别在不同的时间点,对数据表进行DML操作,演示flashback archive特性。
--大规模DML操作前
SQL> select sum(bytes)/1024/1024 "USED MBs" from dba_free_space where tablespace_name='MYTEST';
USED MBs
----------
92
SQL> select count(*) from t;
COUNT(*)
----------
113346
SQL> select sysdate from dual;
SYSDATE
--------------------
2011/9/6 9:06:57
--进行大规模DML操作;
SQL> insert into t select * from dba_objects;
72233 rows inserted
SQL> commit;
Commit complete
SQL> select count(*) from t;
COUNT(*)
----------
185579
SQL> select count(*) from tas of timestamp to_timestamp('2011/9/6 9:06:57','yyyy/mm/dd hh24:mi:ss');
COUNT(*)
----------
113346
SQL> select sum(bytes)/1024/1024 "USED MBs" from dba_free_space where tablespace_name='MYTEST';
USED MBs
----------
75
上面的实验,可以清晰的看出Flashback Archive的特点。从query语法上看,flashback archive与flashback query很相像,都是利用指定过去的一个时间点(timestamp,scn)进行查询。但是,flashback query是利用undo的负效应,将没有被覆写的数据返回给用户。这种方式首先是对所有的数据表均有效果,另一个是时间有限,受到undo大小和数据库事务频繁度的影响。
而flashback archive则是将数据归档做到了数据库的层面上。设置一块专门的区域空间,称之为flashback archive。这个区域是真实对应在表空间上可以进行存储使用的。之后设置这块区域的保留时间。
在数据表层面,可以选择需要进行保留的数据表,让其与flashback archive区域关联,这样在存储区域中,就会保留各个时间点的镜像数据。
本篇中研究了如何设置和使用flashback archive,下面我们一起分析研究下设置flashback archive的数据表特征和其他特点。
Flashback archive是Oracle11g中推出的新历史数据查询新特性。对比过去的flashback query,flashback archive具有更强的时间准确和对象针对性。
5、Flashback Archive组件和FBDA进程
Flashback Archive特性是Oracle11g中新推出的。每个新特性的推出,大都伴随着Oracle体系结构的不断丰富和调整。
Oracle Total Recall组件是Flashback Archive功能的组件名称。作为一个独立组件在Oracle安装的时候是会进行默认安装的。从Oracle官方资料中看,Flashback archive的作用是跟踪一个或者多个数据表的历史history数据变化,将其保存在一个或者多个表空间中。
借助Oracle11g的Total Recall组件,Oracle会对数据表数据的变化进行自动的跟踪记录,记录在设置的flashback archive里。这个过程中,Oracle会进行相应的优化工作,将归档数据进行压缩、分区。这样做的效果可以在最小影响应用程序DML操作,并且对应用程序透明的情况下将数据进行归档保存。
为了实现Flashback Archive的功能,Oracle新引入了一个实例进程为FBDA(Flashback Archived Process)。该进程启动时随着数据库同时启动。FBDA的作用如下:
ü FBDA首先从buffer cache中的undo表空间数据中查找过去数据表时间点数据。这点是与flashback query的特性相似;
ü 如果要查找的数据在undo tablespace中,但是该块没有在buffer cache中。FBDA会从undo segment中获取到数据块,复制在buffer cache中;
ü 当进行flashback archive操作的数据表发生修改的时候,FBDA会去将需要保存的数据存放在对应的适当内部数据表中;
[oracle@oracle11g~]$ ps -ef | grep fbda
oracle 5773 1 1 13:02 ? 00:00:00 ora_fbda_wilson
oracle 5775 5560 0 13:03 pts/0 00:00:00 grep fbda
注意,默认情况下fbda进程是不运行的。如果当前存在使用flashback archive功能的数据表,就会自动启动fbda进程。
归档数据在flashback archive中是压缩进行保存的,称为history table历史表。在内部,历史表时被压缩并且分区保存的。当保存的归档数据超过了设置的retention时间间隔,旧数据会被自动的删除purge。
6、Flashback Archive中的隐含表
Oracle11g中的Flashback Archive本质是将数据表的变化信息加以保存,其中使用压缩和分区表技术。当我们将一个数据表设置为flashback archive之后,会发现出现三个内部internal数据表。
SQL> desc SYS_FBA_TCRV_88294;
Name Type Nullable Default Comments
-------- -------------- -------- ------- --------
RID VARCHAR2(4000) Y
STARTSCN NUMBER Y
ENDSCN NUMBER Y
XID RAW(8) Y
OP VARCHAR2(1) Y
SQL> desc SYS_FBA_HIST_88294
Name Type Nullable Default Comments
-------------- -------------- -------- ------- --------
RID VARCHAR2(4000) Y
STARTSCN NUMBER Y
ENDSCN NUMBER Y
XID RAW(8) Y
OPERATION VARCHAR2(1) Y
OWNER VARCHAR2(30) Y
OBJECT_NAME VARCHAR2(128) Y
(篇幅原因,有省略……)
EDITION_NAME VARCHAR2(30) Y
SQL> desc SYS_FBA_DDL_COLMAP_88294;
Name Type Nullable Default Comments
---------------------- ------------- -------- ------- --------
STARTSCN NUMBER Y
ENDSCN NUMBER Y
XID RAW(8) Y
OPERATION VARCHAR2(1) Y
COLUMN_NAME VARCHAR2(255) Y
TYPE VARCHAR2(255) Y
HISTORICAL_COLUMN_NAME VARCHAR2(255) Y
ü SYS_FBA_TCRV_XXX数据表负责记录在特定的时间范围(对应SCN范围),进行特定操作的信息记录;
ü SYS_FBA_HIST_XXX数据表的列包括数据行信息和对应存在的SCN范围。如果一个数据表发生DML或者DDL操作,引起数据的变化会直接保存在该数据表中;
ü SYS_FBA_DDL_COLMAP_XXX数据表负责记录数据表列DDL变化情况。从数据列的信息来看,就是一个特定的数据列的时间SCN范围;
7、Flashback Archive下的数据表查询执行计划
当使用flashback archive的时候,虽然书写查询query的格式同过去的flashback query基本相同。但是实际上,对应的执行计划完全不同。
SQL> explain plan for select count(*) from t as of timestamp to_timestamp('2011/9/6 9:06:57','yyyy/mm/dd hh24:mi:ss');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2132445860
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1541
| 1 | SORT AGGREGATE | | 1 | |
| 2 | VIEW | | 10394 | | 1541
| 3 | UNION-ALL | | | |
|* 4 | FILTER | | | |
| 5 | PARTITION RANGE SINGLE| | 339 | 8814 | 173
|* 6 | TABLE ACCESS FULL |SYS_FBA_HIST_88294| 339 | 8814 | 173
|* 7 | FILTER | | | |
|* 8 | HASH JOIN OUTER | | 10055 | 19M| 1368
|* 9 | TABLE ACCESS FULL |T | 4309 | 51708 | 1057
|* 10 | TABLE ACCESS FULL |SYS_FBA_TCRV_88294| 201K| 388M| 310
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
4 - filter("TIMESTAMP_TO_SCN"(TIMESTAMP' 2011-09-06 09:06:57.000000000')<1340
6 - filter("ENDSCN"<=13407208 AND "ENDSCN">"TIMESTAMP_TO_SCN"(TIMESTAMP' 2011
09:06:57.000000000') AND ("STARTSCN" IS NULL OR "STARTSCN"<="TIMES
09:06:57.000000000')))
7 - filter("STARTSCN"<="TIMESTAMP_TO_SCN"(TIMESTAMP' 2011-09-06 09:06:57.0000
NULL)
8 - access("T".ROWID=CHARTOROWID("RID"(+)))
9 - filter("T"."VERSIONS_STARTSCN" IS NULL)
10 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>13407208) AND ("STARTSCN"(+) I
"STARTSCN"(+)<13407208))
Note
-----
- dynamic sampling used for this statement (level=2)
35 rows selected
通过执行计划我们可以清晰的看出flashback archive的特征:
ü 对具有flashback archive特性的数据表进行历史数据query的时候,输入到优化器中的执行计划就已经发生变化。隐含的对数据表T以及内部表的检索查询。将时间范围转化为对应的SCN条件,获取对应时间片段的数据镜像;
ü 从执行计划看,三个数据表结合的大规模操作访问效率不会很高。对内部压缩数据表的访问,可能意味着在进行历史归档的查询时效率不会很高。从实际使用看,flashback archive查询的性能与flashback query的相差很多;
8、flashback archive容量实验
在官方的推荐意见中,通常是建议设置一个较大或者可以拓展的flashback archive存储区。对于一些数据操作频繁的数据表,即使压缩的历史数据量也是客观的。
在创建flashback archive的时候,我们使用了retention参数,表示历史数据保留的时间范围。这个retention时间段是具有强制意义的,对Oracle而言反映了绝对的要求。如果历史数据归档的历史期超过了指定时间范围,这部分历史数据会自动的由Oracle进行保存。
但是,如果我们设置的flashback archive空间过小,而数据表变动产生了闪回归档数据量超过了设置空间,这种情况下Oracle如何进行决断呢?
SQL> show user
User is "SYS"
SQL> create flashback archive flar2 tablespace mytest quota10mretention 1 year;
Done
我们创建了一个容量为10m的flashback archive空间,要求保留一年的时间。
SQL> grant flashback archive on flar2 to scott;
Grant succeeded
SQL> alter table t flashback archive flar2;
Table altered
SQL> select sysdate from dual;
SYSDATE
--------------------
2011/9/6 15:25:21
SQL> select count(*) from t;
COUNT(*)
----------
72226
(进行频繁的DML操作)
--在一个DDL操作之后,数据操作hange住
SQL> truncate table t;
如果使用数据表T设置的闪回归档空间不足,而旧数据又没有达到retention归档的范围,此时Oracle会拒绝所有会产生flashback archive的操作。
当拒绝操作的时候,当前会话会被hange住。如果是一个DDL操作,即使将客户端会话切断,也不能终止操作,仍然可以看到后台会话和Server Process。实验中,笔者是使用OS级别的kill -9命令加以终止。
结论:在使用flashback archive的时候,要尽可能设置合理的retention期间和空间大小。不要由于空间问题引起会话操作hange死。
下篇中,我们着重讨论与flashback archive相关的权限问题和DML/DDL操作对闪回归档的影响。
Flashback Archive是Oracle11g推出的具有针对性和强制性的数据透明归档技术。本篇我们集中介绍与该特性相关的权限和DDL操作问题。
9、Flashback Archive相关权限
Oracle一个新特性的推出,经常伴随着一系列新的权限集合的确立。Flashback Archive组件相关的有两个系统权限,分别为flashback archive和flashback archive administrator。
从上面的scott用户的情况中,我们可以看到flashback archive系统权限的作用。就是通过对数据表和已经建立的flashback archive建立关系,开启闪回归档功能。
SQL> conn scott/tiger@ora11g;
Connected to Oracle Database11gEnterpriseEdition Release11.2.0.1.0
Connected as scott
SQL> alter table t flashback archive flar1;
alter table t flashback archive flar1
ORA-55620:无权使用闪回归档
--切换到sys用户上
SQL> grant flashback archive on flar1 to scott;
Grant succeeded
SQL> alter table t flashback archive flar1;
Table altered
但是,只拥有flashback archive权限的用户,对视图dba_flashback_xxx系列获取到的结果是不准确的。
SQL> select owner_name, flashback_archive_name from dba_flashback_archive;
OWNER_NAME FLASHBACK_ARCHIVE_NAME
------------------------------ -------------------------
SQL> select table_name, owner_name from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME
-------------------- ------------------------------ --------------------------------
对只拥有flashback archive系统权限的用户而言,只能去查找user_xxx相关视图。
SQL> select owner_name, flashback_archive_name from user_flashback_archive;
OWNER_NAME FLASHBACK_ARCHIVE_NAME
------------------------------ ------------------------
SYS FLAR1
另一个与flashback archive相关的系统权限是flashback archive administrator,拥有这个权限的用户可以执行下列操作类型:
ü Create flashback archive xxx tablespace yyy quota xx retention zzz;创建闪回归档区,设置空间限制;
ü Alter flashback archive xxx进行清理purge等操作;
ü Drop flashback archive xxx删除指定的闪回归档空间;
SQL> conn sys/oracle@ora11gas sysdba;
Connected to Oracle Database11gEnterpriseEdition Release11.2.0.1.0
Connected as SYS
SQL>grant flashback archive administer to scott;
Grant succeeded
SQL> conn scott/tiger@ora11g;
Connected to Oracle Database11gEnterpriseEdition Release11.2.0.1.0
Connected as scott
SQL> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME
------------------------------ ------------------------------ -----------------------------------
T SCOTT FLAR1
SQL> create flashback archive flar2 tablespace mytest retention 1 year;
Done
SQL> ALTER FLASHBACK ARCHIVE flar2 PURGE ALL;
Done
SQL> drop flashback archive flar2;
Done
如果取消了这个系统权限,那么相关的系列操作就不能进行。
SQL> revoke flashback archive administer from scott;
Revoke succeeded
SQL> create flashback archive flr2 tablespace mytest retention 1 year;
create flashback archive flr2 tablespace mytest retention 1 year
ORA-55612:无权管理闪回归档
注意,并不是只有显示拥有flashback archive administrator权限的用户才能Create/alter/drop archive flashback操作。如果用户是dba角色,也可以实现这个目标。
SQL> conn sys/oracle@ora11gas sysdba;
Connected to Oracle Database11gEnterpriseEdition Release11.2.0.1.0
Connected as SYS
SQL>grant dba to scott;
Grant succeeded
SQL> conn scott/tiger@ora11g;
Connected to Oracle Database11gEnterpriseEdition Release11.2.0.1.0
Connected as scott
SQL> create flashback archive flr2 tablespace mytest retention 1 year;
Done
10、flashback archive与DDL操作
Flashback Archive是保证对数据表进行DML操作时,数据前后镜像都能保存找回的技术。那么,如果一个数据表加入flashback archive之后,进行DDL操作有什么影响呢?我们分别进行试验。
ü 数据列添加add实验
对已经加入flashback archive的数据表添加一个数据列。
SQL> alter table t add m varchar2(10);
Table altered
直观感觉没有什么区别,就是添加数据列的速度要慢很多。此时,我们检查相关的数据表。
SQL> select * from sys_fba_ddl_colmap_88294;
STARTSCN ENDSCN XID OPERATION COLUMN_NAME TYPE HISTORICAL_COLUMN_NAME
---------- ---------- ----- --------- -------------- -------------------- --------------------
13395717 OWNER VARCHAR2(30) OWNER
(篇幅原因,有省略……)
13395717 EDITION_NAME VARCHAR2(30) EDITION_NAME
13424840 M VARCHAR2(10) M
16 rows selected
数据表基表sys_fba_ddl_colmap_88294记录了数据字段变化起效的时间范围(也就是scn范围)。从刚才添加数据列的情况看,数据表中说明从scn=13424840开始,添加起效了数据列m。
ü 数据列修改
修改一个数据列属性。
SQL> alter table t modify m varchar2(20) ;
Table altered
SQL> select * from sys_fba_ddl_colmap_88294;
STARTSCN ENDSCN COLUMN_NAME TYPE HISTORICAL_COLUMN_NAME
---------- ---------- ----------------- ---------------- ----------------------
13395717 OWNER VARCHAR2(30) OWNER
13395717 EDITION_NAME VARCHAR2(30) EDITION_NAME
13424840 13427266 M_13427266_M VARCHAR2(10) M
13427266 M VARCHAR2(20) M
17 rows selected
对于一个数据列修改,Oracle flashback archive将其视为删除列后重新添加。
ü Truncate table
数据表的truncate操作是一个典型的DDL操作,具有flashback archive特性的数据表是否可以truncate操作呢?
SQL> truncate table t;
Table truncated
SQL> select count(*) from t as of timestamp to_timestamp('2011/9/6 10:45:49','yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
17
ü 删除数据列
对一个数据列进行删除操作,flashback archive数据表同样支持。
SQL> alter table t drop column m;
Table altered
SQL> select count(*) from t as of timestamp to_timestamp('2011/9/6 10:45:49','yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
17
SQL> select count(*) from t;
COUNT(*)
----------
0
此时,在sys_fba_ddl_colmap_88294中会记录上删除记录。
SQL> select STARTSCN,ENDSCN,COLUMN_NAME from sys_fba_ddl_colmap_88294;
STARTSCN ENDSCN COLUMN_NAME
---------- ---------- ---------------- -
13395717 OWNER
13395717 NAMESPACE
(篇幅原因,有省略。。。。。。)
13395717 EDITION_NAME
13424840 13427266 M_13427266_M
13427266 13428271 D_13428271_M
17 rows selected
ü Drop删除数据表
对drop数据表操作,flashback archive数据表是不支持的。
SQL> drop table t;
drop table t
ORA-55610:针对历史记录跟踪表的DDL语句无效
要想删除数据表,则需要首先使用alter table xxx no flashback archive;语句进行历史数据归档跟踪关系的解除。之后才能进行删除。
11、结论
数据归档、offline是很多系统都需要有的特定需求。我们经常遇到这样的需求:将历史数据保留几个月或者几年待查。待查的情景是很少,但是我们如果从应用入手,就需要设计额外的数据表和业务处理流程。其中一个更麻烦的就是保留期过删数据的操作,相对较为复杂。
Flashback archive是Oracle可以使用在生产系统中直接使用的归档功能组件。如果开发设计使用这个组件进行归档,有若干个好处。
首先是透明化的归档操作,用户不需要去关心进行归档的业务逻辑。只要设置好了需要归档的数据表,按照查询归档语法进行查找。就可以实现应用系统中最典型的归档查询需要。
其次是高效存储个性。在flashback archive中,数据是保存在内部分区压缩表中,各方面的属性由Oracle进行控制管理。
最后是保留期管理严格化。设置不同的flashback archive retention period,首先可以对不同的数据表适应使用不同的保留期。当数据超期之后,Oracle自动进行数据删除工作,不需要用户进行干预。