第55次和第56、57、58次：2014-10-28，2014-10-30，2014-11-01

botang · 发表于 2014-11-1 20:26:34

本帖最后由 botang 于 2014-11-27 10:13 编辑

2014-11-01-A.txt

select * from v$flashback_database_stat;
select * from v$fixed_table where name like '%USAGE%';
select * from V$FLASH_RECOVERY_AREA_USAGE;
select * from v$restore_point;
create restore point rsp1 ;
select * from v$restore_point;
select flashback_on from v$database;
select * from v$flashback_database_log;
create restore point rsp2 guarantee flashback database;
select * from v$restore_point;
select * from v$flashback_database_logfile;

复制代码

2014-11-01-B.txt

若是管理员决定撤销某个或某些事务，Oracle提供一个专门用来撤销事务的工具——闪回事务。
闪回事务又名撤销事务（Transaction Backout），能够撤销一个或多个事务的修改，其功能由一个名为DBMS_FLASHBACK.TRANSACTION_BACKOUT的存储过程实现。该存储过程的工作原理是自动分析补充重做日志和undo ，挖掘出变更前的值undo sql用以构建撤销SQL（Undo SQL），然后执行撤销SQL最后达到撤销事务的目的。
为了该功能可以正常使用，至少需要事先启用主键补充日志。另外，为了能够跟踪外键依赖还需要启用外键补充日志。
在继续讨论此功能前，首先应了解一个概念：事务的依赖性。比如，两个事务TX1和TX2，若符合以下3个条件的任意一个就可以认为TX2依赖TX1：
（1）WAW依赖（Write After Write），即在TX1修改了表的某行之后，TX2又修改了同一行。
（2）主键依赖，即在一张拥有主键的表中TX1首先删除了一行，之后TX2又插入了具有相同主键值的另一行。
（3）外建依赖，即由于TX1的修改（insert或update）而产生了新的可被外键参考的字段值，之后TX2修改（insert或update）外键字段时利用了TX1所产生的字段值。
了解事务依赖性有助于解决在撤销事务时遇到的矛盾，以主键依赖为例，试想若直接将事务TX1撤销并且不理会事务TX2，岂不是会出现主键值重复的行！
TRANSACTION_BACKOUT存储过程的OPTIONS参数就是为了解决事务依赖性问题而存在的，在该参数上管理员可以使用4种撤销事务的方案，假设被撤销的事务是TX1，若其具有依赖事务，则称为TX2：
（1）NOCASCADE，TX1不可以被任何其他事务依赖（即TX2不存在），否则撤销操作报错。
（2）CASCADE，将TX1连同TX2一起撤销。
（3）NOCASCADE_FORCE，忽略TX2，直接执行TX1的撤销SQL将TX1撤销，如果没有约束上的冲突，操作将成功，否则约束报错导致撤销操作失败。
（4）NONCONFILICT_ONLY，在不影响TX2的前提下，撤销TX1的修改。与NOCASCADE_FORCE的不同点在于会首先过滤一下TX1的撤销SQL，确保它们不会作用在TX2修改的行上。
接下来以WAW依赖为例详细说明，比如有一张表的原有数据如下所示，只有3行且没有约束：
ID
----------
1
2
3
接下来先后发起事务TX1和TX2仅修改该表。在事务TX1（更新了3行）执行后其数据变更为：
ID
----------
11
22
33
之后，在事务TX2（更新了两行，第一行没有修改）执行后其数据变更为：
ID
----------
11
222
333
此例为典型的WAW依赖，TX2依赖TX1。
现在计划将事务TX1撤销，那么使用不同的OPTIONS将产生不同的结果。
若采用NOCASCADE结果是抛出错误“ORA-55504: Transaction conflicts in NOCASCADE mode”，表内容依然是：
------
ID
----------
11
22
33
若采用CASCADE，表的内容恢复到TX2和TX1均未执行的状态：
ID
----------
1
2
3
若采用NOCASCADE_FORCE，TX2的结果不受影响，但被TX1修改的第一行回滚了，闪回事务没有尊重TX1的事务原子性。表的内容变为：
ID
----------
1
222
333
也许读者会感到奇怪，根据NOCASCADE_FORCE的定义，会在所有行上执行撤销SQL，那为什么第2和第3行的内容没有回到TX1执行之前呢？原因是此例中撤销SQL的where语句中还包含ID字段的值，这是启用了主键补充日志的结果：
update <表名> set "ID" = '1' where "ID" = '11' and ROWID = <第1行ROWID>;
update <表名> set "ID" = '2' where "ID" = '22' and ROWID = <第2行ROWID>;
update <表名> set "ID" = '3' where "ID" = '33' and ROWID = <第3行ROWID>;
没记错的话第2和第3行的ID字段已经被TX2分别修改为222和333了，所以虽然执行了3条撤销SQL，但只有第1行得到了修改。
若采用NONCONFILICT_ONLY，在此例中将产生与NOCASCADE_FORCE一样的结果：
ID
----------
1
222
333
读者需要明白本情况中的撤销SQL应该只有一条：
update <表名> set "ID" = '1' where "ID" = '11' and ROWID = <第1行ROWID>;
虽然最后的结果是相同的，但是与NOCASCADE_FORCE所做的尝试是不同的，和TX2有关的对第2行、第3行的更改命令首先被过滤了。试想若在事务TX2之后还有一个事务TX3又将第3行的ID字段改回33，再使用NOCASCADE_FORCE和NONCONFILICT_ ONLY将TX1闪回，结果将会怎样。
使用DBMS_FLASHBACK.TRANSACTION_BACKOUT的步骤如下：
（1）将需要撤销的事务的事务号或事务名载入对应的VARRAY集合变量。
（2）以NOCASCADE方式调用TRANSACTION_BACKOUT。如果报错，再从另外3种方式中选择一个调用BACKOUT_TRANSACTION。
（3）查看闪回事务操作的报告。
（4）最后决定提交或回滚。
下面是一个展示闪回事务战斗力的例子，作为本节的结尾。
首先确认一下201号员工的薪水是13000美元：
SQL> select salary from hr.employees where employee_id=201;
SALARY
------
13000
然后，将全体员工的工资涨500 %，这是一次人为错误，201号员工的收入变为78000美元了：
SQL> update hr.employees set salary=salary*5;
107 rows updated.
SQL> commit;
Commit complete.
紧接着，人事管理应用发出一个正常的操作将201号员工的工资上浮10 %，这里笔者用SQL*Plus模拟HR应用：
SQL> update hr.employees set salary=salary*1.1 where employee_id=201;
1 row updated.
SQL> commit;
Commit complete.
显然HR应用的本意是让201号员工获得13000美元的110%，即14300美元月薪，但是经过前一次错误update的修改，如今该员工的薪水是85800美元：
SQL> select salary from hr.employees where employee_id=201;
SALARY
----------
71500
不久之后，工作人员发现所有员工的薪水高得反常，管理员受理之后通过闪回事务查询查询发现最近（15分钟之内）在hr.employees表上的事务有两个：
SQL> select distinct xid,commit_scn
2 from flashback_transaction_query
3 where table_owner='HR' and
4 table_name='EMPLOYEES' and
5 commit_timestamp > systimestamp - interval '15' minute
6 order by commit_scn;
XID COMMIT_SCN
---------------- ----------
0A00160094020000 1277129
0900070068030000 1277301
然后再利用闪回事务查询观察FLASHBACK_TRANSACTION_QUERY.UNDO_SQL字段，了解到COMMIT_SCN号是1277129、事务号为0A00160094020000的事务很不正常，不但更新了所有员工的SALARY，而且金额太大（因为撤销SQL中的SALARY很低，而当前SALARY很高）：
SQL> select undo_sql from flashback_transaction_query
2 where commit_scn='1277129';
UNDO_SQL
----------------------------------------------------------------------------
update "HR"."EMPLOYEES" set "SALARY" = '3000' where ROWID = 'AAAR5pAAFAAAADPABh';
update "HR"."EMPLOYEES" set "SALARY" = '3100' where ROWID = 'AAAR5pAAFAAAADPABg';
update "HR"."EMPLOYEES" set "SALARY" = '2800' where ROWID = 'AAAR5pAAFAAAADPABf';
update "HR"."EMPLOYEES" set "SALARY" = '3200' where ROWID = 'AAAR5pAAFAAAADPABe';
update "HR"."EMPLOYEES" set "SALARY" = '3900' where ROWID = 'AAAR5pAAFAAAADPABd';
update "HR"."EMPLOYEES" set "SALARY" = '4000' where ROWID = 'AAAR5pAAFAAAADPABc';
update "HR"."EMPLOYEES" set "SALARY" = '2500' where ROWID = 'AAAR5pAAFAAAADPABb';
...省略100行
注意撤销SQL中赋予SALARY字段的值正是事务0A00160094020000执行前的值。现决定用TRANSACTION_BACKOUT闪回该事务，使SALARY恢复正常值：
SQL> declare
2 v_xids sys.xid_array;
3 begin
4 v_xids := sys.xid_array('0A00160094020000');
5 dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.nocascade);
6 end;
7 /
第5行中存储过程的第二个参数是一个容纳事务号的VARRAY集合变量，第一个参数表示VARRAY内事务号的数量，本例中只有一个事务需要撤销，所以等于1。
因为WAW依赖性，这样执行会失败：
declare
*
ERROR at line 1:
ORA-55504: Transaction conflicts in NOCASCADE mode
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5
现在最符合逻辑的做法是使用casecade方式将两个事务全部撤销，修改options参数后重新执行：
SQL> declare
2 xids sys.xid_array;
3 begin
4 xids := sys.xid_array('0A00160094020000');
5 dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.cascade);
6 end;
7 /
PL/SQL procedure successfully completed.
待执行完毕后查看闪回事务的报告：
SQL> select xid,dependent_xid,backout_mode from dba_flashback_txn_state;
XID DEPENDENT_XID BACKOUT_MODE
---------------- ---------------- ----------------
0900070068030000 CASCADE
0A00160094020000 0900070068030000 CASCADE
发现事务0900070068030000也被撤销了。
查看201号员工的薪水：
SQL> select salary from hr.employees where employee_id=201;
SALARY
----------
13000
果然回到了最初的13000美元。现在所有员工的薪水应该都恢复正常了。
DBMS_FLASHBACK.TRANSACTION_BACKOUT是用一个新的事务执行撤销SQL的，现在应执行commit或rollback命令确认或取消闪回事务的结果，这里使用commit：
SQL> commit;
Commit complete.
闪回事务至此结束。

