OCP读书笔记(10)

枫叶飞翔

　　使用闪回技术查询数据
　　闪回查询：就是查询表在过去某个时间点的数据，所用到的技术就是undo数据



SQL> conn scott/tiger
创建测试表
SQL> create table test as select * from dept;
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
插入数据
SQL> select sysdate from dual;
SQL> insert into test select * from test;
SQL> commit;
删除测试表
SQL> select sysdate from dual;
SQL> delete test;
SQL> commit;
直接查询
SQL> select sysdate from dual;
SQL> select * from test;
no rows selected
闪回查询
select * from test as of timestamp to_timestamp('2013-06-16 15:00:47','yyyy-mm-dd hh24:mi:ss');
SQL> conn /as sysdba
SQL> show parameter undo_retention----闪回查询成功与否与此参数有密切关系
　　
　　可以使用scn对表进行闪回查询：
首先通过日志挖掘，找出想要闪回的scn
对dml操作进行挖掘：



SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;---查看是否开启了补充日志的功能
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
begin
dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/orcl/redo03.log',options=>dbms_logmnr.new);
end;
/
begin
dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
end;
/
select username, scn, timestamp, sql_redo from v$logmnr_contents where seg_name='TEST';
exec dbms_logmnr.end_logmnr;
select * from scott.test as of scn
　　
　　如果从当前点到闪回点之间对表进行了ddl操作，闪回不成功如：



SQL> conn scott/tiger
SQL> drop table test purge;
SQL> create table test as select * from dept;
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select sysdate from dual;
SQL> select * from test;
SQL> insert into test select * from test;
SQL> commit;
SQL> select sysdate from dual;
SQL> select * from test;
SQL> select * from test as of timestamp to_timestamp('2012-08-16 09:01:44','yyyy-mm-dd hh24:mi:ss');
SQL> truncate table test; --ddl操作
SQL> select * from test as of timestamp to_timestamp('2012-08-16 09:01:44','yyyy-mm-dd hh24:mi:ss'); --error
　　
　　闪回版本查询
　　什么是版本？
　　所谓版本指的是每次事务所引起的数据行的变化情况，每次变化就是一个版本oracle提供了闪回版本查询，从而可以让我们很清楚的看到数据行的整个变化过程。这里的变化都是已经提交了的事务引起的变化，没有提交的事务引起的变化不会显示
　　



conn scott/tiger
create table test(id number,name varchar2(10));
insert into test values(1,'abc');
insert into test values(2,'def');
commit;
update test set name='ccc' where id=1;
commit;
delete test where id=2;
commit;
update test set name='ddd' where id=1;
commit;
update test set name='eee' where id=1;
commit;
select versions_starttime vst,versions_endtime vet,versions_startscn vsn,versions_endscn ven,versions_xid vxid,versions_operation vop,id,name
from test versions between scn minvalue and maxvalue
where id = 1;
　　
　　闪回事务查询
　　当对数据库中的表进行dml操作时（而且是提交的），oracle都会将对这些表的事务记录到数据中的一个数据字典中: flashback_transaction_query，默认情况下，普通用户没有查询的权限，此表提供了undo sql，可以通过undosql将错误的事务回退



select xid, operation, undo_sql
from flashback_transaction_query
where table_name='TEST' and table_owner='SCOTT'
　　闪回版本和闪回事务可以配合使用



通过闪回版本发现错误的事务，然后通过闪回事务找到undosql将其恢复：
select versions_starttime vst, versions_endtime vet, versions_startscn vsn, versions_endscn ven, versions_xid vxid, versions_operation vop, id, name
from test versions between scn minvalue and maxvalue
where id=2;
发现有一个错误的事务将id=2这一行删除了
通过闪回事务找到undosql：
select XID,OPERATION,UNDO_SQL
from flashback_transaction_query
where table_name='TEST' and table_owner='SCOTT' and xid=hextoraw('04002B00D9000000')
使用unodsql恢复错误的事务
insert into "SCOTT"."TEST"("ID","NAME") values ('2','def');
commit;
SQL> select * from test where id=2;
　　闪回表：将表闪回到过去的某个时间点，使用的技术是undo数据
　　闪回表的步骤：
　　1.打开表的行移动
2.确定时间点
3.执行闪回



