Bo's Oracle Station

查看: 2193|回复: 0

第55次和第56、57、58次:2014-10-28,2014-10-30,2014-11-01

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2014-11-1 20:26:34 | 显示全部楼层 |阅读模式
本帖最后由 botang 于 2014-11-27 10:13 编辑

2014-11-01-A.txt
  1. select  * from v$flashback_database_stat;

  2. select  * from v$fixed_table where name like '%USAGE%';

  3. select  * from V$FLASH_RECOVERY_AREA_USAGE;

  4. select  * from v$restore_point;

  5. create restore point rsp1 ;


  6. select  * from v$restore_point;

  7. select  flashback_on from v$database;

  8. select  * from v$flashback_database_log;

  9. create restore point rsp2 guarantee flashback database;

  10. select  * from v$restore_point;


  11. select * from v$flashback_database_logfile;
复制代码

2014-11-01-B.txt







  1. 若是管理员决定撤销某个或某些事务,Oracle提供一个专门用来撤销事务的工具——闪回事务。

  2. 闪回事务又名撤销事务(Transaction Backout),能够撤销一个或多个事务的修改,其功能由一个名为DBMS_FLASHBACK.TRANSACTION_BACKOUT的存储过程实现。该存储过程的工作原理是自动分析补充重做日志和undo ,挖掘出变更前的值undo sql用以构建撤销SQL(Undo SQL),然后执行撤销SQL最后达到撤销事务的目的。




  3. 为了该功能可以正常使用,至少需要事先启用主键补充日志。另外,为了能够跟踪外键依赖还需要启用外键补充日志。






  4. 在继续讨论此功能前,首先应了解一个概念:事务的依赖性。比如,两个事务TX1和TX2,若符合以下3个条件的任意一个就可以认为TX2依赖TX1:

  5. (1)WAW依赖(Write After Write),即在TX1修改了表的某行之后,TX2又修改了同一行。


  6. (2)主键依赖,即在一张拥有主键的表中TX1首先删除了一行,之后TX2又插入了具有相同主键值的另一行。

  7. (3)外建依赖,即由于TX1的修改(insert或update)而产生了新的可被外键参考的字段值,之后TX2修改(insert或update)外键字段时利用了TX1所产生的字段值。





  8. 了解事务依赖性有助于解决在撤销事务时遇到的矛盾,以主键依赖为例,试想若直接将事务TX1撤销并且不理会事务TX2,岂不是会出现主键值重复的行!

  9. TRANSACTION_BACKOUT存储过程的OPTIONS参数就是为了解决事务依赖性问题而存在的,在该参数上管理员可以使用4种撤销事务的方案,假设被撤销的事务是TX1,若其具有依赖事务,则称为TX2:

  10. (1)NOCASCADE,TX1不可以被任何其他事务依赖(即TX2不存在),否则撤销操作报错。

  11. (2)CASCADE,将TX1连同TX2一起撤销。

  12. (3)NOCASCADE_FORCE,忽略TX2,直接执行TX1的撤销SQL将TX1撤销,如果没有约束上的冲突,操作将成功,否则约束报错导致撤销操作失败。

  13. (4)NONCONFILICT_ONLY,在不影响TX2的前提下,撤销TX1的修改。与NOCASCADE_FORCE的不同点在于会首先过滤一下TX1的撤销SQL,确保它们不会作用在TX2修改的行上。

  14. 接下来以WAW依赖为例详细说明,比如有一张表的原有数据如下所示,只有3行且没有约束:

  15.         ID
  16. ----------
  17.          1
  18.          2
  19.          3

  20. 接下来先后发起事务TX1和TX2仅修改该表。在事务TX1(更新了3行)执行后其数据变更为:

  21.         ID
  22. ----------
  23.         11
  24.         22
  25.         33

  26. 之后,在事务TX2(更新了两行,第一行没有修改)执行后其数据变更为:

  27.         ID
  28. ----------
  29.         11
  30.        222
  31.        333

  32. 此例为典型的WAW依赖,TX2依赖TX1。

  33. 现在计划将事务TX1撤销,那么使用不同的OPTIONS将产生不同的结果。

  34. 若采用NOCASCADE结果是抛出错误“ORA-55504: Transaction conflicts in NOCASCADE mode”,表内容依然是:


  35. ------



  36.         ID
  37. ----------
  38.         11
  39.         22
  40.         33

  41. 若采用CASCADE,表的内容恢复到TX2和TX1均未执行的状态:

  42.         ID
  43. ----------
  44.          1
  45.          2
  46.          3

  47. 若采用NOCASCADE_FORCE,TX2的结果不受影响,但被TX1修改的第一行回滚了,闪回事务没有尊重TX1的事务原子性。表的内容变为:

  48.         ID
  49. ----------
  50.          1
  51.        222
  52.        333

  53. 也许读者会感到奇怪,根据NOCASCADE_FORCE的定义,会在所有行上执行撤销SQL,那为什么第2和第3行的内容没有回到TX1执行之前呢?原因是此例中撤销SQL的where语句中还包含ID字段的值,这是启用了主键补充日志的结果:

  54. update <表名> set "ID" = '1' where "ID" = '11' and ROWID = <第1行ROWID>;
  55. update <表名> set "ID" = '2' where "ID" = '22' and ROWID = <第2行ROWID>;
  56. update <表名> set "ID" = '3' where "ID" = '33' and ROWID = <第3行ROWID>;

  57. 没记错的话第2和第3行的ID字段已经被TX2分别修改为222和333了,所以虽然执行了3条撤销SQL,但只有第1行得到了修改。

  58. 若采用NONCONFILICT_ONLY,在此例中将产生与NOCASCADE_FORCE一样的结果:

  59.         ID
  60. ----------
  61.          1
  62.        222
  63.        333

  64. 读者需要明白本情况中的撤销SQL应该只有一条:

  65. update <表名> set "ID" = '1' where "ID" = '11' and ROWID = <第1行ROWID>;

  66. 虽然最后的结果是相同的,但是与NOCASCADE_FORCE所做的尝试是不同的,和TX2有关的对第2行、第3行的更改命令首先被过滤了。试想若在事务TX2之后还有一个事务TX3又将第3行的ID字段改回33,再使用NOCASCADE_FORCE和NONCONFILICT_ ONLY将TX1闪回,结果将会怎样。

  67. 使用DBMS_FLASHBACK.TRANSACTION_BACKOUT的步骤如下:

  68. (1)将需要撤销的事务的事务号或事务名载入对应的VARRAY集合变量。

  69. (2)以NOCASCADE方式调用TRANSACTION_BACKOUT。如果报错,再从另外3种方式中选择一个调用BACKOUT_TRANSACTION。

  70. (3)查看闪回事务操作的报告。

  71. (4)最后决定提交或回滚。

  72. 下面是一个展示闪回事务战斗力的例子,作为本节的结尾。

  73. 首先确认一下201号员工的薪水是13000美元:

  74. SQL> select salary from hr.employees where employee_id=201;

  75. SALARY
  76. ------
  77. 13000

  78. 然后,将全体员工的工资涨500 %,这是一次人为错误,201号员工的收入变为78000美元了:

  79. SQL> update hr.employees set salary=salary*5;

  80. 107 rows updated.

  81. SQL> commit;

  82. Commit complete.

  83. 紧接着,人事管理应用发出一个正常的操作将201号员工的工资上浮10 %,这里笔者用SQL*Plus模拟HR应用:

  84. SQL> update hr.employees set salary=salary*1.1 where employee_id=201;

  85. 1 row updated.

  86. SQL> commit;

  87. Commit complete.

  88. 显然HR应用的本意是让201号员工获得13000美元的110%,即14300美元月薪,但是经过前一次错误update的修改,如今该员工的薪水是85800美元:

  89. SQL> select salary from hr.employees where employee_id=201;

  90.     SALARY
  91. ----------
  92.      71500

  93. 不久之后,工作人员发现所有员工的薪水高得反常,管理员受理之后通过闪回事务查询查询发现最近(15分钟之内)在hr.employees表上的事务有两个:

  94. SQL> select distinct xid,commit_scn
  95.    2 from flashback_transaction_query
  96.    3 where table_owner='HR' and
  97.    4 table_name='EMPLOYEES' and
  98.    5 commit_timestamp > systimestamp - interval '15' minute
  99.    6 order by commit_scn;

  100. XID              COMMIT_SCN
  101. ---------------- ----------
  102. 0A00160094020000    1277129
  103. 0900070068030000    1277301

  104. 然后再利用闪回事务查询观察FLASHBACK_TRANSACTION_QUERY.UNDO_SQL字段,了解到COMMIT_SCN号是1277129、事务号为0A00160094020000的事务很不正常,不但更新了所有员工的SALARY,而且金额太大(因为撤销SQL中的SALARY很低,而当前SALARY很高):

  105. SQL> select undo_sql from flashback_transaction_query
  106.   2  where commit_scn='1277129';

  107. UNDO_SQL
  108. ----------------------------------------------------------------------------
  109. update "HR"."EMPLOYEES" set "SALARY" = '3000' where ROWID = 'AAAR5pAAFAAAADPABh';
  110. update "HR"."EMPLOYEES" set "SALARY" = '3100' where ROWID = 'AAAR5pAAFAAAADPABg';
  111. update "HR"."EMPLOYEES" set "SALARY" = '2800' where ROWID = 'AAAR5pAAFAAAADPABf';
  112. update "HR"."EMPLOYEES" set "SALARY" = '3200' where ROWID = 'AAAR5pAAFAAAADPABe';
  113. update "HR"."EMPLOYEES" set "SALARY" = '3900' where ROWID = 'AAAR5pAAFAAAADPABd';
  114. update "HR"."EMPLOYEES" set "SALARY" = '4000' where ROWID = 'AAAR5pAAFAAAADPABc';
  115. update "HR"."EMPLOYEES" set "SALARY" = '2500' where ROWID = 'AAAR5pAAFAAAADPABb';

  116. ...省略100行

  117. 注意撤销SQL中赋予SALARY字段的值正是事务0A00160094020000执行前的值。现决定用TRANSACTION_BACKOUT闪回该事务,使SALARY恢复正常值:

  118. SQL> declare
  119.   2   v_xids sys.xid_array;
  120.   3  begin
  121.   4   v_xids := sys.xid_array('0A00160094020000');
  122.   5   dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.nocascade);
  123.   6  end;
  124.   7  /

  125. 第5行中存储过程的第二个参数是一个容纳事务号的VARRAY集合变量,第一个参数表示VARRAY内事务号的数量,本例中只有一个事务需要撤销,所以等于1。

  126. 因为WAW依赖性,这样执行会失败:

  127. declare
  128. *
  129. ERROR at line 1:
  130. ORA-55504: Transaction conflicts in NOCASCADE mode
  131. ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
  132. ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
  133. ORA-06512: at line 5

  134. 现在最符合逻辑的做法是使用casecade方式将两个事务全部撤销,修改options参数后重新执行:

  135. SQL> declare
  136.   2   xids sys.xid_array;
  137.   3  begin
  138.   4   xids := sys.xid_array('0A00160094020000');
  139.   5   dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.cascade);
  140.   6  end;
  141.   7  /

  142. PL/SQL procedure successfully completed.

  143. 待执行完毕后查看闪回事务的报告:

  144. SQL> select xid,dependent_xid,backout_mode from dba_flashback_txn_state;

  145. XID              DEPENDENT_XID    BACKOUT_MODE
  146. ---------------- ---------------- ----------------
  147. 0900070068030000                  CASCADE
  148. 0A00160094020000 0900070068030000 CASCADE

  149. 发现事务0900070068030000也被撤销了。

  150. 查看201号员工的薪水:

  151. SQL> select salary from hr.employees where employee_id=201;

  152.     SALARY
  153. ----------
  154.      13000

  155. 果然回到了最初的13000美元。现在所有员工的薪水应该都恢复正常了。

  156. DBMS_FLASHBACK.TRANSACTION_BACKOUT是用一个新的事务执行撤销SQL的,现在应执行commit或rollback命令确认或取消闪回事务的结果,这里使用commit:

  157. SQL> commit;

  158. Commit complete.

  159. 闪回事务至此结束。
