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

botang

改动磁盘组的属性：

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

复制代码

创建2M AU的磁盘组：

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

复制代码

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

1. SQL> alter diskgroup data offline disk data_0000  drop after 1h;
   
2. alter diskgroup data offline disk data_0000  drop after 1h
   
3. *
   
4. ERROR at line 1:
   
5. ORA-15032: not all alterations performed
   
6. ORA-15283: ASM operation requires compatible.rdbms of 11.1.0.0.0 or higher
   
7. 
   
8. 
   
9. SQL> alter diskgroup data set attribute 'compatible.rdbms'='11.1.0.0.0';
   
10. 
    
11. Diskgroup altered.
    
12. 
    
13. SQL> alter diskgroup data offline disk data_0000  drop after 1h;
    
14. 
    
15. Diskgroup altered.
    
16. 
    

复制代码

模拟盘被拔出，清理：

[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 桌面]#

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

1. SQL> conn / as sysdba
   
2. Connected.
   
3. SQL> create table hr.tbig as select  * from dba_source;
   
4. 
   
5. Table created.
   
6. 
   
7. SQL> conn hr/oracle_4U
   
8. ERROR:
   
9. ORA-28002: the password will expire within 7 days
   
10. 
    
11. 
    
12. Connected.
    
13. SQL> insert into tbig select  * from tbig;
    
14. 
    
15. 623407 rows created.
    
16. 
    
17. SQL> commit;
    
18. 
    
19. Commit complete.
    
20. 
    
21. SQL> insert into tbig select  * from tbig;
    
22. 
    
23. 1246814 rows created.
    
24. 
    
25. SQL> commit;
    
26. 
    
27. Commit complete.
    
28. 
    
29. SQL>
    

复制代码

模拟磁盘重新插回去：
[root@station90 桌面]# chown oracleinstall /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；）

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Sat May 5 14:46:25 2018
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysasm
   
8. Connected.
   
9. SQL> alter diskgroup data offline disk data_0000  drop after 1h;
   
10. 
    
11. Diskgroup altered.
    
12. 
    
13. SQL> alter diskgroup data online disk data_0000  ;
    
14. alter diskgroup data online disk data_0000
    
15. *
    
16. ERROR at line 1:
    
17. ORA-15032: not all alterations performed
    
18. ORA-15281: not all specified disks were brought ONLINE
    
19. ORA-15284: ASM terminated ALTER DISKGROUP ONLINE
    
20. ORA-15282: ASM disk "DATA_0000" is not visible cluster-wide
    
21. 
    
22. 
    
23. SQL> alter diskgroup data drop  disk data_0000  ;
    
24. alter diskgroup data drop  disk data_0000
    
25. *
    
26. ERROR at line 1:
    
27. ORA-15032: not all alterations performed
    
28. ORA-15084: ASM disk "DATA_0000" is offline and cannot be dropped.
    
29. 
    
30. 
    
31. SQL> alter diskgroup data drop  disk data_0000  force  ;
    
32. 
    
33. Diskgroup altered.
    
34. 
    
35. 
    
36. SQL> alter diskgroup data add   failgroup  data_0000  disk  '/dev/raw/raw7' name   data_0000  ;
    
37. 
    
38. Diskgroup altered.
    
39. 
    
40. SQL>
    

复制代码

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

1. SQL*Plus: Release 11.2.0.3.0 Production on Sat May 12 10:49:31 2018
   
2. 
   
3. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
4. 
   
5. SQL> conn / as sysasm
   
6. Connected.
   
7. SQL> alter diskgroup data resize disk data_0000;
   
8. 
   
9. Diskgroup altered.
   
10. 
    
11. SQL> alter diskgroup data resize disk data_0001 disk data_0002 disk data_0003  disk data_0004 disk data_0005  ;
    
12. 
    
13. Diskgroup altered.
    
14. 
    
15. SQL> alter diskgroup fra resize disk fra_0000 disk fra_0001 disk fra_0002    ;
    
16. 
    
17. Diskgroup altered.
    
18. 
    
19. SQL>
    

复制代码

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

1. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
2. 
   
3. SQL> conn hr/oracle_4U
   
4. ERROR:
   
5. ORA-28002: the password will expire within 7 days
   
6. 
   
7. 
   
8. Connected.
   
9. SQL> select  salary from employees where employee_id=100;
   
10. 
    
11.     SALARY
    
12. ----------
    
13.      24000
    