SQL> conn scott/tiger
SQL> create table test as select * from dept;
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> alter table test enable row movement;
SQL> select sysdate from dual;
SQL> select * from test;
SQL> insert into test select * from test;
SQL> commit;
SQL> select sysdate from dual;
SQL> select * from test;
SQL> delete test;
SQL> commit;
SQL> select sysdate from dual;
SQL> select * from test;
SQL> flashback table test to timestamp to_timestamp('2013-06-16 15:17:20','yyyy-mm-dd hh24:mi:ss');
SQL> select * from test;
SQL> flashback table test to timestamp to_timestamp('2013-06-16 15:17:53','yyyy-mm-dd hh24:mi:ss');
SQL> select * from test;
SQL> flashback table test to timestamp to_timestamp('2013-06-16 15:18:32','yyyy-mm-dd hh24:mi:ss');
SQL> select * from test;
　　如果从当前点到闪回点之间对表发生了ddl操作，闪回将会失败



SQL> truncate table test;
SQL> flashback table test to timestamp to_timestamp('2013-06-16 15:17:53','yyyy-mm-dd hh24:mi:ss');
　　当闪回表的时候，索引的数据也会被闪回，但是表的统计信息不会闪回:



SQL> drop table test purge;
SQL> create table test as select * from emp;
SQL> create index ind_test on test(empno);
SQL> alter table test enable row movement;
SQL> exec dbms_stats.gather_table_stats('scott','test');
SQL> analyze index ind_test validate structure;
SQL> select sysdate from dual;
SQL> select lf_rows, del_lf_rows from index_stats;
SQL> select count(*) from test;
SQL> delete test;
SQL> commit;
SQL> analyze index ind_test validate structure;
SQL> exec dbms_stats.gather_table_stats('scott','test');
SQL> select lf_rows, del_lf_rows from index_stats;
SQL> select count(*) from test;
SQL> flashback table test to timestamp to_timestamp('2013-06-16 15:24:28','yyyy-mm-dd hh24:mi:ss');
SQL> select count(*) from test;
SQL> analyze index ind_test validate structure;
SQL> select lf_rows, del_lf_rows from index_stats;
SQL> select NUM_ROWS, BLOCKS from user_tables where table_name='TEST';
SQL> exec dbms_stats.gather_table_stats('scott','test');
SQL> select NUM_ROWS, BLOCKS from user_tables where table_name='TEST';
　　闪回事务处理取消
　　在oracle database 10g中引入了两个闪回新特性，即闪回版本查询和闪回事务查询。允许撤销跟随在数据库中逻辑错误后面的数据所产生的错误变化，在一个可疑的数据错误之后，首先使用闪回版本查询来确定属于该错误事务处理的表行的版本，在确定出错的事务处理后，使用闪回事务处理查询审计该事务处理所做的所有变化，使用由闪回事务处理查询的UNDO_SQL列提供的SQL代码，撤销由错误事务处理所做的变化，这样，闪回事务处理提供了强大的撤销数据库中逻辑错误的方法
　　在oracle database 11g中，可以使用新的闪回事务处理取消特性完成必须由闪回版本查询和闪回事务查询共同完成的任务，通常，一个数据错误会引起其他依赖事务处理使用有错的数据执行，闪回事务处理取消是一个新的逻辑恢复特性，它使你返回目标事务处理，并使依赖事务处理回到原来的状态，闪回事务处理取消特性识别并修正内部的事务处理以及依赖的的事务处理，从而彻底的撤销逻辑数据错误的作用，撤销插入、更新和删除操作的完整集合确保了事务处理的原子性和一致性的原理被维护，这样，当数据库联机时，通过执行一个取消命令（单独运行transaction_backout过程），就可以执行数据库的逻辑恢复，如果正在使用database control，点击一下就可以取消事务处理，依赖事务处理与目标事务处理可能有以下几种关系：
　　1.写后写（write-after-write，waw）关系，依赖事务处理更改了由目标事务处理更改的相同数据
2. 主键约束关系：依赖事务处理插入与目标事务处理删除的相同主键
　　闪回事务处理取消的先决条件



