Oracle10g的Flashback之Flashback Transaction Query
/**
*环境:WindowsXP+Oracle10.2.0.1.0(32位)
*日志模式:归档模式
*作者:蒙昭良
*标题:Oracle10g的Flashback之Flashback Transaction Query
**/
从Oracle9i开始,Oracle将闪回(flashback)引入数据库中,闪(flashback)可以简化用户的数据恢复,不用从逻辑备份和物理备份中恢复过来。
在Oracle10g中,Oracle对闪回特性进行了加强,flashback能够恢复所有的数据库错误(除了文件损坏、介质错误等)。
在Oracle10g中开始,Flashback支持的恢复级别包括:
1、事务闪回:对表进行了insert,update,delete操作,已经commit了,发现弄错了,需要恢复到某个commit操作之前的数据,则需要撤销刚才的某个操作。
2、行级闪回:对表进行了insert,update,delete操作,需要恢复到某个时间点。
3、表级闪回:错误的drop table的操作,需要表和表中的数据。如删除表的某列等。
4、数据库级闪回:错误对整个数据库导入很多表数据,将整个数据库恢复到过去的时间点。
这里主要说明事务闪回,先介绍什么事务?
事务(Transaction)就是对数据执行的一组操作,这些操作钭数据从一种状态更改为另一种状态,他们必须为一个单元运行。事务最主要的功能就是确保多个连续的操作必须全部执行成功,否则回复到未执行任何数据操作的最初状态。比如对表做了insert操作,然后进行commit成功之后,就算完成了一个事务。
Oracle Flashback Transaction Query特性确保检查数据库的任何改变在一个事务级别,可以利用此功能进行诊断问题、性能分析和审计事务。它其实是Flashback Version Query查询的一个扩充,Flashback Version Query说明了可以审计一段时间内表的所有改变,但是也仅仅是能发现问题,对于错误的事务,没有好的处理办法。而Flashback Transaction Query提供了从FLASHBACK_TRANSACTION_QUERY视图中获得事务的历史以及Undo_sql(回滚事务对应的sql语句),也就是说审计一个事务到底做了什么,甚至可以回滚一个已经提交的事务。
某天,有个开发人员很慌张的跑过来来对我说,对dept表执行了insert,update,delete的操作,都已经提交commit。现在想把刚才的update、delete操作撤销,恢复到update、delete之前的数据。
1、查看是哪个版本的数据库,用拥有数据库dba角色权限的用户查看数据库版本(默认最高管理员sys、system都具有)
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production |
从Oracle10开始可以使用 Oracle Flashback Transaction Query特性
2、查看该用户是否具有flashback的权限
SQL> conn scott/mzl 已连接。 |
SQL> select * from session_privs where privilege like 'FLASHBACK%'; PRIVILEGE ---------------------------------------- FLASHBACK ANY TABLE |
3、在这先备份下dept表的数据,以防丢失现在的表数据(小心驶得万年船)
SQL> create table dept_backup20081129 2 as 3 select * from dept; |
Oracle10g可以通过Flashback Version Query允许对不同时间段内数据表的不同版本进行查询,查询可以反映不同时间段内数据表的变更。
Oracle Flashback Version Query特性,利用保存的回滚信息,可以看到特定的表在时间段内的任何修改,如电影的回放一样,可以了解表在该期间的任何变化。Flashback version query一样依赖于AUM,提供了一个查看行改变的功能,能找到所有已经提交了的行的记录,分析出过去时间都执行了什么操作。Flashback version query采用VERSIONS BETWEEN语句来进行查询,常用的方法:
·VERSIONS_SCN – 系统改变号
·VERSIONS_TIMESTAMP – 时间
4、查询什么时间段做哪些操作
SQL> select versions_starttime, versions_endtime, versions_xid, 2 versions_operation, deptno,dname,loc 3 from dept versions between timestamp minvalue and maxvalue VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION DEPTNO DNAME LOC 1 29-11月-08 10.32.12 下午 0A001B00B3040000 U 10 ACCOUNTING ShanXi 2 29-11月-08 10.32.12 下午 10 ACCOUNTING BeiJing 3 20 RESEARCH 4 30 SALES 5 40 OPERATIONS 6 29-11月-08 10.25.32 下午 07001700BB040000 D 70 WenYuan GuangXi 7 29-11月-08 10.25.32 下午 70 WenYuan GuangXi 8 29-11月-08 10.34.33 下午 07002F00BB040000 I 90 WenYuan ShanDong |
VERSIONS_STARTSCN,VERSIONS_STARTTIME:该记录操作时的scn或时间,如果为空,表示该行记录是在查询范围外创建的。
VERSIONS_ENDSCN,VERSIONS_ENDTIME:该记录失效时的scn或时间,如果为空,说明记录当前时间在当前表内存在,或者已经被删除了,可以配合着VERSIONS_OPERATION列来看,如果VERSIONS_OPERATION列值为D,说明该列已被删除,如果该列为空,则说明记录在这段时间无操作。
VERSIONS_XID:该操作的事务ID
VERSIONS_OPERATION:对该行执行的操作:I表示insert,D表示delete,U表示update。
提示:对于索引键的update操作,版本查询可能会将其识别成两个操作:DELETE和INSERT。
4、查出具体什么操作。
由上面可见刚才事务ID:0A001B00B3040000对dept表进行update操作,事务ID:07001700BB040000进行了delete的操作。事务ID:07002F00BB040000进行了insert操作。
SQL>set time on; (在sqlplus中设置打开时间) |
SQL>select xid,start_timestamp,operation,table_name,undo_sql from flashback_transaction_query where xid in ('0A001B00B3040000','07001700BB040000','07002F00BB040000') and table_name='DEPT'; XID START_TIMESTAMP OPERATION TABLE_NAME UNDO_SQL 1 07002F00BB040000 2008-11-29 22:34:33 INSERT DEPT delete from "SCOTT"."DEPT" where ROWID = 'AAANZrAAEAAAATAAAA'; 2 07001700BB040000 2008-11-29 22:25:32 DELETE DEPT insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('70','WenYuan','GuangXi'); 3 0A001B00B3040000 2008-11-29 22:32:09 UPDATE DEPT update "SCOTT"."DEPT" set "LOC" = 'BeiJing' where ROWID = 'AAANZrAAEAAAAS8AAB'; |
如果需要撤销刚才删除的操作,运行insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('70','WenYuan','GuangXi');就可以了
撤销其他的操作,则执行与之相对应的redo_sql中的sql语句则可以了。
5、需要撤销刚才的删除操作,执行
SQL>insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('70','WenYuan','GuangXi'); |
需要撤销刚才的更新操作,执行
SQL>update "SCOTT"."DEPT" set "LOC" = 'BeiJing' where ROWID = 'AAANZrAAEAAAAS8AAB'; |
6、查看dept表的数据是否符合要求,符合要求后删除刚才备份的表
22:53:08 SQL> drop table dept_backup20081129; |
从上面看出2008-11-29 22:34:33 插入了哪些数据呢,运行select t.*,rowid from "SCOTT"."DEPT" t where ROWID = 'AAANZrAAEAAAATAAAA';
22:49:49 SQL> select t.*,rowid from "SCOTT"."DEPT" t where ROWID = 'AAANZrAAEAAAATAAAA' |
DEPTNO DNAME LOC ROWID ---------- ---------- ---------- ------------------ 90 WenYuan ShanDong AAANZrAAEAAAATAAAA |
在2008-11-29 22:25:32又删除了哪些数据呢,可以看出是'70','WenYuan','GuangXi'
在2008-11-29 22:32:09更新的数据是哪些,可以运行select t.*,rowid from "SCOTT"."DEPT" t where ROWID = 'AAANZrAAEAAAAS8AAB';
22:53:07 SQL>select t.*,rowid from "SCOTT"."DEPT" t where ROWID = 'AAANZrAAEAAAAS8AAB'; |
DEPTNO DNAME LOC ROWID ---------- ---------- ---------- ------------------ 10 ACCOUNTING ShanXi AAANZrAAEAAAAS8AAB |
由此对比数据可知:该数据的loc字段由“BeiJing”更新为“ShanXi”。