14. 
    
15. SQL> update employees  set salary=30000   where employee_id=100;
    
16. 
    
17. 1 row updated.
    

复制代码

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


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

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




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

实验过程：

1. SQL> create table t05310_a ( a number ) ;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> insert into t05310_a values ( 1 ) ;
   
6. 
   
7. 1 row created.
   
8. 
   
9. SQL> commit;
   
10. 
    
11. Commit complete.
    
12. 
    
13. SQL> update t05310_a set a=2 ;
    
14. 
    
15. 1 row updated.
    
16. 
    
17. SQL> commit;
    
18. 
    
19. Commit complete.
    
20. 
    
21. SQL> alter table t05310_a  add ( b varchar2(20))  ;
    
22. 
    
23. Table altered.
    
24. 
    
25. SQL> select  * from t05310_a;
    
26. 
    
27.          A B
    
28. ---------- --------------------
    
29.          2
    
30. 
    
31. SQL> update t05310_a  set a=3 , b='c'  ;
    
32. 
    
33. 1 row updated.
    
34. 
    
35. SQL> commit;
    
36. 
    
37. Commit complete.
    
38. 
    
39. SQL> update t05310_a  set a=4 , b='d'  ;
    
40. 
    
41. 1 row updated.
    
42. 
    
43. SQL> commit;
    
44. 
    
45. Commit complete.
    
46. 
    
47. SQL> select  * from t05310_a;
    
48. 
    
49.          A B
    
50. ---------- --------------------
    
51.          4 d
    
52. 
    
53. SQL>
    

复制代码

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

实验过程：

1. SQL> alter table t05310_a drop (b) ;
   
2. 
   
3. Table altered.
   
4. 
   
5. SQL>
   

复制代码

1. SQL> select  a from t05310_a as of timestamp systimestamp-5/1440  ;
   
2. select        a from t05310_a as of timestamp systimestamp-5/1440
   
3.                *
   
4. ERROR at line 1:
   
5. ORA-01466: unable to read data - table definition has changed
   
6. 
   
7. 
   
8. SQL> select  a from t05310_a as of timestamp systimestamp-1/1440  ;
   
9. 
   
10.          A
    
11. ----------
    
12.          4
    
13. 
    
14. SQL> select  a from t05310_a as of timestamp systimestamp-2/1440  ;
    
15. 
    
16.          A
    
17. ----------
    
18.          4
    
19. 
    
20. SQL> select  a from t05310_a as of timestamp systimestamp-3/1440  ;
    
21. 
    
22.          A
    
23. ----------
    
24.          4
    
25. 
    
26. SQL> select  a from t05310_a as of timestamp systimestamp-4/1440  ;
    
27. select        a from t05310_a as of timestamp systimestamp-4/1440
    
28.                *
    
29. ERROR at line 1:
    
30. ORA-01466: unable to read data - table definition has changed
    
31. 
    
32. 
    
33. SQL> select  a from t05310_a as of timestamp systimestamp-4/1440  ;
    
34. select        a from t05310_a as of timestamp systimestamp-4/1440
    
35.                *
    
36. ERROR at line 1:
    
37. ORA-01466: unable to read data - table definition has changed
    
38. 
    
39. 
    
40. SQL> select  a from t05310_a as of timestamp systimestamp-4/1440  ;
    
41. select        a from t05310_a as of timestamp systimestamp-4/1440
    
42.                *
    
43. ERROR at line 1:
    
44. ORA-01466: unable to read data - table definition has changed
    
45. 
    
46. 
    
47. SQL> alter table t05310_a enable row movement;
    
48. 
    
49. Table altered.
    
50. 
    
51. SQL> select  a from t05310_a as of timestamp systimestamp-4/1440  ;
    
52. select        a from t05310_a as of timestamp systimestamp-4/1440
    
53.                *
    
54. ERROR at line 1:
    
55. ORA-01466: unable to read data - table definition has changed
    
56. 
    
57. 
    
58. SQL> flashback  table t05310_a to timestamp   systimestamp-4/1440;
    
59. flashback  table t05310_a to timestamp         systimestamp-4/1440
    
60.                  *
    
61. ERROR at line 1:
    
62. ORA-01466: unable to read data - table definition has changed
    
63. 
    
64. 
    
65. SQL>
    

复制代码

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

1. select  o.object_id
   
2.   from  dba_objects o where o.object_name='T05310_A';  
   
3.   
   
