活动15/16次（2018-05-05星期六上下午）

botang · 发表于 2018-5-5 09:48:40

改动磁盘组的属性：

[oracle@station90 ~]$ . oraenv
ORACLE_SID = [orcl] ? +ASM
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid is /u01/app/oracle
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sat May 5 09:45:45 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysasm
Connected.
SQL> alter diskgroup highdata set attribute 'compatible.asm'='11.1';
Diskgroup altered.
SQL> alter diskgroup highdata set attribute 'compatible.asm'='10.2';
alter diskgroup highdata set attribute 'compatible.asm'='10.2'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.asm
ORA-15238: 10.2 is not a valid value for attribute compatible.asm
ORA-15243: 10.2.0.0.0 is not a valid version number

复制代码

创建2M AU的磁盘组：

create diskgroup highdata high redundancy
failgroup fg1 disk 'ORCL:ASMDISK1' name highdata_0001
failgroup fg2 disk 'ORCL:ASMDISK2' name highdata_0002
failgroup fg3 disk 'ORCL:ASMDISK3' name highdata_0003
attribute 'au_size'='2M';

复制代码

ASM快速镜像重同步：
1. 要数据库实例配合：

SQL> alter diskgroup data offline disk data_0000 drop after 1h;
alter diskgroup data offline disk data_0000 drop after 1h
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15283: ASM operation requires compatible.rdbms of 11.1.0.0.0 or higher
SQL> alter diskgroup data set attribute 'compatible.rdbms'='11.1.0.0.0';
Diskgroup altered.
SQL> alter diskgroup data offline disk data_0000 drop after 1h;
Diskgroup altered.

复制代码

模拟盘被拔出，清理：

[root@station90 桌面]# chmod 000 /dev/raw/raw7
[root@station90 桌面]# ls -l /dev/raw/raw7
c--------- 1 oracle oinstall 162, 7 5月 5 10:33 /dev/raw/raw7
[root@station90 桌面]# chown root:root /dev/raw/raw7
[root@station90 桌面]#

同时打开另一窗口，模拟工作负载：

SQL> conn / as sysdba
Connected.
SQL> create table hr.tbig as select * from dba_source;
Table created.
SQL> conn hr/oracle_4U
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> insert into tbig select * from tbig;
623407 rows created.
SQL> commit;
Commit complete.
SQL> insert into tbig select * from tbig;
1246814 rows created.
SQL> commit;
Commit complete.
SQL>

复制代码

模拟磁盘重新插回去：
[root@station90 桌面]# chown oracle

install /dev/raw/raw7
[root@station90 桌面]# chmod 775 /dev/raw/raw7
[root@station90 桌面]#

结论：在一个小时之内不会有任何重平衡操作：

watch -n 0.3 "ps aux | grep arb"

迅速上线，没有任何重平衡操作：

快速镜像重同步，由于硬件大故障无法online磁盘时的解决办法：
（破坏磁盘的办法：dd if=/dev/zero of=/dev/raw/raw7 bs=100M；或者 dd if=/dev/zero of=/dev/oracleasm/disks/ASMDISK01 bs=100M；）