必须首先开启数据库的补充日志（supplemental logging），然后给要使用闪回事务处理特性的用户授予特定的权限，为了启用数据库补充日志使用以下命令：
alter database add supplemental log data;
alter database add supplemental log data(primary key) columns;
除了启用补充日志外，还要给使用闪回事务处理取消特性的用户授予以下权限：
grant execute on dbms_flashback to hr;
grant select any transaction to hr;
　　用户必须有flashback权限，可以通过授予DBMS_FLASHBACK表的execute权限来授予。另外，用户还需要select any transaction权限
　　如果用户想要取消属于自己模式中的事务处理，则无需增加权限，但是，如果某个用户想要取消其他模式的事务处理，还必须授予事务处理取消影响的所有表的DML权限
　　2.使用TRANSACTION_BACKOUT过程
　　补偿事务处理的思想对于事务处理取消特性至关重要，通过使用撤销数据，一个补偿事务处理可取消一个或多个事务处理，使用DBMS_FLASHBACK包的transaction_backout过程很容易回退不想要的事务处理。
　　transaction_backout过程有4个参数，如：
numtxns：此参数为被取消事务处理的数量
names：此参数为被取消事务处理的列表（按名字排序）
timehint：如果你按名标识事务处理，则可以提供一个时间提示，如在事务处理开始前的某个时间
options：该参数指定某个事务处理及其依赖的事务处理被取消的顺序



conn /as sysdba
SQL> SELECT LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
数据库必须为归档模式，并且开启日志的增补功能
alter database add supplemental log data;
alter database add supplemental log data(primary key) columns;
grant execute on dbms_flashback to scott;
grant select any transaction to scott;
conn scott/tiger
create table t_flash_trans (id number, name varchar2(30));
insert into t_flash_trans values (1, 'a');
insert into t_flash_trans values (2, 'b');
commit;
insert into t_flash_trans select 3 + rownum, 'c' from tab;
commit;
select * from t_flash_trans;
conn /as sysdba
ALTER SYSTEM ARCHIVE LOG CURRENT;
select xid, table_name, operation, undo_sql from flashback_transaction_query where lower(table_name) = 't_flash_trans';
对于T_FLASH_TRANS表存在两个事务,分别插入了2条和5条记录，下面撤销掉第一个事务：
conn / as sysdba
declare
v_xid xid_array;
begin
v_xid := sys.xid_array('09001F009C020000');
dbms_flashback.transaction_backout(1, v_xid);
end;
/
下面看看T_FLASH_TRANS表中的数据：
conn scott/tiger
select * from scott.t_flash_trans;
　　写后写
　　如果撤销一个事务的时候发现被撤销事务和其他事务级联，会有多种情况



conn scott/tiger
drop table t_flash_trans purge;
create table t_flash_trans (id number, name varchar2(30));
insert into t_flash_trans values (1, 'a');
insert into t_flash_trans values (2, 'b');
commit;
select * from t_flash_trans;
update t_flash_trans set name = name || '1';
insert into t_flash_trans values (3, 'c');
commit;
select * from t_flash_trans;
update t_flash_trans set name = name || '2' where id = 1;
insert into t_flash_trans values (4, 'd');
commit;
select * from t_flash_trans;
conn /as sysdba
select xid, table_name, operation, undo_sql from flashback_transaction_query where table_name= 'T_FLASH_TRANS';
　　下面对第二个事物撤销：



declare
v_xid xid_array;
begin
v_xid := sys.xid_array('0200170096020000');
dbms_flashback.transaction_backout(1, v_xid);
end;
/
　　撤销出错，这是由于要撤销第二个事务，这个事务对ID为1的记录进行了更新，但是在随后的事务中又对这条记录进行了修改也就是说这个事务是后面事务的基础，这时没有办法仅仅撤销前面的事务而不影响后面的事务
　　对于这种情况，Oracle有三种不同的解决方法
　　1. CASCADE方式：