4. --74613
   
5. select  d.SUPPLEMENTAL_LOG_DATA_MIN,
   
6.              d.SUPPLEMENTAL_LOG_DATA_PK,
   
7.              d.SUPPLEMENTAL_LOG_DATA_UI,
   
8.              d.SUPPLEMENTAL_LOG_DATA_FK,
   
9.              d.SUPPLEMENTAL_LOG_DATA_ALL
   
10.    from v_$database  d;
    
11.    
    
12.   alter database add  SUPPLEMENTAL log data;
    
13. 
    
14. ---hr drop table t05310_a;
    
15. select  * from flashback_transaction_query f
    
16. where f.table_name='TAB' order by 3 desc;

复制代码

undo_sql：
[/code]undo_sql：

1. insert
   
2. into "SYS"."TAB[        DISCUZ_CODE_10     
   
3.    ]quot;("OBJ#","DATAOBJ#","TS#","FILE#","BLOCK#","BOBJ#","TAB#","COLS","CLUCOLS","PCTFREE[
   
4.        DISCUZ_CODE_10        ]quot;,"PCTUSED[        DISCUZ_CODE_10     
   
5.    ]quot;,"INITRANS","MAXTRANS","FLAGS","AUDIT[        DISCUZ_CODE_10  
   
6.    
   
7.    ]quot;,"ROWCNT","BLKCNT","EMPCNT","AVGSPC","CHNCNT","AVGRLN","AVGSPC_FLB","FLBCNT","ANALYZETIME","SAMPLESIZE","DEGREE","INSTANCES","INTCOLS","KERNELCOLS","PROPERTY","TRIGFLAG","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6")
   
8. values
   
9. ('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',
   
10. 'DD-MON-RR'));

复制代码

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

闪回事务实验用的脚本：
t05310.sql (388 Bytes, 下载次数: 365)

2018-5-5 16:46 上传

点击文件名下载附件

nocascade默认选项实验：

1. SQL> show user
   
2. USER is "HR"
   
3. SQL> @/4/t05310
   
4. Enter value for s1: tnocascade
   
5. old   1: create table &&s1( id number )
   
6. new   1: create table tnocascade( id number )
   
7. 
   
8. Table created.
   
9. 
   
10. 
    
11. old   1: insert into &&s1 values (1)
    
12. new   1: insert into tnocascade values (1)
    
13. 
    
14. 1 row created.
    
15. 
    
16. old   1: insert into &&s1 values (2)
    
17. new   1: insert into tnocascade values (2)
    
18. 
    
19. 1 row created.
    
20. 
    
21. old   1: insert into &&s1 values (3)
    
22. new   1: insert into tnocascade values (3)
    
23. 
    
24. 1 row created.
    
25. 
    
26. 
    
27. Commit complete.
    
28. 
    
29. old   1: update &&s1 set id=11 where id=1
    
30. new   1: update tnocascade set id=11 where id=1
    
31. 
    
32. 1 row updated.
    
33. 
    
34. old   1: update &&s1 set id=22 where id=2
    
35. new   1: update tnocascade set id=22 where id=2
    
36. 
    
37. 1 row updated.
    
38. 
    
39. old   1: update &&s1 set id=33 where id=3
    
40. new   1: update tnocascade set id=33 where id=3
    
41. 
    
42. 1 row updated.
    
43. 
    
44. 
    
45. Commit complete.
    
46. 
    
47. old   1: update &&s1 set id=222 where id=22
    
48. new   1: update tnocascade set id=222 where id=22
    
49. 
    
50. 1 row updated.
    
51. 
    
52. old   1: update &&s1 set id=333 where id=33
    
53. new   1: update tnocascade set id=333 where id=33
    
54. 
    
55. 1 row updated.
    
56. 
    
57. 
    
58. Commit complete.
    
59. 
    
60. old   1: update &&s1 set id=22 where id=222
    
61. new   1: update tnocascade set id=22 where id=222
    
62. 
    
63. 1 row updated.
    
64. 
    
65. 
    
66. Commit complete.
    
67. 
    
68. SQL> select  * from tnocascade;
    
69. 
    
70.         ID
    
71. ----------
    
72.         11
    
73.         22
    
74.        333
    
75. 
    
76. SQL>
    

复制代码

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

复制代码

我们换成cascade选项：

1. select  * from dba_flashback_txn_report;
   
2. 
   
3. begin
   