[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sat May 5 14:46:25 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysasm
Connected.
SQL> alter diskgroup data offline disk data_0000 drop after 1h;
Diskgroup altered.
SQL> alter diskgroup data online disk data_0000 ;
alter diskgroup data online disk data_0000
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15281: not all specified disks were brought ONLINE
ORA-15284: ASM terminated ALTER DISKGROUP ONLINE
ORA-15282: ASM disk "DATA_0000" is not visible cluster-wide
SQL> alter diskgroup data drop disk data_0000 ;
alter diskgroup data drop disk data_0000
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15084: ASM disk "DATA_0000" is offline and cannot be dropped.
SQL> alter diskgroup data drop disk data_0000 force ;
Diskgroup altered.
SQL> alter diskgroup data add failgroup data_0000 disk '/dev/raw/raw7' name data_0000 ;
Diskgroup altered.
SQL>

复制代码

在线扩大磁盘组的大小（通过扩大磁盘）：

SQL*Plus: Release 11.2.0.3.0 Production on Sat May 12 10:49:31 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysasm
Connected.
SQL> alter diskgroup data resize disk data_0000;
Diskgroup altered.
SQL> alter diskgroup data resize disk data_0001 disk data_0002 disk data_0003 disk data_0004 disk data_0005 ;
Diskgroup altered.
SQL> alter diskgroup fra resize disk fra_0000 disk fra_0001 disk fra_0002 ;
Diskgroup altered.
SQL>

复制代码

----------------------------------------------------------------------------------------------------------------------------
闪回事务查询，在flashback_transaction_query中，没提交的事务也看得到，因为这个视图反映的是整个UNDO表空间基于时间和事务把ROWID精细整理过的样子。

Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn hr/oracle_4U
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> select salary from employees where employee_id=100;
SALARY
----------
24000
SQL> update employees set salary=30000 where employee_id=100;
1 row updated.

复制代码

闪回版本查询，基于flashback_transaction_query，其中versions_startscn就是flashback_transaction_query中的commit_scn:

由于闪回版本查询的特点，它往往是闪回表的向导。

-----------------------------------------------------------------------------------------------
在进行版本查询时，em把versions_startscn都减1，所以图形界面查出来的每一行都没提交：

----------------------------------------------------------------------------
as of闪回、versions闪回和闪回表都能跨过添加型的DDL：

实验过程：

SQL> create table t05310_a ( a number ) ;
Table created.
SQL> insert into t05310_a values ( 1 ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> update t05310_a set a=2 ;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table t05310_a add ( b varchar2(20)) ;
Table altered.
SQL> select * from t05310_a;
A B
---------- --------------------
2
SQL> update t05310_a set a=3 , b='c' ;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_a set a=4 , b='d' ;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t05310_a;
A B
---------- --------------------
4 d
SQL>

复制代码

但是as of闪回、versions闪回和闪回表不能跨过删除型的DDL：

实验过程：

SQL> alter table t05310_a drop (b) ;
Table altered.
SQL>

复制代码

SQL> select a from t05310_a as of timestamp systimestamp-5/1440 ;
select a from t05310_a as of timestamp systimestamp-5/1440
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
SQL> select a from t05310_a as of timestamp systimestamp-1/1440 ;
A
----------
4
SQL> select a from t05310_a as of timestamp systimestamp-2/1440 ;
A
----------
4
SQL> select a from t05310_a as of timestamp systimestamp-3/1440 ;
A
----------
4
SQL> select a from t05310_a as of timestamp systimestamp-4/1440 ;
select a from t05310_a as of timestamp systimestamp-4/1440
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
SQL> select a from t05310_a as of timestamp systimestamp-4/1440 ;
select a from t05310_a as of timestamp systimestamp-4/1440
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
SQL> select a from t05310_a as of timestamp systimestamp-4/1440 ;
select a from t05310_a as of timestamp systimestamp-4/1440
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
SQL> alter table t05310_a enable row movement;
Table altered.
SQL> select a from t05310_a as of timestamp systimestamp-4/1440 ;
select a from t05310_a as of timestamp systimestamp-4/1440
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
SQL> flashback table t05310_a to timestamp systimestamp-4/1440;
flashback table t05310_a to timestamp systimestamp-4/1440
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
SQL>

复制代码

Flashback Transaction Query实际上是有包含system undo段的信息，我们的DDL表现成对数据字典表的DML，里头显示的是对象号：

select o.object_id
from dba_objects o where o.object_name='T05310_A';
--74613
select d.SUPPLEMENTAL_LOG_DATA_MIN,
d.SUPPLEMENTAL_LOG_DATA_PK,
d.SUPPLEMENTAL_LOG_DATA_UI,
d.SUPPLEMENTAL_LOG_DATA_FK,
d.SUPPLEMENTAL_LOG_DATA_ALL
from v_$database d;
alter database add SUPPLEMENTAL log data;
---hr drop table t05310_a;
select * from flashback_transaction_query f
where f.table_name='TAB' order by 3 desc;

复制代码

undo_sql：
[/code]undo_sql：

insert
into "SYS"."TAB[ DISCUZ_CODE_10
]quot;("OBJ#","DATAOBJ#","TS#","FILE#","BLOCK#","BOBJ#","TAB#","COLS","CLUCOLS","PCTFREE[
DISCUZ_CODE_10 ]quot;,"PCTUSED[ DISCUZ_CODE_10
]quot;,"INITRANS","MAXTRANS","FLAGS","AUDIT[ DISCUZ_CODE_10
]quot;,"ROWCNT","BLKCNT","EMPCNT","AVGSPC","CHNCNT","AVGRLN","AVGSPC_FLB","FLBCNT","ANALYZETIME","SAMPLESIZE","DEGREE","INSTANCES","INTCOLS","KERNELCOLS","PROPERTY","TRIGFLAG","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6")
values
('74613','74613','4','4','650',NULL,NULL,'1',NULL,'10','40','1','255','1073872897','--------------------------------------',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1','1','536870912','0','736',NULL,NULL,NULL,NULL,TO_DATE('05-MAY-18',
'DD-MON-RR'));