复制代码

2014-11-01-C.txt
  1. alter database add supplemental log data;

  2. alter database add supplemental log data (primary key) columns;

  3. alter database add supplemental log data (foreign key) columns;


  4. alter database drop supplemental log data (foreign key) columns;


  5. select xid,dependent_xid,backout_mode from dba_flashback_txn_state;

  6. select  * from  dba_flashback_txn_report;

  7. select   * from flashback_transaction_query  where table_name = 'T5'  order by  start_scn desc;

  8. select distinct xid,start_scn
  9.     from flashback_transaction_query
  10.    where table_owner='HR' and
  11.    table_name='EMPLOYEES' and
  12.    start_timestamp > systimestamp - interval '15' minute
  13.    order by start_scn;



  14. select  * from flashback_transaction_query  where start_Scn=1138443;



  15. declare
  16.    xids sys.xid_array;
  17. begin
  18.     xids := sys.xid_array('080001002E030000');
  19.     dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.cascade);
  20.    end;
  21.    
  22.    select xid,dependent_xid,backout_mode from dba_flashback_txn_state;
复制代码

2014-11-01-D.txt
  1. select   * from flashback_transaction_query  where table_name = 'T10'  order by  start_scn desc;
  2. --0200010044030000

  3. declare
  4.    xids sys.xid_array;
  5. begin
  6.     xids := sys.xid_array('0200010044030000');
  7.     dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.cascade);
  8.    end;
  9.    
  10.    select   * from flashback_transaction_query  where table_name = 'T11'  order by  start_scn desc;
  11. --0100070083020000

  12. declare
  13.    xids sys.xid_array;
  14. begin
  15.     xids := sys.xid_array('0800070031030000');
  16.     dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.nocascade_force);
  17.    end;
  18.    
  19.      select   * from flashback_transaction_query  where table_name = 'T12'  order by  start_scn desc;
  20. --0800070031030000

  21. declare
  22.    xids sys.xid_array;
  23. begin
  24.     xids := sys.xid_array('0800070031030000');
  25.     dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.nonconflict_only);
  26.    end;