4.    dbms_flashback.transaction_backout(numtxns => 1,
   
5.                                                                  xids => xid_array('0300200096030000'),
   
6.                                                                  options => dbms_flashback.cascade);
   
7. end;
   
8.                                                                
   
9.    select  * from dba_flashback_txn_report;
   
10.    
    
11.    commit;
    
12.    
    
13.    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>

提交以后表的结果：

1. SQL> /
   
2. 
   
3.         ID
   
4. ----------
   
5.          1
   
6.          2
   
7.          3
   
8. 
   

复制代码

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

1. SQL> @/4/t05310
   
2. Enter value for s1: tnocascade_force
   
3. old   1: create table &&s1( id number )
   
4. new   1: create table tnocascade_force( id number )
   
5. 
   
6. Table created.
   
7. 
   
8. 
   
9. old   1: insert into &&s1 values (1)
   
10. new   1: insert into tnocascade_force values (1)
    
11. 
    
12. 1 row created.
    
13. 
    
14. old   1: insert into &&s1 values (2)
    
15. new   1: insert into tnocascade_force values (2)
    
16. 
    
17. 1 row created.
    
18. 
    
19. old   1: insert into &&s1 values (3)
    
20. new   1: insert into tnocascade_force values (3)
    
21. 
    
22. 1 row created.
    
23. 
    
24. 
    
25. Commit complete.
    
26. 
    
27. old   1: update &&s1 set id=11 where id=1
    
28. new   1: update tnocascade_force set id=11 where id=1
    
29. 
    
30. 1 row updated.
    
31. 
    
32. old   1: update &&s1 set id=22 where id=2
    
33. new   1: update tnocascade_force set id=22 where id=2
    
34. 
    
35. 1 row updated.
    
36. 
    
37. old   1: update &&s1 set id=33 where id=3
    
38. new   1: update tnocascade_force set id=33 where id=3
    
39. 
    
40. 1 row updated.
    
41. 
    
42. 
    
43. Commit complete.
    
44. 
    
45. old   1: update &&s1 set id=222 where id=22
    
46. new   1: update tnocascade_force set id=222 where id=22
    
47. 
    
48. 1 row updated.
    
49. 
    
50. old   1: update &&s1 set id=333 where id=33
    
51. new   1: update tnocascade_force set id=333 where id=33
    
52. 
    
53. 1 row updated.
    
54. 
    
55. 
    
56. Commit complete.
    
57. 
    
58. old   1: update &&s1 set id=22 where id=222
    
59. new   1: update tnocascade_force set id=22 where id=222
    
60. 
    
61. 1 row updated.
    
62. 
    
63. 
    
64. Commit complete.
    

复制代码

硬做：

1. select  * from flashback_transaction_query f
   
2. where f.table_name='TNOCASCADE_FORCE'  order by start_scn;
   
3. 
   
4. select  * from dba_flashback_txn_report;
   
5. 
   
6. begin
   
7.    dbms_flashback.transaction_backout(numtxns => 1,
   
8.                                                                  xids => xid_array('0200130094030000'),
   
9.                                                                  options => dbms_flashback.nocascade_force);
   
10. end;
    
11.                                                                
    
12.    select  * from dba_flashback_txn_report;
    
13.    
    
14.    commit;
    
15.    
    
16.    select  * from dba_flashback_txn_report;
    
17.    

复制代码

报告是：
<?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>

1. SQL> select * from tnocascade_force;
   
2. 
   
3.         ID
   
4. ----------
   
5.          1
   
6.          2
   
7.        333
   

复制代码

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

1. SQL> @/4/t05310
   
2. Enter value for s1: tnonconflict_only
   
3. old   1: create table &&s1( id number )
   
4. new   1: create table tnonconflict_only( id number )
   
5. 
   
6. Table created.
   
7. 
   
8. 
   
9. old   1: insert into &&s1 values (1)
   
10. new   1: insert into tnonconflict_only values (1)
    
11. 
    
12. 1 row created.
    
13. 
    
14. old   1: insert into &&s1 values (2)
    
15. new   1: insert into tnonconflict_only values (2)
    
16. 
    
17. 1 row created.
    
18. 
    
19. old   1: insert into &&s1 values (3)
    
20. new   1: insert into tnonconflict_only values (3)
    
21. 
    
22. 1 row created.
    
23. 
    
24. 
    
25. Commit complete.
    
26. 
    
27. old   1: update &&s1 set id=11 where id=1
    