复制代码

2014-11-01-C.txt

alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;
alter database add supplemental log data (foreign key) columns;
alter database drop supplemental log data (foreign key) columns;
select xid,dependent_xid,backout_mode from dba_flashback_txn_state;
select * from dba_flashback_txn_report;
select * from flashback_transaction_query where table_name = 'T5' order by start_scn desc;
select distinct xid,start_scn
from flashback_transaction_query
where table_owner='HR' and
table_name='EMPLOYEES' and
start_timestamp > systimestamp - interval '15' minute
order by start_scn;
select * from flashback_transaction_query where start_Scn=1138443;
declare
xids sys.xid_array;
begin
xids := sys.xid_array('080001002E030000');
dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.cascade);
end;
select xid,dependent_xid,backout_mode from dba_flashback_txn_state;

复制代码

2014-11-01-D.txt

select * from flashback_transaction_query where table_name = 'T10' order by start_scn desc;
--0200010044030000
declare
xids sys.xid_array;
begin
xids := sys.xid_array('0200010044030000');
dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.cascade);
end;
select * from flashback_transaction_query where table_name = 'T11' order by start_scn desc;
--0100070083020000
declare
xids sys.xid_array;
begin
xids := sys.xid_array('0800070031030000');
dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.nocascade_force);
end;
select * from flashback_transaction_query where table_name = 'T12' order by start_scn desc;
--0800070031030000
declare
xids sys.xid_array;
begin
xids := sys.xid_array('0800070031030000');
dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.nonconflict_only);
end;

