设为首页收藏本站

Botang唐波's Oracle Station

查看: 100|回复: 0

活动15/16次

[复制链接]

640

主题

995

帖子

7333

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7333
发表于 2018-5-5 09:48:40 | 显示全部楼层 |阅读模式
改动磁盘组的属性:
  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. SQL*Plus: Release 11.2.0.1.0 Production on Sat May 5 09:45:45 2018

  6. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  7. SQL> conn / as sysasm
  8. Connected.
  9. SQL> alter diskgroup highdata set attribute  'compatible.asm'='11.1';

  10. Diskgroup altered.

  11. SQL> alter diskgroup highdata set attribute  'compatible.asm'='10.2';
  12. alter diskgroup highdata set attribute        'compatible.asm'='10.2'
  13. *
  14. ERROR at line 1:
  15. ORA-15032: not all alterations performed
  16. ORA-15242: could not set attribute compatible.asm
  17. ORA-15238: 10.2 is not a valid value for attribute compatible.asm
  18. 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. SQL> alter diskgroup data set attribute 'compatible.rdbms'='11.1.0.0.0';

  8. Diskgroup altered.

  9. SQL> alter diskgroup data offline disk data_0000  drop after 1h;

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

同时打开另一窗口,模拟工作负载:
  1. SQL> conn / as sysdba
  2. Connected.
  3. SQL> create table hr.tbig as select  * from dba_source;

  4. Table created.

  5. SQL> conn hr/oracle_4U
  6. ERROR:
  7. ORA-28002: the password will expire within 7 days


  8. Connected.
  9. SQL> insert into tbig select  * from tbig;

  10. 623407 rows created.

  11. SQL> commit;

  12. Commit complete.

  13. SQL> insert into tbig select  * from tbig;

  14. 1246814 rows created.

  15. SQL> commit;

  16. Commit complete.

  17. 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. SQL*Plus: Release 11.2.0.1.0 Production on Sat May 5 14:46:25 2018

  3. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  4. SQL> conn / as sysasm
  5. Connected.
  6. SQL> alter diskgroup data offline disk data_0000  drop after 1h;

  7. Diskgroup altered.

  8. SQL> alter diskgroup data online disk data_0000  ;
  9. alter diskgroup data online disk data_0000
  10. *
  11. ERROR at line 1:
  12. ORA-15032: not all alterations performed
  13. ORA-15281: not all specified disks were brought ONLINE
  14. ORA-15284: ASM terminated ALTER DISKGROUP ONLINE
  15. ORA-15282: ASM disk "DATA_0000" is not visible cluster-wide


  16. SQL> alter diskgroup data drop  disk data_0000  ;
  17. alter diskgroup data drop  disk data_0000
  18. *
  19. ERROR at line 1:
  20. ORA-15032: not all alterations performed
  21. ORA-15084: ASM disk "DATA_0000" is offline and cannot be dropped.


  22. SQL> alter diskgroup data drop  disk data_0000  force  ;

  23. Diskgroup altered.


  24. SQL> alter diskgroup data add   failgroup  data_0000  disk  '/dev/raw/raw7' name   data_0000  ;

  25. Diskgroup altered.

  26. SQL>
复制代码

在线扩大磁盘组的大小(通过扩大磁盘):
  1. SQL*Plus: Release 11.2.0.3.0 Production on Sat May 12 10:49:31 2018

  2. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  3. SQL> conn / as sysasm
  4. Connected.
  5. SQL> alter diskgroup data resize disk data_0000;

  6. Diskgroup altered.

  7. SQL> alter diskgroup data resize disk data_0001 disk data_0002 disk data_0003  disk data_0004 disk data_0005  ;

  8. Diskgroup altered.

  9. SQL> alter diskgroup fra resize disk fra_0000 disk fra_0001 disk fra_0002    ;

  10. Diskgroup altered.

  11. SQL>
复制代码



----------------------------------------------------------------------------------------------------------------------------
闪回事务查询,在flashback_transaction_query中,没提交的事务也看得到,因为这个视图反映的是整个UNDO表空间基于时间和事务把ROWID精细整理过的样子。
  1. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  2. SQL> conn hr/oracle_4U
  3. ERROR:
  4. ORA-28002: the password will expire within 7 days


  5. Connected.
  6. SQL> select  salary from employees where employee_id=100;

  7.     SALARY
  8. ----------
  9.      24000

  10. SQL> update employees  set salary=30000   where employee_id=100;

  11. 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. Table created.

  3. SQL> insert into t05310_a values ( 1 ) ;

  4. 1 row created.

  5. SQL> commit;

  6. Commit complete.

  7. SQL> update t05310_a set a=2 ;

  8. 1 row updated.

  9. SQL> commit;

  10. Commit complete.

  11. SQL> alter table t05310_a  add ( b varchar2(20))  ;

  12. Table altered.

  13. SQL> select  * from t05310_a;

  14.          A B
  15. ---------- --------------------
  16.          2

  17. SQL> update t05310_a  set a=3 , b='c'  ;

  18. 1 row updated.

  19. SQL> commit;

  20. Commit complete.

  21. SQL> update t05310_a  set a=4 , b='d'  ;

  22. 1 row updated.

  23. SQL> commit;

  24. Commit complete.

  25. SQL> select  * from t05310_a;

  26.          A B
  27. ---------- --------------------
  28.          4 d

  29. SQL>