复制代码

alter database add SUPPLEMENTAL log data （primary key ）columns;

闪回事务实验用的脚本：

t05310.sql (388 Bytes, 下载次数: 364)

2018-5-5 16:46 上传

点击文件名下载附件

nocascade默认选项实验：

SQL> show user
USER is "HR"
SQL> @/4/t05310
Enter value for s1: tnocascade
old 1: create table &&s1( id number )
new 1: create table tnocascade( id number )
Table created.
old 1: insert into &&s1 values (1)
new 1: insert into tnocascade values (1)
1 row created.
old 1: insert into &&s1 values (2)
new 1: insert into tnocascade values (2)
1 row created.
old 1: insert into &&s1 values (3)
new 1: insert into tnocascade values (3)
1 row created.
Commit complete.
old 1: update &&s1 set id=11 where id=1
new 1: update tnocascade set id=11 where id=1
1 row updated.
old 1: update &&s1 set id=22 where id=2
new 1: update tnocascade set id=22 where id=2
1 row updated.
old 1: update &&s1 set id=33 where id=3
new 1: update tnocascade set id=33 where id=3
1 row updated.
Commit complete.
old 1: update &&s1 set id=222 where id=22
new 1: update tnocascade set id=222 where id=22
1 row updated.
old 1: update &&s1 set id=333 where id=33
new 1: update tnocascade set id=333 where id=33
1 row updated.
Commit complete.
old 1: update &&s1 set id=22 where id=222
new 1: update tnocascade set id=22 where id=222
1 row updated.
Commit complete.
SQL> select * from tnocascade;
ID
----------
11
22
333
SQL>

复制代码

select * from flashback_transaction_query f
where f.table_name='TNOCASCADE' order by start_scn;
begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('0300200096030000'));
end;

复制代码

我们换成cascade选项：

select * from dba_flashback_txn_report;
begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('0300200096030000'),
options => dbms_flashback.cascade);
end;
select * from dba_flashback_txn_report;
commit;
select * from dba_flashback_txn_report;

复制代码

以下是补偿事务报告：
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="0900180098030000">
<TRANSACTION XID="0300200096030000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
      <USQL exec="yes">
      update "HR"."TNOCASCADE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASN3AAEAAAAKNAAC'
      </USQL>
      <USQL exec="yes">
      update "HR"."TNOCASCADE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASN3AAEAAAAKNAAB'
      </USQL>
      <USQL exec="yes">
      update "HR"."TNOCASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASN3AAEAAAAKNAAA'
      </USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
      <TRANSACTION XID="060009007B030000">
      <CHARACTERISTICS>
      </CHARACTERISTICS>
      <UNDO_SQL>
         <USQL exec="yes">
         update "HR"."TNOCASCADE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAASN3AAEAAAAKNAAC'
         </USQL>
         <USQL exec="yes">
         update "HR"."TNOCASCADE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAASN3AAEAAAAKNAAB'
         </USQL>
      </UNDO_SQL>
      <DEPENDENT_XIDS>
         <TRANSACTION XID="05000F003B040000">
         <CHARACTERISTICS>
         </CHARACTERISTICS>
         <UNDO_SQL>
            <USQL exec="yes">
               update "HR"."TNOCASCADE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAASN3AAEAAAAKNAAB'
            </USQL>
         </UNDO_SQL>
         <DEPENDENT_XIDS>
         </DEPENDENT_XIDS>
         </TRANSACTION>
      </DEPENDENT_XIDS>
      </TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>update "HR"."TNOCASCADE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAASN3AAEAAAAKNAAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."TNOCASCADE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAASN3AAEAAAAKNAAC'