复制代码

2014-11-01-E.txt

create tablespace tbsfda_2 datafile size 30M ;
alter flashback archive fda1 add tablespace tbsfda_2 quota 10G;
alter flashback archive fda1 modify retention 5 year;
create flashback archive fda1
tablespace tbsfda quota 10G retention 1 year;
select * from dict where table_name like '%FLASHBACK%';
select * from DBA_FLASHBACK_ARCHIVE;
select * from DBA_FLASHBACK_ARCHIVE_TABLES;
select * from DBA_FLASHBACK_ARCHIVE_TS;
alter flashback archive fda1 set default;
create flashback archive fda2
tablespace tbsfda quota 10G retention 2 year;
select * from DBA_FLASHBACK_ARCHIVE;
select * from DBA_FLASHBACK_ARCHIVE_TABLES;
select * from DBA_FLASHBACK_ARCHIVE_TS;
grant flashback archive on fda1 to hr;
grant flashback archive on fda2 to hr;
alter flashback archive fda1 purge before timestamp systimestamp;
drop flashback archive fda1;

复制代码

2014-11-01-F.sql

select * from dba_flashback_archive;
select * from dba_flashback_archive_tables;
select * from dba_flashback_archive_ts;
select * from hr.SYS_FBA_HIST_74620;
select * from hr.SYS_FBA_HIST_74620;
select * from dba_tables t where t.tablespace_name='TBSFDA';
begin
dbms_flashback_archive.disassociate_fba('HR','FDA3');
end;
select * from hr.SYS_FBA_HIST_74642;
update hr.SYS_FBA_HIST_74642 set a=4;
commit;
begin
dbms_flashback_archive.reassociate_fba('HR','FDA3');
end;

复制代码

2014-11-01-G.sql

SELECT s.sid, p.spid, s.client_info
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND CLIENT_INFO LIKE '%AAA%';
select * from v_$session_longops l where l.SID=200;

复制代码

t05310.sql

undefine s1
create table &s1( id number ) ;
! sleep 5
insert into &&s1 values (1) ;
insert into &&s1 values (2) ;
insert into &&s1 values (3) ;
commit;
update &&s1 set id=11 where id=1;
update &&s1 set id=22 where id=2;
update &&s1 set id=33 where id=3;
commit;
update &&s1 set id=222 where id=22;
update &&s1 set id=333 where id=33;
commit;
update &&s1 set id=22 where id=222;
commit;

复制代码