复制代码


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

实验过程:
  1. SQL> alter table t05310_a drop (b) ;

  2. Table altered.

  3. 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. SQL> select  a from t05310_a as of timestamp systimestamp-1/1440  ;

  7.          A
  8. ----------
  9.          4

  10. SQL> select  a from t05310_a as of timestamp systimestamp-2/1440  ;

  11.          A
  12. ----------
  13.          4

  14. SQL> select  a from t05310_a as of timestamp systimestamp-3/1440  ;

  15.          A
  16. ----------
  17.          4

  18. SQL> select  a from t05310_a as of timestamp systimestamp-4/1440  ;
  19. select        a from t05310_a as of timestamp systimestamp-4/1440
  20.                *
  21. ERROR at line 1:
  22. ORA-01466: unable to read data - table definition has changed


  23. SQL> select  a from t05310_a as of timestamp systimestamp-4/1440  ;
  24. select        a from t05310_a as of timestamp systimestamp-4/1440
  25.                *
  26. ERROR at line 1:
  27. ORA-01466: unable to read data - table definition has changed


  28. SQL> select  a from t05310_a as of timestamp systimestamp-4/1440  ;
  29. select        a from t05310_a as of timestamp systimestamp-4/1440
  30.                *
  31. ERROR at line 1:
  32. ORA-01466: unable to read data - table definition has changed


  33. SQL> alter table t05310_a enable row movement;

  34. Table altered.

  35. SQL> select  a from t05310_a as of timestamp systimestamp-4/1440  ;
  36. select        a from t05310_a as of timestamp systimestamp-4/1440
  37.                *
  38. ERROR at line 1:
  39. ORA-01466: unable to read data - table definition has changed


  40. SQL> flashback  table t05310_a to timestamp   systimestamp-4/1440;
  41. flashback  table t05310_a to timestamp         systimestamp-4/1440
  42.                  *
  43. ERROR at line 1:
  44. ORA-01466: unable to read data - table definition has changed


  45. 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. ---hr drop table t05310_a;
  14. select  * from flashback_transaction_query f
  15. 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;

闪回事务实验用的脚本:


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. Table created.


  8. old   1: insert into &&s1 values (1)
  9. new   1: insert into tnocascade values (1)

  10. 1 row created.

  11. old   1: insert into &&s1 values (2)
  12. new   1: insert into tnocascade values (2)

  13. 1 row created.

  14. old   1: insert into &&s1 values (3)
  15. new   1: insert into tnocascade values (3)

  16. 1 row created.


  17. Commit complete.

  18. old   1: update &&s1 set id=11 where id=1
  19. new   1: update tnocascade set id=11 where id=1

  20. 1 row updated.

  21. old   1: update &&s1 set id=22 where id=2
  22. new   1: update tnocascade set id=22 where id=2

  23. 1 row updated.

  24. old   1: update &&s1 set id=33 where id=3
  25. new   1: update tnocascade set id=33 where id=3

  26. 1 row updated.


  27. Commit complete.

  28. old   1: update &&s1 set id=222 where id=22
  29. new   1: update tnocascade set id=222 where id=22

  30. 1 row updated.

  31. old   1: update &&s1 set id=333 where id=33
  32. new   1: update tnocascade set id=333 where id=33

  33. 1 row updated.


  34. Commit complete.

  35. old   1: update &&s1 set id=22 where id=222
  36. new   1: update tnocascade set id=22 where id=222

  37. 1 row updated.


  38. Commit complete.

  39. SQL> select  * from tnocascade;

  40.         ID
  41. ----------
  42.         11
  43.         22
  44.        333

  45. SQL>
复制代码
  1. select  * from flashback_transaction_query f
  2. where f.table_name='TNOCASCADE'  order by start_scn;

  3. begin
  4.    dbms_flashback.transaction_backout(numtxns => 1,
  5.                                                                  xids => xid_array('0300200096030000'));
  6. end;
复制代码



我们换成cascade选项:

  1. select  * from dba_flashback_txn_report;

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

复制代码
--------------------------------------------------
如果用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. Table created.


  6. old   1: insert into &&s1 values (1)
  7. new   1: insert into tnocascade_force values (1)

  8. 1 row created.

  9. old   1: insert into &&s1 values (2)
  10. new   1: insert into tnocascade_force values (2)

  11. 1 row created.

  12. old   1: insert into &&s1 values (3)
  13. new   1: insert into tnocascade_force values (3)

  14. 1 row created.


  15. Commit complete.

  16. old   1: update &&s1 set id=11 where id=1
  17. new   1: update tnocascade_force set id=11 where id=1

  18. 1 row updated.

  19. old   1: update &&s1 set id=22 where id=2
  20. new   1: update tnocascade_force set id=22 where id=2

  21. 1 row updated.

  22. old   1: update &&s1 set id=33 where id=3
  23. new   1: update tnocascade_force set id=33 where id=3

  24. 1 row updated.


  25. Commit complete.

  26. old   1: update &&s1 set id=222 where id=22
  27. new   1: update tnocascade_force set id=222 where id=22

  28. 1 row updated.

  29. old   1: update &&s1 set id=333 where id=33
  30. new   1: update tnocascade_force set id=333 where id=33

  31. 1 row updated.


  32. Commit complete.

  33. old   1: update &&s1 set id=22 where id=222
  34. new   1: update tnocascade_force set id=22 where id=222

  35. 1 row updated.


  36. Commit complete.