复制代码

2014-11-01-E.txt
  1. create tablespace tbsfda_2  datafile size 30M ;

  2. alter flashback archive fda1 add tablespace   tbsfda_2   quota  10G;


  3. alter flashback archive fda1    modify    retention 5 year;

  4. create flashback archive  fda1
  5. tablespace tbsfda quota 10G retention  1 year;

  6. select  * from dict where table_name like '%FLASHBACK%';

  7. select  * from DBA_FLASHBACK_ARCHIVE;

  8. select  * from DBA_FLASHBACK_ARCHIVE_TABLES;

  9. select  * from DBA_FLASHBACK_ARCHIVE_TS;

  10. alter flashback archive fda1 set default;

  11. create flashback archive  fda2
  12. tablespace tbsfda quota 10G retention  2 year;


  13. select  * from DBA_FLASHBACK_ARCHIVE;

  14. select  * from DBA_FLASHBACK_ARCHIVE_TABLES;

  15. select  * from DBA_FLASHBACK_ARCHIVE_TS;


  16. grant  flashback archive   on  fda1   to  hr;

  17.   grant  flashback archive   on  fda2   to  hr;
  18.   
  19.   alter flashback archive fda1 purge  before   timestamp   systimestamp;
  20.   
  21.   drop flashback archive fda1;