DECLARE
V_XID XID_ARRAY;
BEGIN
V_XID := SYS.XID_ARRAY('0200170096020000');
DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.CASCADE);
END;
/
SELECT * FROM SCOTT.T_FLASH_TRANS;
　　使用CASCADE方式会根据事务提交的相反顺序，依次撤销所有关联的事务
在这个例子中，Oracle不但撤销了第一个UPDATE事务,而且撤销了随后的UPDATE和INSERT事务
　　2. NONCONFLICT_ONLY
　　TRANSACTION_BACKOUT过程是事务性的，也就是说，如果发现撤销事务后，得到的不是预期的结果，可以通过ROLLBACK来回滚事务撤销操作：
ROLLBACK;



SQL> SELECT * FROM SCOTT.T_FLASH_TRANS;
DECLARE
V_XID XID_ARRAY;
BEGIN
V_XID := SYS.XID_ARRAY('0200170096020000');
DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.NONCONFLICT_ONLY);
END;
/
　　对于NONCONFLICT_ONLY方式，Oracle会回滚指定事务中没有被关联的部分，比如这个例子中，UPDATE语句被后面的事务关联，但是INSERT语句并没有被关联，因此撤销了事务中的INSERT语句，对于UPDATE语句，只有关联的那条数据没有被撤销，而没有被关联的数据则被撤销成功
　　3. NOCASCADE_FORCE：



SQL> ROLLBACK;
SQL> SELECT * FROM SCOTT.T_FLASH_TRANS;
DECLARE
V_XID XID_ARRAY;
BEGIN
V_XID := SYS.XID_ARRAY('0200170096020000');
DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.NOCASCADE_FORCE);
END;
/
SQL> SELECT * FROM SCOTT.T_FLASH_TRANS;
　　根据文档上的描述，采用这种方式的撤销后，ID为1的记录NAME应该为A而不是A12，现在的这种结果与NONCONFLICT_ONLY方式没有差别，怀疑目前的结果是个bug，不过实际如何只能等Oracle推出补丁或者声明bug才能了解
　　撤销事务与外键约束的关系
　　建立测试环境：



conn scott/tiger
create table t_primary (id number primary key, name varchar2(30));
create table t_foreign (fid number, foreign key (fid) references t_primary);
insert into t_primary values (1, 'a');
commit;
update t_primary set name = 'a1';
insert into t_primary values (2, 'b');
commit;
insert into t_foreign values (2);
insert into t_primary values (3, 'c');
commit;
conn / as sysdba
select xid, operation, undo_sql from flashback_transaction_query where table_name = 'T_PRIMARY';
　　下面开始进行撤销测试：



DECLARE
V_XID XID_ARRAY;
BEGIN
V_XID := SYS.XID_ARRAY('08000F0086030000');
DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
END;
/
　　由于要撤销的事务中包括对主表记录的插入，而这条记录被子表引用，因此撤销肯定会出现错误，尝试使用CASCADE方式进行撤销：



DECLARE
V_XID XID_ARRAY;
BEGIN
V_XID := SYS.XID_ARRAY('08000F0086030000');
DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.CASCADE);
END;
/
　　CASCADE方式只对关联事务有效，而对这种主外键关系无效，因此，如果撤销事务操作会违反主外键约束那么即使使用CASCADE方式也没有作用。同样的，另外两种方式NONCONFLICT_ONLY和NOCASCADE_FORCE也没有作用：



DECLARE
V_XID XID_ARRAY;
BEGIN
V_XID := SYS.XID_ARRAY('08000F0086030000');
DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.NONCONFLICT_ONLY);
END;
/
DECLARE
V_XID XID_ARRAY;
BEGIN
V_XID := SYS.XID_ARRAY('08000F0086030000');
DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.NOCASCADE_FORCE);
END;
/
　　唯一的方法是包括参考主键信息的外键事务一起进行撤销：



select xid, operation, undo_sql from flashback_transaction_query where table_name = 'T_FOREIGN';
DECLARE
V_XID XID_ARRAY;
BEGIN
V_XID := SYS.XID_ARRAY('08000F0086030000', '06000200AB020000');
DBMS_FLASHBACK.TRANSACTION_BACKOUT(2, V_XID);
END;
/
SELECT * FROM SCOTT.T_PRIMARY;
SELECT * FROM SCOTT.T_FOREIGN;