</EXEC_USQL>
<EXEC_USQL>update "HR"."TNOCASCADE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAASN3AAEAAAAKNAAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."TNOCASCADE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASN3AAEAAAAKNAAC'
</EXEC_USQL>
<EXEC_USQL>update "HR"."TNOCASCADE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASN3AAEAAAAKNAAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."TNOCASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASN3AAEAAAAKNAAA'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>

提交以后表的结果：

SQL> /
ID
----------
1
2
3

复制代码

--------------------------------------------------
如果用nocascade_force选项，

SQL> @/4/t05310
Enter value for s1: tnocascade_force
old 1: create table &&s1( id number )
new 1: create table tnocascade_force( id number )
Table created.
old 1: insert into &&s1 values (1)
new 1: insert into tnocascade_force values (1)
1 row created.
old 1: insert into &&s1 values (2)
new 1: insert into tnocascade_force values (2)
1 row created.
old 1: insert into &&s1 values (3)
new 1: insert into tnocascade_force values (3)
1 row created.
Commit complete.
old 1: update &&s1 set id=11 where id=1
new 1: update tnocascade_force set id=11 where id=1
1 row updated.
old 1: update &&s1 set id=22 where id=2
new 1: update tnocascade_force set id=22 where id=2
1 row updated.
old 1: update &&s1 set id=33 where id=3
new 1: update tnocascade_force set id=33 where id=3
1 row updated.
Commit complete.
old 1: update &&s1 set id=222 where id=22
new 1: update tnocascade_force set id=222 where id=22
1 row updated.
old 1: update &&s1 set id=333 where id=33
new 1: update tnocascade_force set id=333 where id=33
1 row updated.
Commit complete.
old 1: update &&s1 set id=22 where id=222
new 1: update tnocascade_force set id=22 where id=222
1 row updated.
Commit complete.

复制代码

硬做：

select * from flashback_transaction_query f
where f.table_name='TNOCASCADE_FORCE' order by start_scn;
select * from dba_flashback_txn_report;
begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('0200130094030000'),
options => dbms_flashback.nocascade_force);
end;
select * from dba_flashback_txn_report;
commit;
select * from dba_flashback_txn_report;

复制代码

报告是：
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="09000C009D030000" NAME="_SYS_COMP_TXN_4789146_TIM_1525512887">
<TRANSACTION XID="0200130094030000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
      <USQL exec="yes">
      update "HR"."TNOCASCADE_FORCE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASOBAAEAAAAKVAAC'
      </USQL>
      <USQL exec="yes">
      update "HR"."TNOCASCADE_FORCE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASOBAAEAAAAKVAAB'
      </USQL>
      <USQL exec="yes">
      update "HR"."TNOCASCADE_FORCE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASOBAAEAAAAKVAAA'
      </USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
      <TRANSACTION XID="0500150040040000">
      <CHARACTERISTICS>
      </CHARACTERISTICS>
      <UNDO_SQL>
         <USQL exec="no">
         update "HR"."TNOCASCADE_FORCE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAASOBAAEAAAAKVAAC'
         </USQL>
         <USQL exec="no">
         update "HR"."TNOCASCADE_FORCE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAASOBAAEAAAAKVAAB'
         </USQL>
      </UNDO_SQL>
      <DEPENDENT_XIDS>
         <TRANSACTION XID="0A001500C9020000">
         <CHARACTERISTICS>
         </CHARACTERISTICS>
         <UNDO_SQL>
            <USQL exec="no">
               update "HR"."TNOCASCADE_FORCE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAASOBAAEAAAAKVAAB'
            </USQL>
         </UNDO_SQL>
         <DEPENDENT_XIDS>
         </DEPENDENT_XIDS>
         </TRANSACTION>
      </DEPENDENT_XIDS>
      </TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>update "HR"."TNOCASCADE_FORCE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASOBAAEAAAAKVAAC'
</EXEC_USQL>
<EXEC_USQL>update "HR"."TNOCASCADE_FORCE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASOBAAEAAAAKVAAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."TNOCASCADE_FORCE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASOBAAEAAAAKVAAA'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>

SQL> select * from tnocascade_force;
ID
----------
1
2
333

复制代码

-----------------------------------------------------------
其实图形界面是用nonconflict_only来做的：
准备nonconflict_only的实验环境：

SQL> @/4/t05310
Enter value for s1: tnonconflict_only
old 1: create table &&s1( id number )
new 1: create table tnonconflict_only( id number )
Table created.
old 1: insert into &&s1 values (1)
new 1: insert into tnonconflict_only values (1)
1 row created.
old 1: insert into &&s1 values (2)
new 1: insert into tnonconflict_only values (2)
1 row created.
old 1: insert into &&s1 values (3)
new 1: insert into tnonconflict_only values (3)
1 row created.
Commit complete.
old 1: update &&s1 set id=11 where id=1
new 1: update tnonconflict_only set id=11 where id=1
1 row updated.
old 1: update &&s1 set id=22 where id=2
new 1: update tnonconflict_only set id=22 where id=2
1 row updated.
old 1: update &&s1 set id=33 where id=3
new 1: update tnonconflict_only set id=33 where id=3
1 row updated.
Commit complete.
old 1: update &&s1 set id=222 where id=22
new 1: update tnonconflict_only set id=222 where id=22
1 row updated.
old 1: update &&s1 set id=333 where id=33
new 1: update tnonconflict_only set id=333 where id=33
1 row updated.
Commit complete.
old 1: update &&s1 set id=22 where id=222
new 1: update tnonconflict_only set id=22 where id=222
1 row updated.
Commit complete.
SQL>

复制代码

select * from flashback_transaction_query f
where f.table_name='TNONCONFLICT_ONLY' order by start_scn;
select * from dba_flashback_txn_report;
begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('07001300C8020000'),
options => dbms_flashback.nonconflict_only);
end;
select * from dba_flashback_txn_report;
commit;
select * from dba_flashback_txn_report;

复制代码

<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="0200000097030000" NAME="_SYS_COMP_TXN_4789146_TIM_1525513606">
<TRANSACTION XID="07001300C8020000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
      <USQL exec="no">
      update "HR"."TNONCONFLICT_ONLY" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASOCAAEAAAAKdAAC'
      </USQL>
      <USQL exec="no">
      update "HR"."TNONCONFLICT_ONLY" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASOCAAEAAAAKdAAB'
      </USQL>
      <USQL exec="yes">
      update "HR"."TNONCONFLICT_ONLY" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASOCAAEAAAAKdAAA'
      </USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
      <TRANSACTION XID="02001F0096030000">
      <CHARACTERISTICS>
      </CHARACTERISTICS>
      <UNDO_SQL>
         <USQL exec="no">
         update "HR"."TNONCONFLICT_ONLY" set "ID" = '33' where "ID" = '333' and ROWID = 'AAASOCAAEAAAAKdAAC'
         </USQL>
         <USQL exec="no">
         update "HR"."TNONCONFLICT_ONLY" set "ID" = '22' where "ID" = '222' and ROWID = 'AAASOCAAEAAAAKdAAB'
         </USQL>
      </UNDO_SQL>
      <DEPENDENT_XIDS>
         <TRANSACTION XID="07000F00C8020000">
         <CHARACTERISTICS>
         </CHARACTERISTICS>
         <UNDO_SQL>
            <USQL exec="no">
               update "HR"."TNONCONFLICT_ONLY" set "ID" = '222' where "ID" = '22' and ROWID = 'AAASOCAAEAAAAKdAAB'
            </USQL>
         </UNDO_SQL>
         <DEPENDENT_XIDS>
         </DEPENDENT_XIDS>
         </TRANSACTION>
      </DEPENDENT_XIDS>
      </TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>update "HR"."TNONCONFLICT_ONLY" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASOCAAEAAAAKdAAA'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>