复制代码

2014-11-01-F.sql
  1. select  * from dba_flashback_archive;

  2. select * from dba_flashback_archive_tables;

  3. select * from dba_flashback_archive_ts;

  4. select  * from hr.SYS_FBA_HIST_74620;

  5. select  * from  hr.SYS_FBA_HIST_74620;

  6. select * from dba_tables  t where t.tablespace_name='TBSFDA';


  7. begin
  8.     dbms_flashback_archive.disassociate_fba('HR','FDA3');
  9. end;

  10. select * from hr.SYS_FBA_HIST_74642;

  11. update hr.SYS_FBA_HIST_74642   set a=4;

  12. commit;

  13. begin
  14.     dbms_flashback_archive.reassociate_fba('HR','FDA3');
  15.    
  16. end;
复制代码

2014-11-01-G.sql
  1. SELECT s.sid, p.spid, s.client_info
  2.   FROM v$process p, v$session s
  3. WHERE p.addr = s.paddr
  4.   AND CLIENT_INFO LIKE '%AAA%';


  5. select * from v_$session_longops l where l.SID=200;
复制代码

t05310.sql
  1. undefine s1
  2. create table &s1( id number ) ;
  3. ! sleep 5
  4. insert into &&s1 values (1) ;
  5. insert into &&s1 values (2) ;
  6. insert into &&s1 values (3) ;
  7. commit;
  8. update &&s1 set id=11 where id=1;
  9. update &&s1 set id=22 where id=2;
  10. update &&s1 set id=33 where id=3;
  11. commit;
  12. update &&s1 set id=222 where id=22;
  13. update &&s1 set id=333 where id=33;
  14. commit;
  15. update &&s1 set id=22 where id=222;
  16. commit;
复制代码



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 05:35 , Processed in 0.073900 second(s), 24 queries .

快速回复 返回顶部 返回列表