复制代码
硬做:
  1. select  * from flashback_transaction_query f
  2. where f.table_name='TNOCASCADE_FORCE'  order by start_scn;

  3. select  * from dba_flashback_txn_report;

  4. begin
  5.    dbms_flashback.transaction_backout(numtxns => 1,
  6.                                                                  xids => xid_array('0200130094030000'),
  7.                                                                  options => dbms_flashback.nocascade_force);
  8. end;
  9.                                                                
  10.    select  * from dba_flashback_txn_report;
  11.    
  12.    commit;
  13.    
  14.    select  * from dba_flashback_txn_report;
  15.    
复制代码

报告是:
<?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.         ID
  3. ----------
  4.          1
  5.          2
  6.        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. Table created.


  6. old   1: insert into &&s1 values (1)
  7. new   1: insert into tnonconflict_only values (1)

  8. 1 row created.

  9. old   1: insert into &&s1 values (2)
  10. new   1: insert into tnonconflict_only values (2)

  11. 1 row created.

  12. old   1: insert into &&s1 values (3)
  13. new   1: insert into tnonconflict_only values (3)

  14. 1 row created.


  15. Commit complete.

  16. old   1: update &&s1 set id=11 where id=1
  17. new   1: update tnonconflict_only set id=11 where id=1

  18. 1 row updated.

  19. old   1: update &&s1 set id=22 where id=2
  20. new   1: update tnonconflict_only set id=22 where id=2

  21. 1 row updated.

  22. old   1: update &&s1 set id=33 where id=3
  23. new   1: update tnonconflict_only set id=33 where id=3

  24. 1 row updated.


  25. Commit complete.

  26. old   1: update &&s1 set id=222 where id=22
  27. new   1: update tnonconflict_only set id=222 where id=22

  28. 1 row updated.

  29. old   1: update &&s1 set id=333 where id=33
  30. new   1: update tnonconflict_only set id=333 where id=33

  31. 1 row updated.


  32. Commit complete.

  33. old   1: update &&s1 set id=22 where id=222
  34. new   1: update tnonconflict_only set id=22 where id=222

  35. 1 row updated.


  36. Commit complete.

  37. SQL>
复制代码
  1. select  * from flashback_transaction_query f
  2. where f.table_name='TNONCONFLICT_ONLY'  order by start_scn;

  3. select  * from dba_flashback_txn_report;

  4. begin
  5.    dbms_flashback.transaction_backout(numtxns => 1,
  6.                                                                  xids => xid_array('07001300C8020000'),
  7.                                                                  options => dbms_flashback.nonconflict_only);
  8. end;
  9.                                                                
  10.    select  * from dba_flashback_txn_report;
  11.    
  12.    commit;
  13.    
  14.    select  * from dba_flashback_txn_report;
  15.    
复制代码



<?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.         ID
  3. ----------
  4.          1
  5.         22
  6.        333
复制代码


要处理主键:

实验过程:
  1. select  log_mode from v$database;

  2. alter database add supplemental log data;

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

  4. create table hr.t05310_b ( a  number primary key );

  5. insert into hr.t05310_b  values (1);

  6. commit;

  7. delete from hr.t05310_b ;

  8. commit;

  9. insert into hr.t05310_b  values (1);

  10. commit;

  11. select  * from flashback_transaction_query f
  12. where f.table_name='T05310_B'  order by start_scn;

  13. ---0700180044070000

  14. begin
  15.    dbms_flashback.transaction_backout(numtxns => 1,
  16.                                                                  xids => xid_array('0700180044070000'),
  17.                                                                  options => dbms_flashback.cascade);
  18. 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. alter database add supplemental log data;

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



  4. create table hr.t05310_c ( a  number primary key );

  5. insert into hr.t05310_c  values (1);

  6. commit;

  7. create table hr.t05310_d ( a  number references  hr.t05310_c );

  8. insert into hr.t05310_d  values (1);

  9. commit;

  10. select  * from flashback_transaction_query f
  11. where f.table_name in ('T05310_C','T05310_D')  order by start_scn;

  12. ---04001A000B030000

  13. begin
  14.    dbms_flashback.transaction_backout(numtxns => 1,
  15.                                                                  xids => xid_array('04001A000B030000'),
  16.                                                                  options => dbms_flashback.cascade);
  17. end;

  18. select  * from dba_flashback_txn_report;
  19.    
  20.    commit;
  21.    
  22.    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>

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复

使用道具 举报

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

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-5-26 00:48 , Processed in 0.138028 second(s), 25 queries .

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