记录一个Oracle生产环境误删数据表数据的经历,曾经将该经历保存在ITPUT的草稿里,结果今天看不到了,特再此重新记录一次,给自己长点教训,特此留念
那是在几个月前,有次去客户生产环境做运维操作,结果太过自信,一个delete语句忘记增加条件,执行并提交后才发现。当时追悔莫以,幸好最近看过不少Oracle的书,隐约记得flashback可以找回数据,赶紧跟公司DBA联系,考虑恢复事宜。非常感谢公司DBA的大力支持,避免了一次重大事故,再此将当时数据恢复的过程做个记录,以备急需时使用。并在网上找到了相关数据恢复的常用操作,以备不时只需。
操作记录
Alter table table1 enable row movement;
flashback table table1 to timestamp to_timestamp('2021-05-12 10:13:00','yyyy-mm-dd hh24:mi:ss')--十分钟前的时间;
在数据恢复后,我又根据业务逻辑检查了数据是否在十分钟只能由操作,并根据业务逻辑修正了这十分钟只能得数据内容。修复完毕,执行了:
Alter table table1 disable row movement;
整个恢复操作结束,有惊无险的找回了改表的数据。再次感谢我公司DBA的及时准确的支持。
Flashback操作说明
从网上特意查找了flashback的技术总结,在此转载。以下为转载内容:
Flashback 技术是以Undo segment中的内容为基础的, 因此受限于UNDO_RETENTON参数。要使用flashback 的特性,必须启用自动撤销管理表空间。
在Oracle 10g中, Flash back家族分为以下成员: Flashback Database, Flashback Drop,Flashback Query(分Flashback Query,Flashback Version Query, Flashback Transaction Query 三种) 和Flashback Table。
一. Flashback Database
Flashback Database 功能非常类似与RMAN的不完全恢复, 它可以把整个数据库回退到过去的某个时点的状态, 这个功能依赖于Flashback log 日志。 比RMAN更快速和高效。 因此Flashback Database 可以看作是不完全恢复的替代技术。 但它也有某些限制:
-
Flashback Database 不能解决Media Failure, 这种错误RMAN恢复仍是唯一选择
-
如果删除了数据文件或者利用Shrink技术缩小数据文件大小,这时不能用Flashback Database技术回退到改变之前的状态,这时候就必须先利用RMAN把删除之前或者缩小之前的文件备份restore 出来, 然后利用Flashback Database 执行剩下的Flashback Datbase。
-
如果控制文件是从备份中恢复出来的,或者是重建的控制文件,也不能使用Flashback Database。
-
使用Flashback Database锁能恢复到的最早的SCN, 取决与Flashback Log中记录的最早SCN。
Flashback Database 架构
Flashback Database 整个架构包括一个进程Recover Writer(RVWR)后台进程,Flashback Database Log日志 和Flash Recovery Area。一旦数据库启用了Flashback Database, 则RVWR进程会启动,该进程会向Flash Recovery Area中写入Flashback Database Log, 这些日志包括的是数据块的 " 前镜像(before image)", 这也是Flashback Database 技术不完全恢复块的原因。
[oracle@dba ~]$ ps -ef|grep rvw
oracle 12620 12589 0 13:21 pts/1 00:00:00 grep rvw
启用Flashback Database
数据库的Flashback Database功能缺省是关闭的,要想启用这个功能,就需要做如下配置。
- 配置Flash Recovery Area
要想使用Flashback Database, 必须使用Flash Recovery Area,因为Flashback Database Log只能保存在这里。 要配置的2个参数如下,一个是大小,一个是位置。如果数据库是RAC,flash recovery area 必须位于共享存储中。数据库必须处于archivelog 模式.
启用Flash Recovery Area:
SQL>ALTER SYSTEM SET DBRECOVERYFILEDESTSIZE=20G SCOPE=BOTH;
SQL>ALTER SYSTEM SET DBRECOVERYFILE_DEST='/DBA/FB' SCOPE=BOTH;
禁用Flash Recovery Area:
SQL>ALTER SYSTEM SET DBRECOVERYFILE_DEST='' ;
对于Flash Recovery Area,Oracle 是这样建议的,flash recovery area 设置的越大,flashback database 的恢复能力就越强,因此建议flash recovery area 能够放的下所有的数据文件,增量备份,以及所有尚未备份的归档文件,当然还有它自己产生的flashback logs。
在数据库运行过程中,oracle 自动向该区域写入文件,当剩余空间不足15%的时候,它就会在alert 中增加警告,提示你空间不足。但此时不会影响数据库的正常运转,直到所有空间统统被用掉之后,oracle 首先尝试删除寻些过期的文件,冗余文件或备份过的文件,如果这些做完了,还是没有空闲空间的话,数据库就被hang 住了。
对于因Flash Recovery Area导致的数据库hang的处理,请参考:
http://blog.csdn.net/tianlesoftware/archive/2009/10/14/4668991.aspx
或者
http://user.qzone.qq.com/251097186/blog/1244650673
- 启用数据库Flashback 功能
1). 数据库启动到mount状态
SQL> startup mount;
2). 检查Flashback 功能, 缺省时功能是关闭的。
SQL> select name, currentscn, flashbackon from v$database;
NAME CURRENTSCN FLASHBACKON
DBA 945715 NO
3). 启动Flashback功能
SQL> alter database flashback on;
数据库已更改。
SQL> select name, currentscn, flashbackon from v$database;
NAME CURRENTSCN FLASHBACKON
DBA 0 YES
4). 设置初始化参数:DBFLASHBACKRETENTION_TARGET:
SQL>alter system set dbflashbackretention_target=1440 scope=both;
该参数用来控制flashback log 数据保留的时间,或者说,你希望flashback database 能够恢复的最早的时间点。默认值是1440,单位是minute,即24 小时,需要注意的是该参数虽然未直接指定flash recovery area大小,但却受其制约,举个例子假如数据库每天有10%左右的数据变动的话,如果该初始化参数值设置为1440,则flash recovery area 的大小至少要是当前数据库实际容量的10%,如果该初始化参数设置为2880,则flash recovery area 的大小就至少是数据库所占容量的20%。
5). 启动数据库
SQL>alter database open;
Flashback Database操作示例
做操作前先备份数据库
RMAN> backup database;
- 检查是否启动了flash recovery area:
SQL> show parameter dbrecoveryfile
NAME TYPE VALUE
dbrecoveryfile_dest tring D:\oracle/flashrecoveryarea
dbrecoveryfiledestsize big integer 1G
- 检查是否启用了归档
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 USEDBRECOVERYFILEDEST
最早的联机日志序列 9
下一个存档日志序列 11
当前日志序列 11
- 检查是否启用了flashback database
SQL> select flashback_on from v$database;
FLASHBACK_ON
YES
- 查询当前的scn
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
947921
- 查询当前的时间
SQL> select to_char(sysdate,'yy-mm-dd hh24:mi:ss') time from dual;
TIME
09-10-14 14:37:05
- 删除表A
SQL> select * from A;
ID NAME
1 tianle
2 dave
SQL> drop table A;
表已删除。
SQL> commit;
- Flashback Database 实际是对数据库的一个不完全恢复操作,因为需要关闭数据库重启到mount状态。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 79692940 bytes
Database Buffers 121634816 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
- 执行恢复:分timestamp 或者SCN两种
SQL> Flashback database to timestamp to_timestamp('09-10-14 14:37:05','yy-mm-dd
hh24:mi:ss');
闪回完成。
或者:
SQL> Flashback database to scn 947921;
闪回完成。
- 打开数据库:
在执行完flashback database 命令之后,oracle 提供了两种方式让你修复数据库:
1). 直接alter database open resetlogs 打开数据库,当然,指定scn 或者timestamp 时间点之后产生的数据统统丢失。
2). 先执行alter database open read only 命令以read-only 模式打开数据库,然后立刻通过逻辑导出的方式将误操作涉及表的数据导出,再执行recover database 命令以重新应用数据库产生的redo,将数据库修复到flashback database 操作前的状态,然后再通过逻辑导入的方式,将之前误操作的表重新导入,这样的话对现有数据的影响最小,不会有数据丢失。
这里演示,就以resetlogs方式打开:
SQL> alter database open resetlogs;
数据库已更改。
验证数据:
SQL> select * from A;
ID NAME
1 tianle
2 dave
和Flashback Database 相关的2个视图:
- V$database
这个视图可以查看是否启用了Flashback database功能
SQL> select flashback_on from v$database;
FLASHBACK_ON
YES
- V$flashbackdatabaselog
Flashback Database 所能回退到的最早时间,取决与保留的Flashback Database Log 的多少, 该视图就可以查看许多有用的信息。
Oldestflashbackscn / Oldestflashbacktime : 这两列用来记录可以恢复到最早的时点
Flashback_size: 记录了当前使用的Flash Recovery Area 空间的大小
Retention_target: 系统定义的策略
Estimatedflashbacksize: 根据策略对需要的空间大小的估计值
SQL> select oldestflashbackscn os, to_char(oldestflashbacktime,'yy-mm-dd hh2
4:mi:ss') ot, retentiontarget rt,flashbacksize fs, estimatedflashbacksize es
from v$flashbackdatabaselog;
OS OT RT FS ES
946088 09-10-14 13:49:59 1440 16384000 350920704
- V$flashbackdatabasestat
这个视图用来对Flashback log 空间情况进行更细粒度的记录和估计。 这个视图以小时为单位记录单位时间内数据库的活动量,FlashbackData 代表Flashback log产生数量,DBDate 代表数据改变数量,Redo_Date代表日志数量,通过这3个数量可以反映出数据的活动特点,更准确的预计Flash Recovery Area的空间需求
SQL> alter session set nlsdateformat='hh24:mi:ss';
会话已更改。
SQL> select *from v$flashbackdatabasestat;
BEGINTI ENDTIME FLASHBACKDATA DBDATA REDODATA ESTIMATEDFLASHBACK_SIZE
14:43:10 15:15:28 6455296 29310976 3898368 0
Flashback DROP
Flashback Drop 是从Oracle 10g 开始出现的, 用于恢复用户误删除的对象(包括表,索引等), 这个技术依赖于Tablespace Recycle Bin(表空间回收站),这个功能和windows的回收站非常类似。
Flashback 不支持sys用户. system表空间下的对象,也不能从回收站里拿到。故使用SYS 或者SYSTEM用户登陆时, show recyclebin 为空。
- Tablespace Recycle Bin
从Oracle 10g 开始, 每个表空间都会有一个叫作回收站的逻辑区域,当用户执行drop命令时, 被删除的表和表的关联对象( 包括索引, 约束,触发器,LOB段,LOB index 段) 不会被物理删除, 这些对象先转移到回收站中,这就给用户提供了一个恢复的可能。
初始化参数recyclebin 用于控制是否启用recyclebin功能,缺省是ON, 可以使用OFF关闭。
SQL> show parameter recycle
NAME TYPE VALUE
bufferpoolrecycle string
dbrecyclecache_size big integer 0
recyclebin string on
禁用该功能:
SQL> alter system set recyclebin=off;
SQL> alter system set recyclebin=on;
SQL> alter session set recyclebin=off;
SQL> alter session set recyclebin=on;
禁用后删除的对象将直接删除,不会写到Recycle中,当然在删除时,指定purge 参数,表也将直接删除,不会写到recyclebin中。 SQL> drop table name purge;
查看recyclebin中的对象列表:
SQL> select * from A;
ID
----------
1
2
3
SQL> drop table A;
表已删除。
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
A BIN$RWXQQcTPRde0ws4h9ewJcg==$0 TABLE 2009-10-15:12:44:33
查看recyclebin中对象:
SQL> select originalname,objectname from recyclebin;
ORIGINALNAME OBJECTNAME
A BIN$RWXQQcTPRde0ws4h9ewJcg==$0
查看recyblebin对象里的内容:
SQL> select * from "BIN$RWXQQcTPRde0ws4h9ewJcg==$0";
ID
----------
1
2
3
表空间的Recycle Bin 区域只是一个逻辑区域,而不是从表空间上物理的划出一块区域固定用于回收站,因此Recycle Bin是和普通对象共用表空间的存储区域,或者说是Recycle Bin的对象要和普通对象抢夺存储空间。当发生空间不够时,Oracle会按照先入先出的顺序覆盖Recycle Bin中的对象。也可以手动的删除Recycle Bin占用的空间。
1). Purge tablespace tablespace_name : 用于清空表空间的Recycle Bin
2). Purge tablespace tablespacename user username: 清空指定表空间的Recycle Bin中指定用户的对象
3). Purge recyclebin: 删除当前用户的Recycle Bin中的对象
4). Purge dba_recyclebin: 删除所有用户的Recycle Bin中的对象,该命令要sysdba权限
5). Drop table table_name purge: 删除对象并且不放在Recycle Bin中,即永久的删除,不能用Flashback恢复。
6). Purge index recyclebinobject_name: 当想释放Recycle bin的空间,又想能恢复表时,可以通过释放该对象的index所占用的空间来缓解空间压力。 因为索引是可以重建的。
- Flashback Drop 实例操作
SQL> select originalname,objectname from recyclebin;
ORIGINALNAME OBJECTNAME
A BIN$RWXQQcTPRde0ws4h9ewJcg==$0
SQL> flashback table a to before drop;
闪回完成。
SQL> select * from a;
ID
----------
1
2
3
当我们删除表A后,在新建表A,这时在恢复的时候就会报错,此时我们在闪回时,对表重命名就可以了:
SQL> drop table a;
表已删除。
SQL> create table a
2 (id number(1));
表已创建。
SQL> flashback table a to before drop ;
flashback table a to before drop
*
第 1 行出现错误:
ORA-38312: 原始名称已被现有对象使用
SQL> flashback table a to before drop rename to B;
闪回完成。
SQL> select * from B;
ID
----------
1
2
3
当我们删除表A,在新建表A,在删除它,这是在Recycle Bin中就会有2个相同的表明,此时恢复我们就要指定object_name才行.
SQL> select * from B;
ID
----------
1
2
3
SQL> drop table B;
表已删除。
SQL> create table B(name varchar(20));
表已创建。
SQL> drop table B;
表已删除。
SQL> select originalname,objectname from recyclebin;
ORIGINALNAME OBJECTNAME
B BIN$vYuv+g9fTi2exYP9X2048Q==$0
B BIN$geQ9+NekSjuRvzG+TqDVWw==$0
SQL> flashback table "BIN$vYuv+g9fTi2exYP9X2048Q==$0" to before drop;
闪回完成。
SQL> select * from B;
ID
----------
1
2
3
一旦完成闪回恢复,Recycle Bin中的对象就消失了. Flashback Drop 需要注意的地方:
1). 只能用于非系统表空间和本地管理的表空间
2). 对象的参考约束不会被恢复,指向该对象的外键约束需要重建。
3). 对象能否恢复成功,取决与对象空间是否被覆盖重用。
4). 当删除表时,信赖于该表的物化视图也会同时删除,但是由于物化视图并不会被放入recycle bin,因此当你执行flashback table to before drop 时,也不能恢复依赖其的物化视图,需要dba 手工介入重新创建。
5). 对于Recycle Bin中的对象,只支持查询.
Flashback Query
Flashback 是ORACLE 自9i 就开始提供的一项特性,在9i 中利用oracle 查询多版本一致的特点,实现从回滚段中读取表一定时间内操作过的数据,可用来进行数据比对,或者修正意外提交造成的错误数据,该项特性也被称为Flashback Query。
一、Flashback Query 正如前言中所提,Flashback Query 是利用多版本读一致性的特性从UNDO 表空间读取操作前的记录数据!
什么是多版本读一致性 Oracle 采用了一种非常优秀的设计,通过undo 数据来确保写不堵塞读,简单的讲,不同的事务在写数据时,会将数据的前映像写入undo 表空间,这样如果同时有其它事务查询该表数据,则可以通过undo 表空间中数据的前映像来构造所需的完整记录集,而不需要等待写入的事务提交或回滚。
flashback query 有多种方式构建查询记录集,记录集的选择范围可以基于时间或基于scn,甚至可以同时查询出记录在undo 表空间中不同事务时的前映象。用法与标准查询非常类似,要通过flashback query 查询undo 中的撤销数据,最简单的方式只需要在标准查询语句的表名后面跟上as of timestamp(基于时间)或as of scn(基于scn)即可。as of timestamp|scn 的语法是自9iR2 后才开始提供支持。
1、As of timestamp 的示例:
SQL> alter session set nlsdateformat='YYYY-MM-DD hh24:mi:ss';
会话已更改。
SQL> select sysdate from dual;
SYSDATE
2009-10-15 19:04:16
SQL> select * from A;
ID
----------
2
1
3
4
模拟用户误操作,删除数据
SQL> delete from A;
已删除4行。
SQL> commit;
提交完成。
SQL> select * from A;
未选定行
查看删除之前的状态: 假设当前距离删除数据已经有5 分钟左右的话: SQL> select * from A as of timestamp sysdate-5/1440;
ID
----------
2
1
3
4
或者:
SQL>select * from A as of timestamp to_timestamp('2009-10-15 19:04:16','YYYY-MM-DD hh24:mi:ss');
ID
----------
2
1
3
4
用Flashback Query恢复之前的数据:
SQL>Insert into A select * from A as of timestamp to_timestamp('2009-10-15 19:04:16','YYYY-MM-DD hh24:mi:ss');
已创建4行。
SQL> COMMIT;
提交完成。
SQL> select * from A;
ID
----------
2
1
3
4
如上述示例中所表示的,as of timestamp 的确非常易用,但是在某些情况下,我们建议使用as of scn 的方式执行flashback query,比如需要对多个相互有主外键约束的表进行恢复时,如果使用as of timestamp 的方式,可能会由于时间点不统一的缘故造成数据选择或插入失败,通过scn 方式则能够确保记录的约束一致性。
- As of scn 示例
查看SCN: SELECT dbmsflashback.getsystem_changenumber FROM dual; SELECT CURRENTSCN FROM V$DATABASE;
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
1095782
删除数据: SQL> delete from A;
已删除4行。
SQL> commit;
提交完成。
查看删除之前的状态:
SQL> select * from A as of scn 1095782;
ID
----------
2
1
3
4
用Flashback Query恢复之前的数据:
SQL> insert into A select * from A as of scn 1095782;
已创建4行。
SQL> commit;
提交完成。
SQL> select * from A;
ID
----------
2
1
3
4
事实上,Oracle 在内部都是使用scn,即使你指定的是as of timestamp,oracle 也会将其转换成scn,系统时间标记与scn 之间存在一张表,即SYS 下的SMONSCNTIME
SQL> desc sys.smonscntime
名称 是否为空? 类型
THREAD NUMBER
TIME_MP NUMBER
TIME_DP DATE
SCN_WRP NUMBER
SCN_BAS NUMBER
NUM_MAPPINGS NUMBER
TIMSCNMAP RAW(1200)
SCN NUMBER
ORIG_THREAD NUMBER
每隔5 分钟,系统产生一次系统时间标记与scn 的匹配并存入sys.smonscntime 表,该表中记录了最近1440个系统时间标记与scn 的匹配记录,由于该表只维护了最近的1440 条记录,因此如果使用as of timestamp 的方式则只能flashback 最近5 天内的数据(假设系统是在持续不断运行并无中断或关机重启之类操作的话)。
注意理解系统时间标记与scn 的每5 分钟匹配一次这句话,举个例子,比如scn:339988,339989 分别匹配08-05-3013:52:00 和2008-13:57:00,则当你通过as of timestamp 查询08-05-30 13:52:00 或08-05-30 13:56:59 这段时间点 内的时间时,oracle 都会将其匹配为scn:339988 到undo 表空间中查找,也就说在这个时间内,不管你指定的时间点是什么,查询返回的都将是08-05-30 13:52:00 这个时刻的数据。
查看SCN 和 timestamp 之间的对应关系: select scn,tochar(timedp,'yyyy-mm-dd hh24:mi:ss')from sys.smonscntime;
Flashback version Query
相对于Flashback Query 只能看到某一点的对象状态, Oracle 10g引入的Flashback Version Query可以看到过去某个时间段内,记录是如何发生变化的。 根据这个历史,DBA就可以快速的判断数据是在什么时点发生了错误,进而恢复到之前的状态。
先看一个伪列 ORA_ROWSCN. 所谓的伪列,就是假的,不存在的数据列,用户创建表时虽然没有指定,但是Oracle为了维护而添加的一些内部字段,这些字段可以像普通文件那样的使用。
最熟悉的伪列就是 ROWID, 它相当于一个指针,指向记录在磁盘上的位置。ORA_ROWSCN 是Oracle 10g 新增的,暂且把它看作是记录最后一次被修改时的SCN。 Flashback Version Query 就是通过这个伪列来跟踪出记录的变化历史。
举个例子:
SQL> select * from A;
ID
----------
2
1
3
4
SQL> insert into A values(5);
已创建 1 行。
SQL> select * from A;
ID
----------
2
1
3
4
5
SQL> commit;
提交完成。
SQL> select ora_rowscn, id from A;
ORA_ROWSCN ID
1098443 2
1098443 1
1098443 3
1098443 4
1098443 5
获取更多的历史信息
SQL>Select versionsxid,versionsstartscn,versions_endscn,
DECODE(versions_operation,'I','Insert','U','Update','D','Delete', 'Original') "Operation", id from A versions between scn minvalue and maxvalue;
或者
SQL>select xid,commitscn,committimestamp,operation,undo_sql
from flashbacktransactionquery q where q.xid in(select versions_xid from B versions between scn 413946 and 413959);
VERSIONSXID VERSIONSSTARTSCN VERSIONS_ENDSCN Operatio ID
05001A0054020000 1099482 Update 3
05001A0054020000 1099482 Delete 3
05001A0054020000 1099482 Delete 2
05001A0054020000 1099482 Delete 1
0400150005020000 1098443 Insert 5
下面我们来讲下伪列, Flashback Version Query 技术其实有很多伪列,但是ORA_ROWSCN是最重要。它记录的是最后一次被修改时的SCN, 注意是被提交的修改。如果没有提交,这个伪列不会发生变化。
ORAROWSCN 缺省是数据块级别的,也就是一个数据块内的所有记录都是一个ORAROWSCN,数据块内任意一条记录被修改,这个数据库块内的所有记录的ORA_ROWSCN都会同时改变。上例的查询结果以证明。
不过我们可以在建表时使用关键字 rowdependencies, 可以改变这种缺省行为,使用这个关键字后,每条记录都有自己的ORA_ROWSCN。
举例:
SQL> create table B (id number(2)) rowdependencies;
表已创建。
SQL> insert into B values(1);
已创建 1 行。
SQL> insert into B values(2);
已创建 1 行。
SQL> insert into B values(3);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select ora_rowscn, id from B;
ORA_ROWSCN ID
1100560 1
1100560 2
1100560 3
此处SCN一样,一定很奇怪,这正好说明是最后一次被修改时的SCN,如果没有提交,是不会变的,我们重做一下就清楚了。
SQL> analyze table B compute statistics;
表已分析。
SQL> select ora_rowscn, id from B;
ORA_ROWSCN ID
1100560 1
1100560 2
1100560 3
SQL> delete from B;
已删除4行。
SQL> select ora_rowscn, id from B;
未选定行
SQL> insert into B values(1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into B values(2);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select ora_rowscn, id from B;
ORA_ROWSCN ID
1100723 1
1100729 2
Flashback Transaction Query
Flashback Transaction Query也是使用UNDO信息来实现。利用这个功能可以查看某个事务执行的所有变化,它需要访问flashbacktransactionquery 视图,这个视图的XID列代表事务ID,利用这个ID可以区分特定事务发生的所有数据变化。
示例:
SQL> insert into B values(3);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from B;
ID
----------
1
2
3
查看视图,每个事务都对应相同的XID
SQL>Select xid,operation,commitscn,undosql from flashbacktransactionquery where xid in (
Select versions_xid from B versions between scn minvalue and maxvalue);
或者
SQL>select xid,commitscn,committimestamp,operation,undo_sql
from flashbacktransactionquery q where q.xid in(select versions_xid from B versions between scn 413946 and 413959);
XID OPERATION COMMIT_SCN
UNDO_SQL
03001C006A020000 DELETE 1100723
insert into "SYS"."B"("ID") values ('4');
03001C006A020000 DELETE 1100723
insert into "SYS"."B"("ID") values ('3');
03001C006A020000 DELETE 1100723
insert into "SYS"."B"("ID") values ('2');
Flashback Table
注意SYS用户不支持闪回,这点前面已经说明过。
Flashback Table也是使用UNDO tablespace的内容来实现对数据的回退。该命令相对简单,输入:flashback table table_name to scn(to timestamp) 即可。
注意:如果想要对表进行flashback,必须允许表的row movement.
Alter table table_name row movement;
要查看某表是否启用row movement,可以到usertables 中查询(或alltables,dba_tables),
例如: SQL> select rowmovement from usertables where table_name='C';
ROW_MOVE
ENABLED
要启用或禁止某表row movement,可以通过下列语句:
--启用 JSSWEB> ALTER TABLE tablename ENABLE ROW MOVEMENT; 表已更改。 --禁止 JSSWEB> ALTER TABLE tablename DISABLE ROW MOVEMENT; 表已更改。
举例:
SQL> create table C (id number(2));
表已创建。
SQL> insert into C values(1);
已创建 1 行。
SQL> insert into C values(2);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from c;
ID
1
2
SQL> alter session set nlsdateformat="yyyy-mm-dd hh24:mi:ss";
会话已更改。
SQL> select sysdate from dual;
SYSDATE
2009-10-15 21:17:47
SQL> select current_scn from v$database;
CURRENT_SCN
1103864
删除数据并恢复
SQL> delete from C;
已删除2行。
SQL> commit;
提交完成。
SQL> alter table c enable row movement;
表已更改。
SQL> flashback table c to scn 1103864;
闪回完成。
或者:
SQL> flashback table c to timestamp to_timestamp('2009-10-15 21:17:47','yyyy-mm-
dd hh24:mi:ss');
SQL> select * from c;
ID
1
2
Flashback table 命令支持同时操作多个表,表名中间以逗号分隔即可,如果你执行一条flashback table命令时同时指定了多个表,要记住单个flashback table 是在同一个事务中,因此这些表的恢复操作要么都成功,要么都失败。
如:
flashback table a,b ,c to scn 1103864;
一些注意事项
-
基于undo 的表恢复,需要注意DDL 操作的影响 第三个就是修改并提交过数据之后,对表做过DDL 操作,包括: drop/modify 列, move 表, drop 分区(如果有的话), truncate table/partition,这些操作会另undo 表空间中的撤销数据失效,对于执行过这些操作的表应用flashback query 会触发ORA-01466 错误。另外一些表结构修改语句虽然并不会影响到undo 表空间中的撤销记录,但有可能因表结构修改导致undo 中重做记录无法应用的情况,比如对于增加了约束,而flashback query 查询出的undo 记录已经不符合新建的约束条件,这个时候直接恢复显然不可能成功,你要么暂时disable 约束,要么通过适当逻辑,对要恢复的数据进行处理之后,再执行恢复。 另外,flashback query 对v$tables,x$tables 等动态性能视图无效,不过对于dba*,all,user_等数据字典是有效的。同时该特性也完全支持访问远端数据库,比如select * from tbl@dblink as of scn 360;的形式。
-
基于undo 的表恢复,flashback table 实际上做的也是dml 操作(会在被操作的表上加dml 锁),因此还需要注意triggers 对其的影响,默认情况下,flashback table to scn/timestamp 在执行时会自动disable 掉与其操作表相差的triggers,如果你希望在此期间trigger 能够继续发挥做用,可以在flashback table 后附加 ENABLE TRIGGERS 子句。
补充:
什么是Automatic Undo Management( 自动撤销管理表空间) 提到自动撤销管理表空间,就不得不提手动管理的回滚段。在9i 之前,回滚段的管理和监控是需要dba手工介入的,创建合适的回滚段是件非常耗费dba 精力的事情,你可能需要不断关注oracle 运行状况很长一阵子时间后,通过不断的调整才能基本确认一段时期内回滚段的大小,一旦回滚段创建的不合适,就极有可能引起性能问题甚至错误,比如ora-1555 就是典型的回滚段设置不合适触发的。 9i 之后呢(含9i),oracle 为了清晰它的整个概念,取消了回滚段这个说法(实际上并未取消回滚段),而完全以undo 来代替,这也它正好与redo 相对应,一个重做,一个撤销。回滚段可以不再由dba 手工介入,而是完全由它自己在运行时自动分配,这在一定程度上即解放了dba,也确实起到了提高性能的作用,比如采用自动管理表空间就可以最大程序的降低ora-1555发生的机率(注意是降低,不是避免,我们不可能创建一个无限大的回滚段,ora-1555 也并不完全是回滚段造成的,关于ora-1555 的问题这里就不深入讨论了,互联网上已经有太多文章描述和介绍该问题及解决方案)
是否起用自动管理的撤销表空间由二个初始化参数决定: UNDOMANAGEMENT:值为AUTO 表示使用了自动撤销管理表空间,MANUAL 则表示手动管理 UNDOTABLESPACE:当UNDO_MANAGEMENT 值为AUTO 时,该参数用来指定当前的undo 表空间名称。 undo 表空间的大小,直接影响到flashback query 的查询能力,因为多版本查询所依赖的undo 数据都存储在undo 表空间中,该表空间越大,所能够存储的undo 数据自然也越多,如果该表空间可用空间非常小,别说flashback 了,恐怕正常查询都有可能触发ora-1555 吧。
初始化参数UNDORETENTION 该参数用来指定undo 记录保存的最长时间,以秒为单位,是个动态参数,完全可以在实例运行时随时修改通常默认是900 秒,也就是15 分钟。 一定要注意,undoretention 只是指定undo 数据的过期时间,并不是说,undo 中的数据一定会在undo表空间中保存15 分钟,比如说刚一个新事务开始的时候,如果undo 表空间已经被写满,则新事务的数据会自动覆盖已提交事务的数据,而不管这些数据是否已过期,因此呢,这就又关联回了第一点,当你创建 一个自动管理的undo 表空间时,还要注意其空间大小,要尽可能保证undo 表空间有足够的存储空间。 同时还要注意,也并不是说,undoretention 中指定的时间一过,已经提交事务中的数据就立刻无法访问,它只是失效,只要不被别的事务覆盖,它会仍然存在,并可随时被flashback 特性引用。如果你的undo表空间足够大,而数据库又不是那么繁忙,那么其实undoretention 参数的值并不会影响到你,哪怕你设置成1,只要没有事务去覆盖undo 数据,它就会持续有效。因此呢,这里还是那句话,要注意undo 表空间的大小,保证其有足够的存储空间。
只有在一种情况下,undo 表空间能够确保undo 中的数据在undo_retention 指定时间过期前一定有效,就是为undo 表空间指定Retention Guarantee,指定之后,oracle 对于undo 表空间中未过期的undo 数据不会覆盖,
例如: SQL> Alter tablespace undotbs1 retention guarantee; 如果想禁止undo 表空间retention guarantee,
例如: SQL> Alter tablespace undotbs1 retention noguarantee;