登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

飞哥的技术博客

世上无难事,只怕有心人!

 
 
 

日志

 
 
 
 

Oracle Flashback特性  

2009-06-04 09:39:51|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

Oracle Flashback特性(一)

Flashback Database使用Flash Recovery Area中的闪回日志代替UNDO表空间中的撤销实现闪回功能.

Flashback Drop是将删除的表放入表空间中一个虚拟回收站中保留,直到用户使用flashback table ... to before drop 命令恢复表, 或清空回收站, 否则直到表空间新对象需要空间时.

在Oracle 10g和Oracle 11g得到进一步的扩充, DBA可以赋予用户系统和对象权限以允许修复出现的问题, 而无需DBA干涉.

如赋予SCOTT用户在指定表上执行Flashback操作和控制数据库中的事务元数据:

SQL> grant insert, update, delete, select on hr.employees to scott;

Grant succeeded.

SQL> grant insert, update, delete, select on hr.departments to scott;

Grant succeeded.

SQL> grant flashback on hr.employees to scott;

Grant succeeded.

SQL> grant flashback on hr.departments to scott;

Grant succeeded.

SQL> grant select any transaction to scott;

Grant succeeded.

闪回查询(Flashback Query)

至Oracle9i Release 2起, 可以利用select查询的as of子句获得给定时间点或SCN的表状态. 利用该功能可以找回已删除的记录或对表中的记录按不同的时间点进行比较.

删除表数据并通过闪回恢复数据例子:

SQL> delete from employees

  2  where employee_id in (195,196);

2 rows deleted

SQL> commit;

Commit complete

SQL> select employee_id, first_name, last_name from employees

  2  as of timestamp systimestamp - interval '5' minute

  3  where employee_id not in 

  4  (select employee_id from employees);

EMPLOYEE_ID FIRST_NAME      LAST_NAME

------------------ ------------------- -------------------------

        195         Vance                Jones

        196         Alana                Walsh

DBMS_FLASHBACK

同Flashback Query, 区别是DBMS_FLASHBACK在会话级操作, Flashback Query在对象级操作.

当DBMS_FLASHBACK启用时DML操作不被允许, 但可以通过游标实现数据的插入和更新操作.

DBMS_FLASHBACK可用的程序

DISABLE: 禁用会话的Flashback模式

ENABLE_AT_SYSTEM_CHANGE_NUMBER: 为会话启用闪回模式, 指定SCN

ENABLE_AT_TIME: 为会话启用闪回模式, 指定接近于SCN的时间期

GET_SYSTEM_CHANGE_NUMBER: 返回当前的SCN

TRANSACTION_BACKOUT: 停止一个事务和所有依赖的使用事务名称或事务标志符(XIDs)的事务.

删除表数据并通过闪回恢复例子:

SQL> delete from hr.employees where employee_id in (195,196);

2 rows deleted

SQL> commit;

Commit complete

SQL> execute dbms_flashback.enable_at_time(to_timestamp(sysdate - interval '30' minute));

PL/SQL procedure successfully completed

SQL> select employee_id, last_name from hr.employees

  2  where employee_id in (195,196);

EMPLOYEE_ID LAST_NAME

----------- -------------------------

        195 Jones

        196 Walsh

SQL> declare

  2  cursor del_emp is  --保存删除数据的游标

  3  select * from hr.employees where employee_id in (195,196);

  4  del_emp_rec del_emp%rowtype;

  5  begin

  6  open del_emp;  --在闪回模式下打个游标

  7  dbms_flashback.disable;  --关闭闪回模式, 因此我们可以使用DML将恢复删除的数据

  8  loop

  9  fetch del_emp into del_emp_rec;

10  exit when del_emp%notfound;

11  insert into hr.employees values del_emp_rec;

12  end loop;

13  commit;

14  close del_emp;

15  end;

16  /

Flashback Table

Flashback Table不仅恢复过去一个时间点的表的行数据, 数据库在线时同样也恢复表的索引, 触发器和约束, 增加数据库的整体可用性. 表使用时间点或者SCN进行恢复. 在用户小范围错误或极少数几个表的情况下, Flashback Table比其它的闪回方法更有效. 对于恢复大量量, Flashback Database是个更好的选择. Flashback Table不能用于备用数据库, 不能重建所有的DDL操作, 如增加和删除字段.

在使用Flashback Table操作表之前必须在表上启用行移动功能(row movement). Oracle段收缩功能也需要行移动功能支持.

SQL> delete from flashtest;

7 rows deleted

SQL> commit;

Commit complete

SQL> flashback table flashtest

  2  to timestamp systimestamp - interval '15' minute;

flashback table flashtest

to timestamp systimestamp - interval '15' minute

ORA-08189: 因为未启用行移动功能, 不能闪回表

SQL> alter table flashtest enable row movement;

Table altered

SQL> delete from flashtest;

7 rows deleted

SQL> commit;

Commit complete

SQL> flashback table flashtest

  2  to timestamp systimestamp - interval '15' minute;

Done

SQL> select count(*) from flashtest;

  COUNT(*)

----------

         7

Flashback Version Query