28. new   1: update tnonconflict_only set id=11 where id=1
    
29. 
    
30. 1 row updated.
    
31. 
    
32. old   1: update &&s1 set id=22 where id=2
    
33. new   1: update tnonconflict_only set id=22 where id=2
    
34. 
    
35. 1 row updated.
    
36. 
    
37. old   1: update &&s1 set id=33 where id=3
    
38. new   1: update tnonconflict_only set id=33 where id=3
    
39. 
    
40. 1 row updated.
    
41. 
    
42. 
    
43. Commit complete.
    
44. 
    
45. old   1: update &&s1 set id=222 where id=22
    
46. new   1: update tnonconflict_only set id=222 where id=22
    
47. 
    
48. 1 row updated.
    
49. 
    
50. old   1: update &&s1 set id=333 where id=33
    
51. new   1: update tnonconflict_only set id=333 where id=33
    
52. 
    
53. 1 row updated.
    
54. 
    
55. 
    
56. Commit complete.
    
57. 
    
58. old   1: update &&s1 set id=22 where id=222
    
59. new   1: update tnonconflict_only set id=22 where id=222
    
60. 
    
61. 1 row updated.
    
62. 
    
63. 
    
64. Commit complete.
    
65. 
    
66. SQL>
    

复制代码

1. select  * from flashback_transaction_query f
   
2. where f.table_name='TNONCONFLICT_ONLY'  order by start_scn;
   
3. 
   
4. select  * from dba_flashback_txn_report;
   
5. 
   
6. begin
   
7.    dbms_flashback.transaction_backout(numtxns => 1,
   
8.                                                                  xids => xid_array('07001300C8020000'),
   
9.                                                                  options => dbms_flashback.nonconflict_only);
   
10. end;
    
11.                                                                
    
12.    select  * from dba_flashback_txn_report;
    
13.    
    
14.    commit;
    
15.    
    
16.    select  * from dba_flashback_txn_report;
    
17.    

复制代码

<?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>

1. SQL> select  * from tnonconflict_only;
   
2. 
   
3.         ID
   
4. ----------
   
5.          1
   
6.         22
   
7.        333
   

复制代码

要处理主键：

实验过程：

1. select  log_mode from v$database;
   
2. 
   
3. alter database add supplemental log data;
   
4. 
   
5. alter database add supplemental log data ( primary key ) columns;
   
6. 
   
7. create table hr.t05310_b ( a  number primary key );
   
8. 
   
9. insert into hr.t05310_b  values (1);
   
10. 
    
11. commit;
    
12. 
    
13. delete from hr.t05310_b ;
    
14. 
    
15. commit;
    
16. 
    
17. insert into hr.t05310_b  values (1);
    
18. 
    
19. commit;
    
20. 
    
21. select  * from flashback_transaction_query f
    
22. where f.table_name='T05310_B'  order by start_scn;
    
23. 
    
24. ---0700180044070000
    
25. 
    
26. begin
    
27.    dbms_flashback.transaction_backout(numtxns => 1,
    
28.                                                                  xids => xid_array('0700180044070000'),
    
29.                                                                  options => dbms_flashback.cascade);
    
30. 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）:

1. select  log_mode from v$database;
   
2. 
   
3. alter database add supplemental log data;
   
4. 
   
5. alter database add supplemental log data ( primary key ) columns;
   
6. 
   
7. 
   
8. 
   
9. create table hr.t05310_c ( a  number primary key );
   
10. 
    
11. insert into hr.t05310_c  values (1);
    
12. 
    
13. commit;
    
14. 
    
15. create table hr.t05310_d ( a  number references  hr.t05310_c );
    
16. 
    
17. insert into hr.t05310_d  values (1);
    
18. 
    
19. commit;
    
20. 
    
21. select  * from flashback_transaction_query f
    
22. where f.table_name in ('T05310_C','T05310_D')  order by start_scn;
    
23. 
    
24. ---04001A000B030000
    
25. 
    
26. begin
    
27.    dbms_flashback.transaction_backout(numtxns => 1,
    
28.                                                                  xids => xid_array('04001A000B030000'),
    
29.                                                                  options => dbms_flashback.cascade);
    
30. end;
    
31. 
    
32. select  * from dba_flashback_txn_report;
    
33.    
    
34.    commit;
    
35.    
    
36.    select  * from dba_flashback_txn_report;
    

复制代码

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

1. 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>