SQL> select * from tnonconflict_only;
ID
----------
1
22
333

复制代码

要处理主键：

实验过程：

select log_mode from v$database;
alter database add supplemental log data;
alter database add supplemental log data ( primary key ) columns;
create table hr.t05310_b ( a number primary key );
insert into hr.t05310_b values (1);
commit;
delete from hr.t05310_b ;
commit;
insert into hr.t05310_b values (1);
commit;
select * from flashback_transaction_query f
where f.table_name='T05310_B' order by start_scn;
---0700180044070000
begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('0700180044070000'),
options => dbms_flashback.cascade);
end;

复制代码

报告：
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="09001700A9030000" NAME="_SYS_COMP_TXN_9176273_TIM_1525515283">
<TRANSACTION XID="01000400D9020000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
      <USQL exec="yes">
      insert into "HR"."T05310_B"("A") values ('1')
      </USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
      <TRANSACTION XID="0200050097030000">
      <CHARACTERISTICS>
      </CHARACTERISTICS>
      <UNDO_SQL>
         <USQL exec="yes">
         delete from "HR"."T05310_B" where "A" = '1'
         </USQL>
      </UNDO_SQL>
      <DEPENDENT_XIDS>
      </DEPENDENT_XIDS>
      </TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>delete from "HR"."T05310_B" where "A" = '1'
</EXEC_USQL>
<EXEC_USQL>insert into "HR"."T05310_B"("A") values ('1')
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>

要处理外键：
alter database add  SUPPLEMENTAL log data  （foreign key ）columns;
11.2.0.1本身的bug造成在处理外键依赖关系时会报告600错误：

转到12c（做的时候要改归档，切日志，把三个补充日志同时都加上，不然容易出ora-600）:

select log_mode from v$database;
alter database add supplemental log data;
alter database add supplemental log data ( primary key ) columns;
create table hr.t05310_c ( a number primary key );
insert into hr.t05310_c values (1);
commit;
create table hr.t05310_d ( a number references hr.t05310_c );
insert into hr.t05310_d values (1);
commit;
select * from flashback_transaction_query f
where f.table_name in ('T05310_C','T05310_D') order by start_scn;
---04001A000B030000
begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('04001A000B030000'),
options => dbms_flashback.cascade);
end;
select * from dba_flashback_txn_report;
commit;
select * from dba_flashback_txn_report;

复制代码

如果没有外建补充日志，就会出现以下错误：

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

复制代码

<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="06000E0005080000" NAME="_SYS_COMP_TXN_26989413_TIM_1526091213">
<TRANSACTION XID="0B001000BA000000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
      <USQL exec="yes">
      delete from "HR"."T05310_C" where "A" = '1'
      </USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
      <TRANSACTION XID="01001B0049070000">
      <CHARACTERISTICS>
      </CHARACTERISTICS>
      <UNDO_SQL>
         <USQL exec="yes">
         delete from "HR"."T05310_D" where "A" = '1' and ROWID = 'AAAd3VAAEAAAADDAAA'
         </USQL>
      </UNDO_SQL>
      <DEPENDENT_XIDS>
      </DEPENDENT_XIDS>
      </TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>delete from "HR"."T05310_D" where "A" = '1' and ROWID = 'AAAd3VAAEAAAADDAAA'
</EXEC_USQL>
<EXEC_USQL>delete from "HR"."T05310_C" where "A" = '1'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>