Flashback Version Query是另一个依赖重做数据的闪回特性, 提供比as of查询更深层次的细节描述, 返回给定期间或SCN内的所有历史操作记录.

Flashback Version Query使用versions between子句为分析的表指定SCN和期间范围, 并利用一些虚拟列确定改变的SCN和时期, 事务ID和操作类型.

虚拟列说明如下:

VERSIONS_START{SCN|TIME} : 当行版本被改变时的开始SCN和时间戳, 这里行已改变.

VERSION_END{SCN|TIME} : 改变对行不再有效时的结束SCN和时间戳, 若该参数为空, 则行的版本是当前的, 或已经被删除.

VERSIONS_XID : 为行版本创建的事务ID

VERSIONS_OPERATION : 行上执行的操作(I, U, D)

具体见下面实验:

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

                 3002569

SQL> update flashtest set object_id=100

  2  where object_id=46;

1 row updated

SQL> delete from flashtest where object_id='15';

1 row deleted

SQL> insert into flashtest

  2  select * from all_objects

  3  where rownum<2;

1 row inserted

SQL> update flashtest set object_id=200 where object_id= 3;

1 row updated

SQL> commit;

Commit complete

SQL> update flashtest set object_id = 300 where object_id = 54;

1 row updated

SQL> commit;

Commit complete

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

                 3005474

SQL> select versions_startscn startscn, versions_endscn endscn,

  2  versions_xid xid, versions_operation oper,

  3  object_id, object_name

  4  from flashtest

  5  versions between scn 3002569 and 3005474;

  STARTSCN   ENDSCN XID                           OPER  OBJECT_ID OBJECT_NAME

---------------- ---------- -------------------------- ------- ---------- ------------------

   3005473                 05001400A8070000  U         300       I_CDEF2

   3005406                 090015006D070000  U         200       I_OBJ#

   3005406                 090015006D070000  I             20       ICOL$

   3005406                 090015006D070000  D           15       UNDO$

   3005406                 090015006D070000  U         100       I_USER1

                    3005406                                                     46        I_USER1

                                                                                         28        CON$

                    3005406                                                    15        UNDO$

                                                                                        29        C_COBJ#

                    3005406                                                      3         I_OBJ#

                                                                                        25        PROXY_ROLE_DATA$

                    3005473                                                    54        I_CDEF2

12 rows selected

Flashback Transaction Query

一旦我们发现对表做了任何错误或不正确的更改, 可以利用闪回事务查询(Flashback Transaction Query)找出事务包含的任何不适当的更改.

Flashback Transaction Query不象Flashback Version Query需要涉及表有关的DML事务, 只用查询数据字典视图: flashback_transaction_query

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

                 3933733

SQL> update flashtest set object_id=1000

  2  where object_id=15;

1 row updated

SQL> commit;

Commit complete

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

                 3933749

SQL> select versions_startscn startscn, versions_endscn endscn,

  2  versions_xid xid, versions_operation oper,

  3  object_id, object_name

  4  from flashtest

  5  versions between scn 3933733 and 3933749;

STARTSCN  ENDSCN XID                              OPER  OBJECT_ID OBJECT_NAME

------------- ---------- ----------------------------- ------- -------------- -----------------------

3933748                 06001D00D90B0000     U          1000        UNDO$

                                                                                    46         I_USER1

                                                                                    28         CON$

              3933748                                                        15         UNDO$

                                                                                    29         C_COBJ#

                                                                                      3         I_OBJ#

                                                                                    25        PROXY_ROLE_DATA$

                                                                                    54         I_CDEF2

8 rows selected

SQL> select start_scn, commit_scn, logon_user,

  2  operation, table_name, undo_sql

  3  from flashback_transcation_query

  4  where xid = hextoraw('06001D00D90B0000');

XID               START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER                     UNDO_CHANGE# OPERATION                        TABLE_NAME                                                                       TABLE_OWNER                      ROW_ID              UNDO_SQL

---------------- ---------- --------------- ---------- ---------------- ------------------------------ ------------ -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------

06001D00D90B0000    3933746 2008-07-07 19:2    3933748 2008-07-07 19:28 WIND                                      1 UPDATE                           FLASHTEST                                                                        WIND                             AAAR41AAEAAABcMAAJ  update "WIND"."FLASHTEST" set "OBJECT_ID" = '15' where ROWID = 'AAAR41AAEAAABcMA

06001D00D90B0000    3933746 2008-07-07 19:2    3933748 2008-07-07 19:28 WIND                                      2 BEGIN

flashback_transaction_query视图列说明:

XID: 事务ID号

START_SCN: 事务中首个DML操作的SCN

START_TIMESTAMP: 事务中首个DML操作的时间戳

COMMIT_SCN: 事务提交时的SCN

COMMIT_TIMESTAMP: 事务提交时的时间戳

LOGON_USER: 事务的拥有者

UNDO_CHANGE#:

OPERATION: DML操作: DELETE, INSERT, UPDATE, BEGIN或UNKNOWN

TABLE_TABLE: 更改的表

TABLE_OWNER: 更改表的拥有者

ROW_ID: 更改行的ROWID

UNDO_SQL: 撤消DML操作的SQL语句

  评论这张
 
阅读(430)| 评论(1)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018